Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Moving your database to the cloud with SQL Azure


By:   |   Last Updated: 2009-11-05   |   Comments (2)   |   Related Tips: More > Azure

Problem

There has been lots of buzz about cloud computing lately and looking at the benefits it provides (in terms of cost savings, high availability, scalability (scale up/down) etc.) it is now evident that cloud computing is the future for next generation applications. Many of tomorrow's applications will be designed and hosted in the cloud. Microsoft realizes this potential and provides a cloud computing solution with Windows Azure. Windows Azure platform, which is hosted inside Microsoft data centers, offers several services which you can leverage while developing your application if you target them for the cloud. One of them is Microsoft SQL Azure, it's a cloud based relational database service built on Microsoft SQL Server technologies. In this tip series, I am going to show how you can start creating databases and database objects on the cloud with SQL Azure.

Solution

Recently Microsoft launched Microsoft SQL Azure (formerly called SQL Data Services (SDS)) Oct CTP, which provides relational database capabilities on the cloud and is based on SQL Server technologies. You use familiar T-SQL commands to work with your database though not all T-SQL statements are supported with SQL Azure. You can find a complete list of supported/unsupported T-SQL statements here.

SQL Azures enables you to store structured, semi-structured, and unstructured data in your cloud database. Though the current SQL Azure Oct CTP version offers relational database service only, some of the features are not supported yet, but would likely be part of future CTPs or RTM, (refer to the SQL Azure site on MSDN for the latest updates). In the future it's going to support more services for example Reporting Services, Analysis Services etc.

Turning the wheel...

Currently Windows Azure and SQL Azure are in its Community Technology Preview (CTP) version (this example is based on the October CTP). So to work on this, first you need to login with your hotmail or Live ID and you need to register on http://go.microsoft.com/fwlink/?LinkID=149681&clcid=0x09; you will get a form similar to the one shown below.

microsoft connect

On submitting the above form you will get an email from Microsoft with an invitation code. Then you need to go to https://sql.azure.com/, login with your hotmail or Live ID and enter your invitation code in the form as shown below:

sql azure

Once you enter correct invitation code, you will be prompted to accept terms of usage, you can accept by clicking on the "I Accept" button after reading it.

windows azure platform

Then you will see the next screen which will show you a default project created for you. You need to click on the Project Name (in current case it is "SDS-Only CTP Project") in the grid to move to the next step..

sql azure

Now that default project is already created, you need to create a server (with Oct CTP you can create one server and five databases in it including the default master database). For that you need to specify the Administrator user name and password. Please keep the Administrator user name and password safe as it will be required when you want to connect to the server from SQL Server Management Studio (SSMS) as an administrator. The server name will be automatically generated and it will be shown to you on the next screen.

sql azure

Here you can see a server has been created for you and the user name you specified in last screen is the administrator on the server. By default you will notice a master database is created. On the bottom right you can see there are two options ("Create Database" and "Drop Database"). Click on "Create Database", specify the name of the database and its maximum size.

With the current CTP, two editions of databases can be created, first Web edition which has maximum 1 GB size limit and second Business edition which has maximum 10 GB size limit. More details on these different editions and pricing can be found here.

windows azure platform

With the "Create Database" option, I created a database called AdventuresWorksOnCloud which you can see here. To get the connection string, which you will need to connect from your applications, you can select the radio button before the database and then click on "Connection Strings" option on the bottom left.

server administration

Okay so far we created a server on the cloud and a database in it. Now I am going to show how you can connect to your newly created database.

Launch SSMS by going to Start -> SQL Server 2008 -> SQL Server Management Studio. You will prompted to enter your connection details, click cancel on it because the current CTP does not allow you to connect from SSMS Object Explorer, if you try connecting from there you will get an exception error like this "Invalid object name 'sys.configurations'. (Microsoft SQL Server, Error: 208)". So you need to click the "New Query" button and then specify the server name (which was created above), the user name and password as shown below.

micrsoft sql server

Further as the current CTP does not allow using the USE statement to switch to different database you need to specify which database you want to connect to. For that click on the "Options>>" button and in the "Connect to database" combo-box specify the database name.

connect to server

Now click on the "Connect" button and you will get an exception error similar to this.

connect to server

This is because, for security reason, by default all hosts are denied access. So you must specify the IP address ranges of the hosts which will be accessing your server and databases by going to the SQL Azure web portal as shown below:

server administration

Once the Firewall Settings are set you should be able to connect and use familiar T-SQL commands.

Note

The step by step demonstration shown above is based on SQL Azure October CTP. There will probably be some additions or changes in the way current features work in upcoming CTPs or the RTM. Kindly refer to Microsoft SQL Azure site for latest updates.

Next Steps


Last Updated: 2009-11-05


next webcast button


next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, November 09, 2009 - 3:59:31 AM - arshad0384 Back To Top

Obviously it depends on your network (Internet) speed, during our POC we didn't find much differences though.


Thursday, November 05, 2009 - 12:04:24 PM - grobido Back To Top

Arshad have you done much testing with this?  What is the response time like?


Learn more about SQL Server tools