Call the movers at 1800-Move-To-CloudDb
This is the third installment in a blog series. The previous entry is located here
Based on the material covered in the series so far, you should have a good idea of WASD and how to secure access to it. Next we shall look at one of the key steps i.e. how do you actually move your database running on premise SQL Server to WASD?
Let us start with understanding some basic concepts. At this time WASD has two SKUs available for customer database size – Web (5 GB) and Business (150 GB) Edition. At this time we also have a preview feature called Premium Db
For larger databases you need to shard the data across multiple WASD databases by using federations or some code-based custom sharding solution. For the purposes of this blog post, I shall assume that we are dealing with a single database.
When you create a user database on WASD you are using the local storage on the machine that hosts your primary replica (for clarification of this terms see post #1). This is where your transactions are written and database ACID properties maintained.
Before we dig too deep into the options, there are a few of things you should be aware of
- Firstly that WASD is a subset of features on premise SQL Server – which means that there are specific T-SQL syntax that are unsupported. This usually translates to errors when using the tools outlined below and you will have to work around them as I have shown in examples
- Secondly when you move data from on premise to WASD, you get charged for the bandwidth that is consumed to move data in and out of the data center
- Third as the size of your data increases so do the chances that you hit some of the Resource Limits for WASD that are documented here
Let us start with the typical scenario that most of you will encounter i.e. moving your data from on premise SQL Server to WASD. Here are the various options that you can explore
Using SQLPackage.exe to create a bacpac
A bacpac file is contains your data & schema is something that you can import into WASD. SQLpackage.exe is a command line utility that ships with SQL Server 2012 (usually found at "C:Program Files (x86)Microsoft SQL Server110DACbin") that you can use to generate the bacpac.
Here is an example of how to use the exe
Export Syntax :- to create bacpac file from on premise SQL Server
sqlpackage.exe /a:Export /ssn:".SQL12" /sdn:"mydb" /su:"sa" /sp:"P@ssw0rd1" /tf:"C:tempmydb_09242013.bacpac"
/ssn = SERVERNAME for the on premise SQL Server
/sdn = database that needs to be exported
/su,/sp = username/password for the on premise SQL Server
/tf= location of bacpac file
Import Syntax :- to create WASD user database from bacpac file
sqlpackage.exe /a:Import /tsn:"xyz.database.windows.net" /tdn:"mydb_wasd" /tu:"rohit@xyz" /tp:"P@ssw0rd1" /sf:"C:temp mydb_09242013.bacpac "
/tsn = WASD server where we want to import the data
/tdn = WASD database name
/tu,/tp = username/password for the WASD user db
/sf= location of bacpac file
A few of the common failures that we have seen customers experience are as follows
Error SQL71564: The element Full-text Catalog: [FT_ADW] is not supported when used as part of a data package (.bacpac file).
This indicates that the current database is using full-text indexes and has a full-text catalog- both of which are features not supported by WASD. In order to generate the bacpac you drop the full-text functionality. Here are queries that can help you find them in your on premise database
--Run this query to list all the full-text indexes and associated catalogs
t JOIN sys.fulltext_indexes
t.object_id = i.object_id
i.fulltext_catalog_id = c.fulltext_catalog_id
--Run this query to list all the full-text catalogs in your database
Error SQL71564: Element Primary Key on [dbo].[mytab] has an unsupported property FillFactor set and is not supported when used as part of a data package.
The workaround is to script the specific index out, drop it and recreate it without specifying anything in the WITH clause – and then try the export to bacpac.
This way the index gets created with the following defaults under sys.indexes
- Import/Export Wizard i.e. SSIS package
- Using bcp utility that ships with SQL Server
Here is a rough outline of the steps that you need to follow
Generate schema and bcp.out files from on premise SQL Server
You can do this very quickly using PowerShell. The following script that creates two files against a sample AdventureWorks2012 database on premise
AdventureWorks2012_Schema_FromOnPremise.sql – This generates a .sql file that you can run directly on WA SQL Database
AdventureWorks2012_BcpCommands_FromOnPremise.sql – This generates a bunch of bcp out commands that you will need to run via command prompt
# PowerShell script to bcp out data from on-premise SQL Server
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
####EDIT TO POINT TO YOUR ON-PREMISE SQL SERVER
new-object ('Microsoft.SqlServer.Management.Smo.Server') ".SQL12"
#Start of script generation process via the Scripter object
new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
#Set options for the Scripter object per http://msdn.microsoft.com/en-us/library/Microsoft.SqlServer.Management.Smo.ScriptOption\_properties.asp
#Generate bcp out commands for all tables
#bcp.exe $table out $table+".bcp -q -h "TABLOCK" - | out-File D:PSScriptsScripts.sql -Append
"bcp ""$table"" out ""C:CasesScript$table.bcp"" -T -S $s -n -q -E" | out-File
A more complex version of this script is available here
Create a blank database on WA SQL Database and apply the schema file generated in Step1
Bcp data into the database on WA SQL Database :- For this you will have to manually create the bcp in command
For steps 2 and 3 refer to the sample Adventureworks database that you can deploy to WA SQL Database as outlined here
A second scenario that we see customer's encountering is having the need to move their database from one server to another within WASD itself e.g. moving the database from UAT to Production. Typically this is one using a storage option called Windows Azure Storage account a.k.a. the blobstore which you can think of as the equivalent of file storage for on premise SQL Server.
Here are some common methods used to accomplish this move
Database copy:- As the name suggest this creates a (transactionally consistent)copy of your WASD user database. The copy can either be created on the same database server (under a different name) or a different database server. We have seen this feature being used a lot by customers for Web databases as they transition from QA to UAT to Production.
While the database is in the process of copying the progress can be monitored using the following query:
-- Retrieve copying details.
However this query will return zero rows as soon as database is finished copying. A couple of gotchas to be aware of with this approach are as follows
- You cannot copy a database across data centers or even separate clusters within the same data center
- For the copy operation to succeed you need to create the same login on the destination server that was used to create the database on the source server i.e. provisioning a new account and giving it db_owner on the source server will not work and will return the following error
CREATE DATABASE <destination_db> as copy of <src_server>.<src_db>
Msg 262, Level 16, State 1, Line 1
CREATE DATABASE permission denied in database <src_db>.
More information about this feature are given here
You can create the bacpac file and automated export to blob storage as outlined here
A third alternative is to use PowerShell cmdlets for WASD. Here is an example of writing bacpac file to blobstore and restoring on another server.
#Setup your credentials
System.Management.Automation.PSCredential("username@xyz", ("Password" | ConvertTo-SecureString
#Setup access to your storage
$StorageName="azstore" #Replace with name of your store
$StorageKey="=CX...... asd=" #Replace with Storage key
$ContainerName="testdbcontainer" #Replace with container name
$srcDatabaseName="testdb" #Replace with database name
$BlobName="testdb_1030" #Replace with bacpac name