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