Setting Up Mirroring using Transact-SQL Statements

Setting Up Mirroring Using Transact-SQL Statements

In addition to using SQL Enterprise Manager, you can use the DISK MIRROR, DISK UNMIRROR, and DISK REMIRROR statements to set up mirroring.

The DISK MIRROR statement starts disk mirroring. The database device to be mirrored must have already been initialized by a DISK INIT statement. The device that contains the mirror should not be initialized with DISK INIT.

When you mirror a device, the DISK MIRROR command creates a physical device and puts an exact copy of the existing device on the newly created mirroring device. When you first execute the DISK MIRROR command, it may take a few minutes if the device you are mirroring has a lot of information on it. A disk and its mirror constitute one logical device, and the mirror name is added to the mirrorname column in the sysdevices table.

To mirror a device

DISK MIRROR
NAME = 'logical_name',
MIRROR = 'physical_name'
[, WRITES = {SERIAL | NOSERIAL }]

where

logical_name: Is the name of the device to be mirrored.
physical_name: Is the operating-system filename of the device. It cannot be an already existing operating-system file.
SERIAL: Specifies that writes to the original disk must finish before writes to the mirror disk begin. With Windows NT, mirroring is always SERIAL.
NOSERIAL: Specifies that writes can occur either simultaneously to the original disk and to the mirror disk, or serially to the original disk before the mirror disk. With Windows NT, mirroring is always SERIAL. NOSERIAL is retained for compatibility with other versions of SQL Server.

In the following example, TRANLOG is the database device name for the device to be mirrored. The TRANLOG device has been initialized with DISK INIT and is used as a transaction log device, as in CREATE DATABASE... LOG ON TRANLOG. For example, to mirror the EMPLOYEE device to the D: disk, type:

DISK MIRROR 
NAME = 'employee',
MIRROR = 'd:\employee.mir'

To mirror the TRANLOG transaction log device to the F: hard disk:

DISK MIRROR 
NAME = 'tranlog',
MIRROR = 'f:\tranmir.dat'

The DISK UNMIRROR statement stops the mirroring process. When you unmirror a device, you can choose to unmirror the device or its mirror. For example, if the device you're mirroring fails, you can unmirror the mirror and replace the device with it.

You can also unmirror the device, unmirror permanently, or temporarily pause mirroring.

To unmirror a device

DISK UNMIRROR
NAME = 'logical_name'
[, SIDE = {PRIMARY | SECONDARY}]
[, MODE = {RETAIN | REMOVE}]

where

logical_name: Is the name of the device to be unmirrored. This name is recorded in sysdevices.
PRIMARY: Is the device listed in the name column of sysdevices.
SECONDARY: Is the device listed in the mirrorname column of sysdevices.
RETAIN: Specifies that the unmirroring is temporary. RETAIN is the default.
REMOVE: Specifies that the unmirroring is permanent.

For example, to temporarily unmirror the EMPLOYEE device, type:

DISK UNMIRROR
NAME = 'employee',
SIDE = primary,
MODE = retain

To permanently unmirror the TRANLOG device, type:

DISK UNMIRROR
NAME = 'tranlog',
SIDE = primary,
MODE = remove

To temporarily unmirror the EMPLOYEE mirror, type:

DISK UNMIRROR
NAME = 'employee',
SIDE = secondary,
MODE = retain

A mirror process that has been suspended due to a device failure or with the DISK UNMIRROR statement can be restarted with DISK REMIRROR. If you set RETAIN when you unmirrored the device, you can use the DISK REMIRROR statement.

To remirror a device

DISK REMIRROR
NAME = 'logical_name'

where

logical_name: Is the device to be remirrored.

For example, to remirror the EMPLOYEE device, type:

DISK REMIRROR
NAME = 'employee'

For more information about the DISK MIRROR, DISK UNMIRROR, and DISK REMIRROR statements, see the Microsoft SQL Server Transact-SQL Reference.