Brute Force Migration of Existing SQL Server Databases to SQL Azure

Article
09/07/2010

This post is intended for those who are already familiar with SQL Server and want to get started working with SQL Azure. One of the best ways to get up and running quickly is to migrate an existing SQL Server database to SQL Azure. I chose the AdventureWorksDW2008R2 database because it’s optimized for building sample queries and reports.

It’s important to note before you begin that SQL Azure is a paid, subscription-based cloud service. Subscriptions are billed based upon database edition (Web or Business), size, and the amount of data transferred in and out. There are a variety of special offers available that significantly reduce the expense of getting started, such as the Windows Azure Platform MSDN Premium offer for MSDN subscribers.

So think through what you are doing in terms of the databases you create, editions, sizes and the amount of data you move in and out.

It’s also important to note that SQL Azure does not support all SQL Server data types and features. This can complicate the process of migrating your database. Make sure you understand all of the Guidelines and Limitations before you get started.

Initial Attempts

It’s worth noting that I tried a few approaches before I found something that worked, which I call the brute force approach. Here’s a quick overview of some of my initial attempts. Please note that at the time of this writing I was working with SQL Server 2008 R2 RTM tools and SQL Azure Service Update 4.

Copy Database Wizard

First I tried the Copy Database Wizard in SQL Server Management Studio, but it does does not yet support SQL Azure and failed with the error message:

Operation not supported on version 10.25. (Microsoft.SqlServer.SqlEnum)

Import and Export Wizard

My next attempt was to try the SQL Server Import and Export Wizard in SQL Server Management Studio. This wizard generates a SQL Server Integration Services package that is capable of copying schema and data, but not other database objects like constraints and stored procedures. Still, I wanted to see if it would work. The Migrating Databases to SQL Azure topic in the online documentation documentation indicates that it does work, but every time I tried to connect I got the following error message:

Cannot get the supported data types from the database connection "Provider=SQLNCLI10;Data Source=myserver.database.windows.net;User ID=mysqladminuser@myserver;Auto Translate=false;Initial Catalog=AdventureWorksDW2008R2".

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator. (Microsoft SQL Server Native Client 10.0)

At this point I was going to build my own custom Integration Services package, but I decided not to because I needed to migrate more than just schema and data. I wanted to migrate all of the objects in the database as well.

SQL Azure Migration Wizard

There is a handy utility on CodePlex called the SQL Azure Migration Wizard (thanks to George Huey). This tool does a great job of analyzing an existing database and generating scripts to create a compatible database in SQL Azure, then migrates the data. While the scripts it generates are really useful, I found the tool itself was not very stable. It crashed on me a couple of times.

The most recent release at the time of this writing is 3.3.7 and was published on 3-Sep-2010. There seems to be a lot of activity in this project so you can be sure that George will continue to make improvements.

Brute Force Approach

After my initial attempts were unsuccessful, I resorted to what always works best for me and that’s the brute force approach. Doing things this way gave me a much better understanding of the differences between SQL Server and SQL Azure, and now I have a nice library of routines I can use for future projects which I’ll share with you in this post.

Here’s an outline of the brute force approach. More detail is provided in the sections that follow.

Provision the source SQL Server database
Provision the destination SQL Azure database
Generate a DDL script
Execute the DDL script against SQL Azure
Create migration assistant stored procedures
Disable foreign key constraints
Disable non-clustered indexes
Use bcp to export table data from your source SQL Server database
Use bcp to import table data into your destination SQL Azure database
Rebuild non-clustered indexes
Enable foreign key constraints

1. Provision the source SQL Server database

In this step you will install SQL Server 2008 R2 and the AdventureWorksDW2008R2 database.

Download and install SQL Server 2008 R2
Download and install the Sample Databases for Microsoft SQL Server 2008 R2. Make sure to install the AdventureWorksDW2008R2 database. This will create all of the necessary database objects and load a bunch of sample data (about 75 MB worth).

2. Provision the destination SQL Azure database

In this step you will provision an empty SQL Azure database that you will use to migrate all of the AdventureWorksDW2008R2 database objects contained in your local SQL Server instance.

Make sure you have a Windows Live ID set up. You will need it to sign up for SQL Azure.
Review the Windows Azure Platform Options and purchase the right one for your needs. Since I’m an MSDN Premium subscriber, I took advantage of the Windows Azure Platform MSDN Premium offer.
Create and activate your SQL Azure subscription. You will be asked to create an Administrator Username and password and choose a location where your server will be hosted. Make a note of the password you select.

Tip: Avoid using special characters like “@” in your password. Stick with alphanumeric characters and hyphens. Some database utilities like bcp (bulk copy program) may have problems encoding special characters in a password.
Sign into the Windows Azure Developer Portal, click on the SQL Azure tab and select the Project Name associated with the SQL Azure subscription you just activated.
Make a note of the information in the Server Information section, including:
Server Name (e.g. myserver.database.windows.net)
Administrative Username (e.g. mysqladminuser)
In the Databases tab, click on Create Database, and enter the following information:
Name of your database: AdventureWorksDW2008R2
Specify an edition: Web
Specify the max size: 1 GB

Tip: Right now the main difference between Web and Business database editions is storage and pricing. Web edition databases can be provisioned for up to 5 GB of relational storage. Business edition databases can be provisioned for up to 50 GB of relational storage. Web edition is significantly less expensive for paid subscriptions. As SQL Azure evolves, “enterprise” features will most likely be monetized by requiring Business edition.
Click Create
In the Firewall Settings tab, check the “Allow Microsoft Services access to this server” option. This will allow other services running in the Azure Services environment to interoperate with your SQL Azure server.
In the Firewall Settings tab, click the Add Rule button, and enter the correct IP address range to grant access to the IP addresses you will use to connect to SQL Azure, for example:
Name: GrantAccessToAllAddresses
IP Range: 0.0.0.0 to 255.255.255.255

Tip: The example shown here grants access to all IP addresses. Grant access only to the IP addresses you actually need to avoid unauthorized usage.

3. Generate a DDL script

In this step you will generate a DDL script capable of recreating all of the objects in your source database. DDL stands for “Data Definition Language” which is shorthand for the various Transact-SQL commands used to create database objects such as tables, views and stored procedures.

Launch SQL Server Management Studio.
Connect to the SQL Server instance that you installed the AdventureWorksDW2008R2 database on.
Expand the Databases node in Object Explorer, right-click on AdventureWorksDW2008R2 and select Tasks > Generate Scripts.
In the Choose Objects dialog, choose the “Select Specific Database Objects” option, then check the Tables, Views, Stored Procedures and User-Defined Functions options.

Tip: AdventureWorksDW2008R2 contains a DDL Trigger. SQL Azure does not currently support DDL Triggers so do not select this option.
Expand the Tables node and uncheck the following tables:
1. AdventureWorksDWBuildVersion
2. DatabaseLog
3. FactFinance Tip: SQL Azure requires all tables to have a clustered index. These tables do not have clustered indexes, and they are not essential for this particular sample database.
In the Choose Objects dialog, click Next.
In the Set Scripting Options dialog, click the Advanced button and make the following changes, the click OK:
1. Set the General > “Script for the database engine type” option to “SQL Azure Database”
2. Set the Table/View Options > Script Indexes option to True
In the Set Scripting Options dialog, set the output type to “Save scripts to a specific location”, and choose the “Save to new query window” option, then click Next.
Review the information in the Summary dialog, then click Next. At this point the wizard will interrogate the schema of the source database and generate a script to rebuild all of the objects in the database into a new query window.
Review the information in the Save or Publish Scripts dialog then click Finish.
Return to SQL Server Management Studio and review the script that was generated.

4. Execute the DDL script against SQL Azure

In this step you will execute the DDL Script you generate against the AdventureWorksDW2008R2 database you provisioned in SQL Azure. Once completed, all of the tables, views, stored procedures and other objects from your source database will exist in your newly provisioned SQL Azure database. At this point there will be no data in the tables.

Right-click in the query window and select Connection > Change Connection. This will bring up the Connect to Database Engine dialog so you can connect to your newly created AdventureWorksDW2008R2 database in SQL Azure. Enter the connection information you noted earlier, for example:
Server name: myserver.database.windows.net
Authentication: SQL Server Authentication
Login: mysqladminuser@myserver
Password:
mysqladminuserpassword
Tip: You can select the Remember Password option to avoid having to retype your password each time you connect.
Click Connect.
Change the active database to AdventureWorksDW2008R2 using theh Available Databases drop-down list box in the tool bar.

Tip: Always check your current database context before running a script to avoid making changes to the wrong database.
Execute the query by pressing F5 or by selecting the Query > Execute menu option.
Review the status information at the bottom of the query window and verify that the query executed successfully.
Using Object Explorer, connect to your SQL Azure server using the appropriate credentials. Expand the Databases node, then expand the Tables node. Review the tables that were created and compare them to those in your local SQL Server instance.

5. Create migration assistant stored procedures

In this step you will create two stored procedures in the SQL Azure AdventureWorksDW2008R2 database that will simplify the process of migrating your data.

The SetForeignKeyEnabledStatus stored procedure will be used to disable foreign key constraints during data migration. This will permit you to load your tables in whatever order is convenient without regard to primary key / foreign key relationships. You will run this stored procedure again when data migration is complete to re-enable all of the foreign key constraints.

The SetIndexEnabledStatus stored procedure will be used to disable all non-clustered indexes during data migration. This speeds up the data loading process. You will run this stored procedure again when data migration is complete to rebuild all of the non-clustered indexes.

Use the following procedure to create the stored procedures. The source code for the CreateMigrationAssistantProcedures.sql Transact-SQL script file referenced in the instructions is included at the end of this section. To execute the script, you will use the sqlcmd utility, which is a command-prompt utility useful for performing batch operations against SQL Server and SQL Azure.

Copy the source code for the CreateMigrationAssitantProcedures.sql script file into notepad and save it in a convenient folder.
Open a command prompt and change the current directory to the folder in step 1.
Execute the following command using the appropriate connection details for your SQL Azure server:

sqlcmd -S myserver.database.windows.net -U mysqladminuser@myserver -P mysqladminuserpassword -d AdventureWorksDW2008R2 -i CreateMigrationAssistantProcedures.sql –b

Tip: The –b parameter ensures that sqlcmd will return a non-zero error code if an error occurs when running a script. This can be used to terminate a batch file if an error occurs.
Launch SQL Server Management Studio, connect to your SQL Azure server, and expand the Databases > AdventureWorksDW2008R2 > Programmability > Stored Procedures node. Verify that the SetForeignKeyEnabledStatus and SetIndexEnabledStatus stored procedures are in the list.

Here’s the source code for the CreateMigrationAssistantProcedures.sql script referenced above:

IF OBJECT_ID('[dbo].[SetForeignKeyEnabledStatus]','P') IS NOT NULL
DROP PROCEDURE [dbo].[SetForeignKeyEnabledStatus];
GO

CREATE PROCEDURE [dbo].[SetForeignKeyEnabledStatus]
(
@enabled bit
)
AS
BEGIN
DECLARE
@schema_name sysname,
@table_name sysname,
@fk_constraint_name sysname,
@cmd_txt varchar(8000);

    DECLARE fk_cursor CURSOR FOR
SELECT
sc.name AS schema_name,
so.name AS table_name,
so2.name AS fk_constraint_name
FROM
sys.objects so
inner join sys.schemas sc ON so.schema_id = sc.schema_id
inner join sys.foreign_key_columns fk ON so.object_id = fk.parent_object_id
inner join sys.objects so2 ON so2.object_id = fk.constraint_object_id
WHERE
so.type = 'U';

    OPEN fk_cursor;

    FETCH NEXT FROM fk_cursor INTO
@schema_name,
@table_name,
@fk_constraint_name;

    WHILE @@FETCH_STATUS = 0
BEGIN
IF @enabled = 0
SET @cmd_txt = 'ALTER TABLE [' + @schema_name + '].[' + @table_name + '] NOCHECK CONSTRAINT [' + @fk_constraint_name + '];'
ELSE
SET @cmd_txt = 'ALTER TABLE [' + @schema_name + '].[' + @table_name + '] WITH CHECK CHECK CONSTRAINT [' + @fk_constraint_name + '];';
PRINT @cmd_txt;
EXECUTE (@cmd_txt);
FETCH NEXT FROM fk_cursor INTO
@schema_name,
@table_name,
@fk_constraint_name;
END;

    CLOSE fk_cursor;
DEALLOCATE fk_cursor;
END;
GO

IF OBJECT_ID('[dbo].[SetIndexEnabledStatus]','P') IS NOT NULL
DROP PROCEDURE [dbo].[SetIndexEnabledStatus];
GO

CREATE PROCEDURE [dbo].[SetIndexEnabledStatus]
(
@enabled bit
)
AS
BEGIN
DECLARE
@schema_name sysname,
@table_name sysname,
@index_name sysname,
@cmd_txt varchar(8000);

    DECLARE idx_cursor CURSOR FOR
SELECT
sc.name AS schema_name,
so.name AS table_name,
si.name AS index_name
FROM
sys.objects so
inner join sys.schemas sc ON so.schema_id = sc.schema_id
inner join sys.indexes si ON so.object_id = si.object_id
WHERE
so.type = 'U'
AND si.index_id > 1;
OPEN idx_cursor;

    FETCH NEXT FROM idx_cursor INTO
@schema_name,
@table_name,
@index_name;

    WHILE @@FETCH_STATUS = 0
BEGIN
IF @enabled = 0
SET @cmd_txt = 'ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' + @table_name + '] DISABLE;'
ELSE
SET @cmd_txt = 'ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' + @table_name + '] REBUILD;'
PRINT @cmd_txt;
EXECUTE (@cmd_txt);
FETCH NEXT FROM idx_cursor INTO
@schema_name,
@table_name,
@index_name;
END;

    CLOSE idx_cursor;
DEALLOCATE idx_cursor;
END;
GO

These two stored procedures iterate through objects (foreign keys and indexes) in system catalog views. For each iteration I dynamically construct a Transact-SQL command to enable or disable the object, then execute the dynamically constructed command. I hope you find them useful.

6. Disable foreign key constraints

In this step you will use the SetForeignKeyEnabledStatus stored procedure created in the previous step to disable all of the foreign key constraints in the AdventureWorksDW2008R2 SQL Azure database. This will make it easier to load data by removing the need to do it in a specific order to avoid violating foreign key constraints. We will re-enable the foreign key constraints later after you are finished loading your data.

Note that by default bcp will bypass constraint checking, however it is still useful to do this if you are using other mechanisms to load your data, such as script files or some other application.

Open a command prompt.
Execute the following command using the appropriate connection details for your SQL Azure server:

sqlcmd -S myserver.database.windows.net -U mysqladminuser@myserver -P mysqladminuserpassword -d AdventureWorksDW2008R2 -Q "EXECUTE [dbo].[SetForeignKeyEnabledStatus] 0" –b

Tip: Use the –Q parameter to send a single command instead of executing a script file.

7. Disable non-clustered indexes

In this step you will use the SetIndexEnabledStatus stored procedure created previously to disable all of the non-clustered indexes in the AdventureWorksDW2008R2 SQL Azure database. This will improve the performance of data loading by avoiding incremental index rebuilds during the loading process. We wil lre-build the non-clustered indexes later after you are finished loading your data. Note that we will not disable clustered indexes since the data be loaded in clustered index order, and SQL Azure requires clustered indexes on all tables.

Open a command prompt.
Execute the following command using the appropriate connection details for your SQL Azure server:

sqlcmd -S myserver.database.windows.net -U mysqladminuser@myserver -P mysqladminuserpassword -d AdventureWorksDW2008R2 -Q "EXECUTE [dbo].[SetIndexEnabledStatus] 0" –b

8. Use bcp to export table data from your source SQL Server database

In this step you will use the bcp (bulk copy program) command-line utility to export data from your source SQL Server database. This step will need to be done once for each table in the AdventureWorksDW2008R2 database (with the exception of the three tables we excluded in step 3). When you complete this step, you should have one “native” file for each of the tables.

Open a command prompt.
Execute the following command using the appropriate connection details for your SQL Azure server:

bcp dbo.MyTableName out MyTableName.dat -n -S MySqlServerName –T

Tip: Use the –n parameter to use the “native” file format for export. The native format improves performance on import by avoiding unnecessary conversions.

Tip: Use the –T parameter to connect to SQL Server using your Windows credentials.
Examine the output and verify that the file was created.

9. Use bcp to import table data into your destination SQL Azure database

In this step you will use the bcp command-line utility to import data from a “native” file into your destination SQL Azure data base. This step will need to be done once for each table in the AdventureWorkdsDW2008R2 database (with the exception of the three tables we excluded in step 3). When you complete this step, all of your data should be loaded.

Open a command prompt.
Set the current directory to the folder that contains the files created in step 8.
Execute the following command using the appropriate connection details for your SQL Azure server:

bcp dbo.MyTableName in MyTableName.dat -n -S myserver.database.windows.net -Umysqladminuser@myserver -Pmysqladminuserpassword -E

Tip: Bcp is very picky about parameter formats. Do not use a space after the –U and –P parameters.

Tip: Be careful when importing data into columns that use the IDENTITY property. Use the –E parameter to use values in the data file for IDENTITY columns instead of generating new identity values during import. If you don’t do this you risk breaking foreign key relationships.
Examine the output to ensure that all data was successfully copied into the destination table.

10. Rebuild non-clustered indexes

In this step you will use the SetIndexEnabledStatus stored procedure to rebuild the non-clustered indexes you disabled in step 7. It’s best to do this in one shot after loading all of your data.

Open a command prompt.
Execute the following command using the appropriate connection details for your SQL Azure server:

sqlcmd -S myserver.database.windows.net -U mysqladminuser@myserver -P mysqladminuserpassword -d AdventureWorksDW2008R2 -Q "EXECUTE [dbo].[SetIndexEnabledStatus] 1" –b

11. Enable foreign key constraints

In this step you will use the SetForeignKeyEnabledStatus stored procedure to enable the foreign key constraints you disabled in step 6.

Open a command prompt.
Execute the following command using the appropriate connection details for your SQL Azure server:

sqlcmd -S myserver.database.windows.net -U mysqladminuser@myserver -P mysqladminuserpassword -d AdventureWorksDW2008R2 -Q "EXECUTE [dbo].[SetForeignKeyEnabledStatus] 1" –b

Putting it all together

Now that you know how to do each of the steps, here’s a handy batch file called bcp.adventureworksdw2008r2.cmd you can use to automate all of the operations performed in steps 5-11. The only other file you will need to run this batch is the CreateMigrationAssitantProcedures.sql script file you created in step 5.

@ECHO OFF

SET SourceSqlServerName=localhost
SET DestSqlServerName=myserver.database.windows.net
SET DestSqlUserName=mysqladminuser@myserver
SET DestSqlUserPassword=mysqladminuserpassword
SET SqlDbName=AdventureWorksDW2008R2

ECHO ******************************
ECHO Creating Migration Assistant Procedures
ECHO ******************************

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -i CreateMigrationAssistantProcedures.sql -b
IF ERRORLEVEL 1 GOTO ABORT

ECHO ******************************
ECHO Disabling Foreign Key Constraints
ECHO ******************************

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "EXECUTE [dbo].[SetForeignKeyEnabledStatus] 0" -b
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.AdventureWorksDWBuildVersion

ECHO ******************************
ECHO Disabling Nonclustered Indexes
ECHO ******************************

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "EXECUTE [dbo].[SetIndexEnabledStatus] 0" -b
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimAccount

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimCurrency

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimCustomer

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimDate

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimDepartmentGroup

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimEmployee

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimGeography

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimOrganization

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimProduct

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimProductCategory

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimProductSubCategory

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimPromotion

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimReseller

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimSalesReason

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimSalesTerritory

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.DimScenario

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.FactAdditionalInternationalProductDescription

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.FactCallCenter

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.FactCurrencyRate

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.FactInternetSales

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.FactInternetSalesReason

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.FactResellerSales

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.FactSalesQuota

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.FactSurveyResponse

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

SET TableName=dbo.ProspectiveBuyer

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "DELETE %TableName%;" -b

bcp %SqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E

if exist %TableName%.dat DEL %TableName%.dat
IF ERRORLEVEL 1 GOTO ABORT

ECHO ******************************
ECHO Enabling Nonclustered Indexes
ECHO ******************************

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "EXECUTE [dbo].[SetIndexEnabledStatus] 1" -b

SET TableName=dbo.AdventureWorksDWBuildVersion
IF ERRORLEVEL 1 GOTO ABORT

ECHO ******************************
ECHO Enabling Foreign Key Constraints
ECHO ******************************

sqlcmd -S %DestSqlServerName% -U %DestSqlUserName% -P %DestSqlUserPassword% -d %SqlDbName% -Q "EXECUTE [dbo].[SetForeignKeyEnabledStatus] 1" -b
IF ERRORLEVEL 1 GOTO ABORT
GOTO ENDBATCH

:ABORT

ECHO !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
ECHO Batch aborted due to error condition
ECHO !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

GOTO ENDBATCH

:ENDBATCH
ECHO !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
ECHO Batch execution completed
ECHO !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Technorati Tags: SQL Server 2008 R2,SQL Azure,Migration

Comments

Anonymous
September 26, 2010
Thank you for this well written and thorough step-by-step walkthrough. This is definitely some information I will use when I migrate my next database to SQL Azure. It makes sense to save money by building a local Database and them moving it to SQL Azure for final testing and implementation. I too have found that the latest tools to migrate databases to SQL Azure are not as easy and capable as the SSMS 2008 R2 "Generate and Publish Scripts" tool as you described in step "3. Generate a DDL script". If your database does not contain an very large amount of data you can also generate a script for foth the Schema and the data by setting the General > "Types of data to script" option to "Schema and Data". Or you can generate the DDL in one script and the data in another by selecting General > "Types of data to script" > "Data only".

Share via