SQL Server Migration Assistant 5.1 Supports SQL Server v-Next

This entry has been cross-posted from Brian Swan's blog.

On the heels of today’s announcement at Microsoft’s Worldwide Partner Conference of the availability of SQL Server  code name “Denali” CTP3, the SQL Server Migration Assistant Team announced the release of the SQL Server Migration Assistant for MySQL 5.1. (You can download SSMA here.) This big news in this release of SSMA is support for SQL Server v-Next (code name “Denali”).

I am, of course, interested in how these releases might be of interest to PHP developers. For PHP developers looking to add support for SQL Server or SQL Azure to their applications or frameworks, SSMA allows you to migrate MySQL schemas and data to SQL Server 2005, 2008, “Denali”, or SQL Azure:

 

Migrating an existing MySQL database to SQL Server allows for easy validation and testing of your work toward building in SQL Server support. (The walkthrough I wrote a while back will show you how to convert schemas and data.)

However, schema and data migration is only part of the story towards adding support for additional database vendors. Converting queries to another database vendor’s syntax is another key step. Fortunately, SSMA can help with that as well. In this post, Converting MySQL Queries to MS SQL Server Queries, I walk through how to use SSMA to convert a MySQL LIMIT/OFFSET query to SQL Server syntax. If you read that post, you’ll notice that the converted query uses the ROW_NUMBER/OVER syntax (not exactly a simple, straightforward conversion). One of the nice things about SQL Server “Denali” is the new OFFSET/FETCH syntax. And, SSMA 5.1 leverages that new syntax in converting LIMIT/OFFSET queries when you are converting to SQL Server “Denali”. Here’s how to convert a query…

After creating a new project, connecting to MySQL, and selecting a database (details in this post), right click on Statements in the MySQL Metadata Explorer and select Add Statement:

In the SQL tab, type in the query you want to convert:

Back in the MySQL Metadata Explorer, right-click Statements and select Create Report:

When prompted to save changes to the MySQL Editor, click Yes. The resulting converted query is MUCH nicer than before:

In fact, the new OFFSET/FETCH syntax makes it so easy to convert queries that you might wonder why you would need SSMA for this. Well, the OFFSET/FETCH syntax only works for SELECT queries (unlike MySQL), and there are some corner cases in which the MySQL LIMIT/OFFSET syntax doesn’t convert to the SQL Server OFFSET/FETCH syntax. Fortunately, SSMA handles those corner cases nicely (although, in some cases it reverts to conversion using the ROW_NUMBER/OVER syntax). So, in some cases, SSMA might still come in handy when converting queries.

I’m excited to see the releases of SQL Server “Denali” CTP3 and SSMA for MySQL 5.1, And, as always, I’m eager to hear your questions and/or comments.

Thanks.

-Brian