Wednesday
Jul072010

Handling SQL Database Sprawl

  • How to place databases
  • When to use SQL Express
  • Using instances to your advantage
  • Decision tree document new SQL databases

Background

We have a number of applications for various pet projects coming up every day at the grind and it seems everyone needs a database to go with them.

Our "go to" SQL server is nearing capacity and I don't want to be seen as the grumpy dba.  So who gets their database and where?

There's options, you know. 

Points to consider

When adding a database to a server already in use you obviously start splitting precious hardware resources like CPU and memory even thinner.

When adding a database to the same instance you start splitting things like TEMPDB. 

Security can be compromised too:  your system databases are all shared instance-wide, so any security entries in MODEL are carried over unbeknownst to any new databases created in that instance.  I've also ran into the neat-o third-party vendor that presumably wanted to proactively prevent support calls caused by Restricted Access, so they gave their service accounts undeservedly privileged access or even prompted for the SA password during their automated installation (I wonder what for...?).

Third-party applications don't synch their release cycles with your company's, so any compliance obligations like Change Control procedures will need to be taken into account when updating this software.

Enter SQL Express

First the pros

While the High Availability and DR capabilities of Express edition are limited you can actually implement a form of log-shipping in SQL Express.  It's outlined in a chapter of this book.  One more reason not to overlook this solution.

Express edition allows you to host the application and data tiers on the same server.  There's a number of benefits to this scenario:

Your trusty application administrators can be turned loose on the server.  If there's a patch needed to get the third-party application working those admins have carte blanche to install and subsequently reboot the server as they can only adversely affect that application.

Generally anything running on SQL Express doesn't need to be configured for point-in-time recovery (PITR) or High Availability which lessens the dba's workload and that of your ever-paranoid backupadmin.

The cons

There's officially a size limitation to the databases you can host with SQL Express.  While I've personally never ran into more than Event Viewer annoyances you probably shouldn't procure software that won't scale.  Most likely that limitation is imposed on data files and not transaction logs (reference).

Hell...if the size limitations rear their ugly heads just start truncating the table data.  Your data retention policies for these one-off apps can easily be accommodated by performing well-timed full backups.

Performance will be limited.  SQL Express is not going to handle "enterprise-class" load.  Per the specs listed here it supports a disappointing 1GB memory, single proc, and 4GB storage.  (Though technically it will install on multiprocessor machines and will occasionally exceed the RAM usage as noted in the above-mentioned link.)

MySQL, anyone...anyone?

There's other freebie solutions out there besides SQL Express.  Don't rule them out if you're on a tight budget.

Instances

Consider configuring additional instances on existing SQL servers, but be aware of the limitations.  You can enable the SQL Browser service (responsive on TCP 1434 by default) to direct users to the appropriate db.

The benefit of a separate instance is that separate system databases and even registry spaces will be carved out for your one-off application.

Conversely SQL Browser is generally considered a bad/lazy practice.  Alternatively you can configure a SQL alias so users connecting don't have to specify the port numbers in SSMS or connection string properties.  Additional firewall holes will need to be punched.

Payoff - So how to decide?

I recently spent several hours of my life putting together a decision tree ("matrix?") for where we should put every new SQL database we have.  It was time well spent, but hopefully this helps someone else out there.