Database Mirroring in the Real World

1. Database Mirroring in the Real World Craig Purnell

2. About Me • Database Administrator with Baker Hostetler • Experience with SQL Server 7/2000 and up • Previously consulted in the transportation industry,programming custom UNIX ERP solutions • MCSE / MCSA / MCITP (DBA) etc.

3. Overview • Mirroring is a high availability technology • Continuous stream of log records are sent to the mirror and “replayed” • Mirror is unavailable for client connections • Protects a single database at a time

4. Mirroring Demystified • Synchronous with Witness • No data loss • Auto detection of Failure / Failover • Synchronous without Witness • No data loss • Possibility of downtime • Asynchronous • Manual failover • Possibility of data loss.

5. Asynchronous Mirroring

6. Synchronous Mirroring

7. Primary Data Center Disaster Recovery Data Center Witness Principal Mirror Log Shipping Secondary Synchronous Database Mirroring Log Shipping Synchronous Mirroring with Witness • Microsoft’s SAP system

8. Mirroring with Certificates • Different domains without a trust (mergers, acquisitions, DMZ) • Internet (partners, affiliates)

9. Mirroring at Baker Hostetler • 11 Offices; All SQL is centralized • Early adopter of the technology • Was mirroring from Cleveland to Denver • Currently in a state of transition…. • Future: Eagan, MN to Cleveland

10. Mirroring at Baker Hostetler

11. Endpoints (1) • Required by mirroring • SQL Server Object that maps in TCP socket • Authentication • Encryption (RC-4 etc) • See BOL for options

12. Limitations (32bit) • Best Practice: 10 mirrored databases per server. • Effects of mirroring on the target: SQL runs out of VAS (32 bit) • Band-aid: -g512 (increase memtoleave)

13. Mirror Configuration • Identical Hardware not required • Memory / Disk requirements • Drive Letter layouts • File growths/shrinks

14. Demo #1 (T-SQL)Megadata from I1 to I2

15. WAN acceleration (1) • Riverbed Steelhead (hardware based) • Turn off encryption and compression • SQL 2008 Effects (native compression) • Trace Flag -T1462

16. WAN Acceleration (2) Index Rebuilds : 70-75% compression

17. MPLS Network QOS

18. Mirroring Timeouts

19. Mirroring Timeouts Test: 2 servers / 10Mbps crossover, dedicated endpoints

20. Demo #2 (GUI)Adventureworks from I1 to I2

21. Mirror on a Dedicated IP • Assign IP to 2nd NIC • Create Endpoint to listen only on that IP • Repeat for mirror • Have Network ops team configure routing

22. Mirroring and Licensing (1) • Witness can be SQL Express • Mirror server can be unlicensed – as long as you do not have anything running queries against the instance. • No snapshots allowed. • See SQL Licensing White paper for more details

23. Mirroring and Licensing (2) • Must use Standard or Enterprise • Synchronous vs. Asynchronous mirroring • Can’t mix versions.

24. Demo #3 (T-SQL)Rolling Upgrade of Northwind and Pubs

25. Mirroring: Lessons Learned • Not a substitute for T-Log backups • Network latency definitely is a factor • Timeouts due to “design flaw” in Riverbed dual WAN configuration • Log Buildup on Primary • QOS network rules • Great way to shovel data from A to B

26. Index Rebuilds • Carefully Schedule production instances • Selective rebuild script really helps. • Your mileage may vary.

27. Monitoring Mirroring (1) • System Monitor Counters • T-SQL • Mirroring Monitor GUI • DMVs:sys.dm_db_mirroring_connections sys.database_mirroring sys.database_mirroring_witnesses sys.database_mirroring_endpoints sys.tcp_endpoints sys.server_principals sys.database_recovery_status

28. Demo #4 Index Rebuilds and Monitoring

29. SQL Agent Alerting • Configure SQL Agent to alert you of mirroring problems • See Technical Article: Alerting on Mirroring Events (escape macros are wrong).

30. Error messages: T-SQL vs. GUI Msg 1418, Level 16, State 1, Line 1 The server network address "TCP://MYMIRROREDSERVER.domainname.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. Same error condition (mixing SQL versions)

31. Troubleshooting • netstat –ano • tcping hostname 5022 • Verify endpoints are using the same encryption, authentication • Are you running SQL as LocalSystem? • Use the GUI to configure the endpoints for you • Try and recreate the problem with the GUI

32. Troubleshooting • Verify the service accounts are Logins • Verify both service accounts has CONNECT rights on the endpoint(s) • You must make a Backup and a T-Log backup AFTER the DB is in full recovery mode (watch for extraneous Tlog backups) • Look in the Error log • Windows Firewall must be configured • IPV6!

33. Other Issues • Logins are NOT transferred • Use sp_help_revlogin to get an exact sid / SQL login list • SQL Agent Jobs are not transferred • Extended Stored Procedures • Linked servers

34. Key Takeaways • Database Mirroring can eliminate the single point of failure of clustering (aka the shared disk problem) • Database Mirroring, coupled with clustering can raise your business database uptime an order of magnitude. • This technology can be implemented without the need of expensive SAN…just a little creativity on YOUR part

35. Any Questions?

36. Contact Information cpurnell@bakerlaw.com or craig.purnell@gmail.com

37. References (1) • Tcping http://www.elifulkerson.com/projects/tcping.php • SQL Server Licensing White Paper • http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc • Asynchronous Database Mirroring with Log Compression in SQL Server 2008 http://sqlcat.com/technicalnotes/archive/2007/12/17/asynchronous-database-mirroring-with-log-compression-in-sql-server-2008.aspx • Database Mirroring White Paper http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/DatabaseMirroring.doc • Database Mirroring: Best Practices and Performance Considerationshttp://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/DBM_Best_Pract.doc • Mirroring with Certificates:http://msdn2.microsoft.com/en-us/library/ms191140.aspx • Transferring Logins and Passwords between instances: http://support.microsoft.com/kb/918992

38. References (2) • Alerting on Database Mirroring Events http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx • How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instances http://msdn.microsoft.com/en-us/library/bb677181.aspx • Asynchronous Database Mirroring with Log Compression in SQL Server 2008 http://sqlcat.com/technicalnotes/archive/2007/12/17/asynchronous-database-mirroring-with-log-compression-in-sql-server-2008.aspx • High Availability and Disaster Recovery for Microsoft’s SAP Data Tier: A SQL Server 2008 Technical Case Study http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/MSIT_SAP_Data_Tier_HA_DR.docx

39. Monitoring Mirroring (3) • EXEC [msdb].[sys].[sp_dbmmonitorresults] @dbname, @interval WHERE: @dbname – the mirrored database @interval - 0 = last row, 1 last two hours, 2 last four, 3 last eight, 4 last day, 5 last two days, 6 last 100, 7 last 500, 8 last 1000, 9 ALL