Microsoft Azure SQL Database Step by Step
Chapter 4. Migrating databases
—Eric Boyd
In our experience helping customers develop and migrate applications to Microsoft Azure, there has always been a need to migrate data along with those applications, even for “all new development” projects. So you really need to know about the solutions that are available for migrating your data to SQL Database and to understand their strengths and weaknesses. In this chapter, you will work with multiple tools and techniques for migrating data to SQL Database, including Transact-SQL (T-SQL) scripts, SQL Data-Tier Applications (BACPAC), bulk copy (bcp), and the SQL Database Migration Wizard.
Note
As mentioned in Chapter 1, “Getting started with Microsoft Azure SQL Database,” and practiced throughout this book, the term SQL Database refers specifically to Microsoft Azure SQL Database in the cloud, whereas the term SQL Server refers specifically to local (on-premises) SQL Server.
In addition to the tools and techniques discussed in this chapter, there are many other solutions available from both Microsoft and third-party vendors that might also fit your scenario and requirements. For example, SQL Server Integration Services (SSIS) is a great solution if you need to import data from data sources beyond SQL Server, like Excel spreadsheets, or other database platforms like Oracle. If you are starting with an existing database in SQL Database and you want to apply incremental changes and updates to your database, third-party tools like Red-Gate SQL Compare and Data Compare are also good solutions. You should explore these and all other available solutions to help you migrate data from your on-premises data stores and database servers to SQL Database. You need to understand the capabilities and limitations of each option so that you can effectively choose a solution that best fits your scenario.
Making the case for data migration
The percentage of an IT budget that gets spent on maintenance versus new software projects is a popular metric that technology and business executives like to monitor and measure. It is always the goal to minimize maintenance and be able to invest more in innovation and new projects. Software development and technical teams typically prefer to work on new projects (often referred to as greenfield projects) rather than maintaining and extending existing codebases (brownfield projects). However, if you’ve been in the IT industry for any length of time, you know how much time is invested in maintaining and managing existing codebases and data stores.
The existing code and data we manage (often referred to as legacy, even if it was born in the last year) drives us to consider migration strategies when evaluating the public cloud and Microsoft Azure. In this chapter, we demonstrate various ways to move data into SQL Database from existing legacy systems and on-premises SQL Server servers, and we discuss other things to consider when migrating data to SQL Database.
Migrating data using Transact-SQL scripts
SQL Database uses virtually the same Transact-SQL (T-SQL) syntax as “regular” SQL Server, which is a significant advantage if you are already familiar with SQL Server. Thus, one simple option for populating data in SQL Database is to run T-SQL scripts.
In the next section, you will use SQL Server Management Studio (SSMS) to write T-SQL scripts that create and populate a local SQL Server database. Note that you can also use SQL Server Data Tools (SSDT) inside Microsoft Visual Studio to build and run T-SQL scripts. (You will learn much more about SSDT in Chapter 10, “Building cloud solutions.”) Also, note that all the scripts in this chapter can be downloaded from the book’s companion website. (See the Introduction for details.)
Setting up a local SQL Server database
To get started, you will set up the Wine database from previous chapters in your local environment. Doing this requires a local SQL Server database.
If you have access to a SQL Server instance that you can create a local database on, you can use that SQL Server instance. Otherwise, you will need to install the SQL Server Express edition to host the database on your local machine. A step-by-step procedure for doing so can be found in the Introduction, in the section “Installing the SQL Server Express edition.”
Note
This chapter assumes you are using the SQL Server Express edition for your local SQL Server database, which has a server instance name of .\sqlexpress. If you are using another edition, you must replace the instance name .\sqlexpress specified in the instructions with the name of server instance you are using. For example, if you are running a primary instance of the SQL Server Developer edition on your local machine, you can simply specify the dot (.) symbol, or localhost. If you are running a named instance on your local machine, append a backslash followed by the name of the instance (for example, .\myinstance or localhost\myinstance).
The T-SQL code to create the local WineDb database is shown in Listing 4-1. This script creates the WineDb database, and then creates the Customer, Order, and Wine tables. It also establishes all the foreign-key relationships between the tables. This is a similar design as theWineCloudDb database found in other chapters throughout this book.
LISTING 4-1 Creating the local WineDb database
CREATE DATABASE WineDb
GO
USE WineDb
GO
CREATE TABLE Wine(
WineId int IDENTITY PRIMARY KEY,
Name nvarchar(50) NOT NULL,
Category nvarchar(15) NOT NULL,
Year int,
Price MONEY DEFAULT 0 NOT NULL,
AddedOn datetime2 DEFAULT SYSDATETIME() NOT NULL,
UpdatedOn datetime2)
CREATE TABLE Customer(
CustomerId int IDENTITY PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
FavoriteWineId int,
CONSTRAINT FK_Customer_Wine FOREIGN KEY (FavoriteWineId) REFERENCES Wine(WineId))
CREATE TABLE [Order](
OrderId int IDENTITY PRIMARY KEY,
OrderedOn datetime2 DEFAULT SYSDATETIME() NOT NULL,
CustomerId int NOT NULL,
WineId int NOT NULL,
Quantity int NOT NULL,
Price MONEY NOT NULL,
CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerId) REFERENCES Customer(CustomerId),
CONSTRAINT FK_Order_Wine FOREIGN KEY (WineId) REFERENCES Wine(WineId))
To create the WineDb database using this T-SQL, follow these steps:
1. Launch SSMS. An easy way to do this is to press the Windows key, type sql server management studio on the Start screen, and press Enter.
2. In the Connect To Server dialog, connect to your local SQL Server instance using the appropriate credentials, as shown in Figure 4-1.
FIGURE 4-1 Connecting to SQL Server from the Connect To Server dialog in SSMS
3. Once you are connected, your SQL Server instance will be listed in the Object Explorer pane. Right-click on your SQL Server instance, and choose New Query as shown in Figure 4-2. This opens a new query window.
FIGURE 4-2 The New Query context menu option in SSMS Object Explorer
4. Type the code shown in Listing 4-1 into the query window (or paste it in from the listing file downloaded from the book’s companion website).
5. Press F5 (or click the Execute button in the toolbar) to run the script.
6. Expand the Databases node beneath your SQL Server instance in Object Explorer (or, if it’s already expanded, right-click it and choose Refresh). The WineDb database now appears.
7. Expand the WineDb database node, and then expand the Tables node beneath it to view the Customer, Order, and Wine tables.
There is now a WineDb database running on your local SQL Server instance. This is the source database you will migrate to Microsoft Azure using various tools and techniques throughout the rest of this chapter.
Creating the T-SQL scripts
You are now ready to populate the local WineDb database tables with data. In this section, you will create simple T-SQL scripts to populate the Customer and Wine tables with records. One of the great benefits of SQL Database is that you can use the same T-SQL syntax you use when working with SQL Server. So the same scripts can be used to insert data into either SQL Server or SQL Database.
Listing 4-2 shows the T-SQL script you will run in the next procedure. This script populates the Wine table with 15 rows of data and the Customer table with 3 rows of data. Notice how the IDENTITY_INSERT setting is turned on before inserting rows into a table, and then turned off again after. Turning this setting on allows the script to provide explicit values for each new record’s primary key, which would normally be assigned automatically by SQL Server because the primary keys were designated with IDENTITY. (Refer to Listing 4-1 earlier.)
LISTING 4-2 Populating the local WineDb database
SET IDENTITY_INSERT Wine ON
INSERT Wine (WineId, Name, Category, Year) VALUES (1, 'Chateau Penin', 'Bordeaux', 2008)
INSERT Wine (WineId, Name, Category, Year) VALUES (2, 'McLaren Valley', 'Cabernet', 2005)
INSERT Wine (WineId, Name, Category, Year) VALUES (3, 'Mendoza', 'Merlot', 2010)
INSERT Wine (WineId, Name, Category, Year) VALUES (4, 'Valle Central', 'Merlot', 2009)
INSERT Wine (WineId, Name, Category, Year) VALUES (5, 'Mendoza', 'Malbec', 2010)
INSERT Wine (WineId, Name, Category, Year) VALUES (6, 'Tuscany', 'Brunello', 2007)
INSERT Wine (WineId, Name, Category, Year) VALUES (7, 'Willamette Valley', 'Pinot Noir',
2009)
INSERT Wine (WineId, Name, Category, Year) VALUES (8, 'Bordeaux', 'Cabernet', 2009)
INSERT Wine (WineId, Name, Category, Year) VALUES (9, 'Barossa Valley', 'Shiraz', 2010)
INSERT Wine (WineId, Name, Category, Year) VALUES (10, 'Napa Valley', 'Syrah', 2010)
INSERT Wine (WineId, Name, Category, Year) VALUES (11, 'Barossa Valley', 'Grenache', 2006)
INSERT Wine (WineId, Name, Category, Year) VALUES (12, 'La Mancha', 'Mourvedre', 2009)
INSERT Wine (WineId, Name, Category, Year) VALUES (13, 'Beaujolais', 'Gamay', 2011)
INSERT Wine (WineId, Name, Category, Year) VALUES (14, 'Sonoma', 'Zinfandel', 2011)
INSERT Wine (WineId, Name, Category, Year) VALUES (15, 'Tuscany', 'Sangiovese', 2010)
SET IDENTITY_INSERT Wine OFF
SET IDENTITY_INSERT Customer ON
INSERT Customer (CustomerId, FirstName, LastName, FavoriteWineId) VALUES
(1, 'Jeff', 'Hay', 4)
INSERT Customer (CustomerId, FirstName, LastName, FavoriteWineId) VALUES
(2, 'Mark', 'Hanson', 3)
INSERT Customer (CustomerId, FirstName, LastName, FavoriteWineId) VALUES
(3, 'Jeff', 'Phillips', 2)
SET IDENTITY_INSERT Customer OFF
To populate the database with this T-SQL script, follow these steps:
1. Open a new query window in SSMS (or delete all the code in the same query window you used in the previous procedure).
2. Type the code shown in Listing 4-2 into the query window (or paste it in from the listing file downloaded from the book’s companion website).
3. Press F5 (or click the Execute button in the toolbar) to run the script.
Generating T-SQL scripts
At this point, you have set up the WineDb database on your local SQL Server instance and populated it with some data. Of course, you could now execute the same T-SQL script to create the schema for a SQL Database instance, and execute the T-SQL script to add rows and populate data in that SQL Database. But creating T-SQL scripts like this for large amounts of data is tedious and time consuming.
You might be thinking there must be a better way, and of course, there is. SSMS can examine the database and generate a T-SQL script with INSERT statements for all the data in the tables. In this next procedure, you will use SSMS to automatically generate a T-SQL script from your local WineDb SQL Server database that you can then use to populate your WineCloudDb SQL Database, effectively migrating the data from SQL Server to SQL Database.
1. If you’ve closed SSMS since the previous procedure, start it up again and connect to your local SQL Server instance that contains the WineDb database.
2. In Object Explorer, expand the node for your SQL Server instance name.
3. Beneath your SQL Server instance name, expand the Databases node to display the list of databases.
4. If the WineDb database does not appear, right-click the Databases node and choose Refresh.
5. Right-click on the WineDb database, and choose Tasks | Generate Scripts. This launches the Generate And Publish Scripts wizard.
6. On the Introduction page, click Next to display the Choose Objects page.
7. On the Choose Objects page, you have the option of scripting the entire database or selecting specific objects you want to script. This is not limited to tables; it can also include other database objects, such as views, stored procedures, triggers, and so on. Leave the default option selected to script the entire database, and click Next to display the Set Scripting Options page.
8. On the Set Scripting Options page, click the Advanced button to display the Advanced Scripting Options dialog.
9. Scroll down to the Script For The Database Engine Type property. (It’s towards the bottom of the General category.) By default, this option is set to Stand-alone instance. You can also choose SQL Azure Database to generate scripts compatible with SQL Database. In our current scenario, we want to use SQL Database, so choose SQL Azure Database.
10. Scroll down to the Types Of Data To Script property. (It’s the last property in the General category.) By default, this option is set to script only the database schema. You can also choose to script only data or both schema and data. In our current scenario, we only want to script the data, so choose Data Only, as shown in Figure 4-3.
FIGURE 4-3 Advanced Scripting Options in the Generate Scripts wizard
11. Click OK to return to the Set Scripting Options page.
12. Choose the Save To New Query Window radio button.
13. Click Next to advance to the Summary page.
14. Click Next to advance to the Save Or Publish Scripts page.
15. Click Finish to generate the script, which is then displayed in a new query window, as shown in Figure 4-4.
FIGURE 4-4 Viewing the generated T-SQL in SSMS
At this point, you have created a WineDb database, with both schema and data, in your local SQL Server instance. You created T-SQL scripts by hand that you executed to create the schema and insert data into your local SQL Server database. You also learned how to generate these T-SQL scripts using SQL Server Management Studio. The focus of this section was to set up the source database that will be migrated to SQL Database throughout the rest of this chapter, but you can execute this same T-SQL script in SQL Server and SQL Database. To execute this script and populate a SQL Database instance, connect your SSMS query window to the SQL Database instance instead of the local SQL Server database, and execute the T-SQL script in that window. You can also execute these T-SQL scripts using the SQL Database management portal as mentioned in the “Creating a SQL Database instance” section found in Chapter 1.
SQL Data-Tier Applications
Data-Tier Applications (DACs) provide a simple but powerful way to develop, deploy, and manage database and instance objects. DAC enables developers to package SQL Server and SQL Database objects into a single DAC package (a .dacpac file) for convenient deployment across development, test, and production environments.
When migrating a database from on-premises SQL Server to SQL Database, you often want to migrate your data along with your database and instance objects, and that is when BACPAC (.bacpac files) becomes useful. BACPAC is similar to DACPAC, but in addition to the database objects (schema), it also includes the actual data from the database in the package.
To migrate data to SQL Database using BACPAC, you first need to create a .bacpac file from your on-premises SQL Server database and upload it to a blob container in Microsoft Azure Storage. Then you can import the uploaded .bacpac file into SQL Database. This section walks you through these steps.
Creating a Microsoft Azure Storage account
First, you need a place to store your .bacpac in Microsoft Azure, and that place is in a Microsoft Azure Storage blob container. In this section, you will create a blob container using the Storage service, which first requires you to create a Storage account.
Microsoft Azure Storage accounts authenticate access using one of two 512-bit storage access keys (a primary and a secondary). These keys are automatically generated for you when you create a storage account. You can regenerate these keys at any time in the Microsoft Azure management portal (and via the Microsoft Azure Service Management API). To help keep your storage account secure, it is recommended that you regenerate your access keys periodically. Changing authentication credentials to services that other services and applications depend on without causing downtime can be challenging. Microsoft Azure simplifies this by providing the two access keys, which allows you to rotate access keys without causing downtime.
To create a Microsoft Azure Storage account you can use to upload and store your .bacpac file, follow these steps:
1. Log in to the Microsoft Azure portal at https://manage.windowsazure.com. This brings you to the main portal page showing ALL ITEMS.
2. Click STORAGE in the vertical navigation pane on the left.
3. Click the NEW button at the bottom of the page.
4. Click the QUICK CREATE link.
5. In the data entry area to the right of the QUICK CREATE link, do the following:
a. For URL, type mywinestorage. This will be the name of your storage account. (It can be any name from 3 to 24 lowercase letters and numbers.) This must be a globally unique name, so you’ll need to choose something other than mywinestorage if the portal informs you that the specified storage account name is already in use (which is very probable).
b. For LOCATION/AFFINITY GROUP, select the Microsoft Azure data center where you want to create your storage account from the drop-down list. This should be the same data center that hosts your SQL Database server. (See Chapter 2, “Configuration and pricing,” to understand the pricing implications of choosing a data center location.)
c. For REPLICATION, leave the default Geo-Redundant setting, which enables geo-replication. This synchronizes a copy of your data with another Microsoft Azure data center, to enable recovery in the event of a data center disaster. (Again, we say more on this in Chapter 2.) The portal should appear similar to Figure 4-5.
FIGURE 4-5 Creating a new Microsoft Azure Storage account in the management portal
6. Click CREATE STORAGE ACCOUNT to start provisioning the new storage account. In a few moments, you will see it appear in the portal with an Online status, as shown in Figure 4-6.
FIGURE 4-6 Viewing the new storage account in the management portal
7. Click on the storage account name (mywinestorage, or whatever name you assigned in the previous procedure).
8. Click the MANAGE ACCESS KEYS button at the bottom of the page to display the Manage Access Keys dialog. This displays the Primary Access Key and Secondary Access Key, as shown in Figure 4-7. These keys are used for authentication to your storage account, and you will need the primary access key shortly to upload a .bacpac file.
FIGURE 4-7 Viewing the primary and secondary access keys generated for the new storage account
9. Click the copy button to the right of the PRIMARY ACCESS KEY text box (the icon that looks like two documents) to copy the primary access key to the clipboard. You will paste this key in a later step, so be sure not to copy anything else to the clipboard until then.
10. If you are prompted by the browser to permit clipboard access, click Allow Access.
11. Click the checkmark icon in the lower-right side of the dialog to return to the Storage Account home screen.
Now that you have created a storage account, the next step is to create a blob container for it. Then you will be able to upload a .bacpac file to the blob container within the storage account, and finally import the .bacpac file to SQL Database. To create the blob container, follow these steps:
1. Click the CONTAINERS link at the top of the page, as shown in Figure 4-8.
FIGURE 4-8 Creating a new blob container for the storage account
2. Click the ADD button at the bottom of the page to display the New Container dialog.
3. For NAME, type dbimport.
4. For ACCESS, leave the default Private setting, which ensures that only the account owner (you) can access the new container.
5. Click the checkmark icon in the lower-right side of the dialog to create the container. When the process is complete, you’ll see a notification at the bottom of the portal.
6. Click OK to dismiss the notification that the container was created.
Exporting a BACPAC to Microsoft Azure Storage
You are now ready to create a .bacpac file from the local WineDb database. In the following steps, you will export a .bacpac file and upload it to the blob container in the Microsoft Azure Storage account you just created:
1. If it’s not still opened from an earlier procedure, launch SSMS and connect to your local SQL Server instance that contains the WineDb database.
2. In the Object Explorer, expand the node for your SQL Server instance name.
3. Beneath your SQL Server instance name, expand the Database node to display the list of databases.
4. Right-click on the WineDb database, and choose Tasks | Export Data-Tier Application, as shown in Figure 4-9. This launches the Export Data-Tier Application wizard.
FIGURE 4-9 The Export Data-Tier Application menu item in SSMS
5. On the Introduction page, click Next to advance to the Export Settings page.
6. In the Settings tab on the Export Settings page, click the Save To Microsoft Azure radio button.
Tip
If you don’t want to include the entire database in the .bacpac file, you can choose just the database objects you want to export in the Advanced tab of the Export Settings page.
7. Click the Connect button to launch the Connect To Microsoft Azure Storage dialog.
8. For Storage Account, type mywinestorage (or whatever globally unique name you assigned to the storage account when you created it). Notice that the HTTPS check box at the bottom of the dialog gets selected automatically when you type the account name. This is expected, and you should leave it selected.
9. In the Account Key text box, right-click the mouse and choose Paste (or press CTRL+V) to insert the primary access key you copied to the clipboard when you created the storage account. (If the key is no longer available from the clipboard, return to the Manage Access Keys dialog in the portal to copy it again, as shown in Figure 4-7.) The Connect To Windows Azure Storage dialog should now appear as shown in Figure 4-10.
FIGURE 4-10 The Connect To Microsoft Azure Storage dialog
10. Click the Connect button. The wizard connects to the storage account and returns to the Export Settings page, where the Container drop-down list has now become enabled.
11. For Container, select dbimport from the drop-down list. (This is the container you created in the previous procedure.) The wizard should now appear as shown in Figure 4-11.
FIGURE 4-11 Exporting a .bacpac file to Microsoft Azure Storage
12. Click Next to advance to the Summary page.
13. Review the Summary page. If everything looks correct, click Finish to begin the export.
Once the export has finished successfully, the wizard displays the Results page with a list of all the tasks it completed. The .bacpac file has now been exported and uploaded to your Microsoft Azure Storage account. You can now click Close to close the Export Data-Tier Application wizard.
Importing a BACPAC to Microsoft Azure SQL Database
At this point, you have created a storage account with a blob container, into which you have uploaded a .bacpac file exported from an existing on-premises SQL Server database. The last thing to do is import this .bacpac file into a new SQL Database instance.
To import the .bacpac file, follow these steps:
1. Log in to the Microsoft Azure portal at https://manage.windowsazure.com. This brings you to the main portal page showing ALL ITEMS.
2. Click SQL DATABASES in the vertical navigation pane on the left
3. If you have a WineCloudDb database in your list of databases from previous chapters, delete it now:
a. Click on the any column to the right of the Name column to select the WineCloudDb database. (Don’t click on the database name itself.)
b. Click the DELETE button at the bottom of the page.
c. Click YES, DELETE when prompted to confirm.
d. If that was the only database on the SQL Database server, you will also be asked if you also want to delete the server. Because you are going to import your .bacpac file into a new database on this server, click NO.
e. Click OK to dismiss the notification message that the database was deleted.
4. Click the NEW button at the bottom of the page.
5. Click IMPORT, as shown in Figure 4-12. This displays the IMPORT DATABASE dialog box.
FIGURE 4-12 Creating a new database by using the Import option in the management portal
6. Click the folder icon to the left of the BACPAC URL text box. This opens the BROWSE CLOUD STORAGE dialog box.
7. An explorer tree that displays your Microsoft Azure Storage accounts and their containers appears on the left side of the dialog. Expand your storage account to display the dbimport container inside of it.
8. Click the dbimport container to display its contents on the right. You can see the WineDb.bacpac file you recently uploaded to the container, as shown in Figure 4-13.
FIGURE 4-13 The contents of the dbimport container displayed in the BROWSE CLOUD STORAGE dialog
9. Click the WineDb.bacpac file to select it.
10. Click the Open button. This returns you to the IMPORT DATABASE dialog with the URL of the .bacpac file populated in the BACPAC URL text box.
11. For NAME, change the database name from WineDb (the name of the local SQL Server database, which was discovered from the .bacpac file that the database was exported to) to WineCloudDb, which is the actual name you want to give the new SQL Database instance.
12. For SERVER, choose any available server from the drop-down list to host the database (or choose New SQL Database Server from the drop-down list to create a new server on the fly). Once you choose a server, the SERVER LOGIN NAME and SERVER LOGIN PASSWORD text boxes appear. The SERVER LOGIN NAME text box is automatically populated with your administrator login name.
Important
If you create the storage account and the SQL Database server in different regions, you will incur additional Microsoft Azure billing charges for network bandwidth between the two regions. In this event, the Microsoft Azure management portal will alert you with a warning message. See Chapter 2 for more information on pricing for Microsoft Azure.
13. For SERVER LOGIN PASSWORD, type the password for your login. The IMPORT DATABASE dialog should appear similar to Figure 4-14.
FIGURE 4-14 Importing a .bacpac file from Microsoft Azure Storage into a new SQL Database instance
Note
By default, a database imported from a .bacpac file will be a 1-GB, Web edition database. If you need a larger database, select the CONFIGURE ADVANCED DATABASE SETTINGS check box at the bottom of the Import Database dialog. This converts the simple IMPORT DATABASE dialog into a two-page wizard, where the second page provides you with options to set the SQL Database edition and size. For more information, see the section “Configuring the database edition and size” in Chapter 2.
14. Click the checkmark icon in the lower-right side of the dialog to begin the import. Once the import has completed, a notification that the import was successful appears at the bottom of the page, and the new WineCloudDb database appears in the list of SQL databases. (Sometime, it is necessary to refresh the page by pressing F5 to get the new database to appear.)
Using BACPAC Data-Tier Applications is one of the simplest ways to migrate both the database schema and data to a SQL Database instance. You can also use DACPAC Data-Tier Applications to migrate only the schema, if that meets your migration requirements. One of the things that makes SQL Data-Tier Applications easy to work with is that you can use familiar tooling, including SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT) in Visual Studio. As you just saw, you can perform a database migration entirely using only the familiar SQL Server tools and the Microsoft Azure management portal, which makes this technology accessible for almost all Microsoft developers.
You should be aware of one limitation of using BACPAC: you cannot import a .bacpac file into an SSDT database project. (Chapter 10 covers SSDT database projects.) If you need to make any schema modifications between exporting your database and importing your database into SQL Database, you cannot achieve this with a .bacpac file. Instead, you can import .dacpac files (which contain only schema information) into SSDT database projects, but you would not have the data in your package using a DACPAC. Because of feature limitations and syntax differences between SQL Server and SQL Database, you will often need to make schema changes before deploying your databases to SQL Database. As a result, you will either need to extract .dacpac files without data or make any necessary schema and syntax changes to your local SQL Server database prior to exporting your .bacpac files.
SQL Server Bulk Copy (bcp)
SQL Server Bulk Copy (bcp) is a command-line utility that is intended to be used for high-performance, bulk-data migrations to and from SQL Server and SQL Database. Migrating data using bcp is a two-step process; first, you export data from the source table into a bcp data file, and then you import data into your destination table from the exported data file.
In this section, you will learn how to use the bcp utility to export data files from a source SQL Server database and import them into a destination SQL Database instance.
Migrating Schema
To use SQL Server Bulk Copy (bcp) to migrate your database to Microsoft Azure, you must first have the destination database schema deployed to a SQL Database instance. This is because the bcp utility moves only data—it will not migrate any database objects (such as table definitions or other schema). To create your database objects, you can build and run T-SQL scripts, design and publish a database project using SSDT (as shown in Chapter 10), or you could even manually design your tables using the SQL Database management portal (as shown in Chapter 1).
For the purposes of this exercise, you will drop and re-create the WineCloudDb tables populated by the BACPAC migration you performed in the previous section. To do this, you will use the SQL Database management portal to run the T-SQL script shown in Listing 4-3. (Remember, though, you can also run T-SQL scripts against a SQL Database instance using any of the familiar locally installed tools, such as SSMS or SSDT.) You’ll notice that this script is almost exactly the same as the one in Listing 4-1 that you used to create a new local SQL Server database at the start of the chapter. The only difference is that this T-SQL script starts with three DROP TABLE statements that delete the existing tables (populated by the BACPAC migration you performed in the previous section), which are then re-created as empty. This has the net effect of migrating just the schema of a database without any data.
LISTING 4-3 T-SQL script to drop and re-create the local WineCloudDb tables
DROP TABLE [Order]
DROP TABLE [Customer]
DROP TABLE [Wine]
CREATE TABLE Wine(
WineId int IDENTITY PRIMARY KEY,
Name nvarchar(50) NOT NULL,
Category nvarchar(15) NOT NULL,
Year int,
Price MONEY DEFAULT 0 NOT NULL,
AddedOn datetime2 DEFAULT SYSDATETIME() NOT NULL,
UpdatedOn datetime2)
CREATE TABLE Customer(
CustomerId int IDENTITY PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
FavoriteWineId int,
CONSTRAINT FK_Customer_Wine FOREIGN KEY (FavoriteWineId) REFERENCES Wine(WineId))
CREATE TABLE [Order](
OrderId int IDENTITY PRIMARY KEY,
OrderedOn datetime2 DEFAULT SYSDATETIME() NOT NULL,
CustomerId int NOT NULL,
WineId int NOT NULL,
Quantity int NOT NULL,
Price MONEY NOT NULL,
CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerId) REFERENCES Customer(CustomerId),
CONSTRAINT FK_Order_Wine FOREIGN KEY (WineId) REFERENCES Wine(WineId))
To run this T-SQL script, follow these steps:
1. Log in to the Microsoft Azure portal at https://manage.windowsazure.com. This brings you to the main portal page showing ALL ITEMS.
2. Click SQL DATABASES in the vertical navigation pane on the left.
3. Click the WineCloudDb database. (This is the database you imported from a BACPAC file in the previous section.)
4. Click the DASHBOARD link at the top of the page.
5. Scroll the page down a bit, find the MANAGE URL link in the “Quick Glance” section at the right of the page, and click the link. This opens a new browser tab to the SQL Database portal’s login page.
Note
The SQL Database portal is Silverlight-based. If you don’t have Silverlight installed, you will first be prompted to download it before you can use the portal.
6. For USERNAME and PASSWORD, type the administrator login name and password for the server, respectively, and click Log On.
7. Click the New Query button in the toolbar at the top of the SQL Database management portal to open a blank query window.
8. Type the code shown in Listing 4-3 into the query window (or paste it in from the listing file downloaded from the book’s companion website).
9. Click the Run button in the toolbar to execute the script.
The WineCloudDb SQL Database instance now has empty Wine, Customer, and Order tables that are ready for migration with bcp. Before running your first bcp command, it’s a good idea to become acquainted with bcp syntax. Table 4-1 shows the common bcp parameters that need to be specified for a typical import or export operation.
TABLE 4-1 Common SQL Server Bulk Copy (bcp) parameters
Note that bcp is very particular about the first three parameters. The database object, operation, and data file parameters must always be specified in that order. The remaining switch parameters can appear in any order on the command line.
Exporting data
As you might have already inferred by its syntax, the bcp utility migrates data into and out of individual tables and not an entire database. The local WineDb database contains three tables: Wine, Customer, and Order. The Wine and Customer tables both have data, and the Order table is empty, so you will export data from the database by running the bcp utility twice: once for the Wine table and a second time for the Customer table.
To export data from your local WineDb database into bcp data files, follow these steps:
1. Launch a command-prompt window. An easy way to do this is to press the Windows key, type cmd on the Start screen, and press Enter.
2. Type bcp WineDb.dbo.Wine out Wine.dat –S .\sqlexpress –T –n –q, and press Enter.
3. Type bcp WineDb.dbo.Customer out Customer.dat –S .\sqlexpress –T –n –q, and press Enter.
Note
These instructions assume you are using the SQL Server Express edition, which has a server name of .\sqlexpress. If you are using another edition, you must replace the server name .\sqlexpress specified in the instructions with the name of server you are using. Furthermore, if your server doesn’t support Windows authentication, you cannot specify –T, and must instead use the –U and –P switches for SQL Server authentication.
When bcp runs, it displays status information, including the number of rows exported, the packet size, the duration of the export, and the average throughput, as shown in Figure 4-15.
FIGURE 4-15 Exporting SQL Server tables to data files with bcp
Importing data
Now you will use bcp once more to import the data files you just exported into the WineCloudDb SQL Database instance, only this time you will specify in to perform an import operation.
When importing with bcp, you need to pay attention to the size of the data file being imported. If your data set is large, you will likely need to split it up into multiple chunks. You can easily do this using the –b switch parameter to specify the number of rows to import as one batch. Each batch is imported and logged as a separate database transaction so that if an error occurs, only inserts from the current batch are rolled back. By default, bcp imports all rows in a data file as one batch, but if you are importing large numbers of rows, you will likely experience connection loss and throttling from SQL Database if you don’t specify a smaller batch size. You might need to experiment with your data set to determine the right batch size to avoid throttling and connection loss with bcp.
The bcp syntax provides special switches to support batched import operations and to let you specify hints that enable other options. These additional switch parameters are shown in Table 4-2.
TABLE 4-2 SQL Server Bulk Copy (bcp) import parameters
It is also a recommended practice to disable nonclustered indexes, triggers, and constraints on the destination database during the import process and then re-enable them again after. Doing so can significantly improve the performance and speed of the import. Given the simplicity and size of the WineDb database, there is nothing to disable, nor is there any need to break up the import operation into batches (although we will still demonstrate batching with the 15-row Wine table, just so you can learn how to do it with much larger tables).
To import the data files exported from the local WineDb SQL Server database into the WineCloudDb SQL Database instance, follow these steps:
1. If it’s not still opened already from the previous export operation, launch a new command prompt.
2. Type bcp WineCloudDb.dbo.Customer in Customer.dat –S tcp:<server>.database.windows.net –U <login-id>@<server> –P <password> –n, and press Enter. This command imports the entire Customer.dat data file into the Customer table. When the command is completed, bcp displays status information as shown in Figure 4-16.
FIGURE 4-16 Importing a data file to a SQL Database table with bcp
Note
Replace <server>, <login-id>, and <password> with the server name, administrator user name, and administrator password of the SQL Database server hosting your WineCloudDb database.
3. Type bcp WineCloudDb.dbo.Wine in Wine.dat –S tcp:<server>.database.windows.net –U <login-id>@<server> –P <password> –n –b 5 –F 1 –L 5 –h “TABLOCK”, and press Enter. This imports the first batch of rows (rows 1 through 5) from the Wine.dat file into the Winetable. The –h switch specifies the TABLOCK hint, which tells bcp to lock the Wine table while importing.
4. Type bcp WineCloudDb.dbo.Wine in Wine.dat –S tcp:<server>.database.windows.net –U <login-id>@<server> –P <password> –n –b 5 –F 6 –L 10 –h “TABLOCK”, and press Enter. This imports the second batch of rows (rows 6 through 10) from the Wine.dat file into theWine table, which gets locked during the process.
5. Type bcp WineCloudDb.dbo.Wine in Wine.dat –S tcp:<server>.database.windows.net –U <login-id>@<server> –P <password> –n –b 5 –F 11 –L 15 –h “TABLOCK”, and press Enter. This imports the third (and last) batch of rows (rows 11 through 15).
You have now imported both the Wine and Customer tables from the local WineDb SQL Server database into the WineCloudDb SQL Database instance using the bcp utility. In our example, we migrated a very small data set to SQL Database, so it wasn’t really necessary to break the Wine table up into 3 batches of 15 rows each (but now you’ve learned how). In fact, bcp was designed to efficiently migrate large amounts of data into and out of SQL Server. So if you have large tables of data to migrate into and out of SQL Database (and/or SQL Server), this exercise has shown you how to batch the overall import operation with bcp.
SQL Database Migration Wizard
SQL Database has a number of noteworthy differences and limitations when compared to SQL Server. (These are detailed in Chapter 3, “Differences between SQL Server and Microsoft Azure SQL Database.”) These differences need to be factored into your database migration projects. Your database schemas and T-SQL scripts must conform to the supported features and syntax of SQL Database, but inspecting your SQL Server schemas and scripts for unsupported features and syntax can be a lengthy, painful, and error-prone process. The great news is that the Microsoft Azure SQL Database Migration Wizard greatly simplifies and reduces the pain of this otherwise tedious process.
All the database migration tools and solutions we’ve explored to this point are built into the Microsoft SQL Server tools or Visual Studio. However, there are other nice and useful tools outside of the commercial Microsoft toolset, and the Microsoft Azure SQL Database Migration Wizard is one of them. This is a free, open source tool that interactively walks you through the process of migrating a database to SQL Database. The migration wizard was created by George Huey, a Principal Architect at Microsoft, back in the early days of Microsoft Azure SQL Database when it was still called SQL Azure. It has been battle-tested by thousands of users and is often updated with bug fixes and feature enhancements, often as a result of great community feedback.
Note
Even though this tool was created by a Microsoft employee, it is not an official Microsoft product and is not supported by Microsoft.
If you have an existing SQL Server database, and you’re not sure it satisfies the requirements and limitations of SQL Database, the Microsoft Azure SQL Database Migration Wizard is a great place to start. It will not only deploy schema and data from SQL Server to SQL Database, but it will also identify compatibility issues, and it can even resolve certain compatibility issues automatically.
The Microsoft Azure SQL Database Migration Wizard greatly simplifies migrating databases to SQL Database by doing these three things very well:
Analyzes a SQL Server database, SQL Profiler trace, or T-SQL script for SQL Database compatibility issues
Generates T-SQL scripts for creating database schema in SQL Database
Migrates data to SQL Database using the bcp utility
You already worked with T-SQL scripts and migrated data to SQL Database using BACPAC and bcp. But one thing you haven’t done to this point is analyze the database for incompatibilities, and that’s one of the major benefits of the Microsoft Azure SQL Database Migration Wizard.
Downloading the tool
To get started with the SQL Database Migration Wizard, you need to download it from Codeplex and install it. The SQL Database Migration Wizard has dependencies on SQL Server assemblies, which requires that SQL Server already be installed.
At the time of this writing, there are two different versions of the SQL Database Migration Wizard. Version 3.X supports SQL Server 2008 R2, and version 4.X supports SQL Server 2012. We assume you are running SQL Server 2012, so you should install version 4.X. It’s reasonable to expect that future versions of the tool will be released to work with future versions of SQL Server, so you just need to pay attention to which version of the tool you are downloading.
To download and install the SQL Database Migration Wizard, follow these steps:
1. Navigate your web browser to http://sqlazuremw.codeplex.com. This takes you to the tool’s dedicated Codeplex page. (Note that the URL has a reference to “SQL Azure” in it, because the tool was created back when SQL Database was named SQL Azure.)
2. Click the DOWNLOADS button at the top of the page. This takes you to a page that lists all the available SQL Database Migration Wizard downloads.
3. Scroll down to find and click the download link for SQLAzureMW v4.0.18 Release Binary for SQL Server 2012 (or, as mentioned, find and click the Release Binary link with the version number that corresponds with the version of SQL Server you are running).
4. When prompted to Open or Save, click Save to begin the download.
5. When prompted, click Open Folder to launch an Explorer window to the location on your computer where you saved the downloaded .zip file.
6. Right-click the .zip file, and choose Properties.
7. In the Properties dialog, click the Unblock button, as shown in Figure 4-17. If you don’t unblock the .zip file, you will still be able to extract it, but you won’t then be able to run the tool from the extracted location.
FIGURE 4-17 Unblocking the downloaded .zip file using the Properties dialog
8. Click OK to close the Properties dialog.
9. Right-click the .zip file, and choose Extract All.
10. Click Extract to extract the contents of the .zip file to a new folder in the same location and with the same name as the .zip file.
After the .zip file is extracted, the folder with the extracted files opens up automatically in a new Explorer window, and you are ready to begin using the tool.
Migrating a database
To use the SQL Database Migration Wizard to migrate the WineDb database, follow these steps:
1. In the Explorer window opened to the extracted files, double-click the file SQLAzureMW.exe to launch the tool. This displays the wizard’s Select Process page, as shown in Figure 4-18.
FIGURE 4-18 The Select Process page of the SQL Database Migration Wizard
2. In the options on the right, choose the Database radio button beneath Analyze / Migrate, and click Next. This displays the Connect To Server dialog.
Note
The TSQL File radio button is useful if you already previously scripted your database objects to a T-SQL file, in which case the tool can also analyze and migrate using that T-SQL file.
3. For Server Name, type .\sqlexpress (or the name of your local SQL Server instance that contains the WineDb database).
4. If your local server requires SQL Server authentication, choose the Use A Specific User ID And Password radio button and supply your login name and password.
5. Leave the other options set to their defaults, and click the Connect button at the bottom of the dialog.
6. The Select Source page now displays a list of the databases installed on the local SQL Server instance you just connected to. Select the WineDb database, and click Next.
7. The Choose Objects page appears. By default, this page is set to script all the database objects, but you can select specific database objects if you want. Leave the default option selected to script the entire database, and click Next.
8. The Script Wizard Summary page now appears. Take a moment to expand the information and review it for correctness, and then click Next.
9. When prompted to generate the SQL script, Click Yes. This creates a script to generate the database schema and runs bcp to export the individual tables from the local WineDb database.
10. When processing completes, the wizard displays the Results Summary page, as shown in Figure 4-19. You should encounter no errors with the WineDb database. However, if there are errors, this is where you will discover them, because the wizard will refuse to migrate the database until you resolve the errors.
FIGURE 4-19 The Results Summary page after successfully generating and running a script
Note
The Results Summary page uses color coding to make it easy for you to spot problems. Green and blue indicate success, but if there are compatibility issues, they will show up in either red or dark red. Red indicates an error that prevents migration, which you need to resolve, while dark red text indicates that an incompatibility was found, but the SQL Database Migration Wizard knows that it can resolve the issue automatically.
11. Click the SQL Script tab to view the T-SQL schema creation script that the SQL Database Migration Wizard has generated, as shown in Figure 4-20.
FIGURE 4-20 The SQL Script tab displays the generated schema creation script
12. If you would like to save the script to a file for later use or review, click Save and select a location to save the file.
13. Click Next to begin configuring the deployment to SQL Database. This launches the Connect To Server dialog.
14. In the Connect To Server dialog, do the following:
a. For Server Name, type <server>.database.windows.net (replace <server> with the name of your SQL Database server).
b. For User Name, type <login-id>@<server> (replace <login-id> with the server’s administrator user name, and replace <server> with the name of your SQL Database server).
c. For Password, type the type server’s administrator password. The Connect To Server dialog should appear similar to Figure 4-21.
FIGURE 4-21 Connecting to SQL Database to deploy using the SQL Database Migration Wizard
d. Click Connect to connect to your SQL Database server. This closes the Connect To Server dialog and returns to the wizard.
15. The Setup Target Server Connection page appears, and lists all the databases on the server. If you have been following along with the previous procedures, you will see the WineCloudDb database appear in the list. You want to begin with an empty database, so delete the current one as follows:
a. Click the WineCloudDb database.
b. Click the Delete Database button at the bottom of the dialog.
c. When prompted to confirm, click Yes to return to the wizard.
16. Click the Create Database button at the bottom of the dialog. This launches the Create Database dialog.
17. For Enter Database Name, type WineCloudDb. The Create Database dialog should appear similar to Figure 4-22.
FIGURE 4-22 Creating the target database for deployment with the SQL Database Migration Wizard
18. Click the Create Database button. This creates an empty SQL Database named WineCloudDb, closes the Create Database dialog, and returns to the wizard.
19. Select the newly created WineCloudDb database in the list, and click Next.
20. When prompted to execute the script, click Yes.
21. As the deployment progresses, you will see status updates written to the Target Server Response page. When the deployment completes successfully, the Target Server Response page should appear similar to Figure 4-23.
FIGURE 4-23 The Target Server Response page after a successful deployment
Note
The Target Server Response page also uses color coding to indicate success (green and blue) and failure (red).
22. Click Exit to close the wizard.
You have now deployed both the schema and data to the WineCloudDb SQL Database instance using an intuitive step-by-step tool, thanks to the Microsoft Azure SQL Database Migration Wizard. Beyond deploying both your database schema and data, it also analyzed your schema for compatibility issues when migrating from SQL Server to SQL Database.
To summarize, the tool performed the following actions:
1. Generated T-SQL scripts for all the database objects (schema) in the local SQL Server database
2. Exported data into data files using bcp
3. Analyzed the generated T-SQL script with a pattern matching rules engine that uncovers known incompatibilities and limitations
4. Deployed the database schema to SQL Database by executing the generated (and potentially autocorrected) T-SQL scripts
5. Imported data into SQL Database from the exported data files using bcp
All these steps (with the exception of the analysis step) could have been performed independently, as you did in the previous sections of this chapter. The SQL Database Migration Wizard just packages everything up in an easy-to-use tool that visually and interactively walks through the process, without you needing to use multiple tools and command prompts. But the rules engine analysis that the SQL Database Migration Wizard conducts on your local database schema is not something you can do with the other tools. This analysis is a unique and extremely compelling capability of the wizard.
The Microsoft Azure SQL Database Migration Wizard is open source, and you can look at the internals of this tool if you want. If you discover an incompatibility between SQL Server and SQL Database that the tool doesn’t catch, or you’re just curious about the predefined syntax rules, you can easily view the rules. They are defined in an XML file named NotSupportedByAzureFile.Config, which can be found in the same directory as the SQLAzureMW.exe. If you are comfortable with regular expressions, you can even add your own rules to the SQL Server Migration Wizard by modifying this XML file with a text editor.
Summary
It is rare to work on a project that is entirely greenfield and all new development. You’re much more likely to work on a project involving Microsoft Azure SQL Database that will require the migration of existing databases. There are many ways to migrate your SQL Server databases to SQL Database, and they each have their own pros and cons to fit different scenarios. In this chapter, we walked through a number of tools and techniques for migrating existing SQL Server databases to SQL Database, including T-SQL scripts, .bacpac files, bcp, and the SQL Database Migration Wizard.
For lightweight scenarios, you saw how T-SQL scripts can be generated from a SQL Server database and executed against a SQL Database instance. SQL Data-Tier Application .bacpac files make it easy to package an entire database, including both schema and data, and import that into a SQL Database instance, but it operates at the database level and doesn’t allow you to migrate individual database objects. Furthermore, for larger databases, the size of the .bacpac file can make it difficult to migrate to SQL Database. Bulk Copy with bcp is an efficient and high-performance way to migrate large amounts of data to SQL Database, but it doesn’t do anything to migrate your database objects (schema). Finally, the Microsoft Azure SQL Database Migration Wizard is a free, open source project on Codeplex that is not commercially supported by a software vendor that brings together the process of migrating the schema and data to SQL Database while generating T-SQL scripts and automating bcp.