TexCopy Sql Server 2005

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

• Options for name, port, encryption
  
• Careful – Names, FQDN, IP addresses
  
• Endpoint encryption
  
• See http://msdn.microsoft.com/en-us/library/ms181591.aspx
  
• Authentication (domain accounts, certificates)
  
• See http://msdn.microsoft.com/en-us/library/ms190456.aspx
  
• DNS/WINS – name resolution is important, use FQDN
  
• See http://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 http://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 http://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 http://msdn.microsoft.com/en-us/library/ms179344.aspx
  
• Witness: third system to be able to do failover (any edition, including Express)
  
• See http://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 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

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

SQL Server 2008

• Logstream compression
  
• See http://technet.microsoft.com/en-us/library/cc645581.aspx
  
• Automated page repair
  
• See http://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 http://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 http://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 http://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
  
• http://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 cache/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 http://technet.microsoft.com/en-us/library/ms189931.aspx
  
• See http://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 http://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
  
• Enterprise edition only
  
• See http://msdn.microsoft.com/en-us/library/bb677167.aspx
  
• Looking at suspect_pages
  
• See http://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 http://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 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

• http://blogs.technet.com/josebda/archive/2009/04/02/sql-server-2008-log-shipping.aspx
  
• http://blogs.technet.com/josebda/archive/2009/04/02/sql-server-2008-database-snapshots.aspx
  
• http://blogs.technet.com/josebda/archive/2009/04/02/sql-server-2008-failover-clustering.aspx
  
• http://www.sqlservercentral.com/blogs/robert_davis/archive/2008/11/18/How-many-mirrored-databases-can-I-have-on-a-server_3F00_.aspx
  
• http://blogs.msdn.com/john_daskalakis/archive/2009/01/15/9320483.aspx
  
• http://www.sqlskills.com/BLOGS/PAUL/category/Conference-Questions-Pot-Pourri.aspx