Share via

  • Article

Detaching a Scalable Shared Database

Eventually, the reporting data becomes too outdated to be useful, and the reporting database is said to be stale. Updating a scalable shared database involves a three-phase update cycle, which begins with the detach phase. Essentially, the detach phase involves detaching a stale reporting database and dismounting the reporting volumes from the reporting servers. On being detached from a given server instance, the stale reporting database ceases to be a scalable shared database on that server instance.

The Steps of the Detach Phase

In this phase, you carry out the following on each of the reporting servers:

  1. Optionally disable new queries on the database and allow current queries to complete gracefully. For more information, see "Strategies for Preparing to Detach a Stale Reporting Database," later in this topic.

  2. Detach the database from each of the server instances.

    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.

  3. Dismount the set of reporting volumes on each of the reporting servers.

    To dismount a volume by using the DiskPart utility, enter the following commands at the command prompt:

    DiskPart

    DISKPART> select volume=<drive-number>

    DISKPART> remove

    DISKPART> exit

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

  4. Mask the logical unit number (LUN) corresponding to the reporting volume to hide it from the reporting servers. To do this, use your hardware vendor's utilities. If the database uses multiple reporting volumes, perform this step for each of the volumes.

Note

The detach phase is the first phase of an update cycle for a given set of reporting volumes. However, you can use two, alternate sets of reporting volumes for the stale and fresh versions of a reporting database. This allows you to overlap the detach and update phases of the two sets of volumes. For more information, see Maximizing Availability of a Scalable Shared Database.

Strategies for Preparing to Detach a Stale Reporting Database

When replacing the stale version of a database, it is important to consider the business requirements for your reporting environment. You should assess which of the following business requirements is more important: allowing currently running queries to complete, or completing the update as quickly as possible.

Based on which requirement is more important, you can decide how to manage the detach phase on each of your reporting servers.

  • Allowing queries to complete

    To preserve all in-progress queries, begin the detach phase by stopping the flow of transactions to the database, for example by stopping I/O activity. Then, on each server instance, wait for all of the current queries to complete. When the database has been detached from all of the server instances, you can dismount the reporting volume.

  • Updating the database as quickly as possible

    To complete the update quickly, obtain exclusive access to the database on each of the server instances by terminating queries immediately or after a specified number of seconds. Terminated queries can restart after a refreshed version of the database is attached.

    For example, to give current queries 60 seconds to complete before terminating any remaining queries, use the following Transact-SQL statement:

    USE master;
ALTER DATABASE AdventureWorks2008R2
SET SINGLE_USER
WITH ROLLBACK AFTER 60;
GO

    You can now detach the database from each of the server instances, and dismount the reporting volume or volumes from each of the reporting servers.

For more information, see ALTER DATABASE (Transact-SQL).

At this point, the dismounted set of reporting volumes is ready for the build or refresh phase of its next update cycle.

Alternatively, before beginning the detach phase on the currently mounted set of reporting volumes, you can refresh the database on an alternate set of reporting volumes. For more information, see Maximizing Availability of a Scalable Shared Database.