In our articles published on this forum, we have been discussing challenges associated with deployments of data services in Windows Azure, focusing in particular on SQL Database. As we have pointed out, a transition from existing, on-premise SQL Server installations is likely to necessitate changes to their structure and content, primarily due to limitations inherent to their cloud-based Platform as a Service (PaaS) counterparts. We have already presented methods that can be used in order to identify and carry out such changes (refer to Migrating SQL Server Database to Windows Azure – Resolving Incompatibility Issues for more detail). Now it is time to turn our attention to an actual deployment and migration of locally stored data.
Our preferred methodology (as demonstrated in the article referenced above) of resolving the majority of SQL Database-specific incompatibilities relied on the functionality built into SQL Server 2012 Data Tools (available from Microsoft Download Center). We took advantage of it when stepping through a relatively straightforward remediation process applicable to the AdventureWorksLT2012_Data.mdf database (published on the CodePlex website), yielding a Visual Studio project containing Azure PaaS compatible representation of the database schema. As long as you followed our approach, you should be able to publish such a project to a target SQL Database (which you can pre-create by employing any of the techniques described by us in our Introduction to Windows Azure SQL Databases). This action is invoked via the Publish item from the Build menu in the SQL Server Data Tools console (assuming that the database project is currently open). In the resulting Publish Database dialog box, click on the Edit command button to display the Connection Properties window, where you need to specify the name of the SQL Database server (hosting the newly created target database) along with the SQL Authentication credentials. Once you confirm your choices and return to the Publish Database dialog box, you will be able to either apply the Azure compatible schema to the target database (by clicking on the Publish command button) or to capture the corresponding changes in the T-SQL format (by clicking on the Generate Script command button). The publishing process will result in the SQL Database-equivalent of our on-premise AdventureWorksLT2012_Data.mdf with Azure adjusted schema (although still without the corresponding data) taking its presence in the cloud.
One way to complete the remaining step of transferring database content involves the same publishing process we just employed to modify the target SQL Database (in order to make sure that it conforms to the Azure PaaS standards), but this time applying it to the local SQL Server instance. Since a direct data migration from the AdventureWorksLT2012_Data.mdf database to its cloud counterpart is bound to fail (due to schema differences between the two), we can modify schema of the former first (note that this might result in some data loss) and, once the two match, use one of many available methods to load data into the latter. Let’s review this procedure in more detail.
Start by switching the target platform back from Windows Azure SQL Database to SQL Server 2012 (from the Project Settings tab of the project’s Properties window in the SQL Server 2012 Data Tools). Next, select the Publish entry in the Build menu and, in the resulting Publish Database dialog box, point to the local SQL Server instance (providing appropriate authentication information). In order to accommodate required changes (note that they might result in data loss, whose extent you should be able to evaluate based on autogenerated publishing scripts), click on the Advanced dialog box and clear the checkbox labeled Block incremental deployment if data loss might occur as well as enable the checkbox labeled DROP objects in target but not in project. This process will yield a fully Azure PaaS compliant local SQL Server database.
At this point, we are finally ready to perform data transfer. Here are a couple of different methods of accomplishing this objective:
- In the Object Explorer window of SQL Server Management Studio console, select the Deploy Database to SQL Azure item from the Tasks submenu of the context sensitive menu of the source database. In the resulting wizard, on the Deployment Settings page, click on the Connect command button to designate the target SQL Azure server. Once the connection is established, specify a new database name (note that this particular approach constitutes a two-step process that consists of database creation followed by upload of its content, both handled by the wizard), its edition (Web or Business) and size, as well as the name of a temporary bacpac file (containing both database schema and its data) that is used during deployment.
- In the Object Explorer window of the SQL Server Management Studio console, select the Export Data item from the Tasks submenu of the context sensitive menu of the source database. In the resulting SQL Server Import and Export Wizard, our sample AdventureWorks2012 database should already automatically appear on the Choose a Data Source page. Click on the Next command button and point to the SQL Database (note that from here you have the option of creating a new database or using an existing one) by providing the SQL Azure server name and proper credentials. On the Specify Table Copy or Query page, you can choose between Copy data from one or more tables or views (which is the appropriate choice in our scenario) and Write a query to specify the data to transfer (if you want to limit the scope of data load). Assuming that you picked the first one, on the Select Source Tables and Views page you should see the matching listings of tables and views at the source and destination databases. Deselect all views from the listing. Next, in order to remediate validation errors that would surface due to attempts to insert values into identity columns in the target database, you will need to turn on the Enable identity insert checkbox on the Column Mappings dialog box (accessible via the Edit Mappings command button) for the following tables:
- [dbo].[BuildVersion] table (to take into account its [SystemInformationID] column),
- [dbo].[ErrorLog] table (to take into account its [ErrorID] column),
- [SalesLT].[Address] table (to take into account its [AddressID] column),
- [SalesLT].[Customer] table (to take into account its [CustomerID] column),
- [SalesLT].[Product] table (to take into account its [ProductID] column),
- [SalesLT].[ProductCategory] table (to take into account its [ProductCategoryID] column),
- [SalesLT].[ProductDescription] table (to take into account its [ProductDescriptionID] column),
- [SalesLT].[ProductModel] table (to take into account its [ProductModelID] column).
- [SalesLT].[SalesOrderDetail] table (to take into account its [SalesOrderDetailID] column).
- [SalesLT].[SalesOrderHeader] table (to take into account its [SalesOrderHeaderID] column).
Another issue you are likely to run into results from the presence of foreign key constraints on the target tables, since the wizard loads data into them without taking into consideration its dependencies on the presence of primary keys. To address this problem, you can disable constraints when inserting new rows and re-enable them afterwards. When dealing with an on-premise SQL Server, such tasks can be easily accomplished by taking advantage of the sp_MSForEachTable stored procedure. While its absence in SQL Database is another one of its shortcomings, its Azure specific implementation is available from github (you can simply execute the provided code in the target SQL Database). Once the stored procedure exists, you need to connect to the target SQL Database (e.g by using SQL Server Management Studio and run the following (assuming that our target database is also called AdventureWorks2012:
USE AdventureWorks2012 GO EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
and once the data transfer is complete, reinstante the constraint checking by running:
USE AdventureWorks2012 GO EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CONSTRAINT ALL'
Getting back to our wizard, the Save and Run Package allows you to invoke the resulting SQL Server Integration Services (SSIS) package immediately as well as save it (to either SQL Server or file system, with the option to protect its content by using user key or password-based encryption). The final page displays the progress of data transfer.
Obviously these are just some of possibile solutions to performing data transfers to SQL Database. In our next article, we will cover the remaining ones, focusing on their strengths and weaknesses.
