Geek Sync | Field Medic’s Guide to Database Mirroring

Field Medic’s Guide to Database Mirroring August 18, 2015

What you’ll learn today Rescue scenarios Why mirroring matters Special setup for mirrors Monitoring mirroring 3 1 2 4

Field medics need to know… Rescue scenarios Why mirroring matters Special setup for mirrors Monitoring mirroring 3 1 2 4

Mirroring: Your friend since 2008 Became supported in SQL Server 2005 SP1 An important performance enhancement called ‘Log Stream Compression’ was added in SQL Server 2008 Works in Standard Edition (synchronous only) • “Basic Availability Groups” are a planned feature for SQL Server 2016 that we believe will be in Standard Edition, but…

(continued) Mirroring is much easier to manage than Availability Groups • No Windows Failover Cluster required • Much simpler to document • Management can all be done using SSMS/TSQL Mirroring has perks, too! • Automatic page repair • Doesn’t require a domain / can be used with multiple domains (using certificates)

Mirroring is ‘deprecated’ … but this doesn’t mean it’s dead It will be removed in a future version of SQL Server • What version that is hasn’t been disclosed. Features may stay ‘deprecated’ for many many versions without being removed. Fully supported in SQL Server 2012 and 2014 Well known, widely documented, established feature • Supported by many vendors • Typically stable

Not ending a TSQL Statement in a “;” Also deprecated…

Mirroring modes Choose between safety and performance “High Safety” mode • Synchronous • Automatic failover is optional • Witness – use a witness if you’re using automatic failover. (Can be Express Edition) “High performance” mode • Asynchronous • Enterprise Edition only • No automatic failover • Do not use a witness with this mode

Problems mirroring doesn’t solve Databases are mirrored individually, and fail over individually • No distributed transaction support (not in AGs prior to 2016, feature is planned at date of this presentation) • If you want them to fail over as a group, you have to write custom code and make sure it happens You must keep SQL Agent Jobs and logins in sync and manage where they run Mirroring lots of databases on one instance can be difficult due to thread requirements (Also true for AGs)

Mirroring is the right choice when… One mirror is enough Individual database failover is enough (not a group) You aren’t ready to manage the complexity of a cluster And you may not have many other choices if you’re using SQL Server 2008 or SQL Server 2008R2

Demo: automatic page repair in action

High safety with no witness – no automatic failover

We change to high performance mode

Let’s corrupt a page dbo.CriticalTable has only one data page The table has a single row where the ‘val’ column is quoting a famous sloth meme We’re going to corrupt that page and see if mirroring can fix the issue

Here’s the offset of the page in the data file We have to shut down the SQL Instance this time to edit the page

Open the data file with a hex editor

CTRL+G, set offset as dec relative to begin

Type over the values to corrupt

The fine print Automatic page repair only kicked in when corruption was detected • Maximum of 100 rows per database • Rows in sys.dm_db_mirroring_auto_page_repair are cleared on instance restart You still need • alerts and monitoring • to set page_verification to CHECKSUM for the database • to run CHECKDB (to detect corruption where reads haven’t been happening) • to investigate root cause when this happens But isn’t this much better than repairing the page yourself?

This feature is also in AlwaysOn Availability Groups. Mirrors are just easier to manage!

Setting up logins You can script these all out from SQL Server Management Studio If you allow SQL Authentication, this can be complex though • You have to look up the passwords yourself, and you don’t want to save them in a script in plain text • The SID of the login matters so that the login will map up with the database users, and the SID doesn’t script automatically Robert Davis has published a free script that helps you automate this and solve both of these problems: http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a- database-mirror

‘Orphaned users’ If you do have a login where the SID wasn’t transferred over to the mirror, the users associated with the login will be ‘orphans’ – and they won’t work You can detect and fix this without recreating the login if it happens using sp_change_users_login: http://msdn.microsoft.com/en-us/library/ms174378(SQL.90).aspx

CLR Assemblies: Trustworthy? CLR Assemblies are part of the database and are mirrored But they may not work when you first fail over without extra steps • The TRUSTWORTHY database property isn’t automatically restored with the database. If you need this, it must be enabled the first time you fail over • Not all CLR assemblies require this-- you can see your current setting in the sys.databases DMV

Setting up SQL Server Agent jobs The easy way to script out all jobs in SSMS: View: Object Explorer Details In the Object Explorer Details pane, you highlight all jobs and script them out in a single script But beware… • If job steps use log files, make sure the same path exists where you’re installing it • Make sure you enable/disable the right jobs on the mirror

Making jobs work auto-magically after failover You need custom code to help your jobs out: • If an automatic failover occurs, the right jobs need to run automatically, too • You need to accomplish this without having jobs fail all the time if they’re running against the mirror– because that will destroy monitoring for failed jobs Each job needs to check “Am I the principal?” and act accordingly This goes for your application jobs as well as maintenance

Special setup: Certificates If you’re using certificates that are signed in the master database, these need to be set up on the mirror also Mirroring doesn’t replace the need to securely back up those certificates in a place where you’re sure you won’t use them!

Split brain in High Safety Mode Split brain is as bad as it sounds: everything shuts down Let’s say you’re using high safety mode with a principal, a mirror, and a witness •  If the principal can’t see either the mirror or the witness, it can’t know if the database is online at the witness. It will take the database offline. •  If the mirror can’t see the principal or the witness, it freaks out too, and puts the database offline. The database is offline everywhere. We’ve lost quorum! Constant communication between the principal, mirror, and witness is critical

Split brain The database could be online somewhere else. I’d better shut it down! Derp derp derp derp The database could be online somewhere else. I’d better shut it down!

Suspended mirror ‘Suspended’ status can happen after things go wrong • Forced service • SQL Server bugs in some versions (Examples: http://support.microsoft.com/kb/2403218 , http://support.microsoft.com/kb/975681 ) ‘Suspended’ means that data is not being transferred from the principal to the mirror • The log will grow on the principal • You’re no longer protected by mirroring To fix, on either partner run: ALTER DATABASE YourMirrorDB SET PARTNER RESUME;

Suspended mirrorI’ll save all the changes in my transaction logs for when you come back! What’s going on?

Disconnected witness We’ve seen this if there have been network interruptions to the witness, or problems in DNS Mirroring may keep running (depending on whether or not the communication between the primary and the mirror is interrupted) If you have a disconnected witness, you may fix this by restarting the endpoints on the witness To prevent unplanned failovers, I prefer to: • Switch to high performance mode and remove the witness • Repair the witness • Add the witness back and switch to high safety mode

Disconnected witness I sure hope there’s no network interruptions… Me too! Derp derp d derp

Forcing up the mirror Example: you’re using asynchronous mirroring (“high performance” mode). This doesn’t have automatic failover. The principal server is lost! You need to bring things up on the mirror. To do this, you run: •  ALTER DATABASE YourMirrorDB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS; Discussion: • What do you do to identify how much data was lost? • What other steps need to be done immediately if you have to do this?

Mirroring and Virtualization Technically, it’s supported by VMware: “AlwaysOn Availability Groups and database mirroring can be used in combination with vSphere vMotion, vSphere HA, and DRS to maximize SQL Server availability.” … SQL Server on VMware Availability and Recovery Options http://www.vmware.com/files/pdf/solutions/ SQL_Server_on_VMware- Availability_and_Recovery_Options.pdf

But it’s not all that simple Communication is critical Network interruptions can cause big problems: • Unplanned failovers in mirroring • Loss of quorum Even planned movements of virtual machines can cause packet loss and communication drops (and we’re not even talking about storage migration) – this can cause failovers And you need to make sure that all components in mirroring are never on the same host

Your biggest takeaways Monitoring is critical • If you have automatic failover, you need to make sure the witness and mirror are healthy • If you ignore failures, the likelihood of split brain taking everything down goes up Placement of the witness is important • If you’re using multiple datacenters, the witness should be in the primary datacenter

sys.database_mirroring This DMV helps you quickly see which databases are mirrored For each database that’s mirrored, you can see: • Mirroring mode • Who’s the partner and what their state is • Who’s the witness and what their state is • Endpoint address

Database Mirroring Monitor Job This is created automatically if you use SQL Server Management Studio to set up mirroring • Runs every one minute by default • Calls the built in sp_dbmmonitorupdate procedure (which you can use in your own code, if you want) This job allows you to see the current mirroring status by using: • The Database Mirroring Monitor tool • The sp_dbmmonitorresults procedure

Launching the monitor Right click on the database name in SSMS Snoop around under ‘Tasks’

Performance counters Per database mirrored Tracking and baselining these is helpful in case you need to troubleshoot performance problems Some highlights from the full list: •  Log Send Queue KB •  Redo Queue KB •  Pages sent / sec •  Redo bytes / sec •  Transaction Delay http://msdn.microsoft.com/en-us/library/ms189931.aspx

Thresholds and alerts • Set in the Database Mirroring Monitor (hard to see) • These are set per database mirrored • You must set up alerts (with Database Mail and operators) to get notified, and it’s a lot to tackle if you mirror multiple dbs

It’s better to buy monitoring You can build your own, but: • Complex and time consuming to set up alerts for state changes and performance alerts yourself • You’ll get lots of duplicate alerts (especially if you’re mirroring multiple databases) • The Database Mirroring Monitor shows a limited amount of history… troubleshooting past failovers can be tricky

The medic’s advice for mirroring

Become a mirroring pro Create a test environment • Run through setup multiple times • Practice planned failover, switching modes Implement and test monitoring • Notification of state changes (pausing, failovers) • Create a delay by performing multiple index rebuilds on the primary – do you get notifications of latency? • Shut the witness down – do you get a notification Make it fail • Practice forcing service on a mirror • Practice recovering afterwards

Here’s How … Idera Can Help You.

Geek Sync | Field Medic’s Guide to Database Mirroring

More Related Content

What's hot

Similar to Geek Sync | Field Medic’s Guide to Database Mirroring

More from IDERA Software

Recently uploaded

Geek Sync | Field Medic’s Guide to Database Mirroring