Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 22, 2010

Migrating Your SQL Server Database Applications to SQL Azure - Page 2

By Jani Järvinen

Setting up an SQL Azure database

If you don’t yet have experience in working with SQL Azure, a good way to start learning is with a prototype database on the cloud. If you already have an Azure subscription for instance through your MSDN subscription (MSDN subscription Azure benefits were recently increased from eight to 16 months), go ahead and log in to SQL Azure.  The web portal address is http://sql.azure.com/.

Once you have signed in (follow the link at the top of the page), navigate to the “SQL Azure” part of the service (the link on the left), and if you already haven’t, create yourself a project. Then, click the project name, and you will be taken to a page that displays your databases (Figure 3).

Click for larger image

The SQL Azure
web management portal at sql.azure.com
Figure 3. The SQL Azure web management portal at sql.azure.com.

At this point, click the Create Database button. This will show a “dialog box” on the screen, asking for the name and maximum size of your database (Figure 4). Remember that you pay for what you use: a larger database is more costly than a smaller one, and two databases cost twice the price of one. Thus, proceed with care.

Creating a
    new database requires you to give a name and select the database size.
Figure 4. Creating a new database requires you to give a name and select the database size.

Once you have created your database inside SQL Azure, it is important to remember to configure firewall settings. By default, the firewall in the Azure computing centre is restrictive, and does not allow any connections to the TCP port 1433. You can however allow any range of IP addresses, and at first, this would probably be your own development machine. Remember that you must type in your public IP address as given by your ISP, and not your internal IP address. You are now working with a public server, after all.

After the SQL Azure is created and you have enabled access to it, you should be able to connect to your Azure database using either SQL Server’s Management Studio or Visual Studio’s Server Explorer (Figure 5). Remember that you will need to have SQL Server Management Studio version 2008 R2 or Visual Studio 2010 for this to work. Earlier versions of SQL Server Management Studio will report errors if you try to connect to SQL Azure.

Connecting
    to SQL Azure with SQL Server Management Studio.
Figure 5. Connecting to SQL Azure with SQL Server Management Studio.

With an available connection with either of these tools, go ahead and create some tables that your current application requires. At first, you might not need all tables. Instead, focus on the initial or core tables, and then work your way onwards from there.

Testing your application with an Azure database

In an ideal world, migrating your database application to use SQL Azure would be a two-step process: first, migrate your data to Azure, and then change the connection string in your application to point to the cloud database. In reality however, things usually are not so simple.

Even so, one strategy that works especially with smaller applications is to dive head-first into Azure. Go ahead and create some tables in your Azure database, and then point your application to your cloud database. Record the error messages, and start working from there.

For larger applications, it is better to plan more in advance. During a database code review, you should aim at identifying code parts that use features in SQL Server that are not available in Azure. On MSDN, there is a topic called “SQL Server Feature Limitations” that lists features not supported by SQL Azure (see again Table 1). With the non-supported code blocks marked, you should work to change your code so that it becomes compatible.

In larger applications, such changes might, depending on code complexity and coupling, easily cause chain-effects that require editing of code on multiple levels. If this is the case, it might be worthwhile to create a new prototype application that only contains parts of the original code. With such a separate application, even larger code changes are usually much easier to manage. This is sometimes called the “divide and conquer” method.

In this method, the prototype application should initially have your database logic to barely connect to the Azure database. Once that works, you add more functionality from the original application. This process is repeated until you can see that the functionality of your original application starts to work. It doesn’t need to have full functionality, but enough so that you can see what things in the original application need to be changed.

Finally, with the lessons learned from the prototype application, you can apply the solutions you found to the real application. After some number of iterations in writing code, testing and debugging, you should in the end have an application that is compatible with SQL Azure. This is indeed a goal worth striving for.

Conclusion

Cloud based applications and databases are tempting to developers and users alike. For one thing, almost unlimited scalability and not having to worry about server maintenance are things that can save organizations big dollars. However, moving applications to the cloud does not always happen in a snap.

In this article, you learned strategies to migrate your database applications into Microsoft’s SQL Azure cloud database, and what kind of issues you might run into while doing so. Remember, that each application is different and must be treated individually. The rule of thumb is that the more modern and loosely-coupled your application architecture is in the beginning, the easier will be your task to move your SQL database to Azure.

This article walked you through different SQL Server migration options, and also showed how to get started with SQL Azure databases. Finally, you discovered compatibility issues that most commonly affect code compatibility when moving from SQL Server to SQL Azure.

The old proverb holds true: there’s a silver lining to every cloud.

Jani Järvinen

# # #

About the Author

Jani Järvinen is a software development trainer and consultant in Finland. He is a Microsoft C# MVP, a frequent author and has published three books about software development. He is the group leader of a Finnish software development expert group at ITpro.fi, former of a Finnish .NET user group and a board member of the Finnish Visual Studio ALM User Group. His blog can be found at http://www.saunalahti.fi/janij/. You can send him mail by clicking on his name at the top of the article.

# # #

Related Articles

Internet.com Cloud Computing Showcase Sponsored by Microsoft ...
Windows Azure Platform for Enterprises
Get Help Sponsored by Microsoft Windows Azure Platform

Links

SQL Azure management portal:
http://sql.azure.com/

SQL Server 2008 R2 Express:
http://www.microsoft.com/express/Database/

SQL Azure Migration Wizard v3.3.7:
http://sqlazuremw.codeplex.com/

Windows Azure Platform Benefits for MSDN Subscribers:
http://msdn.microsoft.com/en-us/subscriptions/ee461076.aspx

Microsoft SQL Azure Data Sync CTP:
http://www.microsoft.com/windowsazure/sqlazure/datasync/default.aspx

SQL Server Feature Limitations in SQL Azure:
http://msdn.microsoft.com/en-us/library/ff394115.aspx



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM