SQL Azure Migration – Moving the Data [FAQ]

There are several methods available to migrate data to a SQL Azure database.

• BCP using the .exe bulk load/export tool.
• SSIS
• Bulk API’s in ADO.NET and ODBC (see below).
• Third Party Tools
• Sync Framework

BCP

BCP is probably the fastest way to move the data. BCP is a command line tool introduced with SQL Server 2005 SP1 which allows for copying data in and out of SQL Server. The tool uses standard TSQL Syntax conventions.
// BCP example

SET SRV=aserver.database.windows.net

SET LOGIN=judo@someserver

SET PW=something

SET S_DR=C:flats

bcp TPCH2.dbo.supplier in %S_DR%supplier.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t “|”

bcp TPCH2.dbo.nation in %S_DR%nation.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t “|”

bcp TPCH2.dbo.region in %S_DR%region.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t “|”

bcp TPCH2.dbo.customer in %S_DR%customer.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t “|”

bcp TPCH2.dbo.part in %S_DR%part.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t “|“

bcp TPCH2.dbo.supplier out %S_DR%supplier.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t “|”

bcp TPCH2.dbo.nation out %S_DR%nation.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t “|”

bcp TPCH2.dbo.region out %S_DR%region.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t “|”

bcp TPCH2.dbo.customer out %S_DR%customer.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t “|”

bcp TPCH2.dbo.part out %S_DR%part.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t “|”

ADO.NET Bulk Copy API:

// Bulk data import

using (SqlBulkCopy bulk = new SqlBulkCopy(new SqlConnection(conn))

{

DestinationTableName = “dbo.data”,

BatchSize = 2000, // Transaction size (length)

BulkCopyTimeout = 10000, // Transaction timeout

NotifyAfter = 1000, // Progress callback

})

{

bulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(

myProgressCallback);

bulk.WriteToServer(sourceDataReader);

}

Third Party Tools

Currently the primary third party tool for migration is the SQL Azure Migration Wizard