SQL Server: Service Packs and Database Mirroring

Usually the Mirroring feature of SQL Server is pretty resilient to random failures at several parts of the link between the two servers. It is so resilient because it uses only built-in features that are not affected by the outside world – so you can boot your servers, cut and reconect the link, do a lot of stuff and the database will still resume mirroring once everything is good again.

But a Service Pack is something that messes with those built-in SQL features, so this can definetily mess up with your mirroring session. Please be safe and perform these kinds of upgrades gracefully. Kinda, like reading this link: http://www.mssqltips.com/sqlservertip/2701/steps-to-apply-a-service-pack-or-patch-to-mirrored-sql-server-databases/ .

Because if you fail to do so, you may end with two suspended databases. And in this case you will have to take all your servers down, upgrade them all to the same SP level, restart them all, make use of the three little commands noted below, and pray for the database god/goddess/entity to resume your mirroring session. If everything goes fine, you will see your mirroring session back to “Synchonized” state.

See, I can not guarantee that this will work. But, you know, mirroring is resilient.

USE MASTER
GO
ALTER DATABASE <your_db> SET SAFETY FULL
GO
ALTER DATABASE <your_db> SET PARTNER RESUME
GO
--optional
ALTER DATABASE <your_db> SET SAFETY OFF
GO

Edit: I though about creating a new post just to show how resilient Database Mirroring is. But instead I will just do this quick note: even if your mirror server runs out of space, Mirroring will be able to resume as soon as you add some space. You may see the event below in your mirror instance, so be sure to keep an eye open for it (this could cause your main DB Log file too grow a lot too, so you have two symptoms for this situation).

Error: 1454, Severity: 16, State: 1.
Database mirroring will be suspended. Server instance 'YOUR_SERVER' encountered error 5149, state 3, severity 16 when it was acting as a mirroring partner for database 'YOUR_BASE'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.
Database mirroring is inactive for database 'YOUR_BASE'. This is an informational message only. No user action is required.
Bypassing recovery for database 'YOUR_BASE' because it is marked as a mirror database, which cannot be recovered. This is an informational message only. No user action is required.

To fix it, simply expand the data disks on your mirror server (I’m assuming you know how to do this, since you are reading this kind of article) and issue the command “ALTER DATABASE SET PARTNER RESUME”. Let the Mirroring magic happen, et voilà, database synchronized again.

cya!

Advertisements

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s