1 / 39

Database Mirroring in the Real World

Database Mirroring in the Real World. Craig Purnell. 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. . Overview .

shania
Download Presentation

Database Mirroring in the Real World

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related