Wednesday 20 July 2011

Microsoft SQL Azure (Cloud Server)

Microsoft has started working on cloud and as of now been released few products based on cloud computing – if you have heard buzz called SQL Azure – so probably be thinking what is Microsoft SQL Azure.
SQL Azure is cloud based technology for people who want to work with RDBMS model in cloud space.
You just think of one scenario you are working in organization where you have to manage databases that include software to manage it and dedicated server definitely and after few months you probably asking for some patched to fix some database related issues. This is not the end- you have to manage high availability, disaster recovery that may increase overall cost if your planning is to scale up or scale out.

Other Cloud Database may be USED for such functionality.

But with a cloud technology especially you can manage hosted database – this will probably decrease your cost overall. But with cloud technology you will have to face scaling issues.

Why SQL Azure in Cloud?

SQL Azure is an environment for managing RDBMS databases on cloud they are already distributed on different node.
SQL Azure is not like an environment where you have to deploy your databases on different machines for different purposes SQL Azure is already deployed on different sql instances.
You do not have to worry about its patch or you have to manage it physically, just do all stuff on cloud with all RDMBS feature plus you can pay some cost and scale up your RDMBS environment. This means you are saving physical architecture cots of RDMBS environment when you need it to scale out.
SQL Azure provides you scalability, availability and reliability anyway. Azure is an environment of clustered SQL Servers based in cloud in Microsoft data centers.

SQL statements not supported for SQL Azure

When working with SQL Azure, you primarily have to work with Transact SQL (TSQL) instead of a graphic user interface. There is limited functionality that is available that will give you somewhat of a basic user interface for working with SQL Azure. As such you need to have a good grasp on Transact SQL.

Having said that, you will find that quite a few SQL statements are just not supported for SQL Azure Cloud databases. Some of these make sense as they interact with the operating system or the underlying hardware. Remember SQL Azure is in the cloud environment so an end-user will have limited need for Hardware/OS related issues. Regardless we have compiled a few SQL commands that you may have used in your on-premise SQL Server 2008, however they will simply not work in SQL Azure. Few select statements you can use supported in  SQL Azure environment.

Some facts about Microsoft SQL Azure:

a)        Microsoft has released backup functionality in SQL Azure just copy object.
b)       Microsoft may Support full text indexing in SQL Azure.
c)        Each Table must have clustered index. Tables without a clustered index are not supported.
d)       Each connection can use single database. Multiple databases in single transaction are not supported.
e)        ‘USE DATABASE’ cannot be used in Azure.
f)        Global Temp Tables (or Temp Objects) are not supported.
g)       As there is no concept of cross database connection, linked server is not the concept in Azure at this moment.
h)       SQL Azure is shared environment and because of the same there is no concept of Windows Login.
i)         Always drop TempDB objects after their need as they create pressure on TempDB.
j)         During buck insert use batch size option to limit the number of rows to be inserted. This will limit the usage of Transaction log space.
k)       Avoid unnecessary usage of grouping or blocking ORDER by operations as they leads to high end memory usage.
l)         Microsoft may support rich Reporting Services.
m)      Distributed transaction not supported in SQL Azure Cloud.
n)       Select Into statement is not supported in SQL Azure.

Now, ready to take off – install SSMS 2008 R2 to create your own database in cloud and user database can be 1gb to 5gb on web edition. SQL Azure database support standard API like ODBC, ADO.NET these two technologies are very famous to manipulate data.

1 comment: