Building or Refreshing a Reporting Database
This topic describes how to build or refresh a reporting database that is intended for use as a scalable shared database. A reporting database might be a copy of a production database, as in this figure, or the reporting database might contain reporting data imported from one or more production databases. The process of building or refreshing a reporting database is known as the build phase or refresh phase, depending on whether a new reporting database is being built or a stale reporting database is being refreshed.
The life of a scalable shared database begins by building a new reporting database on a set of reporting volumes. Eventually, the reporting data becomes too outdated to be useful, and the reporting database is said to be stale. A stale reporting database is refreshed as part of each update cycle. Refreshing a stale reporting database involves either updating its data or building a completely new, fresh version of the database.
To build or refresh a reporting database, you can use any of the data-copy methods provided by SQL Server for copying data or databases. For more information, see "Methods for Building or Refreshing a Reporting Database," later in this topic.
Methods for Building or Refreshing a Reporting Database
Note
Before a reporting database is built or refreshed, its set of reporting volumes must be mounted on the production server and each of the volumes must be marked as read-write. For a description of all of the steps in this process, see "Steps for Building or Refreshing the Database," later in this topic.
SQL Server 2005 and later versions support the following methods for building or refreshing a reporting database:
SQL Server Integration Services (SSIS)
You can create or copy a database by running SSIS packages and using the Execute SQL task or the Transfer Database task:
The Execute SQL task runs SQL statements or stored procedures from a package. Using the Execute SQL task you can create a database by running a CREATE DATABASE statement and then populating the database by copying in one or more tables or views. For more information, see Execute SQL Task.
The Transfer Database task can copy a database within the same server instance or between instances. For more information, see Transfer Database Task.
Note
You can also create a database using the SQL Server Import and Export Wizard, but you must copy at least one table or view. For more information, see Importing and Exporting Data by Using the SQL Server Import and Export Wizard.
Backup and restore
You can restore a backup of a production database onto the reporting volume. This involves restoring and recovering a full database backup on to the reporting volume.
If you are using the same drive letter, mount the reporting volume on to a different host and connect to a server instance there to restore the database.
If the reporting volume uses a different drive letter from the production volume, you must add a WITH MOVE clause to your RESTORE DATABASE statement that specifies the drive letter of the reporting volume in the pathname of the restored database.
For information about using backup and restore to copy a database, see Copying Databases with Backup and Restore.
Copying the production database
Before you can copy a database manually or use the Detach and Attach Method of the Copy Database Wizard, you must take the database offline, and after copying it, bring it back online. The Copy Database Wizard, however, offers an alternative method, the SMO Transfer method, that copies the database while it remains online. Though slower than the Detach and Attach Method, the SMO Transfer method has the advantage of preserving active connections to the database.
For more information, see Using the Copy Database Wizard.
Note
We recommend that you always use the same pathname for the production and reporting databases when building a reporting database, and, if possible, the same drive letter for the production and reporting volume (when mounted on the reporting servers).
It might be beneficial to optimize the reporting database for the anticipated query workload. Such optimizations could include:
Updating or recreating statistics on affected tables and indexes as needed.
Rebuilding indexes with a FILL factor of 100.
Putting the reporting database into simple recovery mode and performing a checkpoint to truncate the transaction log and save log space.
Note
For guidelines about how to use a reporting database as scalable shared database, see Ensuring a Correct Environment for a Scalable Shared Database.
When the database is ready, you must mark the reporting volumes as read-only and dismount them from the production server. For more information, see "Steps of the Build or refresh phase," later in this topic.
Steps of the Build or refresh phase
Note
Before you can refresh a reporting database, its set of reporting volumes must have been dismounted from all of the reporting servers. For more information, see Detaching a Scalable Shared Database.
To build or refresh a reporting database, do the following on the production server:
Using your hardware vendor's utilities, unmask the logical unit number (LUN) corresponding to each reporting volume to make the volume accessible to the production server. If the database uses multiple reporting volumes, perform this step for each of the volumes.
Mount each reporting volume and mark it as read-write. The DiskPart list volumes command, which displays all disks and volumes on a computer, is useful for obtaining volume information. To use the DiskPart utility to mount the volume, at the command prompt, enter the following commands:
DiskPart
DISKPART> select volume=<drive-number>
DISKPART> assign letter=<drive-letter>
DISKPART> attribute clear readonly
DISKPART> exit
Where <drive-number> is the volume number assigned by the Windows operating system and <drive-letter> is the letter assigned to the reporting volume. If the database uses multiple reporting volumes, perform this step for each of the volumes.
If you are refreshing an existing reporting database:
Attach the database to a server instance (typically, this would be the production server instance).
For more information, see CREATE DATABASE (Transact-SQL) or How to: Attach a Database (SQL Server Management Studio).
Set the database to read write access.
You can accomplish this by using the following Transact-SQL statement:
ALTER DATABASE <database_name> SET READ_WRITE
Build or refresh the database.
The administrator builds or refreshes the database using any of the methods building or refreshing a reporting database. The reporting database can use any valid database name. For more information, see "Methods for Building or Updating a Database," earlier in this topic.
Note
In reporting databases, we recommend that PAGE_VERIFY be set to CHECKSUM, the default. For more information, see ALTER DATABASE (Transact-SQL).
Detach the database from the production server instance.
You can accomplish this by using the following Transact-SQL statement:
sp_detach_db @dbname='<database_name>'Where <database_name> is the name of the database.
Note
By default, sp_detach_db runs UPDATE STATISTICS to resample the statistics. However, some implementations might require you to run UPDATE STATISTICS separately with the FULL SCAN option.
Mark each reporting volume as read-only and dismount it from the production server.
To use the DiskPart utility to dismount the volume, at the command prompt, enter the following commands:
DiskPart
DISKPART> select volume=<drive-number>
DISKPART> attribute set readonly
DISKPART> remove
Where <drive-number> is the volume number assigned by the Windows operating system and <drive-letter> is the letter assigned to the reporting volume. If the database uses multiple reporting volumes, perform this step for each of the volumes.
Important
Reporting volumes must be read-only before being dismounted from the production server.
The reporting database can now be made available as a scalable shared database. For more information, see Attaching a Reporting Database as a Scalable Shared Database.
See Also