Share via

Call the movers at 1800-Move-To-CloudDb

  • Article

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

Comments

  • Anonymous
    November 08, 2013
    I think this is a good topic, especially for those not acquainted with nuance of WASD.  I think it may be worthwhile to demonstrate how one can leverage SSDT to validate the schema as Azure compliant (at least to some degree) before undertaking some of the more laborious, time-consuming bacpac approaches (or any approach that entails data movement).  Nonetheless still a useful article that I'm glad to see up here.