The scalable shared database feature provides a solution to scale out a read-only reporting database. Using commodity hardware for servers and volumes, multiple SQL Server instances attach to a single copy of the reporting database stored on the Storage Area Network (SAN). This equates to a single copy of the reporting data files, which reduces storage requirements across your environment.
The reporting database must reside on a set of dedicated, read-only volumes whose primary purpose is hosting the database. After the reporting database is built on a set of reporting volumes, the volumes are marked as read-only and mounted to multiple reporting servers. On each reporting server, the reporting database is then attached to an instance of Microsoft SQL Server and becomes available as a scalable shared database. Once established as a scalable shared database, a reporting database can be shared by clients that use different reporting servers. To query the database, a user or application can connect to any server instance to which the database is attached. For a given version of a reporting database, clients on different servers obtain an identical view of the reporting data, making query results consistent across servers.
Benefits – A scalable shared database presents a number of benefits.
Introduces 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 stores 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. This also benefits reporting environments that make heavy use of work tables and other tempdb objects.
Guarantees an identical view of reporting data from all servers. All attached reporting applications use an identical snapshot of the data, which improves consistent reporting across the enterprise. This assumes that all of the server instances are configured identically. For example, all servers would use a single collation.
Only SQL Server Enterprise Edition supports this cost-effective scale-out solution to support your most demanding reporting requirements. The relational engine was added as part of SSD in SQL Server 2005 edition and we extended the support to SQL Server Analysis Services from the 2008 editions.
Highly recommend reading this KB article on SSD implementation details.
This entry was posted on Friday, September 24th, 2010 at 07:20 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.