Jaa

Performing a Synchronized Update of a Scalable Shared Database

  • Artikkeli

If you use alternate sets of reporting volumes for updating a scalable shared database configuration, you can update the database by either a rolling update or a synchronized update. If you want your reporting database to return identical results to clients, perform a synchronized update. In a synchronized update, you detach the old version of the database from all of the server instances before attaching the new version to any of them.

Follow this strategy if:

  • You must keep the reporting database in sync to return identical results to clients on all reporting servers. That is, stale and fresh versions of the database must never co-exist.
  • Optionally, you must complete the update as quickly as possible to meet a deadline that is more critical than preserving currently running queries.

Synchronizing the database on all of the reporting servers means that the reporting database becomes unavailable temporarily between the detach phase for a stale version of the database and the attach phase of the fresh version. To minimize downtime for the database, you can start the attach phase for the fresh database on all the servers, then complete the detach phase on all the servers. Finally, attach the database on each of the reporting servers as quickly as possible.

Synchronizing Detach and Attach Phases

To synchronize the update cycle on all of the server instances and, optionally, complete the update cycle as quickly as possible:

  1. On each of the reporting servers, begin the attach phase for the fresh database, by mounting the alternative set of volumes containing the fresh database onto the reporting server.
  2. Complete the detach phase on all of the reporting servers. For a time-sensitive update, terminate any long-running queries before detaching the stale database and dismounting the reporting volumes.
  3. Attach the fresh database to the server instance on each of the reporting servers. As soon as the database is attached on a given instance, terminated queries can be restarted on that instance.

The following figure illustrates the use of a pair of alternating reporting volumes to maintain a single scalable shared database that is synchronized on all of the server instances.

Scalable shared database using 2 reporting volumes

The figure illustrates two-and-a-half update cycles for a reporting database using a pair of alternating reporting volumes. Initially, no reporting database exists.

  1. Building a new reporting database on volume A: One of the two reporting volume, A, is mounted on the production server and marked read-write. The initial version of the reporting database is built. Then, the reporting volume is marked read-only, and it is dismounted.

    Note

    The alternate volume is not yet in use.

  2. Attach phase for volume A: The volume is mounted as a read-only volume on each of the reporting servers and attached to each of the server instances.

  3. While the reporting database is available as a scalable shared database on volume A, the other reporting volume, B, is mounted on the production server and marked as read-write to build a fresh, up-to-date version of the database for the second update cycle. When the fresh version of the reporting database is ready on volume B, that volume is dismounted.

  4. The detach phase for volume A and the attach phase for volume B are overlapped:

    • First, the alternate volume, B, is mounted on the reporting server.
    • Next, the stale database, on volume A, is detached.
    • Then, the refreshed reporting database, on volume B, is attached to each of the reporting servers.
    • Finally, volume A is dismounted from each of the reporting servers.

  5. While the reporting database is available as a scalable shared database on volume B, the other reporting volume, A, is mounted to the production server and marked as read-write to in preparation for refreshing the stale version of the reporting database. This process might involve updating the existing database or building a completely new database.

See Also

Concepts

Overview of Scalable Shared Databases
Ensuring a Correct Environment for a Scalable Shared Database
Building or Refreshing a Reporting Database
Attaching a Reporting Database as a Scalable Shared Database
Detaching a Scalable Shared Database

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

5 December 2005
New content:
  • Added this topic about the new scalable shared database feature.