The two types of settings for database mirroring are high safety -synchronous or high performance -asynchronous. The synchronous mode requires the mirror to write a new transaction which will ensure safety but cost in performance. In the asynchronous mode, the session does not wait for the mirror to write which can allow a delay in processing on the mirror. The length of the processing gap depends on how high the work load on the principal database is. A large gap can result on data loss, or less data protection. (MSDN, 2007)
The high safety mode with automatic failover requires a third server instance and is named by Microsoft as the ‘witness’. (MSDN, 2007) The witness verifies that the mirror is connected. A fact worth noting is that the witness and the mirror must be able to communicate even if the primary database is not online. The witness does not serve data, it only watches the transactions between the primary and mirror databases. (MSDN, 2007)
Before a mirror can be implemented, a second instance of the primary database must be established on a different server instance. This can be accomplished by restoring a current backup of the primary database to the second instance WITH NORECOVERY. Any logs that occurred after the backup was made should also be restored WITH NORECOVERY. Both instances should run on the same version of SQL Server, uses full recovery mode, and have equal hardware performance specifications. Also, a windows authentication login must exist on the master of both instances since this will be required for network access. (MSDN, 2007)
After the mirror is established, connect to the primary server instance and at the database node level, open the database properties window, select -configure security- and the mirror wizard launches. The wizard assesses the role of the server instances (primary, mirror, or witness) and provides the server network addresses or endpoint. (Note: Since windows authentication is used here, the endpoint must be configured to use certificates for both the primary and mirror server instances.) There are three safety levels provided by the wizard. They are high security with automatic failover, high security without auto failover, and high performance. To set a high security option, an endpoint for the witness server must be specified first, otherwise, the high security options will not be available. (MSDN, 2007)
As an exercise in writing SQL code, I have taken the following example of how to configure a database mirror in Transact SQL.
1. To use Transact SQL to set up a mirror, first change the recovery mode of the primary database:
USE master;
GO
ALTER DATABASE [primary database]
SET RECOVERY FULL;
GO
2. Create a full backup on the primary server:
BACKUP DATABASE [primary database]
TO DISK = ‘C:\[primary database].bak’
WITH FORMAT
GO
3. Restore the backup on the mirror server:
RESTORE DATABASE [primary database]
FROM DISK = ‘C:\[primary database].bak’
WITH NORECOVERY
***IF THE PATH FOR THE MIRROR IS DIFFERENT, USE THE FOLLOWING MOVE STATEMENT***
MOVE ‘[primary database]_Data’ TO
‘mirror server path’
MOVE ‘[primary database]_Log’ TO
‘mirror server path’
GO
4. Create a log backup:
BACKUP LOG [primary database]
TO DISK = ‘C:\[primary database].bak’
GO
Apply the log backup:
RESTORE LOG [primary database]
FROM DISK = ‘C:\[primary database].bak’
WITH FILE=1, NORECOVERY
GO
Any other logs must be applied in sequence:
RESTORE LOG [primary database]
FROM DISK = ‘C:\[primary database].bak’
WITH FILE=2, NORECOVERY
GO
RESTORE LOG [primary database]
FROM DISK = ‘C:\[primary database].bak’
WITH FILE=3, NORECOVERY
GO
5. Now endpoints must be created on all three server instances. Below is an example of the code for the principal instance:
CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO
***create a login for the witness server instance***
USE master;
GO
CREATE LOGIN [domain]\witnessuser FROM WINDOWS;
GO
***grant endpoint connection to witness***
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain]\witnessuser;
GO
6. On the mirror server instance, set the primary server as partner:
ALTER DATABASE [mirror database]
SET PARTNER = ‘TCP://[primary database].com:7022’
GO
7. On the principal server instance, set the mirror as the partner:
ALTER DATABASE [primary database]
SET PARTNER = ‘TCP://[mirror database].com:7022’
GO
8. Set the witness on the principal server:
ALTER DATABASE [witness database]
SET WITNESS = ‘TCP://[witness database].com:7022’
GO