Scalable Shared Databases supported by Sql Server 2005 RTM
http://support.microsoft.com/Default.aspx?kbid=910378
This sounds really sweet. There have been instances where I could have used this feature over the years.
Copied from the KB article:
Scalable shared databases
The scalable shared database feature allows you to attach a read-only reporting database to multiple server instances over a storage area network (SAN). A reporting database is a read-only database that is built from one or more production databases that are used exclusively for reporting purposes. To be made into a scalable shared database, a reporting database must reside on one or more dedicated, read-only volumes whose primary purpose is to host the reporting database or a coordinated set of reporting databases. These volumes are known as reporting volumes.
Benefits
Scalable shared databases offer the following benefits:
| • | Provide workload scale-out of reporting databases 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. |
| • | Provide workload isolation. Each server uses its own memory, CPU, and tempdb database. |
| • | Guarantee 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. Note Optionally, you can update the reporting database on a second reporting volume. For more information, see the "Maximizing the availability of a scalable shared database" section. |
Restrictions
The following restrictions exist for a scalable shared database:
| • | The database must be on a read-only volume. |
| • | The data files are accessible over an SAN. |
| • | The scalable shared database feature is supported by Windows Storage running only on Microsoft Windows Server 2003 Service Pack 1 (SP1) or a later version of Windows Server 2003. |
Update cycle of a reporting database
Using the scalable shared database feature for a reporting database involves a three phase update cycle. The update cycle of a reporting database begins with a build phase. The attach phase, which makes the database available as a scalable shared database, comes after the build phase. The attach phase must be performed on each of reporting servers individually.
The third phase is the detach phase. Typically, the current version of a reporting database eventually becomes stale. The database must be refreshed to keep the reporting data up to date. The process of removing a stale reporting database from service as a scalable shared database is known as the detach phase. Before you can make an updated reporting database available on a given reporting server, the detach phase must be completed on that server.
Note As a best practice to maximize the availability of reporting data, we recommend that you alternate update cycles between two reporting volumes. While the first reporting volume is still mounted to the reporting servers, you can mount the second volume to the production server and build an up-to-date version of the reporting database. For more information, see the "Maximizing the availability of a scalable shared database" section.
Each of the three phases consists of a series of steps that must be performed by a user who has Database Administrator rights. In this article, that user will be referred to as the database administrator.
Important Configuring a scalable shared database requires that the storage area network environment is already working properly.
The three phases of the update cycle are as follows:
| • | Build phase: Before a reporting database can be built, the administrator mounts the reporting volume on the production system and makes it read-write. It is crucial to observe that when a volume is in a read-write state, it can only be mounted on a single system, or filesystem corruption could occur. The administrator then builds the database by using one of the data-copy methods provided by SQL Server 2005 for copying data or databases. After the database is built, the administrator sets the volume to read-only, and then dismounts it. |
| • | Attach phase: This phase makes a reporting database available as a scalable shared database. In preparation for configuring the reporting database as a scalable shared database, the administrator mounts the read-only reporting volumes onto a reporting server over the SAN. After making sure that each volume is set to read-only, the administrator attaches the reporting database on an instance of SQL Server. The reporting database on an instance of SQL Server is also known as a reporting server instance. Because each reporting volume is read-only, attaching the database sets it to read-only. At this point, the reporting database becomes a scalable shared database that can be accessed by clients using that reporting server. The attach phase is required on each of the reporting servers individually. Note If you use a second reporting volume when updating the reporting database, you choose between performing a rolling upgrade or a synchronized upgrade. For more information, see the "Maximizing the availability of a scalable shared database" section. |
| • | Detach phase: When a reporting database needs to be refreshed, it must be detached from all of the server instances. To begin the detach phase, the database administrator first stops the query work load that is coming in to the database from all of the server instances. On each of the server instances, the database administrator then obtains exclusive access to the database and detaches it. The database administrator then dismounts the volume from each of the host systems. When the detach phase completes, the reporting volume is disconnected from the SAN. |