Design a database migration plan from SQL Server to SQL Azure
May include but is not limited to: differences between SQL Azure and SQL Server, concessions for unsupported features, schema, data, reporting and analytic tooling
Differences between SQL Azure and SQL Server
As I stated in the previous blog post, there are a lot of differences between SQL Azure and SQL Server. Let’s start at the beginning. When you create an SQL Azure database, you’ll have to provide the edition of SQL Azure and the size of database you wish to use. Here’s the list of database sizes available:
- Web edition
- 1 GB
- 5 GB
- Business edition
- 10 GB
- 20 GB
- 30 GB
- 40 GB
- 50 GB
- 100 GB
- 150 GB
Note that the pricing varies – you pay $9.99 per database per month up to 1 GB, or $49.95 per database per month up to 5 GB database size in the Web Edition. The business edition costs $99.99 per 10 GB of database per month, and maxes out in $499.95 per database.
As you can see, you can have a maximal size of 150 GB / database. Of course you can partition your databases horizontally or vertically to reduce their sizes, but be aware of this limitation.
I worked mostly from this MSDN page about the limitations of SQL Azure, so feel free to browse it, I can’t be that complete.
You have much less support for various drivers and protocols in SQL Azure compared to SQL Server. The most important providers (from a .NET point of view) such as ADO.NET, EF and the SQL Server Native Client works as expected, and there are also a PHP driver and a Java driver, too. Keep in mind that OLE DB is not supported.
When connecting to SQL Azure you can only use TCP/IP and use the 1433 port. It is also possible that you have to use a login@server form of your login name in the connection string.
Backup and restore: you cannot back up your SQL Azure databases, nor you can restore them (the RESTORE keyword isn’t even supported). Attaching isn’t permitted, too. The closest you can get to a classic backup is to copy your SQL Azure database into another one. The syntax is:
CREATE DATABASE target AS COPY OF myserver.source
However, note that this doesn’t allow you to create a normal backup history. As you can guess, the only way to go is to create multiple databases (back to a week, for example) and pay accordingly.
SQL Server Agent and jobs are not supported, but you can use your on-premise SQL Server to run the agent, and connect to SQL Azure.
The Distributed Transaction Coordinator is not present in SQL Azure. I’ve had a nice week trying to get used to it and to make my design comply with this fact. Write your transactions accordingly (even in application code), and try to avoid transaction promotion.
In SQL Azure each and every table requires a clustered index, no heap tables are supported.
SQL Azure doesn’t let you set collation on the server level, but you can use custom collations on the database level, if you wish. To be complete, the default collation used is SQL_LATIN1_GENERAL_CP1_CI_AS, which means a case-insensitive, accent-sensitive US English collation with the 1252 code page.
Last but not least a major concern when working with SQL Azure is throttling. Throttling is a nicer name for terminating database connections. It can occur when you use up too much resources (since your database lives in a multitenant environment, Azure needs to be fair), idle connections for 30 minutes, and of course server failures. You have to write your application code accordingly, specifically implement retry logic in your applications.
Migrating schema and data
There are several ways to migrate schema and/or data to SQL Azure from an existing SQL Server database. The easiest one is to use the Generate and Publish Scripts wizard in SQL Server Management Studio. If you go to the advanced scripting options windows you can set the “Script for the database engine type” to “SQL Azure Database” and the wizard automatically disables functions which are unavailable in SQL Azure. You can set various options in this wizard, and note that it is capable of exporting schema and data as well.
You can also create an SSIS package to fine tune your data transition. Note that SSIS packages aren’t capable of migrating schemas, so you have to get your schema up to Azure before using them. When using SSIS you have much more control over the migration process, you can also use .NET code to shape your data, etc. Because SSIS is a huge topic in itself, I wouldn’t say more on it in this topic.
There is a third Microsoft tool to migrate data (again, data only) to SQL Azure, and this is the bcp utility. Bcp is a command line tool that lets you export and then import data on a per table basis. For more information check it on MSDN. Note that bcp has issues with the uniqueidentifier (GUID) data type.