How to apply patches on SQL Database Mirror Servers

The following list of steps can be performed against Database Mirror servers to apply patches

1. Take backup of all dbs in Principal Database Server
2. Pause Mirroring on Principal Database Server for all mirrored dbs
3. The Principal server databases becomes (Principal,Suspended)
3. Stop all SQL services on Mirror Database Server (Including WMI service)
4. Apply CU patch on mirrored servers
5. Unpause Mirrors (wait for them to catch up)
6. Failover the databases (the mirror server becomes Principal)
7. Pause Mirroring
8. Hand-over to Application users to test and wait for their confirmation to proceed.
9. Stop all SQL services on Mirror Server (Including WMI service)
10. Apply CU patch on Principal Database servers
11. Unpause mirrors
12. Failback databases.

Pausing and Resuming Database Mirroring

The database owner/dba can pause and later resume a database mirroring session at any time. Pausing preserves the session state while suspending mirroring. During bottlenecks, pausing might be useful to improve performance on the principal server.


When a session is paused, the principal database remains available. Pausing sets the state of the mirroring session to SUSPENDED, and the mirror database no longer keeps up with the principal database, causing the principal database to run exposed

How Pausing and Resuming Affect Log Truncation


Normally, when an automatic checkpoint is performed on a database, its transaction log is truncated to that checkpoint after the next log backup. While a database mirroring session remains paused, all of the current log records remain active because the principal server is waiting to send them to the mirror server. The unsent log records accumulate in the transaction log of the principal database until the session resumes and the principal server has sent the log records to the mirror server.

When the session is resumed, the principal server immediately begins sending the accumulated log records to the mirror server. After the mirror server confirms that it has queued the log record corresponding to the oldest automatic checkpoint, the principal server truncates the log of the principal database to that checkpoint. The mirror server truncates the redo queue at the same log record. As this process is repeated for each successive checkpoint, the log is truncated in stages, checkpoint by checkpoint.

Avoiding a Full Transaction Log


If the log fills up (either because it reaches its maximum size or the server instance runs out of space), the database cannot perform any more updates. To avoid this problem, you have two alternatives:

Resume the database mirroring session before the log fills up, or add more log space. Resuming database mirroring lets the principal server send its accumulated active log to the mirror server and puts the mirror database in the SYNCHRONIZING state. The mirror server can then harden the log to disk and start to redo it.

Stop the database mirroring session by removing mirroring.

Unlike pausing a session, removing mirroring drops all information about the mirroring session. Each partner server instance retains its own copy of the database. If the former mirror copy is recovered, it will have diverged from the former principal copy and be behind by the amount of time that has elapsed since the session was paused.

Monitor database mirroring servers

--Simple way to monitor database mirror servers

SELECT d.name as dbname, d.database_id,

@@servername as Principal_Server,
m.mirroring_role_desc,
m.mirroring_state_desc,m.mirroring_safety_level_desc,
m.mirroring_partner_name, m.mirroring_partner_instance,
m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL

High Availability with SQL Server 2008 by Paul S. Randal

Paul S. Randal written white paper about High Availability with SQL Server 2008.
This white paper describes the technologies available in SQL Server 2008 that can be used as part of a high-availability strategy to protect critical data.

Source : Microsoft White paper
http://msdn.microsoft.com/en-us/library/ee523927.aspx

To change the time-out value for mirroring database (high-safety mode only)

You can change timeout value for database mirroring.

use master
go
ALTER DATABASE  db_name SET PARTNER TIMEOUT 60

go

SELECT Mirroring_Connection_Timeout
FROM sys.database_mirroring
WHERE database_id = db_id('db_name')
go

--Output
60

How to view Mirroring database information

Using Dynamic Management Views (DMVs) to view Mirroring database information

SELECT d.name, d.database_id, m.mirroring_role_desc,

m.mirroring_state_desc,m.mirroring_safety_level_desc,
m.mirroring_partner_name, m.mirroring_partner_instance,
m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL

Witness Server Information

The sys.database_mirroring_witnesses catalog view provides information on the witnesses server
SELECT principal_server_name, mirror_server_name,

database_name, safety_level_desc
FROM sys.database_mirroring_witnesses


Endpoints


Query the catalog view sys.database_mirroring_endpoints to find useful information regarding the endpoints, such as the status of the endpoint, encryption settings, etc.

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, t.port, e.is_encryption_enabled, e.encryption_algorithm_desc, e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id

SQL Server Replication Providing High Availability using Database Mirroring

Another wonderful Whitepapers from Gopal Ashok and Paul S. Randal for SQL Server Replication Providing High Availability using Database Mirroring.

Writer: Gopal Ashok (Microsoft Corporation) and Paul S. Randal (SQLskills.com)

To download the entire paper please reference:

http://sqlcat.com/whitepapers/archive/2011/04/08/sql-server-replication-providing-high-availability-using-database-mirroring.aspx

How to apply Cumulative update package for SQL Server 2005/2008 on Mirror Databases

1. Take backup of all dbs in Principal Server


2. Pause Mirroring on Principal Server for all mirrored dbs

3. The Principal server databases becomes (Principal,Suspended)

3. Stop all SQL services on Mirror Server (Including WMI service)

4. Apply CU patch on mirrored servers

5. Unpause Mirrors (wait for them to catch up)

6. Failover the databases (the Mirror server becomes Principal server)

7. Pause Mirroring

8. Hand-over to Application users to test and wait for their confirmation to proceed.

9. Stop all SQL services on Mirror Server (Including WMI service)

10. Apply CU patch on Principal servers

11. Unpause mirrrors

12. Failback databases.

Proven SQL Server Architectures for High Availability and Disaster Recovery

Proven SQL Server Architectures for High Availability and Disaster Recovery

If you are looking for your organisation to implement High Availability and Disaster Recovery for SQL Server R2. Please read Paul S. Randal wrote this white paper. You can get it from the following url.

http://blogs.msdn.com/b/tommills/archive/2010/06/02/new-sql-server-2008-r2-high-availability-whitepaper-published.aspx

Paul S. Randal (SQLskills.com)

The transaction log for Principal database 'My_DB' is full

The database mirror environment Principal server database 'My_DB' Transaction log is full.

Principal database 'mydb' (Principal, Suspended.) is not Synchronized with mirror database.

Error: 9002, Severity: 17, State: 5


The transaction log for database 'My_DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases


Solution:
1. Run DBCC SQLPERF ('logspace')
2. Check the SQL Server error log
3. ALTER DATABASE My_DB to increase log file size
4. Run DBCC SQLPERF ('logspace')

5. Resume Database sync

Slowly the transaction log spaced used will be decreasing. 

FILESTREAM

The new FILESTREAM funcationality  in the SQL Server 2008 allows you to configure a VARBINARY(max) column, so that the actual data in stored in the file system/storage area rather than within the database.

To enable FILESTREAM feature you must configure SQL Server Service for that instance.

sp_configure 'filestream_access_level' , 2


go
Reconfigure

Note : Database Mirroring is not compatiable with FILESTREAM data

The mirror database has insufficient transaction log data

When you Setting Database Mirroring if you will get the error message like  "The mirror database has insufficient transaction log data".


The mirror database, "Northwind", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

Error : The mirror database has insufficient transaction log data
Solution :
1.. Take the Transaction Log backup from Principal server database and
2. Restore Log on Mirror database
 Restore Log on Northwind from disk = 'c:\mssql\backup\DBP_TLog.TRN' with NORECOVERY
3. Now try to setting up Database Mirroring again from Principal Server