Call the movers at 1800-Move-To-CloudDb - Microsoft SQL Server Support Blog - Site Home

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 Server\110\DAC\bin\") 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:\temp\mydb_09242013.bacpac"
  where
  /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 "
  where
  /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
SELECT t.name AS TableName, c.name AS FTCatalogName
FROM sys.tables t JOIN sys.fulltext_indexes i
ON t.object_id = i.object_id
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id

--Run this query to list all the full-text catalogs in your database
SELECT fulltext_catalog_id, name FROM sys.fulltext_catalogs

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
select name, fill_factor,is_padded,is_disabled,is_hypothetical,allow_row_locks,allow_page_locks from sys.indexes

where name='PK_mytab_id'

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
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') ".\SQL12"
$dbs = $s.Databases

#Start of script generation process via the Scripter object
$scrp = 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.aspx
$scrp.Options.ScriptDrops = $False
$scrp.Options.IncludeHeaders = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.Indexes = $True
$scrp.Options.WithDependencies = $True
$scrp.Options.Triggers=$True

$scrp.Options.ToFileOnly = $True
$scrp.Options.AppendToFile = $False
$scrp.Options.FileName = "C:\Scripts\AdventureWorks2012_Schema_FromOnPremise.sql"
$scrp.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$dbs["AdventureWorks2012"].Tables)

#Generate bcp out commands for all tables
foreach ($table in $dbs["AdventureWorks2012"].Tables)
{
#bcp.exe $table out $table+".bcp -q -h "TABLOCK" - | out-File D:\PSScripts\Scripts.sql -Append
"bcp ""$table"" out ""C:\Cases\Script\$table.bcp"" -T -S $s -n -q -E" | out-File "C:\Scripts\AdventureWorks2012_BcpCommands_FromOnPremise.sql"
}

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.
SELECT * FROM sys.dm_database_copies copies
Inner JOIN sys.databases databases ON copies.database_id = databases.database_id
WHERE databases.name = 'xyz';
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
$srcServerCredential = new-object System.Management.Automation.PSCredential("username@xyz", ("Password" | ConvertTo-SecureString -asPlainText -Force))
$srcServerName="xyz"
$srcSqlCtx = New-AzureSqlDatabaseServerContext -ServerName $ServerName -Credential $servercredential

#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

$StorageCtx = New-AzureStorageContext -StorageAccountName $StorageName -StorageAccountKey $StorageKey
$Container = Get-AzureStorageContainer -Name $ContainerName -Context $StorageCtx

#Create a new Firewall Rule to Allow Windows Azure Services access to source server
New-AzureSqlDatabaseServerFirewallRule –ServerName $srcServerName -RuleName "AllowWAServices" -StartIpAddress 0.0.0.0 –EndIpAddress 0.0.0.0
#Submit the export request
$ExportRequest = Start-AzureSqlDatabaseExport -SqlConnectionContext $srcSqlCtx -StorageContainer $Container -DatabaseName $srcDatabaseName -BlobName $BlobName

## Check on status of an import
Get-AzureSqlDatabaseImportExportStatus -Request $ExportRequest

## Import a database on another server
$destServercredential = new-object System.Management.Automation.PSCredential("username@abc", ("Password" | ConvertTo-SecureString -asPlainText -Force))
$destServerName="abc"
$destDatabaseName="testb_restore_1030"
$destSqlCtx = New-AzureSqlDatabaseServerContext -ServerName $destServerName -Credential $destServerCredential

#Create a new Firewall Rule to Allow Windows Azure Services access to destination server
New-AzureSqlDatabaseServerFirewallRule –ServerName $destServerName -RuleName "AllowWAServices" -StartIpAddress 0.0.0.0 –EndIpAddress 0.0.0.0
#Submit the import request
$ImportRequest = Start-AzureSqlDatabaseImport –SqlConnectionContext $destSqlCtx –StorageContainer $Container -DatabaseName $destDatabaseName –BlobName $blobName

## Check on status of an import
Get-AzureSqlDatabaseImportExportStatus -Request $ImportRequest
#Drop the Firewall Rule to Allow Windows Azure Services access to servers
Remove-AzureSqlDatabaseServerFirewallRule -ServerName $srcServerName -RuleName AllowWAServices
Remove-AzureSqlDatabaseServerFirewallRule -ServerName $destServerName -RuleName AllowWAServices

In conclusion there are multiple different options available to you for moving data between on premise SQL Server and WA SQL Database. Your mileage may vary based on factors like database size, network bandwidth, supported vs. unsupported features & T-SQL syntax.