Performing a Rolling Update of a Scalable Shared Database

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 can tolerate clients temporarily getting different results on different server instances during the upgrade, a rolling update is appropriate.

A rolling update completes the update on one reporting server at a time. During the interval between the first and final upgrades, the stale and fresh versions of the database are each available to clients on one or more server instances. Generally, a rolling upgrade is completed as quickly as possible to minimize the period in which the two versions overlap.

The following figure illustrates a rolling update.

1. With volume A mounted on all servers and the reporting database attached to the server instances, the reporting database is rebuilt on volume B.
2. Detach the database on volume A from the server instance on the first reporting server, and dismount volume A. Immediately, mount volume B to the first reporting server, and attach the refreshed reporting database to the server instance.
3. Detach the database on volume A from the server instance on the second reporting server, and dismount volume A. Immediately, mount volume B to the second reporting server, and attach the refreshed reporting database to the server instance.
4. Detach the database on volume A from the server instance on the third reporting server, and dismount volume A. Immediately, mount volume B to the third reporting server, and attach the refreshed reporting database to the server instance.
5. With volume B mounted and the refreshed database available as a scalable shared database on all the reporting servers, execute the build of the reporting database on volume A.

Depending on your business requirements, a rolling update can be relatively open ended, to allow current queries to complete, or it can occur within a limited timeframe.

Open-Ended Rolling Updates

In this strategy, a rolling update allows the database administrator to wait for long-running queries to complete on one reporting server, while refreshing the database on another reporting server. This strategy addresses the following business requirements:

• The reporting servers do not need to be kept in sync, that is, stale and fresh versions of the database can temporarily co-exist on different reporting servers.
• You have an unlimited timeframe to accomplish the update, or your deadline is less critical than preserving currently running queries.

To perform this form of rolling update, do the following on each reporting server:

1. Begin the attach phase for the fresh database, by mounting the alternative set of volumes containing the fresh database onto the reporting server.

   Note

   Even for a rolling update, you can perform this step on all of the reporting servers before proceeding to the next step on any of them.

2. Preserve all in-progress queries by stopping I/O activity to the original reporting volume. If a long-running query delays the update on a given server instance, wait for it to complete. After all of the queries complete on the server instance, complete the detach phase on that server.

3. Attach the fresh reporting database to the server instance, making available as a scalable shared database for reporting queries.

Repeat these steps on each of the reporting servers.

Rolling Update of Reporting Servers (Completed Quickly)

In this strategy, a rolling update allows the database administrator to maintain uninterrupted reporting service by briefly allowing the stale version of the database to remain available to new queries on some reporting servers while updating the database on another reporting server. This strategy addresses the following business requirement:

• The reporting servers do not need to be kept in sync, that is, stale and fresh versions of the database can temporarily co-exist on different reporting servers.
• You must complete the update as quickly as possible to met a deadline that is more critical than preserving currently running queries.

To perform a time-sensitive of rolling update, do the following on one reporting server at a time:

1. Begin the attach phase for the fresh database, by mounting the alternative set of volumes containing the fresh database onto the reporting server.

   Note

   Even for a rolling update, you can perform this step on all of the reporting servers before proceeding to the next step on any of them.

2. Stop I/O activity on the reporting volume and optionally wait for short queries to complete on a server instance before detaching its reporting database.

3. Complete the detach phase on that server.

4. Attach the fresh reporting database available to make it available as a scalable shared database for reporting purposes. Queries to the fresh copy of the database may obtain different results than queries to any remaining stale copies of the reporting database.

Such a rolling update ensures that the overall reporting capability is never interrupted. This allows you to tolerate fairly long-running transactions on some of the server instances. However, given the limited timeframe for updating all of the reporting databases, if a long-running query significantly delays the update on a server instance, you will need to terminate that query. The query can be re-run on the same server instance after its reporting database has been refreshed, or the query can be restarted sooner on an already updated server.

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.