Bulk Database Migration from On-Premise SQL Server to SQL Azure - Step 1: Bulk .bacpac Export

  • Article

I recently had a task to perform a bulk migration of hundreds of databases to Windows Azure SQL Database (formerly known as SQL Azure) from an instance of SQL Server 2012.  If you need to migrate a very small number of databases, the easiest way is likely through the SSMS export Data-Tier Application wizard.  You can manually create a .bacpac file for each of the databases and move them to your blob storage.  However, for hundreds of databases, this is not a nice solution.  There are several ways to automate this task, but I decided to use TSQL scripting to build up the correct SQLPackage.exe command line call, then use xp_cmdshell to execute.  Similarly, the script below can be modified to generate the appropriate command line syntax and be executed outside of SQL Server if needed since most SQL Server environments have xp_cmdshell disabled. 

There are several different techniques for migrating an on-premise SQL Server database to SQL Database.  My current technical preference is to "export" the database using Data-Tier Application functionality to create a self-contained .bacpac file (NOTE: .bacpac, not .dacpac) that can then be uploaded to blob storage and imported directly into your SQL Database environment.  One benefit of using .bacpac export is that the process includes a validation step to verify that all objects in your on-premise database will be supported in SQL Database.  Other techniques (scripting, Import/Export wizard, extracting .dacpacs, etc) do not perform this verification during the extraction phase and will result in a failed database creation in the SQL Database environment if you have unsupported objects (ex:  a table without a clustered index).  These other techniques give different benefits, such as the flexibility to manually modify scripts, etc., but for ease of use, I'm currently sticking with .bacpac export.

Task: Bulk Migration from On-Premise SQL Server to SQL Database - Step 1: Performing Bulk .bacpac Export through TSQL Scripting & SQLPackage.exe

Code Snippet

  1. /*
  2. Purpose: This script performs bacpac export for all databases in a given server. If the database contains objects fully supported in
  3. SQL Database / SQL Azure, then the bacpac export will succeed. If not, the errors/reasons for failure will be displayed in the result set.
  4. After running the script, save the output to text to help determine what objects need to be modified in order for each DB to be ready to migrate.
  5.  
  6. Author: Sam Lester (MSFT)
  7. Create Date: 12/1/12
  8.  
  9. Sample Call for bacpac export
  10. sqlpackage.exe /Action:Export /SourceServerName:. /sdn:"DB_Foo" /tf:"c:\MyBacpacs\DB_Foo.bacpac"
  11. */
  12.  
  13. /* Create table to hold DB names to process */
  14. create table files(files varchar(max))
  15. create table publish(filename varchar(1000), cmd varchar(max))
  16. create table output (output varchar(1000) null)
  17. go
  18.  
  19. declare @filepath varchar(1000), @dircmd varchar(1000), @sqlpackagepath varchar(1000)
  20.   select    @filepath = 'c:\MyBacpacs\'/* path to export .bacpacs -- Create this directory before running */
  21.         ,@sqlpackagepath = 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe'  -- Update this to the correct location for your DacFramework.msi install
  22.  
  23. -- Store the list of databases that we want to export. If you want a particular subset, add a WHERE clause to filter this list.
  24. insert into files
  25. select name from sys.databases where database_id > 4  --Skip the system DBs
  26.  
  27. /* Build the command line string that we'll later execute in the WHILE loop. In this example, the instance name is hardcoded
  28. to the default instance (using . in /ssn:.). If you have a named instance, update this string to "/ssn:ServerName\InstanceName" */
  29. insert into publish
  30.   select files, ' /Action:Export /sdn:' + files +' /ssn:. /tf:' + @filepath + files + '.bacpac'
  31. from files
  32.  
  33. DECLARE @counter int, @limit int, @rc int
  34. SET @counter =(select count(*) from publish)
  35. SET @limit = 0
  36.  
  37. -- Loop through and execute each command line to perform the export
  38. WHILE (@limit < @counter)
  39. BEGIN
  40.  
  41.     declare @txt varchar(1000)
  42.     select @txt = 'cmd /c ""' + @sqlpackagepath + '"" ' +(select top 1 cmd from publish) + ' >> ' + @filepath + 'results_bacpac_export.txt'
  43.  
  44.     insert output select '----------------------------------------'
  45.     insert output select top 1 cmd from publish
  46.     insert output select '----------------------------------------'
  47.     insert output exec @rc = master.dbo.xp_cmdshell@txt
  48.  
  49.     if (ISNULL(@rc,0) = 0)
  50.     begin
  51.         insert output select '--- SUCCESSFUL EXPORT FOR DATABASE = ' +(select top 1 filename from publish)
  52.     end
  53.  
  54.     delete top (1) from publish
  55.     select @limit = @limit + 1
  56.  
  57. END
  58.  
  59. select * from output where output is not null
  60.  
  61. /* Cleanup
  62.  
  63. drop table files
  64. drop table publish
  65. drop table output
  66. */

 

The result of running the script is the following:

1.  For databases that can be successfully migrated to SQL Database, a .bacpac file is created in the file location supplied in the script.  These can then be moved to blob storage and imported. 
2.  For databases that cannot be successfully migrated to SQL Database, the output of the script provides the list of unsupported objects.  Save this output to text to help assess your migration strategy. 

Ex:

Hope this helps,
Sam Lester (MSFT)

 

Standard disclaimer:  As with any script/code, please run this in a test server to make sure it works for your specific version/edition/environment settings before tinkering in your production environment.