SQL server migration to Azure

SQL Azure Database is a cloud version of SQL Server developed by Microsoft specialists. SQL Azure product is very similar to the local version of SQL Server; it is a cloud relational database that you can connect to, and has many well-known management and development tools from the arsenal of SQL Server.

In the article below I will explain how to get started with SQL Azure Database and how to connect to SQL Azure and use it as an internal database for your applications.

Migration SQL server to SQL Azure

Let’s start working with SQL Azure databases

The first stage of preparation for working with SQL Azure Database is to create an SQL Azure account. The original CTP versions of SQL Azure provided free access. These versions expired in November 2009, when Microsoft replaced them with a working version of SQL Azure. There are two levels of SQL Azure accounts. SQL Azure Web Edition allows you to store up to 1 Gbyte of data and costs the consumer $9.99 per month.

SQL Azure Business Edition provides storage of up to 10 GB of data, and the monthly fee for its use is $ 99.99. You can create an SQL Azure account by visiting sql.azure.com and following the registration process.

To be able to manage your SQL Azure database, a user who subscribes to the SQL Azure service registers with the system. To do this, they need to open the website https://azure.microsoft.com/sqlazure/ and register there using the Windows Live ID.

Upon completion of the initial setup of SQL Azure service, the user is prompted to enter the administrative account used to login and a password that must meet strict security requirements. After entering the administrative credentials, the SQL Azure management interface is displayed on pic.1.

SQL Azure Management window allows you to perform two key tasks: create databases and connect clients to SQL Azure server. During the initial connection, special attention should be paid to correctly spelling the name of the SQL Azure server, which must be entered to establish the connection between the client and the database.

When starting to work with SQL Azure, first of all, you need to create a database. To do this, go to the Databases tab and click the Create Database button. You will see a dialog box where you can specify a database name and its maximum size. I had the option of choosing one of two parameters – 1 Gbyte or 10 Gbytes. For the initial testing, I created a pubs database of 1 Gbyte in size.

Once the database is created, we will get a connection string to the database; to do this, you need to check the box before the data and click on the Connection Strings button, which is located at the bottom of pic.1. This is important because, unlike the local SQL Server system, the user can not change the databases after connecting to SQL Azure.

It is necessary to establish a new connection to connect to another database. The listing provides an example of connection strings to SQL Azure from ADO.NET and ODBC.

Next, you must activate the firewall. Connection to SQL Azure database is not possible until you activate the range of IP addresses that will be used by client systems for connection. To add new firewall entries, go to the Firewall Settings tab and click Add Record.

The Custom Firewall Settings dialog box will appear. In this dialog box you need to enter the range of IP addresses from which you can connect to the SQL Azure database. One thing to note here is that if you are using Network Address Translation (NAT) technology, you will need to use the system’s public IP addresses (i.e. not the internal IP addresses of your client system). SQL Azure can help you solve this issue by displaying the current external (public) IP address in the Custom Firewall Settings dialog box.

Connecting to the cloud

SQL Azure supports client connectivity using the native SQL Server Tabular Data Stream (TDS) protocol, and you can connect to SQL Azure using SQL Server Management Studio (SSMS). But it is important to keep in mind that Object Explorer does not function as it requires access to the sys.configurations table, which is not available on SQL Azure.

Therefore, when solving SQL Azure management tasks, you will have to be satisfied with Query Editor. To connect to SQL Azure, open SSMS. This will automatically open the Connect dialog box. In it you need to click on Cancel, after which Object Explorer will connect to the database. An empty SSMS window will appear on the screen. Click New Query, and then fill out the Connect dialog box as shown in pic.2.

From the window on screen 2 it is clear that I gave the server the name obtained in the SQL Azure management window. I chose SQL Server Authentication and entered an administrator name for registration and the password that I used when creating the SQL Azure service.

I then clicked Options and at the Connect to database command line I entered a name for the pubs database and clicked Connect. This brought up the Query Editor window which connected to the SQL Azure system. At this stage, you can create a database schema as well as an object by executing T-SQL commands from Query Editor.

Assigning initial values to the cloud

During the testing I used the SQL Azure CTP version, and I must say that in this situation I did not have an acceptable option to obtain data from the local SQL Server systems for transfer to SQL Azure. I thought that the easiest way to create a pubs database to run the first tests was to use the insrpubs.sql script; this script, included in the SQL Server 2000 demo package, can be downloaded from Microsoft.

However, I was immediately confronted with difficulties: the script was not executed in any way. The fact is that although SQL Azure is similar to the local SQL Server system, there are significant differences between them. The first problem that I had was that the USE command was not supported. This is reasonable, because the user can only connect to one database. Further, I was a bit surprised that SQL Azure tables require a primary clustered index. Faced with these difficulties, I decided to refer to the operational documentation.

Microsoft experts recommend the following method of entering data into SQL Azure for CTP version users: run the Database Script Wizard and then manually edit the created script. In order to convert the pubs demo database, I right-clicked the pubs item and then selected Tasks, Generate Scripts. After doing the necessary steps in the Welcome dialog box, I went to the Select Database dialog box and selected the pubs database as shown in pic.3.

Then in the Choose Script Options dialog box, I changed the script settings that are shown in the table. These settings show almost all of the requirements for a script, since SQL Azure does not support custom data types, advanced properties, or the USE instruction. Since setting up Script Data involves writing both data and schema to the script, this method is not suitable for large databases.

Clicking the Next button in the Choose Script Options dialog box brings up the Choose Object Types dialog box, where I successively select Stored procedures, Tables, and Views items. Then, by clicking the same Next button, I went from one window to the next where I selected all of the objects in the Choose Stored Procedures, Choose Tables, and Choose Views dialog boxes. In the last wizard window, I clicked the Finish button and a new Query Editor window generated a pubs database creation script.

Next, I edited the script, i.e. removed all the items that the SQL Azure system “doesn’t like”. First of all, I deleted the following instruction in all lines where it was used:

SET ANSI_NULLS ON

I then edited all the CREATE TABLE instructions and removed the following sentence:

WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE =
OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS =
ON) ON [PRIMARY]

Finally, I made sure that all CREATE TABLE instructions had a limitation on the clustered primary key. Such restrictions already exist in most pubs database tables, but as it turned out, I had to edit the employee, discounts and roysched tables. After making the necessary changes, I ran the script. The result was that objects were created in the pubs database and the tables were filled. After that I was able to query the pubs database in SQL Azure and get the result, which is shown on pic.4.

As you might expect, SQL Azure supports all standard T-SQL sample queries, including multi-table consolidation and sample subqueries.

Strive for a high goal

After filling in the database, I tried to connect to SQL Azure from the ADO.NET application. No additional tools were needed for this. By the way, I would like to point out that Microsoft provides an additional Visual Studio component called Windows Azure Tools for Microsoft Visual Studio. However, this component is not required for creating database applications executed in SQL Azure environment.

The connection of Visual Studio 2008 project to SQL Azure by the data linking method was flawless. At first I opened Visual Studio 2008 and selected items File, New Project. Then I chose Windows Forms application in New Project dialog box and gave the new project the name SQL Azure. Finally, I assigned the SQL Azure Demo value to the Text property in Form and then resized the form to a horizontal rectangle, towed the DataGridView control to the form and fixed all four sides of the form.

To add a SQL Azure data source, I clicked the DataGridView job arrow and selected Add Project Data Source from the Choose Data Source drop-down list. This brings up the Choose Data Source Configuration wizard window.

In the Choose a Data Source Type dialog box, I selected the Database entry and clicked the Next button. Next, in the Choose Your Data Connection dialog box, I selected the New Connection item, and the Add Connection dialog box appeared. Initially, I tried to add a Microsoft SQL Server (SqlClient) connection. The test connection was established, but I received an error message when I tried to add a data source to the project.

Instead, I had to select the Microsoft ODBC Data Source, and then I would see the ODBC Add Connection dialog box shown in pic.5.

I selected the Use connection string option and entered the connection string obtained earlier in the SQL Azure window. The connection string I used looks like this:

Driver={SQL Server Native Client 10.0};
Server=tcp: gvlxq1rlrw.database.windows.net;
Database=pubs;
Uid=mikeo@gvlxq1rlrw; Pwd=myPassword

Next, I saved the connection as pubsConnectionString. After that, I selected the authors table in the Choose Your Database Objects dialog box. The result was a data set that provides for adding, editing and deleting data from the pubs table in SQL Azure. In general, the process of forming the application, which took only a couple of minutes, was very similar to working with a normal SQL Server system. Execution of this simple application is illustrated in pic.5.

From time to time, there were situations where I had to make another attempt to connect to a SQL Azure database. In all other respects, the application development process using SQL Azure was very similar to the corresponding process in the local SQL Server system. Although this demo application was very simple, I found that the response time was less than one second, i.e. comparable to the time seen when running the application on the local system.

So far, everything is not smooth?

When I started working on this article, I had a weak idea of what SQL Azure service is and how it can be used. But after working for a while with SQL Azure, I realized for myself its resemblance to the SQL Server database and realized how exactly this circumstance facilitates the introduction and operation of SQL Azure.

However, it must be borne in mind that the differences in schema requirements and limited data types make it difficult to port applications to the SQL Azure platform. And even if such a process is successfully implemented, it is quite possible that the benefits received by companies from using the SQL Azure platform, will remain very modest – given that the local SQL Server systems have good scalability and have many tools to ensure high availability.

While it is generally accepted that cloud computing is in its early stages of development, it is probably already possible to say that from the point of view of independent software vendors who have a need for widely available databases, SQL Azure seems to be the most appropriate solution.

The main advantage of this system is that it can be accessed from anywhere in the world. Due to this fact, this database becomes an attractive platform for creating new applications by independent developers. More information about SQL Azure can be found at Microsoft Azure.