Wednesday, September 29, 2010

Tip on using the SQL Azure migration wizard

If you’ve been developing or just playing around for Windows Azure and using the SQL Azure, inevitably you’ve been using SQL Azure Migration Wizard. If not – go ahead and download it! It’s the ultimate tool to migrate your SQL Server data to and from SQL Azure and vise-versa.

I would like to share a tip, that most probably you have noticed but you are not sure what it is. Since couple of release SQL Azure Migration Wizard relies on SQL Server 2008 R2 bits (Express also works). There is small problem when you also have earlier version of SQL Server and/or Management Studio. The tool from management studio that is useful to SQL Azure Migration Wizard is called bcp. It is a command line tool to bulk coping SQL Server tables. And there is a difference in version that comes with SQL Server 2008 R2, and the one that comes with SQL Server 2008 (-). The most recent one has command-line option “-d”, while the others don’t. The trick is to change your PATH environment variable. Remove anything related to older version of SQL Server like this:

C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn

Where the old BCP resides. If you run BCP from that folder you will see:

C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn>bcp
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]

There is no option “-d” which is to select a database, which option exists in R2 version of SQL Server 2008:

C:\>bcp
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]

The point that having earlier version of SQL Server Management Studio it’s folder appears earlier in the PATH environment variable. When you run SQL Azure Migration Wizard it tries to launch bcp.exe without specifying folder relaying on the fact that the folder will be part of the PATH environment variable. But the earlier version will come first and that bcp will be executed, so you will get errors in SQL Azure Migration Wizard. To avoid that errors and run everything smoothly, just remove the old folder from the PATH variable.

  1. How to edit PATH variable?
  2. Click on START then navigate to “Computer”
  3. Right click on “Computer” and select “Properties”
  4. From properties window select “Advanced system settings”:

image

  1. On the new window that will popup, a tab “Advanced” will be selected. There is a button “Environment Variables” at lower right corner. Click on it:

image

  1. Edit the “PATH” variable, which is under “System variables”. Do not edit the one under “User variables”:

image

Good luck and enjoy the cloud!

2 comments:

Boris Hristov said...

I upgraded(!) my SQL Server 2008 to R2 and everything finished without any problems. I did that, because of SQL Azure and the SSMA.
However, when I try to upload an Access database into SQL Azure as in the demo from TechEd 2010, I have a problem, when I check the "Link Tables" checkbox. It says to me that I need a newer version of SNAC, when my version is 10.5 and it's ok if you see the requirements of the SSMA. My paths are OK, obviously because I have upgraded, not unistalled. That's what I have as paths:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;

C:\Program Files\Microsoft SQL Server Migration Assistant 2008 for Access\bin\

What could it be? What Am I missing?

Anton Staykov said...

Hi,
I am explaining how to use SQL Azure Migration Wizard (http://sqlazuremw.codeplex.com/, not the SQL Server Migration Assitant, which is totally different tool.
You can migrate your database to SQL Azure, also with just SQL Server Management Studio 2008 R2. Right click on the Data Base, select "Generate Scripts", there is option to generate script against SQL Azure instance. I will post about it later.