Newsletters




SQL in the Cloud, Feet on the Ground


Bookmark and Share

SQL Azure is Microsoft's cloud-based relational database service hosted in their data centers, and it's got some DBAs worrying about the future. The more I look at the technology, however, the more I see practical applications for it and the less I think people should be concerned.

Working with smaller companies, I often find SQL Server struggling along without a specialist DBA. It is generally an exercise in keeping your head above water and hoping nothing fundamental goes wrong. By moving suitable applications to the cloud, companies can remove the burden of worying about areas such as hardware, high availability, and patching. Microsoft will manage resource allocation and transparent failover, but normal database and security administration is still in the hands of the IT department. You have no control over CPU, memory, or storage configuration, but you still manage schemas, indexes, statistics, and query optimization.

Any organization considering SQL Azure should be aware of some important limitations, which are fundamental to the decision process:

Editions

Web: maximum of 1 or 5GB of data.

Business: maximum of 50GB of data, charged in increments of 10GB.

For example, chargeable size is calculated purely on your objects, data, and indexes, not system tables. You can have multiple databases, but you cannot query across them. You are charged by edition, size, and bandwidth usage.

Growth is automatic, as is switching between editions; you just incur increased costs. You can set a maximum size to prevent excessive costs; however, this will prevent data inserts when the threshold is met.

Security

SQL authentication only-for obvious reasons, integrated security cannot be implemented, but this may be an issue for your security model.

Unsupported features compared to SQL Server 2008 include:

  • SQL Agent
  • Profiler
  • Replication
  • Database Mirroring
  • Service Broker
  • Log Shipping
  • Reporting Services

    Trace Flags

  • Analysis Service
  • Database Tuning Advisor
  • Resource Governor

The two that really stand out for me here are SQL Agent, so fundamental for scheduling anything with SQL server, and Profiler, which, particularly for an in-house developed application, is like trying to drive without looking at the road.

Interestingly, every table must have a clustered index-my heart jumped a little when I read this-finally a way to enforce this.

Timeouts

Long-running queries and idle connections will be closed. Also, excessive resource usage will result in the closing of a connection. I struggled to find a definition of what excessive resource usage would equate to, but this is an inevitable constraint, as others will be sharing the resources allocated to your database, much like your ISP's "fair usage policy."

Disaster Recovery

Backup and restore are not supported, which is terrifying to the traditional DBA. Microsoft will handle high availability and restores if necessary, but this is not enough. If you need to roll back to a previous point in time, you will need to take regular extracts of your data. Alternatively, Microsoft allows you to take copies of your database to another Azure database with relevant costs.

With these limitations taken into account, SQL Azure, as it stands, is not a good fit for high end, volume transaction, large storage environments, but perfect for a segment of the SMB market-particularly companies with no DBA and limited budgets, such as a startup company looking to keep costs down until its business shows success.

Further information is available at www.microsoft.com/en-us/sqlazure/default.aspx.     

 


Sponsors