Scalable Shared Databases enhancements in SQL Server 2008
Posted by decipherinfosys on February 13, 2009
Scalable Shared Databases was a new feature in SQL Server 2005. If you have not looked into this feature yet, you can read this KB article from MSFT to understand the concepts as well as see the steps involved. This scheme provides an alternative approach to scaling out for read only databases. The word “read only” is the key here. Using this scheme, one can mount the same physical drives on even less powerful machines and thus allow multiple instances of SQL Server to use the same set of data files. Reminds you of Oracle RAC where multiple instances work off the same database, however over there it is for both read and write and is much more powerful and of course, the architecture is very different.
So, this scheme does not require data duplication since all those instances work off the same data files but does allow to use the additional processing power of those machines – so, you can take advantage of more CPU, more memory, tempdb. But this does restrict the IO bandwidth since the same set of physical files are being shared across the instances. These are mostly used for offloading reporting (or adhoc read only queries – by Operations folks) load.
So, what’s new in SQL Server 2008 in this regard? When this feature came out in SQL Server 2005, one of the immediate questions raised was whether this is supported for Analysis Services as well? It is supported now in SQL Server 2008. So, a single read only copy of an analysis services database can be now shared across different Analysis Services instances through a virtual IP address. Advantages are obvious ones – such a load balancing architecture allows us to better utilize the disk resources since there is no redundancy of the data, cube processing is not impacted by queries thus allowing us to process a larger number of concurrent queris with faster response times.
This feature is available only in the Enterprise Edition.