Create Database in SQL Azure

In the past couple of days I am spending some time on SQL Azure and it's really exiting. If you are regular user to a SQL Server DB then you will find it real easy. In this post and we will see how to create a new SQL Azure database and what the prerequisites are,

You should be member of Windows Azure Platform. If you don’t have one click here to create one.  You need to provide the credit card info and up to 500 MB storage and 1GB web DB (90 days only) free and you won’t be charged if you are not crossing the limit.

After you create the windows azure account you will receive a confirmation email to your live id and your live id is your Account Owner (AO).  Account Owner is set to Service Administrator (SA) by default and it can be changed any time by AO.  Please refer the below link to understand how the accounts work in Windows Azure.

http://silverliningincloud.typepad.com/my-blog/2010/04/windows-azure-buying-process-and-account-management.html

Once the account is created, login to Windows Azure platform management portal and follow the below steps to crate your first SQL Azure DB,

Step1: Click “Database” from the left navigation and select the Subscription and click create from the top ribbon bar to create the SQL Server. Provide the admin credentials and use the proper firewall setting while creating the server and you can always use it to restrict to your current IP.

Once the server is created make sure you are able to access it by clicking the “Test Connectivity” and provide the proper credentials that you have used when you created the server.

Step2: By default master DB will be created when the server is created and you can create your own DB from the top ribbon bar. While creating the DB the default edition is Web and the maximum size is 1GB, leave it to the default so that your credit card won’t get charged.

Step3: Click mange from the top ribbon to connect to the database, you will be redirected to the “Database Manager” which is similar to web based SQL server management studio with lots of limitations. You need to know the database name that you are connecting and you cannot change the database after you login, you have to log out and log back in. This is the first limitation.

You can use the same connection string to connect SQL Azure DB from SQL Server Query Analyzer.

Step4: When you are connected to the database you can create objects like tables, views, stored procedures. You can also use scripts to create them by using “New Query” option under “Database” Tab. You can also save and open an existing query to/from your desktop.

There are certain limitations in using SQL azure Database, please refer the below article and I strongly recommend reading this, http://msdn.microsoft.com/en-us/library/ee336245.aspx

Things to remember,

• You need to connect to master database in case if you are creating a new database from the query window.
• Create Database query cannot be executed with any other query.
• You cannot insert data into table if it doesn’t have clustered index. It sounds weird to me, when we want the Datawarehousing to be in the cloud you cannot expect every table to have a clustered index. I am able to insert data into a temp table without the index, in case if you are writing any complex logic in your stored procedures this might be useful.
• There are also lots of limitation in the SQL query, like you cannot use “USE” statement and you can never simply export the script from your normal SQL Server database and execute them in SQL Azure DB. You need to make some modifications before you execute them successfully.
• If the connection is idle for 30 minutes, it will be closed automatically and please close the connection if you are not using it. You may be disturbing somebody else’s performance since you are not the only one who connected to the Windows Azure.

If you were able to follow the above steps then you have created a new Windows Azure subscription , new SQL Azure server, new database and some DB objects. It’s pretty simple till now, and i will write how to insert data from your in house SQL server database to SQL Azure database in the coming posts.