Wade Wegner

Significant updates to the SQL Azure Migration Wizard

George Huey has done it again!  He has just published some significant updates to the SQL Azure Migration Wizard.

Previously, I’ve described the SQL Azure Migration Wizard as a tool that helps you migrate your SQL Server database into SQL Azure.  This is still true, but now, thanks to updates made by George Huey, you can also migrate from SQL Azure-to-SQL Server and SQL Azure-to-SQL Azure.  These are significant updates to the tool!

Please watch the following video for an updated explanation of the tool:

As I said, the updates made by George enable all the following scenarios for database migration …

• SQL Server-to-SQL Azure
• SQL Azure-to-SQL Server
• SQL Azure-to-SQL Azure

These last two updates are significant!  Take a look at this thread on the SQL Azure Migration Wizard codeplex site – the user had a scenario where they wanted to migrate a 1 GB database in SQL Azure into a 10 GB database in SQL Azure.

Please take a look at the SQL Azure Migration Wizard up on Codeplex, where you can download the source code and/or binaries.

Chunking BCP output to upload lots of data into SQL Azure

Note: This is a guest post from George Huey, Architect Evangelist in the Developer and Platform Evangelism group.

When you upload your data into SQL Azure, SQL Azure replicates your data to three different locations in order to provide triple redundancy.  Therefore, it needs a little more time to get the data in the proper places.

One of the things that we found out during a series of Windows Azure Platform Migration Labs held in the Chicago MTC is that you cannot upload hundreds of thousands of records without giving SQL Azure time to catch up.  Consequently, you have to chunk your data and give SQL Azure time to process each chunk before uploading the next chunk of data.

The tool that we used for migrating our customer databases to SQL Azure was the SQL Azure Migration Wizard.  The migration wizard uses BCP to download data from an on premise SQL Server database and then uses BCP to upload the data to SQL Azure.  BCP allows you to specify the first row (-F), the last row (-L), and the batch size (-b).  These options will allow you to chunk the data beginning uploaded to SQL Azure.  For example:

BCP MyDb.dbo.Transactions out Transactions.dat -E -q -n –T 

The above command extracts data from table Transactions in the database MyDb.  At the end of the BCP output, you will find the number of records copied to file (for example: 2,524,520 rows copied).

In order to upload in chunks, you would do something like this:

BCP  MyDb.dbo.Transactions in  Transactions.dat -E -q -n –b 5000 –F1 –L250000 -S  tcp:azureserver.ctp.database.windows.net -U admin@azureserver -P password

BCP  MyDb.dbo.Transactions in  Transactions.dat -E -q -n –b 5000 –F250001 –L500000 -S  tcp:azureserver.ctp.database.windows.net -U admin@azureserver -P password

BCP  MyDb.dbo.Transactions in  Transactions.dat -E -q -n –b 5000 –F500001 –L750000 -S  tcp:azureserver.ctp.database.windows.net -U admin@azureserver -P password

BCP  MyDb.dbo.Transactions in  Transactions.dat -E -q -n –b 5000 –F750001 –L1000000 -S  tcp:azureserver.ctp.database.windows.net -U admin@azureserver -P password

BCP  MyDb.dbo.Transactions in  Transactions.dat -E -q -n –b 5000 –F1000001 –L1250000 -S  tcp:azureserver.ctp.database.windows.net -U admin@azureserver -P password

BCP  MyDb.dbo.Transactions in  Transactions.dat -E -q -n –b 5000 –F1250001 –L1500000 -S  tcp:azureserver.ctp.database.windows.net -U admin@azureserver -P password

BCP  MyDb.dbo.Transactions in  Transactions.dat -E -q -n –b 5000 –F1500001 –L1750000 -S  tcp:azureserver.ctp.database.windows.net -U admin@azureserver -P password

BCP  MyDb.dbo.Transactions in  Transactions.dat -E -q -n –b 5000 –F1750001 –L2000000 -S  tcp:azureserver.ctp.database.windows.net -U admin@azureserver -P password

BCP  MyDb.dbo.Transactions in  Transactions.dat -E -q -n –b 5000 –F2250001 –L2500000 -S  tcp:azureserver.ctp.database.windows.net -U admin@azureserver -P password

BCP  MyDb.dbo.Transactions in  Transactions.dat -E -q -n –b 5000 –F2500001 –L2524520 -S  tcp:azureserver.ctp.database.windows.net -U admin@azureserver -P password

Note, you will have to put some kind of delay between BCP commands to give SQL Azure time to store the data (say 15 seconds).  You will probably find that sometimes the 15 seconds is not enough time and that, during the upload of one of your BCP chunks, SQL Azure might shut it down.  If that happens you will see something like this happen:

5000 rows sent to SQL Server. Total sent: 145000

5000 rows sent to SQL Server. Total sent: 150000

5000 rows sent to SQL Server. Total sent: 155000

SQLState = S1000, NativeError = 21

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Warning: Fatal error 40501 occurred at Oct 30 2009  4:15PM. Note the error and time, and contact your system administrator.

BCP copy in failed 

From the above BCP output, you will see that a total of 155,000 rows were sent before SQL Azure closed the connection.  Thus you would have to adjust your next BCP command to start at your –F value + 155000.

While this process works reasonably well, it can make the process of uploading data a little tedious if you have a large number of tables with a large number of records per table.  In order to simplify the process, we have modified the SQL Azure Migration Wizard to do all of this work for you.  It allows you to specify the chunk size, the batch size, and the time to wait between BCP chunks in SQLAzureMW.exe.Config.  It also catch BCP errors and adjust for records processed and then retry.

Try it out, review the source code, and be sure to provide some good feedback!

White papers: Migration from Oracle or Sybase to SQL Server 2005

Just noticed these two gems (again from the MSDN Architecture Center):

Guide to Migrating from Oracle to SQL Server 2005

Summary: “This white paper explores challenges that arise when you migrate from an Oracle 7.3 database or later to SQL Server 2005. It describes the implementation differences of database objects, SQL dialects, and procedural code between the two platforms. The entire migration process using SQL Server Migration Assistant for Oracle (SSMA Oracle) is explained in depth, with a special focus on converting database objects and PL/SQL code.”

Guide to Migrating from Sybase ASE to SQL Server 2005

Summary: “This white paper covers known issues for migrating Sybase Adaptive Server Enterprise database to SQL Server 2005. Instructions for handling the differences between the two platforms are included. The paper describes how SQL Server Migration Assistant, the best tool for this type of migration, can help resolve various migration issues.”

I definitely could have used these two documents a few months ago!

I hope this helps!

Script the creation of SQL logins and role assignments for Commerce Server 2007

One of the more manually intense steps in the installation of Commerce Server is setting up the appropriate SQL logins and Database Role User Mapping.  This task can easily take 30 – 60 minutes to complete if done manually.  Futhermore, it’s likely that this is the easiest step to make a mistake, which will cause problems for you down the road.

So, to make this process quicker and less prone to errors, I’ve created an SQL script that you can run against your database.  This script performs two tasks:

1. Creates the SQL login accounts (e.g. COMPUTERNAME\CatalogWebSvc).
   
2. Associates the SQL login accounts to database roles.

Note: this script is only for SQL Server 2005.  SQL Server 2000 uses a different set of database roles.

Take a look the Grant Web Applications and Window Services Access to the Databases section of the Installation Guide for Commerce Server 2007, and you’ll see that the number of role assignments is quite extensive.

Rather than pasting the entire SQL script into this post, I am only going to upload the .SQL file.  You can modify this file as necessary in order to adapt it to your environment (e.g. changing the computer name, you may have different names for logins, or don’t need services like the direct mailer).

CreateCSLoginsAndAssignRoles.sql.txt (12.54 KB) (just remove the .txt extension)

I hope this helps!

[Update]

I found it useful to create an abbreviated verion of this script that is used for adding new sites.  Whereas the script “CreateCSLoginsAndAssignRoles.sql.txt” is for brand new installations of Commerce Server 2007, the following script is useful for when you add a new site and re-use users and logins.

CreateCSLoginsAndAssignRolesForNewSites.sql.txt (8.42 KB) (just remove the .txt extension)

The database version (C.0.8.40) does not match your reporting services installation.

As I was preparing a new virtual machine for Commerce Server 2007 (one capable of utilizing the Commerce Server Adapters with BizTalk Server 2006 and Data Warehousing), I ran into a problem when configuring SQL Server Reporting Services.

I had taken a virtual machine that I use as my base installation (e.g. Windows Server 2003 R2, IIS, .NET 2.0, Visual Studio 2005, and the SQL Server 2005 Database Engine) and was following the Commerce Server 2007 Pre-install Requirements and Procedures from the Installation Guide when I encoutered the following error:

The database version (C.0.8.40) does not match your reporting services installation.  You must upgrade your reporting services database.

Couldn’t generate the upgrade script.  There is no upgrade script available for this version.

Turns out that that I had installed Service Pack 2 after I originally installed the Database Engine but before I installed Reporting Services.  Consequently, when I went to configure a reporting database under the Database Setup step it complained that the versions were incorrect.

To resolve the problem, I simply ran the Microsoft SQL Server 2005 Service Pack 2 installation again, and updated the Reporting Services instance.  This allowed me to finish the configuration of SQL Reporting Services without any problems.

A simple little problem, but one that had me baffled for a little while (it’s probably just the sleep deprivation caused by the little one).

I hope this helps!