At first database migration from SQL Server to SQL Azure may seem like a pretty easy thing to do. In fact when it comes to a small project of around 20 tables and about a dozen of stored procedures, this really is the case. However if you get a project of hundreds of tables, stored procedures, functions and triggers, if cross-database access is being used, and there is dynamic objects creation, then you will have to face a number of complex and interesting tasks which would require a not so obvious and simple approach, and solutions.
In this article I will try to address some of the challenges I have faced when developing SQL Server to SQL Azure migration solutions.
The first less complicated, but definitely not pleasant issue, is that SQL Azure requires each table to contain a clustered index, otherwise you won’t be able to insert any data to it. One would think: so what’s the big deal- you create a clustered index for each table, and proceed with the development. However this minor inconvenience is the core of the second problem: SQL Azure does not support statements like:
1 | SELECT ... INTO |
This statement is being used by many, and I’ve seen it in almost every project I’ve been involved in. The convenience of using it is especially felt when creating a new table with hundreds of columns, since having a single line code is sufficient. An alternative solution can be creating a new table (or a temporary table or table variable) with the list of all the fields. Inconvenient, but solvable.
SQL Azure doesn’t support running SQL Server Agent, i.e. if you had some tasks that were running every certain period of time- they would not be supported. As a solution you can write a service that would work on Windows Azure, part of which is SQL Azure, or move the tasks to SQL Server Agent of the regular SQL Server.
Another pretty serious problem, but at the same time quite a brain-teaser, is inability to view the data of one database through the context of the other. This makes it impossible to create views, which would sum up data from several databases and would thus be useful for instance for reports generation. The only way to overcome this problem is to create new schemas in the same database, instead of creating several databases. In this case however you might get confused with objects access rights for different users. In addition to that SQL Azure does not support linked servers, while the regular SQL Server allows creating a linked server on SQL Azure.
As one of the most time consuming procedures, I would like to point out the creation of a new database. It can only be created from the Master DB context, and at the same time it’s impossible to replace the current database from connect since USE element is not supported. In addition to that after the new database is created, in order to create a user you need to switch to the new DB context, while in order to create a new log in you need to switch back to the DB Master context. Solution I have found is changing the code of the app. First you create connect to Master DB, then you create connect to the new database and you keep working with the latter. The logics for creation of the database and its objects is being split into 2 or more parts.
Any DB in SQL Azure is SQL_Latin1_General_CP1_CI_AS by default, and this cannot be changed. To resolve this issue you will have to set the necessary values in all the string tables. E.g.:
1 2 3 4 | CREATE TABLE tab ( col01 nvarchar(100) COLLATE Cyrillic_General_CI_AS ) |
SQL Azure doesn’t support some of the system functions, such as SUSER_NAME(), SYSTEM_USER, HOST_NAME() this and other data can be obtained from the system view:
1 2 3 | SELECT * FROM sys.dm_exec_sessions WHERE session_id = @@spid |
There is a minor issue with connect – if connect to session has not been used for the last five minutes, SQL Azure automatically disconnects the session. You need to be aware of this special feature of the application. As far as I know, leaving a session open for a long period of time, is considered to be a bad tone in any case, so this can be considered as a necessary evil.
Since SQL Azure is located in the cloud, there is no need to allocate discs for data files and transaction logs; there are no system views similar to sys.master_files. On the one hand the absence of file management on premises is a great thing, on the other hand it gives you a weird feeling of incompleteness. The future however will definitely be all about cloud computing and technologies.
Both Rowguidcol property и filestream type are not supported by SQL Azure, and if you can simply ignore the former, the organization of the latter would require re-writing of the application, and as an alternative change the type to varbinary(max). For the most part the filestream type is being used for SQL Server Express editing to avoid hitting the database size limit. Windows Azure Storage allows you to store up to 100Tb of data under a single account. The subscriber gets a total of 5 accounts by default, and he can certainly expend it based on his needs. Unfortunately SQL Azure won’t be a panacea if the size of your database exceeds say 1Tb. Web Edition has 5Gb limit for DB, Business Edition extends it to 150GB. Therefore a solution to such situation might be either Windows Azure Storage or some third-party solutions, which can be easily found, but of course will be offered at additional cost. I personally consider 150Gb database limit to be the weakest link of SQL Azure.
Sequence is not supported, and you would need to create a table and functions to work with this object. Those of you who are familiar with ORACLE to SQL Server migration can easily perform this task. Interesting fact: sequences only appeared in SQL Server 2012 as a tribute to ORACLE, who in turn have taken Identity property for columns from SQL Server in 2013.
SQL Azure does not support extended properties. In this case you need to create a separate table, which would store all the values, and would re-write the logics of the stored procedures work and of the application. Although it sounds scary, in reality this change doesn’t pose particular challenges and is rather easy to make. I successfully automated this process on the stored procedures side, and this way had saved myself dozens of hours that might have been spent.
Full Text Search is not supported, but there is a free alternative – Lucene.Net, for this however you will need a C# developer, who will have to re-write the application logics, since you won’t be able to do it with the help of t-sql.
Bulk Insert is not supported, since SQL Azure doesn’t have such thing as a file system. You can use bcp or SSIS of the regular SQL Server to obtain the desirable result. In general I find the main DB on SQL Azure and users DB on the clients premises, that work on free SQL Server Express or even SQL Server Compact, to be a rather convenient system, as far as the development and implementation go.
As a conclusion, I can state that SQL Azure is a great and suitable solution for either small projects migration, or for those that are initially being developed for SQL Azure. As far as the big project migration goes, it might look complicated at first, however you’d be surprised how with the help of simple methods these challenges (e.g. overloaded code) can be easy overcome. If you ask me I see these types of migration projects as something that can push me to think in a non-standard way, learn and explore something new, and as a result something that turns a routine migration work, into a challenging and interesting project.
