Sql Azure is Microsoft’s solution for relational database cloud computing, it comes as part of Azure platform. As with the Azure platform in general, to implement such a solution many limitation were placed on the Azure version of SQL server. The implantation as technical solution is amazing and requires some IT background to figure the complexity of the solution.
The best place to start the explaining SQLAzure is with describing the infrastructure. As a user you are granted with a token, this token is used to create all your databases. First DB to set up is the Master DB, very similar the SQL Server Master DB(more on the functionality of the Master DB in the next post). All traffic to the SqlAzure “Server” is routed through a firewall that allows you to limit the accesses to specific sources using a well design web interface. Then the connection is rerouted to a gateway that diverts the connection to the current server hosting the online copy of your database. All SQL server instances that build the SQLAzure platform are installed on cluster machines to support high availability to improve availability the created database is automatically replicated across different machines . In the case of failure the gateway will automatically point to the current online database instance.
To increase scalability of your application and to maintain overall balances of the SQLAzure platform servers, your databases are placed across multiple servers. I.E. even though it looks like it, physically the your company databases are not placed on one server! What actually happens is that several companies share the same hardware! So how do you reach a specific database? The trick is in the login : the connection string must include the destination database, your connection undergoes authentication using the login setting saved in the Master DB and then diverted to the requested database that will probably be located on a different server.
Farther more to allow a fair resource distribution between the different databases and SQLAzure clients, several limitations are set on the databases settings and resource utilization.
A detailed list of the limitation can be found in the following link, I bring the highlights:
- The major change is that can’t manipulates local resources:
- There is only one file group per database.
The following technical resources are a good starting point to understand the SQLAzure platform :
- No partitioning.
- No fill factor.
- No user define data types.
- No full text indexes.
- No memory settings or any other server level settings.
- All tables must include a clustered index.
- Each connection can only function in the boundaries of the database it is connected to.
- No Trace or Profiler, due to the fact that when you connect to a specific DB you can interact with the Master DB .
- Can’t use the USE <database> command.
- Can’t use distributed transactions or queries.
- Can’t use global temp tables.
- No service broker
- All logins are sql server logins, there is no domain support.
- Maximum database size of 10G.
- No SSIS or SSAS support
Connection throttling, to support fair resources distribution, is placed in the form of limitation on
- Execution time limited to 5 min
- Maximum open connection time
- Limiting long running transactions and extensive resource usage CPU and memory.
These limitations will actively disconnect the client connection and STOP QUERY PROCESSING. Selecting the 10 GB (Business Edition) version over the 1 GB (Web Edition) version will grant your database with more resources and raise the limitation settings.
It is evident from the description above that SQLAzure is not a straight forward solution for high load OLTP databases or data warehouse implementation. Working tips scalability design considerations and more on the next blog.
Guy