Migrating SQL Server Database to Windows Azure – Resolving Incompatibility Issues

In our recent articles published on this forum, we have been discussing the process of implementing data services in Windows Azure, focusing in particular on SQL Database (formerly referred to as SQL Azure Database). While creating new databases in such scenarios is straightforward, migration of existing ones tends to be a non-trivial undertaking. This is a direct result of limitations inherent to Platform as a Service (PaaS) based deployments, which introduce a range of incompatibilities that need to be addressed as part of the migration process. We have already described methods that can be used in order to identify such issues (refer to our previous post for more details) – now it is time to turn our attention to their remediation.

Let’s start with a brief overview of features whose supportability status will likely need to be taken into account during migration from on-premise SQL Server instances to the SQL Database platform. The most commonly encountered ones include the following (for a more comprehensive listing, refer to the MSDN article Guidelines and Limitations (Windows Azure SQL Database)):

  • Application Roles – facilitate a separate authentication and permission model independent of Windows and SQL Server authentication for on-premise data access. This approach is not available in SQL Database.
  • User-defined Common Language Runtime Aggregates, Assemblies, Types, or Extended Stored Procedures – none of these CRL related objects are supported in SQL Database (as reflected by the lack of an Assemblies node in the Server Explorer window, when connected to SQL Azure via SQL Server Management Studio).
  • Symmetric Keys, Asymmetric Keys, and Certificates – standard data encryption capabilities available on-premise – including Transparent Data Encryption – are not supported in the Azure PaaS, which means that you have to resort to your own key management mechanism and application level encryption (for an example of such solution, refer to MSDN Library).
  • Broker Priorities, Contracts, Queues, Message Types, Routes, Services, and Remote Service Bindings – used to define and control characteristics and behavior of Service Broker, which is not supported in SQL Azure PaaS.
  • Clustered Indexes on tables (more specifically, their absence) – SQL Database does not support heaps, effectively forcing you to ensure that all of the database tables have clustered indexes defined. (While you can create tables without a cluster index in a SQL Database, you will be prevented from adding data to them until at least one such index exists). Choosing the most appropriate column for this purpose depends primarily on data values and usage patterns, but you might want to also consider additional information provided on the SQL Azure Team Blog. If you decide to create an identity column as part of such remediation, note that NEWSEQUENTIALID() function is not supported in SQL Azure (but you can use NEWID() instead).
  • Defaults, Rules, and Numbered Stored Procedures have been deprecated in the current version of SQL Server, so their current lack of support in SQL Database is unlikely to change. When migrating to Azure, you can replace Default objects by using the DEFAULT keyword within CREATE TABLE statements.
  • Extended Properties on database objects – implemented typically via the stored procedure sys.sp_addextendedproperty, they facilitate customization of database objects by providing their description (typically for self-documenting purposes) or identifying their content (through formatting rules and input masks). These properties are frequently leveraged by end-user applications, so removing them (which is required since they are not supported in SQL Databases) is likely to affect their functionality.
  • Filegroups – used for physical partitioning of data storage in on-premise SQL Server databases; they are not available in the case of SQL Databases, because of the abstraction layer inherent to Azure PaaS. This lack of supportability extends also to other storage partitioning-related features, including Partition Functions and Partition Schemes.
  • Presence of FileTables – intended for storing unstructured data in SQL Server database tables that can be subsequently full-text indexed and semantically searched; there is no equivalent in SQL Databases.
  • Full-Text Stoplists, Full-Text Catalogs, and Full-Text Indexes – none of the Full-Text indexing and search related features are supported directly in SQL Databases .
  • Sequences – these user-defined objects that generate a sequence of numeric values are currently not supported in SQL Databases .
  • XML Schema Collections and XML Indexes – while XML data type is supported by SQL Database, this does not extend to typed XML and XML Indexing.

In order to demonstrate an efficient approach to addressing these incompatibilities, we will step through the process of migrating schema (we will deal with actual data in our upcoming articles) of AdventureWorksLT2012 database, available from the CodePlex website. Once you download the AdventureWorksLT2012_Database.zip file, extract AdventureWorksLT2012_Data.mdf and AdventureWorksLT2012_log.ldf into arbitrarily chosen folders intended for our sample database and its log. While connected to your on-premise SQL Server instance, attach the newly extracted database either by using the Attach entry from the context sensitive menu of the Database node in the Object Explorer window of SQL Server Management Studio or by running the following (assuming that the .mdf and .ldf files reside, respectively, in D:Data and E:Logs folders):

CREATE DATABASE AdventureWorks2012 
ON (FILENAME = 'D:DataAdventureWorksLT2012_Data.mdf'), (FILENAME = 'E:LogsAdventureWorksLT2012_Log.ldf') 
FOR ATTACH;

Next, launch SQL Server 2012 Data Tools (which you can obtain from the Microsoft Download Center). From the SQL top level menu, select the New Schema Comparison entry of the sub-menu of the Schema Compare item. This creates a new schema comparison construct that can be subsequently used to identify differences between two databases or database projects. From the same Schema Compare sub-menu, select Options to display the Schema Compare Options dialog box. Switch to the Object Types tab, expand the Application-scoped section, and clear checkboxes next to all of the object types not supported in SQL Database (refer to the list above). Save the exclusions you specified as a file with the extension .scmp.

From the File->New top-level menu, start a new project based on the SQL Server Database Project template. We will use it in order to perform schema comparison against the newly attached AdventureWorks2012 database and automatically remove any incompatible object types. Save it with a meaningful name reflecting its purpose. Use the File->Open menu to open the previously configured .scmp file. The schema comparison should appear (as an extra tab) in the central pane. From the SQL->Schema Compare submenu, choose the Select Source item (alternatively, you can carry out the same action directly from the Select Source listbox in the upper left corner of the central pane). In the resulting dialog box, ensure that the Database option is selected and create a new connection to the AdventureWorks2012 database (or choose an existing one if already present). Similarly, set the value of the Select Target item from the same sub-menu (or from the top right corner of the central pane). To accomplish this, in the Select Target Schema dialog box, switch to the Project option and pick the current one from the corresponding list box. At this point, the Compare command button in the toolbar of the central window pane should become available. Clicking on it will initiate the comparison, listing actions that need to be carried out in order to make both schemas match, but, at the same rate, automatically ignoring all excluded earlier object types stored in the .scmp file (which drastically reduces the total number of tasks necessary to make the schema in our project SQL Database compliant). To apply these changes to the target, use the Update command button in the central pane’s toolbar.

The update will result in a single error due to an unresolved reference to an XML Schema Collection, since this is one of the object types we intentionally excluded based on its incompatibility with SQL Database. More specifically, [SalesLT].[ProductModel].[CatalogDescription] will have an unresolved reference to XML Schema Collection [SalesLT].[ProductDescriptionSchemaCollection]. To address this issue, double-click on the entry in the Error List window to display the offending code. On the ProductMode.sql tab (in the central pane), change the type of the [CatalogDescription] column to XML (by removing the (CONTENT [SalesLT].[ProductDescriptionSchemaCollection] entry) and save your change.

Now you are ready to evaluate whether the project is suitable for deployment to SQL Database. To accomplish this, from its context sensitive menu in the Solution Explorer window, change its Target platform to Windows Azure SQL Database. Any warning and error messages will be automatically displayed in the Error List window. While the extent of issues in this case appears to be still significant (a total of 130 errors and 34 warnings), it is considerably smaller than those we would encounter without exclusions, and, more importantly, most of them can be resolved by applying relatively few steps (Find and Replace comes in really handy at this point):

  • Delete references to Windows built-in accounts ([NT AUTHORITYNETWORK SERVICE])
  • Delete instances of the NOT FOR REPLICATION option
  • Delete instances of the ROWGUIDCOL option

The next step involves invoking the Build action, however, this is bound to fail due to the fact that [dbo].[BuildVersion] table does not have a clustered index. To resolve this issue, you can simply designate one of its columns as the primary key by changing the table definition to:

CREATE TABLE [dbo].[BuildVersion] (
    [SystemInformationID] TINYINT       IDENTITY (1, 1) NOT NULL,
    [Database Version]    NVARCHAR (25) NOT NULL,
    [VersionDate]         DATETIME      NOT NULL,
    [ModifiedDate]        DATETIME      CONSTRAINT [DF_BuildVersion_ModifiedDate] DEFAULT (getdate()) NOT NULL,
    CONSTRAINT [PK_SystemInformationID] PRIMARY KEY CLUSTERED ([SystemInformationID])
);

Finally, with all Azure-specific incompatibilities eliminated, we are ready to deploy the schema of our sample database to the cloud and transfer its data. We will be describing implementation of these steps in our upcoming articles.

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