If your company is planning on using the Azure platform and SQL Azure for a high load OLTP application, you should change the way you think about application and database design.
The limitation on SQL Azure described in my former post, will force you to think differently regarding scaling your applications. Application wise, you must take into consideration that long running queries will be disconnected by the SQL Azure server and connections which are open for a long period over the internet, tend to disconnect due to network issues.
To limit the effect of these issues you should implement the following:
- Connection polling in your application (built-in feature using .Net), will reduce the load on the database server. Connection polling also saves resources on creation and reduces the latency of creating a connection over the internet.
- Use short transactions\batches, open the connection in the last second, execute the query commit and close the connection as soon as possible.
- Implement retry mechanism in the events you get disconnected or unable to create the connection over the internet.
When you considering scaling options, since you don’t have any control over the hardware resources, scaling out is your only option. You have several options for scaling out, depending on your application. All of them will require a logic method in your application that will select the appropriate connection string, or in other words: your application must be partition aware.
Let’s review some of the options:
- One Database per client- if you are a SaaS provider, you can open a database per client. This option has a few advantages: privacy and security wise, each client has its own database. Billing wise, you can bill your client just for SQL Azure recourses their database is using (see below: Master DB).
- Configuration Database – if your database holds static information, updated once in a long period of time, you can create multiple copies of your database using Sync Framework to synchronies the changes from the primary one. You will also need to add a round robin method on your application side to select the connection string and distribute the load on the different instances.
- Logical partitioning or Elastic provisioning– what if your application design allows that? You might be able to separate your data according to a natural key. Could be as easy as modulo 10 your client user id or opening a database per country\game\event … in your application. Of course, that too will require changes in the application connection string module logic. One company all ready implementing such a solution is www.ticketdirect.com. You can read more on Microsoft Case Studies.
I just had the privilege to join a closed Microsoft Connect session on SQL Azure . It seems that some new exiting features are on the way, which will help with scaling. Some of the performance execution limitations will be reduced and a new clone feature is being developed to help with instantly creating copies of your database for scale out usage and backup.
To enable the partitioning process, Master DB has changed considerably: it no longer sits at the center of the SQL Server and two new role gateways were added to login operation and logger of usage operations.
The login process uses two tables of sys.sql_logins for the login operation a new table sys.firewall_rules for configuring access to the “DataBase Server”. A successful login must be backed up with valid data in both tables (information about the login process can be found in my former post).
The second role as an activity logger for billing process is back up by two new tables named sys.bandwidth_usage sys.database_usage. By monitoring the values on these tables, you can calculate the operation cost of any single DB and add it to your client billing quote or monitor the operation cost of an event on your system.
Guy