Don’t sweat migration from Oracle, Sybase to SQL Azure

Are you considering moving your database to Azure? Concerned about complexity? Read this tip from expert Denny Cherry. Migrating to SQL Azure might be the easiest move ever.

You can move a lot more than just SQL Server-based applications to SQL Azure. Applications running on Sybase, Oracle or other database platforms can also be moved to Microsoft’s cloud database service. While transferring a SQL Server application to the SQL Azure database is easiest, that doesn’t mean databases running on Oracle or Sybase can’t be migrated as well. They just take a little more work.

The first thing to consider is mapping old data types to new ones. An example is the Oracle data type VARCHAR2, which doesn’t exist in SQL Azure or SQL Server. The good news is that this data type conversion isn’t hard, and every data type on the source system has a corresponding data type on the SQL Azure side. Once the data types are converted, you can re-create the schema in SQL Azure.

For more on SQL Azure

Learn the recommendations and best practices for SQL Azure

Get the top three tips for SQL Azure migration

Get the basics on SQL Azure

The next order of business is ensuring the data will fit the schema. The best way to move data en masse is by using SQL Server Integration Services (SSIS). The package can be saved and run again if there are problems you need to resolve. As SSIS is based on the .NET framework, it has data conversion objects that can convert legacy systems data types to new SQL Azure data types. The downside is SSIS isn’t included with SQL Azure. But you can download it for free with the Express edition of SQL Server with Advanced Services. While this isn’t the same super powerful version of SSIS that comes with SQL Server Enterprise Edition, it is good enough to migrate data from one database to another. SSIS uses the Visual Studio shell as its editor; it’s available as a 32-bit shell only, so you’ll need to have the 32-bit Oracle or Sybase drives installed so you can connect it to your source system.  

The hardest part will be converting the source system’s database code. Any stored procedures and functions -- as well as inline dynamic SQL code -- will need to be tested and possibly converted from the legacy platform’s code to the SQL Azure dialect of Transact-SQL (T-SQL). If you are familiar with SQL Server’s T-SQL language constructs, then SQL Azure constructs will be no problem. They are exactly the same. Moving a system from Oracle will require more code changes than moving a system from Sybase, since SQL Server’s roots are the Sybase database engine. But the platforms split about 20 years ago, so there are differences you will need to address. 

Microsoft tool aids in database migration
Microsoft has the Database Migration Assistant, an application written specifically to help you easily move data from one database platform to SQL Server. I don’t recommend using only this to move your database unless it is very small. Otherwise, the application won’t handle the work in a single pass; it would take much too long. This doesn’t have anything to do with the tool itself, but with latency over the Internet.

But the Database Migration Assistant can be helpful: It will handle a lot of the code checking and data type issues for you automatically. Any code it generates should be tested to ensure that the end result is correct, but it can save a lot of work moving to the SQL Azure platform. Install the free edition of SQL Server, called SQL Server Express, and use it as a destination for the schema. While SQL Server Express supports more features than SQL Azure, it will give you a great starting point without Internet latency issues, so you can test schema and stored-procedure issues before moving to SQL Azure. The Database Migration Assistant can’t help with dynamic SQL inline in the application; you’ll need to do that manually.

Moving a database application between two different vendors’ databases can be a challenge, but it isn’t impossible. Microsoft provides some great tools that can be used to migrate applications into the SQL Server database engine, and there’s no reason we can’t leverage these tools to move these applications into the SQL Azure cloud as well. There are also third-party tools available from companies like Red Gate and Quest that can assist with schema change and stored-procedure code change.

Denny Cherry is an independent consultantwith more than a decade of experience working with SQL Server, Hyper-V, vSphere and storage systems. A Microsoft Certified Master and MVP, Cherry has written on SQL Server management and integration. Check out his blog, SQL Server with Mr. Denny.

Dig Deeper on SQL Server Migration Strategies and Planning