Scale Out Reporting with SQL Server SSD

Unless you've been under a rock for the past few weeks, you know that SQL Server 2005 launched in November with much fanfare. But you might not know that Microsoft sneaked a nifty new feature into the product at the last minute. SQL Server Scaleable Shared Databases (SSD) will be a new weapon in your arsenal when you need to create a scale-out reporting environment for read-only data, which is, of course, a relatively common need in large and midsized environments.

I had the chance to chat by email with SQL Server Storage Team Program Manager Kevin Farlee about this new feature. Kevin has more than 15 years in the database world and deep experience with the database-engine and server-storage layers. His career spans stints at Digital, Legato, and EMC. He joined Microsoft last May and is now one of the key people responsible for SQL Server SSD.

As Kevin explained, "The scalable shared database (SSD) feature enables the scale-out of databases that are used exclusively for reporting (read-only access) with periodic updates. Specifically, SSD allows you to attach a read-only reporting database to multiple server instances over a storage area network (SAN). This means that you can use multiple commodity servers to scale out your reporting access without having to copy the database and multiply your storage requirements." I asked Kevin what he thinks SSD's major benefits are. Here's a list of the major benefits he described:

Provides workload scale-out of reporting databases that are using commodity servers. A scalable shared database is a cost-effective way of making read-only data marts or data warehouses accessible to multiple-server instances for reporting purposes, such as running queries or using SQL Server 2005 Reporting Services.

Provides workload isolation. Each server uses its own memory, CPU, and tempdb database. This prevents a runaway query from monopolizing all resources and affecting other queries.

Guarantees an identical view of reporting data from all servers. This assumes that all of the server instances are configured identically. For example, all servers would use a single collation.

Thanks to Kevin for sharing some of his insights about this new feature! You can learn more about SSD by reading the Microsoft article "Scalable shared databases are supported by SQL Server 2005" at http://lists.sqlmag.com/t?ctl=1C2F7:285886 and reviewing the SSD topic in the newest version of SQL Server 2005 Books Online, available at http://lists.sqlmag.com/t?ctl=1C2DE:285886 . Please note that the version of Books Online that shipped with the SQL Server 2005 RTM bits doesn't discuss SSD.

SSD is a handy new feature, but please don't think it's a panacea for every possible reporting scale-out need you might have. This new feature lets you scale out the server instances accessing the data, but each instance is still hitting the same database files on the same SAN. So, SSD is likely to produce large gains if the reporting workload is CPU intensive or the database is small enough to ensure that large parts of it can fit in RAM. (Note: "small" today is a lot bigger than it used to be because of the availability of 64-bit SQL Server and large-memory servers.) Conversely, SSD isn't likely to produce significant gains if the reporting workload on a single instance is I/O bound and the SAN is already having a hard time keeping up. Never forget that understanding your workload is a key first step before embarking on any sort of long-term plan for a scale out strategy.