SQL Server Database Mirroring: Part 2

Back to Part 1

Prerequisites

The database must be using the FULL RECOVERY or BULK LOGGED recovery models.
On the secondary server, we must have already restored a full database backup with the NORECOVERY or STANDBY options, so that transaction logs may be applied.

Restrictions

FILESTREAM is not supported by database mirroring. As a FILESTREAM filegroup cannot be created on the principal server. So, we can say that database mirroring cannot be configured for a database that contains FILESTREAM filegroups.
Database mirroring can support a maximum of about 10 databases per server instance, on a 32-bit system.
Database mirroring is not supported with either cross-database transactions or distributed transactions.

EndPoints

Endpoints must be correctly configured: Make sure that each server instance (the principal server, mirror server, and witness, if any) has a database mirroring endpoint.
Check that the port numbers are correct. To identify the port currently associated with database mirroring endpoint of a server instance, use the following Transact-SQL statement.
1. SELECT type_desc, port FROM sys.tcp_endpoints;
2. GO
Note: Inspect each server instance to determine whether it is listening on the correct ports.
Make sure that the endpoints are started (STATE=STARTED). On each server instance, use the following Transact-SQL statement.
1. SELECT state_desc FROM sys.database_mirroring_endpoints
To start an endpoint, use the following Transact-SQL statement.
1. ALTER ENDPOINT Endpoint_Mirroring
2. STATE = STARTED
3. AS TCP (LISTENER_PORT = <port_number>)
4. FOR database_mirroring (ROLE = ALL);
5. GO
Check that the ROLE is correct. On each server instance use the following Transact-SQL statement.
1. SELECT role FROM sys.database_mirroring_endpoints;
2. GO
Make sure that the login from the other server has CONNECT permission. To determine who has CONNECT permission for an endpoint, on each server instance use the following Transact-SQL statement.
1. SELECT 'Metadata Check';
2. SELECT EP.name, SP.STATE,
3. CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
4. AS GRANTOR,
5. SP.TYPE AS PERMISSION,
6. CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
7. AS GRANTEE
8. FROM sys.server_permissions SP , sys.endpoints EP
9. WHERE SP.major_id = EP.endpoint_id
10. ORDER BY Permission,grantor, grantee;
11. GO
Endpoints must be correctly configured: Make sure that each server instance (the principal server, mirror server, and witness, if any) has a database mirroring endpoint.
Check that the port numbers are correct. To identify the port currently associated with database mirroring endpoint of a server instance, use the following Transact-SQL statement.
1. SELECT type_desc, port FROM sys.tcp_endpoints;
2. GO
Note: Inspect each server instance to determine whether it is listening on the correct ports.
Make sure that the endpoints are started (STATE=STARTED). On each server instance, use the following Transact-SQL statement.
1. SELECT state_desc FROM sys.database_mirroring_endpoints
To start an endpoint, use the following Transact-SQL statement.
1. ALTER ENDPOINT Endpoint_Mirroring
2. STATE = STARTED
3. AS TCP (LISTENER_PORT = <port_number>)
4. FOR database_mirroring (ROLE = ALL);
5. GO
Check that the ROLE is correct. On each server instance use the following Transact-SQL statement.
1. SELECT role FROM sys.database_mirroring_endpoints;
2. GO
Make sure that the login from the other server has CONNECT permission. To determine who has CONNECT permission for an endpoint, on each server instance use the following Transact-SQL statement.
1. SELECT 'Metadata Check';
2. SELECT EP.name, SP.STATE,
3. CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) |
4. AS GRANTOR,
5. SP.TYPE AS PERMISSION,
6. CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
7. AS GRANTEE
8. FROM sys.server_permissions SP , sys.endpoints EP
9. WHERE SP.major_id = EP.endpoint_id
10. ORDER BY Permission,grantor, grantee;
11. GO

Creating a Mirror Database For Mirroring

The mirror database must exist before a database mirroring session can begin.

Steps to do this are as under

Make sure that Microsoft SQL Server 2008 is installed on both the principal and mirror server instances.
Then Make it verify that the database uses the full recovery model.
Back up the principal database to a full database backup.
Confirm it that the system on which we are planning to create the mirror database has a disk drive with sufficient space to hold the database.
Until the backup is on a network drive it is accessible from both systems, copy the database backup to that system.
Restore the full database backup onto the mirror server instance to create the mirror database. RESTORE DATABASE (database name statement must specify) WITH NORECOVERY.

Note: If we restore the database filegroup by filegroup, be sure to restore the whole database.
Typically, at least one log backup must be taken on the principal database, copied to the mirror server, and restored on the mirror database (using WITH NORECOVERY). So that, a log backup might be unnecessary, if the database has just been created and no log backup has been taken yet, or if the recovery model has just been changed from SIMPLE to FULL.

Example

Let's take an example to apply database mirroring with one of the database named "PatientInfo", Open SQL Server Management Studio 2008 and make sure that the database is in the Full Recovery mode. To check the recovery mode, right click the database name, select Properties and then select Options, or by using the following T-SQL:
1. USE master;
2. GO
3. ALTER DATABASE PatientInfo
4. SET RECOVERY FULL;
5. GO
Note: If it is not in the FULL recovery Mode then, modify it to use the full recovery mode:
Once we modify the recovery model of our database to FULL, it's time to create a full Backup on the principal server instance, create a full backup of the principal database as follows:
1. BACKUP DATABASE PatientInfo
2. TO DISK = 'D:\PatientInfo.bak'
3. WITH FORMAT
4. GO
Now, copy the full backup to the mirror server.
Restore the full backup WITH NORECOVERY onto the mirror server instance. The restore command depends on whether the paths of principal and mirror databases are identical. Following T-SQL is used to do this:
1. RESTORE DATABASE PatientInfo
2. FROM DISK = 'D:\PatientInfo.bak'
3. WITH NORECOVERY
4. GO
Note: If the path names of the principal and mirror databases differ, we cannot add a file, because on receiving the log for the add file operation, the mirror server instance attempts to place the new file in the location used by the principal database.
After creating a full backup, it is important to create a log backup on the principal database. The following Transact-SQL statement backs up the log to the same file used by the preceding full backup:
1. BACKUP LOG PatientInfo
2. TO DISK = 'D:\PatientInfo.bak'
3. GO
Note: Also the database name on the mirror must be the exact same name as the principal.
Before we can start mirroring, we must apply the required log backup (and any subsequent log backups). For example, the following Transact-SQL statement restores the first log from D:\PatientInfo.bak:
1. RESTORE LOG PatientInfo
2. FROM DISK = 'D:\PatientInfo.bak'
3. WITH FILE=1, NORECOVERY
4. GO
If any additional log backups occur before starting mirroring, we also restore all of those log backups, in sequence, to the mirror server using WITH NORECOVERY. For example, the following Transact-SQL statement restores two additional logs from D:\PatientInfo.bak:
1. RESTORE LOG PatientInfo
2. FROM DISK = 'D:\PatientInfo.bak'
3. WITH FILE=2, NORECOVERY
4. GO
5. RESTORE LOG PatientInfo
6. FROM DISK = 'D:\PatientInfo2.bak'
7. WITH FILE=3, NORECOVERY
8. GO

Continue to Part 3