Identifying Incompatibility Issues When Migrating SQL Server Database to Windows Azure

In our most recent article published on this forum, we have presented general support options for data-oriented services in Windows Azure, focusing in particular on SQL Database (formerly referred to as SQL Azure Database). We also have described the process of setting up a new cloud-based SQL Server instance, connecting to it via SQL Server Management Studio, and creating a new database (by using the graphical interface components or equivalent T-SQL statements). Now we will turn our attention to migrating existing SQL Server databases to Windows Azure, starting with identifying obstacles associated with such migrations.

However, before we delve into this subject, let’s take a quick look at the remaining steps required in order to implement a fully functional cloud-resident database. We can illustrate this quite easily by leveraging the AdventureWorks Community Samples Databases for Azure SQL Database (in both SQL Server 2008 R2 and SQL Server 2012 versions) available from the CodePlex web site. Published in the form of a single, compressed .zip file, the download includes a batch file named CreateAdventureWorksForSQLAzure.cmd, which you need to execute from an elevated command prompt with three parameters, designating (respectively) the fully qualified name of your SQL Azure server instance, its administrator account, and the corresponding password. The command creates the database (by running CREATE DATABASE statement), loads its schema (which involves a fairly elaborate T-SQL script), and populates it with sample data (by using the bcp utility).

Obviously we can use the same three basic steps (creating a database hosted by an Azure-based SQL Server instance, constructing its schema, and performing a data load) to deploy any custom SQL database to the cloud as long as its characteristics do not conflict with limitations inherent to Windows Azure SQL Database, whose comprehensive listing you can find in the MSDN article General Guidelines and Limitations (Windows Azure SQL Database). While this might sound relatively straightforward, it is important to note that converting schema and data of existing databases in order to comply with these limitations can be potentially a non-trivial undertaking.

Fortunately, the ability to discover incompatibilities that should be addressed while relocating on-premise databases to Windows Azure is incorporated into the most common migration tools, including the following ones (note that availability of these tools is, in some cases, dependent on the version of the local instance of SQL Server or Visual Studio):

  • Generate and Publish Scripts Wizard – available from the Object Explorer window of SQL Server Management Studio (for the full description of its characteristics, refer to the SQL Server Books Online). To invoke it, select the Generate Script item from the Tasks context-sensitive menu of the source database. On the Choose Objects page of the resulting wizard, select the Script entire database and all database objects option. On the Set Scripting Options page, click on the Advanced command button and in the corresponding dialog box (labeled Advanced Scripting Options), ensure that its settings are configured according to the following list:
  • the value of Script for the database engine type is set to SQL Azure Database,
  • the value of Convert UDDTs to Base Types is set to True (SQL Azure does not support User Defined Data Types),
  • the value of Types of data to script is set to Schema only or Schema and Data (note that you also have the Data only option),
  • the value of Script Extended Properties is set to False (SQL Azure does not support Extended Properties). This setting should already have the appropriate value automatically assigned (once you choose SQL Azure Database as the target database engine type) and be grayed out.
  • the value of Script Logins is set to False. Just like the previous setting, this one should already have the appropriate value (once you choose the target database engine type) and be grayed out.
  • the value of Script USE DATABASE is set to False. The same principle applies here as well – the appropriate value should be automatically set and displayed as non-modifiable.

Once you review your selections on the Summary page and click on the Next command button, the wizard will start generating schema and object scripts, with the outcome of each step displayed on the Saving or publishing scripts page, in the form of Success or Error entries in the Results column.

  • Deploy Database to SQL Azure Wizard – available (only in SQL Server 2012) from the Object Explorer window of SQL Server Management Studio; allows you to perform migration to, from, and between individual Azure SQL Databases (for a more comprehensive review of its characteristics, refer to the SQL Server Books Online). To invoke it, select the Deploy Database to SQL Azure entry from the Tasks context-sensitive menu of the source database. On the Deployment Settings page of the resulting wizard, click on the Connect command button and type in the fully qualified name of a target Azure SQL Server instance along with the corresponding administrative credentials. Once the connection is established, specify the name of a target database, as well as select the desired edition of SQL Azure database and its maximum size. You also have an option of modifying the default location of a temporary Data-tier Application BACPAC Package (a file with the .bacpac extension), which will contain database schema and data (the latter encoded in the JavaScript Object Notation format). Once you review the summary of your choices and initiate the deployment, you will be presented with the Results page. This allows you to review the status of individual steps of the deployment process, including details of any of them that resulted in an error.
  • SQL Database Migration Wizard – available from the CodePlex web site as SQLAzureMW v3.9.13 and v4.0.16(requiring, respectively, a local installation of SQL Server 2008 R2 and SQL Server 2012) and invoked by running SQLAzureMW.exe file included in the download. The wizard offers the ability to analyze an existing database for its compliance with Windows Azure requirements, generate necessary migration scripts, and carry out the actual migration. In addition, each of these actions can be applied to migration to, from, and between individual Azure SQL Databases. (There are also other, equally interesting features, such as performing Azure SQL Database compatiblity analysis based on SQL Profiler traces or T-SQL scripts). After connecting to the source server (which, in our case, would be an on-premise SQL Server instance), you need to select one or more databases and its or their objects that you intend to migrate. Your selection serves as the basis for a T-SQL script generated by the wizard in the next step. Any issues encountered by the wizard will be listed on the Results Summary page, along with corresponding remediation methods, which are automatically incorporated into the script (displayed on the SQL Script tab on the same page).
  • SQL Azure database project – available from SQL Server Data Tools (included as part of Visual Studio 2012 installation or downloadable separately from the MSDN Web site). To start, select the Create New Project entry from the context-sensitive menu of the source database in the SQL Server Object Explorer window (obviously you will need to connect to the SQL Server instance hosting that database first). In the resulting Import Database dialog box, Source database connection will already be assigned (and not modifiable). Accept the default Import Settings and provide a name and location of the new project. Once the import process completes, the project’s hierarchical structure should appear in the Solution Explorer window. Such project represents characteristics of the current on-premise database. In order to evaluate its Windows Azure readiness, set its target platform to SQL Azure. To do so, select the Properties item from the context-sensitive menu of the project node in the Solution Explorer window. Switch to the Project Settings tab in the resulting window and select the Windows Azure SQL Database entry in the Target platform listbox. With the new platform assigned, invoking the Build Solution action will trigger database schema validation, listing all incompatibility issues in the Error List window.

This concludes our high-level overview of methods that can be used in order to identify (and, in some cases, also help with resolving) compatibility issues encountered when migrating SQL Server on-premise databases to Windows Azure. In our upcoming articles, we will explore these methods in-depth, providing examples of the most common resolution steps.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles