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 2005 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

SQL Server 2005 currently supports 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 Creating Packages 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.

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.

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

To build or refresh a reporting database, do the following on the production server:

1. 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.

2. 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.

3. 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
     

4. Build or refresh the database.
   The administrator builds or refreshes the database using any of the methods provided by SQL Server 2005 for copying data or databases. 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).

5. 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.

6. 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

Concepts

Attaching a Reporting Database as a Scalable Shared Database
Detaching a Scalable Shared Database
Ensuring a Correct Environment for a Scalable Shared Database
Maximizing Availability of a Scalable Shared Database
Overview of Scalable Shared Databases

Other Resources

ALTER DATABASE (Transact-SQL)
sp_detach_db (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance