In the previous post Exposing your data using .NET WCF Data Services: Part 1, I wrote about creating a WCF Data Services application to expose data from a SQL Database. In this post we’ll go through publishing the database created in Part 1 to the Windows Azure cloud.
Creating the Windows Azure database
The first step in migrating the Quotes data services application that we created in Part 1 is to move our database into the cloud. To get started with this the initial step is to create a new database instance in Azure, which we can do in the management portal by the following steps.
Navigate to the SQL Databases node
Once we are logged into the Azure portal, we can specify the database details for the database we wish to create via the Databases node as shown below. From there we can choose options like the database type, size and collation. The Business version of the databases offers larger storage capacity.
Manage database details from the SQL Databases dashboard
Once our database instance is created, we will be able to see the database details in the dashboard of the portal and are able to start managing/configuring the database details as shown below. The dashboard will allow you to see server events, errors, amount of storage used and so on.
Attempt to connect to the database
With the database instance now created we are able to view the server details on the dashboard. From there we will be able to view the URL which we can use to connect to the server using the credentials created in the previous step.
However, when I attempt to establish a connection using SQL Server Management Studio, I’m presented with the error below. The reason for this error is that for being able to connect to a SQL Azure database the IP Address for the client computer – or an IP Address range – must be allowed in the database firewall rules.
To correct this issue we need to allow the IP address reported in the error above – masked– into the SQL Azure firewall. This can be done by accessing the Manage allowed IP addressessetting and then adding the desired IP address or ranges as shown below.
Establish a connection to the Azure database from SSMS
Once the IP addresses have been allowed in the SQL Azure firewall we are now able to connect to the database from management studio as can be seen here.
Migrate our database to SQL Azure
With our connection now established, the main remaining task is to migrate our data out to the Azure database. One way to do this in the past was by using the SQL Azure migration Wizard. However, given we are using SQL 2012 in this instance there is a way which I consider simpler. You may be aware of the SQL Server Management Studio Generate Scripts utility. Now this same utility can be using to export scripts to a format that is suitable to run in Azure. The article How to: Migrate a Database by Using the Generate Scripts Wizard (Windows Azure SQL Database) explains the options that need to be chosen in SSMS to do this. Below is a quick summary:
Set Output Type as Save script to a specific location. Select Save to file. Click Single file. Type the file name and location in File name. Click Advanced.
In Advanced Scripting Options set “Script for the database engine type” option as “SQL Database”, set “Convert UDDTs to Base Types” option as “True”, and set “Types of data to script” option as “Schema and data”. Click OK.
Once these options are set, the script is generated as can be seen below.
Execute the generated script on the SQL Azure database
Now that the script is ready we can execute it on our target database and once this is successful, our tables will be created in the target instance as shown below.
Connect our local application instance to the Azure database
With our data now in the cloud, the final step is to connect our local application instance to the cloud database. This can be achieved by simply updating the connection string in our project to point to the SQL Azure instance as shown below.
After the above step is completed we should be able to view our data in the same way as we did in Part 1. By running the application locally we can see that our data displays as expected as shown here.
Summary
The above steps in this post show how we can easily create a SQL Azure database instance using the management portal and migrate our local database and application to use that instance. In the next post we will go through the final step of moving the application’s code to Windows Azure.