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"

    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

1. 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 https://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:ScriptsAdventureWorks2012_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:PSScriptsScripts.sql -Append

      "bcp ""$table"" out ""C:CasesScript$table.bcp"" -T -S $s -n -q -E" | out-File
  "C:ScriptsAdventureWorks2012_BcpCommands_FromOnPremise.sql"

  }

A more complex version of this script is available here

1. Create a blank database on WA SQL Database and apply the schema file generated in Step1

2. 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