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
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.
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.
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.
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.
# # #
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.
# # #
Internet.com Cloud Computing Showcase Sponsored by Microsoft ...
Windows Azure Platform for Enterprises
Get Help Sponsored by Microsoft Windows Azure Platform
SQL Azure management portal:
SQL Server 2008 R2 Express:
SQL Azure Migration Wizard v3.3.7:
Windows Azure Platform Benefits for MSDN Subscribers:
Microsoft SQL Azure Data Sync CTP:
SQL Server Feature Limitations in SQL Azure: