SQL Server 2008 Database Mirroring

Article
04/02/2009

Here are some notes on “SQL Server 2008 Database Mirroring” I took while attending an advanced class on SQL Server taught by Greg Low (from https://sqlblog.com/blogs/greg_low/ and https://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Database Mirroring

Software solution for high availability.
Per database and not per server.
Increase data protection, availability, upgrade availability.
Careful – A lot of things live outside the database
SQL HA options for the masses, without the high-end hardware requirement.
Very popular option.
See https://msdn.microsoft.com/en-us/library/ms189852.aspx

Requirements and Limitations

Full recovery model
Express (witness only), Standard (can’t do async) or Enterprise
Logins (logins are not in the database, you need to make sure they are on the other side)
Cannot have multiple mirrors – use log shipping if you need this
Not intended for a large number of databases – Depends on traffic
Careful – In multi-database applications, failover behavior
Cannot mirror master, msdb, tempdb, model

Endpoints

Options for name, port, encryption
Careful – Names, FQDN, IP addresses
Endpoint encryption
See https://msdn.microsoft.com/en-us/library/ms181591.aspx
Authentication (domain accounts, certificates)
See https://msdn.microsoft.com/en-us/library/ms190456.aspx
DNS/WINS – name resolution is important, use FQDN
See https://msdn.microsoft.com/en-us/library/ms189921.aspx
Careful – domain suffix issues
Consider the LISTENER_IP option in endpoint configuration – private network for mirroring
Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)
See https://msdn.microsoft.com/en-us/library/ms191140.aspx

Modes

Roles: Principal, Mirror, Witness
Modes: high performance (async), high safety (sync), high safety with HA (sync with witness)
Async: DR scenarios, long distrances, no need for witness – Consider log shipping instead
See https://msdn.microsoft.com/en-us/library/ms187110.aspx
Sync: Avoid data loss, performance impact (wait for both logs to write before ack to client)
See https://msdn.microsoft.com/en-us/library/ms179344.aspx
Witness: third system to be able to do failover (any edition, including Express)
See https://msdn.microsoft.com/en-us/library/ms189902.aspx
Careful – Keep the witness as a third site. One witness can work with multiple mirrors.
Encryption: Can be used, encrypted data is usually not larger than original data

Clients

Clients: ADO.NET v2 required for automatic failover: Failover partner in connection string.
Careful – Client will ask the primary upon connect. Can try the partner if primary not there.
In SQL Server 2008: Failover partner cached in registry upon first connect to primary (no change to connection string required if primary available on first connect)
See https://msdn.microsoft.com/en-us/library/ms366348.aspx

Reporting on Mirror

Mirror can be access as read-only.
Database snapshots can be created on mirror, you can use snapshot for reports.
See https://msdn.microsoft.com/en-us/library/ms175511.aspx
Snapshot cannot be restored to mirror. Snapshot performance implications.
Careful – refreshing snapshot, finding the correct snapshot, failover situations
Consider using snapshot as source for a separate reporting database.
Consider having a process that keeps track of which is the latest snapshot.

Clustering and Mirroring

Using database mirroring combine with failover clustering.
Commonly used as a DR solution.
Do not use auto failover.
See https://msdn.microsoft.com/en-us/library/ms191309.aspx

SQL Server 2008

Logstream compression
See https://technet.microsoft.com/en-us/library/cc645581.aspx
Automated page repair
See https://msdn.microsoft.com/en-us/library/bb677167.aspx
Incoming log stream processing improvements
Transparent client redirection

Failover

Manual failover – Must be in sync, use SSMS or ALTER DATABASE...
See https://msdn.microsoft.com/en-us/library/bb522476.aspx
Automated failover – Need witness, must avoid “split brain” scenario
Careful – Lack of synchronization
Careful – Option to “Force Service” when not in sync, allows data loss
See https://msdn.microsoft.com/en-us/library/ms189977.aspx
After failover, if mirroring isn’t disabled, original principal becomes mirror

Upgrade Steps

Switch to high safety (sync). Make sure it’s in sync.
Disable the witness.
Perform a rolling upgrade (upgrade mirror, manual failover to mirror, upgrade original principal, manual failover to original principal).
Switch back to high performance (async)
Re-enable the witness.
See https://msdn.microsoft.com/en-us/library/bb677181.aspx
Careful – Some data loss may occur

Technet Best practices

Looking at Database Mirroring Best Practices and Performance Considerations
https://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/DBM_Best_Pract.doc
Comparing Workload1 and Workload2 – OLTP vs. batch-type workload
Looking at performance with no mirroring vs. Safety OFF vs. Safety FULL
Looking at time to index creation, index rebuild – impact with ONLINE=ON due to waiting on mirror

Performance counters

Log send queue rate
Log bytes redone from cache/sec
Log bytes sent from chace/sec
Log compressed bytes rcvd/sec (2008)
Log compressed bytes sent/sec (2008)
Log harden times (ms)
Log remaining for undo KB
Log scanned for undo KB
Mirror write transactions/sec
See https://technet.microsoft.com/en-us/library/ms189931.aspx
See https://msdn.microsoft.com/en-us/library/ms408393.aspx

Failover process

Failover occurs
Some time to detect the failure (hard failures (network outage) are quicker than soft failures)
Some time to coordinate with the witness
Decision to failover
Some time (order of a second) to actually fail over
Database available on new principal
Database Undo continues
Time to failover typically just a few seconds (varies depending on case – pull network cable, power off principal, stop sql server service, shutdown principal, manual failover)

Network

Log send flow control time (ms)
Looking at typical network latency (LAN, MAN, WAN)
Looking at whitepaper numbers on network latency effects on sync mirroring
SQL Server 2008 – Log stream compression, trade off: data volume vs. CPU, affects both sides
Looking at long stream compression effect on transactions/sec (SQL CAT team test)
See https://sqlcat.com/technicalnotes/archive/2007/09/17/database-mirroring-log-compression-in-sql-server-2008-improves-throughput.aspx

Automatic page repair

Pages on both principal and mirror can be repaired using each other’s copy
Detection and repair of 823 and 824 errors
Standard or Enterprise edition
See https://msdn.microsoft.com/en-us/library/bb677167.aspx
Looking at suspect_pages
See https://msdn.microsoft.com/en-us/library/ms191301.aspx

Demo

Database in full recovery mode
Backup original database
Restore both backups with NO RECOVERY
Careful – Consider options to migrate and keep other objects in sync (Logins, SQL Server Agent jobs (disabled), SSIS packages, linked servers, backup devices, maintenance plans, database mail profiles, etc.)
In SSMS, Database “Recovering…”, click on Tasks, Mirror…
Witness – can be enabled from principal or partner, but keep in separate server
Principal – Listener port, encryption, endpoint
Partner – Listener port, encryption, endpoint
Security – Service accounts for principal, partner
Review screen – Principal and Mirror endpoints – format is TCP://computer:port
Option to start mirroring right away…
SELECT * FROM sys.endpoints
SELECT * FROM sys.dm_db_mirroring_connections
Notice that the wizard made a few calls on your behald, like encryption protocol
Careful – If status is “Synchronizing…” all the time, you’re running behind (not keeping up)
Adding a witness
Launching “Database Mirroring Monitor”

Demo with TDE

Redoing from start – using a database with TDE
Backup database, backup certificate and private key
Restore certificate and private key, Restore database with no recovery
Configuring mirroring via TSQL.
If not already there, would need to do CREATE ENDPOINT … on each side.
Then run on each side of the partnership
ALTER DATABASE … SET PARTNER=’ TCP://computer1:port’
Then to fail over
ALTER DATABASE… SET PARTNER FAILOVER
See https://blogs.msdn.com/sqlserverfaq/archive/2009/03/31/setting-up-database-mirroring-in-sql-server-2008-using-t-sql-when-the-database-is-encrypted-using-transparent-data-encryption.aspx

Mirroring Details

Backup: Can’t backup mirror, can’t backup snapshot, Consider log shipping for those
Monitoring: Check if partner is keeping up
Monitoring GUI: GUI tool, dm_monitor monitor role
Monitoring SP: sp_dbmonitorupdate, once per minute by default, updates internal msdb table
See https://msdn.microsoft.com/en-us/library/ms403827.aspx
States: Synchronized, Synchronizing, Suspended (pause), Disconnected
See https://msdn.microsoft.com/en-us/library/ms189284.aspx
Cross domain security: Use certificate security
See https://msdn.microsoft.com/en-us/library/ms191140.aspx
Client design: Consider the added latency when working with high safety
Network issues: No set limits for async, compression helps, consider the consequences
Currently no support for combining filestream and mirroring (consider Clustering)
Careful - Mirroring and multi-dabase apps. Mirror is per database.

Mirror and other technologies

Mirroring and Replication – Supported
Mirroring and Database Snapshots – Supported
Mirroring and Clustering – Supported. Typical: Cluster local, async mirroring to remote
Mirroring and Log Shipping – Supported. Consider carefully
See https://msdn.microsoft.com/en-us/library/bb500117.aspx

SQL Server 2008 Database Mirroring

Additional resources