SQL Server 2008 Database Mirroring

SQL Server 2008 Database Mirroring

  • Comments 3
  • Likes

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 http://sqlblog.com/blogs/greg_low/ and http://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 http://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

Modes

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 http://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 http://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

SQL Server 2008

Failover

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 http://msdn.microsoft.com/en-us/library/bb677181.aspx
  • Careful – Some data loss may occur

Technet Best practices

Performance counters

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

Automatic page repair

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 

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 http://msdn.microsoft.com/en-us/library/ms403827.aspx
  • States: Synchronized, Synchronizing, Suspended (pause),  Disconnected
  • See http://msdn.microsoft.com/en-us/library/ms189284.aspx
  • Cross domain security: Use certificate security
  • See http://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 http://msdn.microsoft.com/en-us/library/bb500117.aspx

Related blog posts

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Here are some notes on “SQL Server 2008 Log Shipping” I took while attending an advanced class on SQL

  • Here are some notes on “SQL Server 2008 Failover Clustering” I took while attending an advanced class

  • I took some advanced SQL Server 2008 classes recently and shared my notes in a series of blog posts.