Ensuring a Correct Environment for a Scalable Shared Database
A scalable shared database must be on a read-only volume that is accessible over a storage area network (SAN). The reporting servers must be running Windows Server 2003 Service Pack 1 (SP1) or later and SQL Server 2005 Enterprise Edition or later version.
Configuring Your Environment
To ensure that your environment supports scalable shared databases, we recommend that you use the following guidelines:
Verify that the reporting servers and associated reporting database are running on identical platforms.
Whenever you update a reporting server, update any other reporting servers that serve the same scalable shared database or databases. For example, if you apply a Windows or SQL Server software update, service pack, or hotfix update to any reporting server, apply it to all servers.
The scalable shared database feature is tested under a concurrent access workload by up to eight server instances. SQL Server does not enforce an instance limit, but we recommend that you limit your scalable shared database configurations to eight server instances per shared database.
The reporting database must have the same layout as the production database.
To simplify building or updating a reporting database, we recommend using the same path for the reporting database and the production database. This includes using both the same drive letter for the reporting volume and the same directory path for the database. For example, if the production database is on E:\SQLdata, if possible, use E as the drive letter of the reporting volume and \SQLdata as the directory of the reporting database. However, if difference exist, you could write a script using explicit paths to handle the differences.
If the reporting volume uses a different drive letter from the production volume, the following considerations apply:
If you build the reporting database by restoring a database backup, your RESTORE DATABASE statement requires a WITH MOVE clause that specifies the full path of the restored data files.
If your reporting database is a copy of the production database, the FOR ATTACH clause of your CREATE DATABASE statement must list every file and specify its full path when you attach the reporting database.
Note
As a best practice, when mounting a set of reporting volumes onto your reporting servers, use the same drive letter on every server to facilitate managing the volume across the different servers.
A scalable shared database must be on a read-only volume that is accessible over your storage area network (SAN) from all of the reporting servers.
After mounting the reporting volume onto a reporting server, you can ensure that it is correctly mounted and that the data files are accessible by entering the following from the command prompt:
DIR<drive-letter>:\<database-directory>
where <drive-letter> is the letter assigned to the reporting volume and <database-directory> specifies the location of the database's data files on the volume. You should perform this test from each of the reporting servers to ensure that you get the same results for all of them.
To ensure that the reporting volume is read-only, you should attempt to create a file on the volume. The easiest way to do this is to attempt to copy or save a plain text file onto the volume. The attempt should fail.
Note
If you are performing these steps manually, we recommend that you repeat these tests update cycle when remounting the reporting volume to each of the reporting servers. These actions can also be automated.
The server instances should all use the same sort order.
The server instances should all use the same memory footprint.