Sunday, June 15, 2014

Creating a SQL Azure Database

If you are just beginning to use the Windows Azure Cloud, at some point in your development lifecycle you will probably also need to use a SQL Azure database.

Below are the steps you will need for creating a SQL Azure database:

In order to view the database connection string for your database, you will need to click on the link for "View SQL Database connection strings for ADO.Net, ODBC, PHP, and JDBC"

Now that you have the database connection string, you will need to connect to the database using SQL Server Management Studio.

Of course, when you attempt to log into the SQL Azure database, you may receive the following error message:

 Therefore, you will need to go back into your SQL Azure configuration and configure the firewall settings to allow your Client IP Address:

Of course, once you have your SQL Azure database set up, you will need to generate Scripts from your existing database.  Here is an article that provides guidelines on how to migrate your on-premise SQL Server database to SQL Azure:

NOTE: Make sure you select the option for the "Script for the database engine type" to SQL Azure Database.   If you are planning to script the data in your database, make sure you select "Schema and Data".  

Of course, if you FORGET to change the database engine type (and leave it as Stand-alone instance) , you may encounter some errors/problems when executing your scripts:

The error message "Filegroup reference and partitioning scheme is not supported in this version of SQL Server." indicates that all of the SQL Scripts that contain the following: ON [PRIMARY] will NOT WORK in the SQL Azure environment.  Therefore, you will have to re-script your database so that those elements are removed from the generated script by changing the database engine type.

Of course, the script generation process for SQL Azure Database is not perfect:

In this case, "Reference to database and/or server name in 'msdbo.dbo.sp_send_dbmail' is not supported in this version of SQL Server." indicates that you will not be able to send e-mail messages from SQL Azure databases.  As you can naturally guess, you will also have to remove any references to these types of operations from your database.

Once you have removed these non-compliant SQL statements from your generated script, if you are able to successfully execute your script on your SQL Azure database, you will have successfully migrated your database to SQL Azure!!

No comments:

Post a Comment