Scalable Shared Databases Overview
The scalable shared database feature allows you to scale out a read-only database built exclusively for reporting purposes (a reporting database). The reporting database must reside on a set of dedicated, read-only volumes whose primary purpose is hosting the database. Using commodity hardware for servers and volumes, you can scale out a reporting database that provides an identical view of the reporting data on multiple reporting servers. This feature also permits a smooth update path for the reporting 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 2005 or later versions and becomes available as a shared scalable database. Once established as a scalable shared database, a reporting database can be shared by clients using 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
Scalable shared databases offer the following benefits:
Scale out of the workload on your reporting databases by using commodity servers and hardware.
A scalable shared database is a cost-effective way of making a read-only data mart or data warehouses accessible to multiple server instances for reporting purposes, such as running queries or using Reporting Services.
Workload isolation.
Each server uses its own memory, CPU, and tempdb database, which prevents one poorly-tuned query from monopolizing all your server resources.
An identical view of reporting data from all servers.
This assumes that all of the server instances are configured identically, for example, that they use a single collation.
Note
You can update the reporting database on a second reporting volume. For more information, see Maximizing Availability of a Scalable Shared Database.
Restrictions
Scalable shared databases have the following limitations:
The database must be on a read-only volume.
The data files are accessible over a SAN.
The databases are supported by Windows Storage running only on Windows Server 2003 SP1 or later.
We recommend that you limit your scalable shared database configurations to eight server instances per shared database.
Scalable shared databases do not support database snapshots.
Important
Configuring a scalable shared database requires that the storage area network environment is already working properly. For guidelines and recommendations for using a scalable shared database, see Ensuring a Correct Environment for a Scalable Shared Database.
Building and Scaling Out a Reporting Database
To configure a new scalable shared database, a database administrator begins by building a new reporting database on a set of reporting volumes or by refreshing a stale version of the reporting database on them (the build or refresh phase). The administrator then scales out the database by configuring it as a scalable shared database on multiple server instances (the attach phase).
The following figure illustrates building a new reporting database using a single reporting volume and attaching the reporting database to make it available as a scalable shared database.
.gif "Scalable shared database using 1 reporting volume")
The build phase in the figure illustrates the process of mounting the reporting volume on the production server and building the reporting database. After being mounted onto the production system, the volume is marked as read-write. Then a reporting database is built on the volume using one of the data-copy methods provided by SQL Server 2005 and later versions for copying data or databases. The reporting database in this figure is a copy of a complete production database. After building the database, the administrator sets each reporting volume to read-only and dismounts it.
The attach phase in the figure illustrates making the reporting database available as a scalable shared database. First, the administrator mounts the read-only reporting volume onto multiple reporting servers over the SAN. Then, on each reporting server, the administrator attaches the reporting database to an instance of SQL Server. The database is attached as a read-only database because the volumes are read-only. When this process is completed on a given reporting server, the reporting database becomes a scalable shared database on that server. However, the attach phase as a whole continues until the database is attached on all of the reporting servers.
A given version of a reporting database remains available as a scalable shared database for as long at is remains attached on any of the reporting servers.
Updating a Set of Reporting Volumes
Because a reporting database is read-only, typically, it eventually becomes stale and must be refreshed to bring the reporting data up-to-date. For a scalable shared database configuration, the complete process of replacing a reporting database on a given set of reporting volumes with a fresh version of the same database is known as an update cycle.
The Update Cycle
The update cycle begins with the detach phase, which ends with dismounting all of the reporting volumes from all of the reporting servers. Next comes the refresh phase (this is equivalent to the build phase of a new reporting database). The refresh phase ends with a fresh, fairly up-to-date version of the database on read-only volumes than are currently not mounted on any server. Finally, the database is established as a scalable shared database during an attach phase that involves the same steps as used to attach a new reporting database.
Detach phase
The first stage of an update cycle removes the stale database from the scalable shared database configuration on each of the reporting servers. The process of removing a stale reporting database from service as a scalable shared database is called the detach phase of the update cycle. Before you can make a fresh version of a reporting database available on a given reporting server, this phase must be completed on that server.
To begin removing the database, the database administrator stops the query work load coming into the database from each of the server instances. Then, on each reporting server, the administrator detaches the database. On being detached from the last server instance, the reporting database ceases being a scalable shared database. To complete this phase, the administrator dismounts the set of reporting volumes containing the stale database.
Refresh phase
The next phase of the update cycle involves refreshing the database on the same set of reporting volumes. Refreshing the database involves either updating it, for example by importing current production data, or rebuilding it, for example by restoring a recent backup of the production database. The preferable method of refreshing a database depends on your business requirements.
Attach phase
To finish the update cycle for a set of reporting volumes, the administrator must scale out the refreshed database. If only one set of reporting volumes is used for a scalable shared database configuration, the attach process during an update is equivalent to the original attach process.
Alternating Versions of the Database Between Two Sets of Reporting Volumes
To maximize availability of a scalable shared database configuration, you can use two, alternate sets of reporting volumes. This allows to overlap the update cycles of a stale database and a fresh database. The fresh reporting database resides on a different set of volumes. Before detaching the stale version of the database and dismounting its volumes, you can refresh the database on the alternate set of volumes and mount those volumes on the reporting servers. Then, when you detach the stale version of the database from a given server instance, you can immediately attach the fresh version.
For more information, see Maximizing Availability of a Scalable Shared Database.