Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Introduction
Cloud computing is an industry buzz word and people are equally excited to know about the technology.
In the cloud world Microsoft is providing multiple ways to store data like Blob, Table, Queue and Microsoft Windows Azure SQL Database.
Blog, table and queue are very much Windows Azure system storage whereas Microsoft Windows Azure SQL Database is similar to an on-premise SQL Server; it is a seamless experience for developers as well as organizations that use different versions of on-premise SQL Server, i.e. SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012.
Microsoft Windows Azure SQL Database is the way to store and manage application data on the cloud, just as it would be maintained on-premise. However, there are few things that you must aware of before you start your journey with Microsoft Windows Azure SQL Server Database.
Microsoft Windows Azure SQL Database is a cloud based relational database service built on SQL Server technologies. By using this service you can deploy relational database solutions on the cloud without any hassle and realize the additional benefits of a distributed data center that provides high availability, scalability and security.
In this Article I’ll share how you can start with Microsoft Windows Azure SQL Database and store data on the cloud.
Start with Microsoft Windows Azure SQL Database
We can store data on Azure SQL Database using two methods; through the Windows Azure Platform Management Portal or an on-premise SQL Server client like SQL Server Management Studio.
It is very easy to manage data using either way; I’ll walk you through the Windows Azure Platform Management Portal in detail to store/manage your data on the cloud.
To store or manage data on Azure SQL database we will follow three steps; initially we will create the database then the table and finally store/manage data in the created table using T-SQL.
First, you should connect to a registered subscription on Windows Azure Portal using appropriate credentials.
Windows Azure
Once you are connected, click on SQL Database from the left pane and click New from the bottom pane.
Click on New
Clicking New will give you three ways to create a database; Quick Create, Custom Create and Import.
The Quick Create option creates the database quickly by specifying only a name. We can do deployment and configuration tasks later.
Quick Create
The Custom Create option helps to create a database with deciding to use an existing server or create a new one.
Custom Create
The Import option creates a database using a saved database from a BLOB storage account.
Import
Out of the above three options, the most common approach is the “Custom Create” approach to create new database because it gives you more control during the creation of the new database.
Clicking on the Custom Create option will give you an option to specify the name of database to be created and other options like edition, database size limit, collation and server. Each option has multiple choices that need to be taken care of as per requirement before creating the database.
Edition: Azure SQL provides you two options, Web or Business edition. These editions define the limit of the database size.
Limit Database Size: It defines the size of the database during the creation of the database. Database size availability is directly related with the Edition. Web edition supports two size options, 1 GB or 5 GB and Business edition provides a max. size of 150 GB option, including 10, 20, 30, 40, 50 and 100 GB.
You have to be cautious to choose the size of database because you are charged accordint to the size of database you choose.
Follow the wizard to create a new database.
New SQL Database
New SQL Database – Custom Create
Specify Database Settings
SQL Database Server Settings
Once you complete the wizard, after specifying all required information, it will start creating the database and show you the progress on bottom.
Progress Bar
Once it’s completed you can check the details; click on the Details icon.
Details
As you click OK, it will land you on the database home page.
Database Home Page
With the help of the above steps we have created TestDB on the cloud, which would work as container to manage other database objects like Tables, View, and Stored Procedure and User Defined functions to store and retrieve the data.
Click on the Manage icon in the bottom bar to start creating and managing database objects.
If you are trying to manage the database for the first time, it may ask to you to add your IP address. You need to follow the wizard for adding the IP address to connect the database, otherwise you won’t able to connect the database.
Why do you need to add the IP address? Once you try to access your SQL Database server, the Windows Azure SQL Database service prevents access with the SQL Database firewall. You can find more details for Firewall settings on MSDN.
Add IP Address
Once you click on the manage icon or after adding the IP address it will open a new login window to connect the newly created database with the login credentials that you specified during database creation.
I used abc@<<server Name>> as the user, which I gave during database creation.
Login Screen
After a successful login, you will land on the database Administration page; here you can see the current state of the database.
Current State of the Database
Store and Manage Data
To store, manage and retrieve data In SQL Server, first we have to create a table and define the column definitions.
You need to click on the Design tab (bottom of left pane) to start with creating tables and columns to store data. The Design tab would provide a place to design/create all of the required database objects, similar to what SSMS provides for on-premise SQL Server.
Design Tab
Click on New Table, which will open new window.
Mention the table name in the Table Name text box (top of the column details) and add Columns. Specify name and other properties for required columns.
Specify Name
After specifying the required information, click Save on top bar. It will create the table with all the specified columns.
Click on the Indexes And Keys link to see the schema diagram of newly created table.
Schema Diagram
Now you can store your data in the created table just as you do with any version of SQL Server. Azure Management Portal supports all sorts of DDL and DML commands. In the next section we explore how we can write and execute DDL and DML to store and manage data.
To manage the data, click on the New Query icon from left top bar, it’ll open new window where we can write any T-SQL statement and execute it.
Write and Execute T-SQL
Enter/store the record using insert T-SQL statement; write the Insert statement on the editor and click on the Run icon (middle of the top bar) to execute the statement. It’ll insert the required rows in the table.
Insert the Rows in the Table
Verify the inserted records using a T-SQL statement on the same or new window.
Verify the Inserted Rows
This is the easiest way to store your data in a table on an SQL Azure Database.
As we have seen how to store data in a table using basic a T-SQL statement, we can execute rest of the DML (update and delete) statements the same way.
There are some T-SQL statements not supported on an Azure SQL Server Database that are available in an on-premise SQL Server 2008 and newer version. You can refer to supported and unsupported T-SQL features on MSDN.
Execute a T-SQL Statement
After executing an update (T-SQL) statement shown in the above image, you can verify the changes.
Verify a T-SQL Statement
In above figure we can see that after executing the update statement, the value of Address has been changed for ID =2.
We can execute delete statement the same way. Let’s execute a delete statement as per the below diagram and validate the results.
Delete Statement
After executing the delete statement, the record that has a value of ID =2 should be removed from the table.
The Record has been Removed
In above diagram, you can see that only 1 record is returning and another one with the ID = 2 has been removed after executing delete statement.
With the help of above exercise we learned how data can be stored and managed on the cloud database.
Summary
We have seen that how easily data can be managed on the cloud with the help of the Azure Platform Management Portal. It is a seamless migration for any developer or organization to manage data on the cloud. Developers/Organizations can leverage various database size options to store data and pay for what they use. You can get the Windows Azure SQL Server price list on Windows Azure Portal.
Overall storing/managing data on the cloud is a cost effective solution with less effort and overhead.
References
http://msdn.microsoft.com/en-us/library/windowsazure/ee336279.aspx
http://msdn.microsoft.com/en-us/library/windowsazure/ee336230.aspx
