Understanding the advantages and drawbacks of a SQL Server to SQL Azure migration is imperative for solution providers as they advise customers. This tip covers the features that make an SQL Azure migration attractive to some as well as the limitations that may have many customers deciding to stick with their existing database configuration.
SQL Azure feature limitations
SQL Azure is primarily a cloud-based database hosting service, not a full database management system. Consequently, many of the features customers rely on in SQL Server are not available in SQL Azure. For example, SQL Azure doesn’t include Analysis Services, Integration Services, Replication, Service Broker, SQL Server Agent or Master Data Services.
SQL Azure also falls short in its lack of extensibility. For example, you cannot implement Common Language Runtime (CLR) components on SQL Azure. That means there’s no support for CLR user-defined data types, including extensions to spatial types and methods. In fact, SQL Azure doesn’t support user-defined types or extended properties. If your customers rely heavily on SQL Server’s capacity for extensibility, they will not have an easy time with SQL Azure.
SQL Azure also lacks support for heaps, so every table must include a clustered index. Additionally, SQL Azure doesn’t support Windows Authentication, only SQL Server Authentication. Although neither of these limitations is insurmountable, solution providers need to be aware of them
SQL Azure migration downside: Physical constraints
When it comes to the physical implementation of databases, SQL Azure is a different animal from SQL Server, particularly where management is concerned. While SQL Azure lets you control such processes as schema creation, index tuning, query optimization and database security, it doesn’t provide for control of SQL Azure’s physical resources.
SQL Azure manages all the tasks related to physical resources, such as replication, load-balancing and performing backups. But, that means you cannot take actions such as assigning data files and indexes to specific hard drives or file groups. In fact, SQL Azure blocks any attempts to manipulate physical resources. As a result, you can’t set server options, run trace flags, or use utilities such as SQL Server Profiler or Database Tuning Advisor.
Performance can also be an important consideration if the SQL Azure database supports in-house applications. SQL Azure’s cloud-based structure is great if for Web-based apps for a global user-base. But in-house users have to use an Internet connection to access data and could see a significant decrease in performance, compared to an on-premises SQL Server database.
Also worth noting is that SQL Azure doesn’t support physical features such FILESTREAM storage, data compression or table partitioning. In addition, SQL Azure databases are limited in size to 150 GB, unlike SQL Server databases, which theoretically have no size limit.
Perhaps the biggest concern for many in IT when it comes to an SQL Azure migration is that the data is being stored off-site, and if that data is highly sensitive, the risks might be deemed too great to let it out of the control of an in-house storage solution. In some cases, compliance and regulatory issues might preclude a cloud-based solution altogether.
Transact-SQL differences in SQL Azure
The T-SQL used in SQL Azure is based on a subset of the language as it is implemented in SQL Server 2008. That means deprecated elements no longer supported in SQL Server 2008 are not supported in SQL Azure. And because SQL Azure uses a subset of T-SQL, not all language elements are available, even if they are supported in SQL Server.
As a result, before you make a SQL Azure migration, you must identify all T-SQL elements that can cause SQL Azure to choke. For example, if your data definition language (DDL) script includes statements to create user-defined types or define extended properties, you must remove those statements from your script.
There are a number of T-SQL statements that SQL Azure does not support, including those that try to control physical resources. Any DDL scripts that contain these statements -- along with any applications that use them -- will have to be corrected before you can implement a SQL Server database.
Preparing for an SQL Azure migration
Despite its limitations, SQL Azure offers plenty of benefits, and under the right circumstances, an organization can gain much by using the service. SQL Azure databases are easy to deploy and maintain, and you can get up to 100 MB of space for only $5/month. For a start-up that wants to try out an idea, SQL Azure allows them to quickly get up and running with none of the overhead and implementation headaches that accompany an on-premises solution. Not only are up-front costs avoided, SQL Azure scales up and down quickly and offers a level of availability that’s difficult to achieve in-house without a significant investment in resources.
So if your customers decide that the service is right for them, solution providers have several options for how to migrate the schema and data. One option is to use SQL Azure Data Sync, the SQL Azure Migration Wizard or the Generate and Publish Scripts Wizard in SQL Server Management Studio. All three options, except the pre-2008 versions of the Generate and Publish Scripts Wizard, allow for the migration of both the data and the schema. You can also use the bcp utility, Integration Services or the SQL Server Import and Export Wizard to copy data from SQL Server during an SQL Azure migration.
Regardless of the approach you take to move your schema and data to SQL Azure, the key to a successful migration lies in understanding what features and language elements are supported and what are not. If you help customers understand that SQL Azure is a basic database hosting service, and not a full-fledged management system, they’ll be better able to make a decision about whether to migrate their databases to SQL Azure and to prepare those database elements that can be migrated.
About the author
Robert Sheldon is a technical consultant and freelance technology writer. He's authored numerous books, articles and training material related to Windows, databases, business intelligence and other areas of technology. He's also published the novel Dancing the River Lightly and the step-by-step guide Ebook Now,and has recently begun working on the 5-Spot ebook travel series.
This was first published in June 2012