Problem solve Get help with specific problems with your technologies, process and projects.

How to work around Azure database migration limitations

Performing a successful Azure database migration is no easy task. There are plenty of limitations and missing features, and this is how you make do.

Microsoft Windows Azure SQL Database, commonly known as SQL Azure, is a relational database in the cloud that is part of the Windows Azure platform. Although Windows Azure SQL Database is built on the SQL Server platform, there are differences you should consider when performing an Azure SQL Database migration. This article describes guidelines and limitations when migrating databases from on-premises SQL Server instances to a Windows Azure SQL Database.

Data transfer with Azure database migration

Windows Azure SQL Database does not support the RESTORE statement or attaching a database to the Windows Azure SQL Database server. So when doing an Azure database migration, you should use either the Generate and Publish Scripts or the Deploy Database to SQL Azure wizard in the SQL Server Management Studio. The Generate and Publish Scripts wizard creates Transact-SQL scripts for your database, which you can then execute against your Windows Azure SQL Database server to create your database. The Deploy Database to SQL Azure wizard directly deploys your database to a Windows Azure SQL Database server. You can also use this wizard to deploy a Windows Azure SQL Database to a local instance of SQL Server, or to move databases from one instance of Windows Azure SQL Database to another.

When doing a large Azure database migration, estimate the data load time. For example, approximately 10 GB of data using a 10 MBPS broadband connection could take 2 to 3 hours using SQL Server Integration Services or the bulk copy utility (BCP.exe). You can improve your data load performance by disabling non-clustered indexes on your database tables. You can also disable foreign keys to load the data in any order, which also speeds up data load times.

Also, ensure every table in the source database has a clustered index, as Windows Azure SQL Database does not support tables without them. You can only do insert operations if there is a clustered index on the table.

Editions and feature limitations

Windows Azure SQL Database supports two database editions: Web and Business. The only difference is size. The Web Edition has 1 GB and 5 GB databases, while the Business Edition has databases that are 10 GB, 20 GB, 30 GB, 40 GB, 50 GB, 100 GB and 150 GB. Unexpected database growth is difficult to handle in Windows Azure SQL Database, as the maximum supported database size is 150GB. You can minimize this risk by archiving older data. In addition, unlike local instances of SQL Server, you can only create a maximum of 149 databases in SQL Azure, excluding the master database.

Some SQL Server features are not yet available in Windows Azure SQL Database: Change Data Capture, Data Compression, Data Auditing, Extended Events, Common Language Runtime (CLR), Extensible Key Management, FILESTREAM Data, Integrated Full-Text Search, Large User-Defined Aggregates (UDAs), Large User-Defined Types (UDTs), Performance Data Collection (Data Collector), Policy-Based Management, Resource Governor, Transparent Data Encryption, Database Mirroring, Table Partitioning, Service Broker and Typed XML and XML Indexing. If your database depends on any of those features, you must rewrite the database to operate without those features or wait until SQL Azure supports the features before you do an Azure database migration.

One missing feature in SQL Azure worth calling out is the lack of support for SQL Agent or Jobs. However, you can use a Windows Azure Worker Role to mimic the functionality of the SQL Server Agent.  Windows Azure Worker Role has similar concepts as the Windows service, including starting, stopping and configuration. You can read more about this here. You could also run SQL Server Agent on-premises and then connect to Windows Azure SQL Database to run scheduled tasks.

Other Azure database migration considerations
By default in Windows Azure SQL Database, READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options are turned ON, and you cannot change these options. Therefore, set the <snapshot_option> in the ALTER DATABASE T-SQL statement in the Windows Azure SQL Database instance. For additional information about row versioning-based isolation levels, see the Microsoft article on it.

Windows Azure does not support changing the collation at the server level. If you want to use the non-default collation with Windows Azure SQL Database, then either set the collation with the Create Database Collate option, or set collation at the column or expression level. Also remember that Windows Azure does not support the SQL Database Collate option with the Alter database command.

Finally, failed database connections are difficult to troubleshoot in Windows Azure SQL Database, as the errors don't specify an exact reason for the failure. Be sure to consider all the error messages listed here to troubleshoot the connection failure.

About the author
Basit Farooq is a lead database administrator, trainer and technical author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms.

Dig Deeper on SQL Server Migration Strategies and Planning