» » » » Introduction to SQL Azure.

By: Hugo Luis Alberto Repetto Posted date: April 05, 2012 Comments: 0

sql-azure2Since the SQL Azure database services are in the cloud, you do not have to worry about high availability and scalability. SQL Azure will maintain a “Monthly Availability” of 99.9% during a calendar month. For example, you or your network administrators do not have to spend time on high availability which is taken care by the services. Also, you don’t have to worry about security patches and service packs which will be installed by the service provider.

Using SQL Azure.

SQL Azure is still in CTP , the SQL Azure Database CTP is available for free and you can register below to receive an invitation. Due to high demand for the SQL Azure Database CTP, there may be some delays in receiving your invitation codes. http://go.microsoft.com/fwlink/?LinkID=149681&clcid=0×09

Editions & Pricing.

There are two editions for SQL Azure, namely Web and Business. While self-managed DB and auto high-availability features are supported in both editions, there are few differences between them.
Main difference is the storage, as the Web edition supports only 1 GB of database storage space and the Business edition supports 10GB. 

In case you need to store more that 10GB of data, you will have to break the databases into small sizes. Apart from basic T-SQL facilities, there will additional features like auto-partition and CLR in the business edition. However, these features are not yet available in CTP.
Upon the availability of production ready SQL Azure, the fee will be as below:
•Web Edition: Up to 1 GB relational database = $9.99 / month
•Business Edition: Up to 10 GB relational database = $99.99 / month
•Bandwidth = $0.10 in / $0.15 out / GB

Using SQL Azure Databases.

As there are no options in the web interface to create tables or databases, you will need to use other tools for creating and designing Azure based databases:
SQLcmd is supported on SQL Azure and you can connect to an Azure database using the below command.
C:>sqlcmd -U <User> -P <PPassword> -S <ServerName> -d master
Management Studio
SQL Server Management Studio (SSMS) is the most frequently used tool to connect to SQL Server databases. However, when connecting to SQL Azure there several additional steps you need to follow.
1. Open SSMS
2. Cancel out of the first connection dialog that pops up and click on the New Query button from the toolbar. If you enter credentials without performing this step the following error will appear: (Microsoft is working on this issue).
3. When you are prompted to connect to a database, cancel that dialog.
4. You should be left with an empty management console. Click New Query.
5. For the server name, take the fully qualified host name from your SQL Azure connection string. It should look something like mlqe0vurwb.database.windows.net.
6. Choose SQL Server Authentication as SQL Azure does not support Windows Authentication. Supply the username and password that you used for your database.

7. Click on the Options button on the bottom right of the dialog box
8. Set the database name, manually, to the name of your database. It will not show up in the drop-down list. Please note that in case you need to connect other databases, you have to connect again.
9. Make sure the server type is set to the database engine.
10. Click Connect.

For SQL Azure SSMS does to show the objects in the Object Explorer and you will need to write T-SQL for all your needs.

Visual Studio
You can view the connection strings for the relevant databases by clicking the Connection String button.

Using above connection strings, you can connect to SQL Azure from Visual Studio.
Third Party Tools:
As demonstrated, SSMS may not be a good tool for SQL Azure. There are couple of third party tools that I have been working with:
Omega Web Client
Omega Web Client has a great set of functionalities. It has a great object explorer. You can access this tool online at https://onlinedemo.cerebrata.com/SQLAzureClient/default.aspx and therefore no installation is needed.

Below is the main view of the UI that you will see from this tool.
SQL Azure Manager
SQL Azure Manager is another tool which supports SQL Azure. However there are quite number of draw backs which will be discussed laterò.
You can download this tool from http://hanssens.org/tools/sqlazuremanager/.
The SQL Azure Manager UI is much better than SSMS or the Omega web client.
Though SQL Azure Manager has a much better UI than SSMS 2008 or Omega, it is still lacking a lot of features in the object explorer. For example, it shows the views in object explorer, but does not allow the user to create the alter script for a view. Also, it does not display stored procedures or functions in the object explorer and can only show data of tables with the dbo schema.

SSIS & SQL Azure.

SQL Server Integration Services (SSIS) is used to integrate data between different data sources.
The first thing to note is that SQL Azure currently does not support OLE DB. The normal recommendation for SSIS is to use the OLE DB Source or Destination to access SQL Server. However, if you want to work with SSIS and SQL Azure, you must use the ADO.NET Source and Destinations. This is fine for 2008, but if you are using SSIS 2005, there is no ADO.NET Destination, so you will have to implement your own through a script component. This can be done by creating a source or destination and then using the SSIS control flow tasks and data flow tasks.

After it will be the standard way of write SSIS packages.

When using SSIS for SQL Azure, tables without a clustered index are not supported hence you are required to create a clustered index.

Migrating From SQL Server to SQL Azure.

The SQL Server Migration Wizard helps you to migrate your local SQL Server 2005 / 2008 databases to SQL Azure. The wizard walks you through the selection of your SQL objects, creates SQL scripts suitable for SQL Azure, and allows you to edit / deploy to SQL Azure. You can download SQL Server Migration Wizard from http://sqlazuremw.codeplex.com/

I have copied few screen shots to demonstrate the product. However, it is fairly simple.

After selecting the Source database, next you have to select the required options from the following screen.
You can select either a new database or an existing database with the credentials to login to the SQL Azure database.

Next, the wizard will transfer data and objects to SQL Azure.

Not Supported on SQL Azure:

· The backup command is not available due to the multi-tenant nature of the service. You can use BCP or SSIS instead.
· Server options (sp_configure)
· SQL Profiler
· SQL traceflag
· Data Types like HierachyID, Geography

More Information.

SQL Azure Database:
SQL Server News Blog:
SQL Azure Team Blog:
If you liked this article, subscribe to the feed by clicking the image below to keep informed about new contents of the blog:
Newer Post
Older Post

No comments:

Leave a Reply

My Favorites in Instagram

Catan, Games, Windows 10 Office 2013, Windows, Microsoft Windows 8, Keyboard, Microsoft Windows Phone 8, Microsoft, Mobile windows 7, apps, Microsoft Windows 10, Microsoft, Upgrade Windows Store, Apps, Microsoft Avidemux, Musica, Multimedia Windows, Silverlight, Microsoft Windows 7, Migration, Windows Vista

Recent Posts

Find Us On Pinterest


Find Us On Facebook

Find Us On Google+

Popular Posts


My Ping in TotalPing.com follow us in feedly

Random Posts