Mirror, Mirror, In the Farm …

SQL Server mirroring support is a welcome addition to SharePoint 2010. Although SharePoint 2010 makes use of the Failover Partner keyword in its connection strings, SharePoint itself doesn’t appear to know whether or not SQL Server has failed-over for any given database. This post explores this topic in more depth and provides a PowerShell script to dump a farm’s mirroring configuration.

This is a post I’ve been meaning to write for some time, but I’m only now getting around to it. It’s a quick one, and it’s intended to share a couple of observations and a script that may be of use to those of you who are SharePoint 2010 administrators.

Mirroring and SharePoint

The use of SQL Server mirroring isn’t something that’s unique to SharePoint, and it was possible to leverage mirroring with SharePoint 2007 … though I tended to steer people away from trying it unless they had a very specific reason for doing so and no other approach would work. There were simply too many hoops you needed to jump through in order to get mirroring to work with SharePoint 2007, primarily because SharePoint 2007 wasn’t mirroring-aware. Even if you got it working, it was … finicky.

SharePoint 2010, on the other hand, is fully mirroring-aware through the use of the Failover Partner keyword in connection strings used by SharePoint to connect to its databases.

(Side note: if you aren’t familiar with the Failover Partner keyword, here’s an excellent breakdown by Michael Aspengren on how the SQL Server Native Provider leverages it in mirroring configurations.)

There are plenty of blog posts, articles (like this one from TechNet), and books (like the SharePoint 2010 Disaster Recovery Guide that John Ferringer and I wrote) that talk about how to configure mirroring. It’s not particularly tough to do, and it can really help you in situations where you need a SQL Server-based high availability and/or remote redundancy solution for SharePoint databases.

This isn’t a blog post about setting up mirroring; rather, it’s a post to share some of what I’ve learned (or think I’ve learned) and related “ah-ha” moments when it comes to mirroring.

What Are You Pointing At?

This all started when Jay Strickland (one of the Quality Assurance (QA) folks on my team at Idera) ran into some problems with one of our SharePoint 2010 farms that was used for QA purposes. The farm contained two SQL Server instances, and the database instances were setup such that the databases on the second instance mirrored the databases on the first (principal) instance. Jay had configured SharePoint’s service applications and Web applications for mirroring, so all was good.

But not really. The farm had been running properly for quite some time, but something had gone wrong with the farm’s mirroring configuration – or so it seemed. That’s when Jay pinged me on Skype one day with a question (which I’m paraphrasing here):

Is there any way to tell (from within SharePoint) which SQL Server instance is in-use by SharePoint at any given time for a database that is being mirrored?

It seemed like a simple question that should have a simple answer, but I was at a loss to give Jay anything usable off the top of my head. I told Jay that I’d get back to him and started doing some digging.

The SPDatabase Type

Putting on my developer hat for a second, I recalled that all SharePoint databases are represented by an instance of the SPDatabase type (Microsoft.SharePoint.Administration.Database specifically) or one of the other classes that derive from it, such as SPContentDatabase. Running down the available members for the SPDatabase type, I came up with the following properties and methods that were tied to mirroring in some way:

  • FailoverServer
  • FailoverServiceInstance
  • AddFailoverServiceInstance()

What I thought I would find (but didn’t) was one or more properties and/or methods that would allow me to determine which SQL Server instance was serving as the active connection point for SharePoint requests.

In fact, the more digging that I did, the more that it appeared that SharePoint had no real knowledge of where it was actually connecting to for data in mirrored setups. It was easy enough to specify which database instances should be used for mirroring configurations, but there didn’t appear to be any way to determine (from within SharePoint) if the principal was in-use or if failover to the mirrored instance had taken place.

The Key Takeaway

If you’re familiar with SQL Server mirroring and how it’s implemented, then the following diagram (which I put together for discussion) probably looks familiar:

SharePoint connecting to mirrored database

This diagram illustrates a couple of key points:

  1. SharePoint connects to SQL Server databases using the SQL Server Native Client
  2. SharePoint supplies a connection string that tells the native client which SQL Server instances (as Data Source and Failover Partner) should be used as part of a mirroring configuration.
  3. It’s the SQL Server Native Client that actually determines where connections are made, and the results of the Client’s decisions don’t directly surface through SharePoint.
    Number 3 was the point that I kept getting stuck on. I knew that it was possible to go into SQL Server Management Studio or use SQL Server’s Management Objects (SMO) directly to gain more insight around a mirroring configuration and what was happening in real-time, but I thought that SharePoint must surely surface that information in some form.

Apparently not.

Checking with the Experts

I hate when I can’t nail down a definitive answer. Despite all my reading, I wanted to bounce the conclusions I was drawing off of a few people to make sure I wasn’t missing something obvious (or hidden) with my interpretation.

  • I shot Bill Baer (Senior Technical Product Manager for SharePoint and an MCM) a note with my question about information surfacing through SharePoint. If anyone could have given me a definitive answer, it would have been him. Unfortunately, I didn’t hear back from him. In his defense, he’s pretty doggone busy.
  • I put a shout out on Twitter, and I did hear back from my good friend Todd Klindt. While he couldn’t claim with absolute certainty that my understanding was on the mark, he did indicate that my understanding was in-line with everything he’d read and conclusions he had drawn.
  • I turned to Enrique Lima, another good friend and SQL Server MCM, with my question. Enrique confirmed that SQL SMO would provide some answers, but he didn’t have additional thoughts on how that information might surface through SharePoint.

Long and short: I didn’t receive rock-solid confirmation on my conclusions, but my understanding appeared to be on-the-mark. If anyone knows otherwise, though, I’d love to hear about it (and share the information here – with proper recognition for the source, of course!)

Back to the Farm

In the end, I wasn’t really able to give Jay much help with the QA farm that he was trying to diagnose. Since I couldn’t determine where SharePoint was pointing from within SharePoint itself, I did the next best thing: I threw together a PowerShell script that would dump the (mirroring) configuration for each database in the SharePoint farm.

<#
.SYNOPSIS
   SPDBMirrorInfo.ps1
.DESCRIPTION
   Examines each of the databases in the SharePoint environment to identify which have failover partners and which don't.
.NOTES
   Author: Sean McDonough
   Last Revision: 19-August-2011
#>
function DumpMirroringInfo ()
{
	# Make sure we have the required SharePoint snap-in loaded.
	$spCmdlets = Get-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction silentlycontinue
	if ($spCmdlets -eq $Null)
	{ Add-PSSnapin Microsoft.SharePoint.PowerShell }

	# Grab databases and determine which have failover support (and which don't)
	$allDatabases = Get-SPDatabase
	$dbsWithoutFailover = $allDatabases | Where-Object {$_.FailoverServer -eq $null} | Sort-Object -Property Name
	$dbsWithFailover = $allDatabases | Where-Object {$_.FailoverServer -ne $null} | Sort-Object -Property Name
	
	# Write out unmirrored databases
	if ($dbsWithoutFailover -eq $null)
	{ Write-Host "`n`nNo databases are configured without a mirroring partner." }
	else
	{ 
		Write-Host ("`n`nDatabases without a mirroring partner: {0}" -f $dbsWithoutFailover.Count) 
		$dbsWithoutFailover | Format-Table -Property Name, Server -AutoSize 
	}

	# Dump results for mirrored databases
	if ($dbsWithFailover -eq $null)
	{ Write-Host "`nNo databases are configured with a mirroring partner." }
	else
	{ 
		Write-Host ("`nDatabases with a mirroring partner: {0}" -f $dbsWithFailover.Count) 
		$dbsWithFailover | Format-Table -Property Name, Server, FailoverServer -AutoSize
	}
	
	# For ease of reading
	Write-Host ("`n`n")
}
DumpMirroringInfo

The script itself isn’t rocket science, but it did actually prove helpful in identifying some databases that had apparently “lost” their failover partners.

Additional Reading and Resources

  1. MSDN: Using Database Mirroring
  2. Whitepaper: Using database mirroring (Office SharePoint Server)
  3. Blog Post: Clarification on the Failover Partner in the connectionstring in Database Mirror setup
  4. TechNet: Configure availability by using SQL Server database mirroring (SharePoint Server 2010)
  5. Book: The SharePoint 2010 Disaster Recovery Guide
  6. Blog: John Ferringer’s “My Central Admin”
  7. Blog: Jay Strickland’s “Slinger’s Thoughts
  8. Company: Idera
  9. MSDN: SPDatabase members
  10. MSDN: SQL Server Management Objects (SMO)
  11. Blog: Bill Baer
  12. Blog: Todd Klindt’s SharePoint Admin Blog
  13. Blog: Enrique Lima’s Intentional Thinking

Wrapping Up 2011

After some time away, I’m getting back to blogging with a recap of the last several months’ worth of events. I cover a couple of SharePoint Saturdays, a webcast, my new whitepaper, and a new CodePlex project for SharePoint administrators.

Over the last several months, I haven’t been blogging as much as I’d hoped to; in reality, I haven’t blogged at all. There are a couple of reasons for that: one of them was our recent house move (and the aftermath), and the other was a little more personal. Without going into too much detail: we were contending with a very serious health issue in our family, and that took top priority.

The good news is that the clouds are finally parting, and I’m heading into the close of 2011 on a much better note (and with more time) than I’ve spent the last several months. To get back into some blogging, I figured I’d wrap-up the last several months’ worth of activities that took place since SharePoint Saturday Columbus.

Secrets of SharePoint (SoS) Webcast

Secrets of SharePoint Webcast BannerA lot of things started coming together towards the end of October, and the first of those was another webcast that I did for Idera titled “’Caching-In’ for SharePoint Performance.” The webcast covered each of SharePoint’s built-in caching mechanisms (object caching, BLOB caching, and page output caching) as well as the Office Web Applications’ cache. I provided a rundown on each mechanism, how it worked, how it could be leveraged, and some watch-outs that came with its use.

The webcast was basically a lightweight version (40 minutes or so) of the longer (75 minute) presentation I like to present at SharePoint Saturday events. It was something of a challenge to squeeze all of the regular session’s content into 40 minutes, and I had to cut some of the material I would have liked to have kept in … but the final result turned-out pretty well.

If you’re interested in seeing the webcast, you can watch it on-demand from the SoS webcast archive. I also posted the slides in the Resources section of this blog.

SharePoint Saturday Cincinnati

SharePoint Cincinnati BannerOn Saturday October 29th, Cincinnati had its first-ever SharePoint Saturday Cincinnati event. The event took place at the Kingsgate Marriott on Goodman Drive (near University Hospital), and it was very well attended – so much so that Stacy Deere and the other folks who organized the event are planning to do so again next year!

Many people from the local SharePoint community came out to support the event, and we had a number of folks from out of town come rolling in as well to help ensure that the event was a big success. I ended up delivering two sessions: my “’Caching-In’ for SharePoint Performance” session and my “SharePoint 2010 Disaster Recovery: New Capabilities, New Possibilities!”

I had a great time at the event, and I’m hoping I’ll be fortunate enough to participate again on the next go ‘round!

New Disaster Recovery WhitePaper

WhitePaper Title PageMy co-author and good friend John Ferringer and I were hard at work throughout the summer and early Fall putting together a new disaster recovery whitepaper for Idera. The whitepaper is titled “New Features in SharePoint 2010: A Disaster Recovery Love Story,” and it’s a bromance novel that only a couple of goofballs like John and I could actually write …

Okay, there’s actually no romance in it whatsoever (thank heavens for prospective readers – no one needs us doing that to them), but there is a solid chunk of coverage on SharePoint 2010’s new platform capabilities pertaining to disaster recovery. We also review some disaster recovery basics in the whitepaper, cover things that have changed since SharePoint 2007, and identify some new watch-out areas in SharePoint 2010 that could have an impact on your disaster recovery planning.

The whitepaper is pretty substantial at 13 pages, but it’s a good read if you want to understand your platform-level disaster recovery options in SharePoint 2010. It’s a free download, so please grab a copy if it sounds interesting. John and I would certainly love to hear your feedback, as well.

SharePoint Backup Augmentation Cmdlets (SharePointBAC)

SharePointBACMany of my friends in the SharePoint community have heard me talk about some of the projects I’ve wanted to undertake to extend the SharePoint platform. I’m particularly sensitive to the plight of the administrator who is constrained (typically due to lack of resources) to use only the out-of-the-box (OOTB) tools that are available for data protection. While I think the OOTB tools do a solid job in most small and mid-size farms scenarios, there are some clear gaps that need to be addressed.

Since I’d been big on promises and short on delivery in helping these administrators, I finally started on a project to address some of the backup and restore gaps I see in the SharePoint platform. The evolving and still-under-development result is my SharePoint Backup Augmentation Cmdlets (SharePointBAC) project that is available on CodePlex.

With the PowerShell cmdlets that I’m developing for SharePoint 2010, I’m trying to introduce some new capabilities that SharePoint administrators need in order to make backup scripting with the OOTB tools a simpler and more straightforward experience. For example, one big gap that exists with the OOTB tools is that there is no way to groom a backup set. Each backup you create using Backup-SPFarm, for instance, adds to the backups that existed before it. There’s no way to groom (or remove) older backups you no longer want to keep, so disk consumption grows unless manual steps are taken to do something about it. That’s where my cmdlets come in. With Remove-SPBackupCatalog, for example, you could trim backups to retain only a certain number of them; you could also trim backups to ensure that they consume no more disk space (e.g., 100GB) than you’d like.

The CodePlex project is in alpha form right now (it’s brand spankin’ new), and it’s far from complete. I’ve already gotten some great suggestions for what I could do to continue development, though. When I combine those ideas with the ones I already had, I’m pretty sure I’ll be able to shape the project into something truly useful for SharePoint administrators.

If you or someone you know is a SharePoint administrator using the OOTB tools for backup scripting, please check out the project. I’d really love to hear from you!

SharePoint Saturday Denver

SharePoint Saturday DenverAs I type this, I’m in Colorado at the close of the third (annual) SharePoint Saturday Denver event. This year’s event was phenomenal – a full two days of SharePoint goodness! Held on Friday November 11th and Saturday November 12th at the Colorado Convention Center, this year’s event was capped at 350 participants for Saturday. A full 350 people signed-up, and the event even had a wait list.

On the first day of the event, I delivered a brand new session that I put together (in Prezi format) titled The Essentials of SharePoint Disaster Recovery. Here’s the amended abstract (and I’ll explain why it’s amended in a second) for the session:

“Are my nightly SQL Server backups good enough?” “Do I need an off-site disaster recovery facility?” “How do I even start the process of disaster recovery planning?” These are just a few of the more common questions that arise when the topic of SharePoint disaster recovery comes up. As with most things SharePoint, the real answer to each question is oftentimes “it depends.” In this business and process-centric session, we will be taking a look at the topic of SharePoint disaster recovery from multiple perspectives: business continuity planner, technical architect, platform owner, and others. Critical concepts and terms will be explained and defined, and an effective process for analyzing and formulating a disaster recovery plan will be discussed. We’ll also highlight some common mistakes that take place when working to build a disaster recovery strategy and how you can avoid them. By the end of this session, you will be armed with the knowledge needed to plan or review a disaster recovery strategy for your SharePoint environment.

The reason I amended the abstract is because the previous abstract for the session didn’t do enough to call out the fact that the presentation is primarily business-centric rather than technically focused. Many of the folks who initially came to the session were SharePoint IT pros and administrators looking for information on backup/restore, mirroring, configuration, etc. Although I cover those items at a high level in this new talk, they’re only a small part of what I discuss during the session.

On Saturday, I delivered my “’Caching-In’ for SharePoint Performance” talk during the first slot of the day. I really enjoy delivering the session; it’s probably my favorite one. I had a solid turn-out, and I had some good discussions with folks both during and after the presentation.

As I mentioned, this year’s event was a two day event. That’s a little unusual, but multi-day SharePoint Saturday events appear to be getting some traction in the community – starting with SharePoint Saturday The Conference a few months back. Some folks in the community don’t care much for this style of event, probably because there’s some nominal cost that participants typically bear for the extra day of sessions. I expect that we’ll probably continue to see more hybrid events, though, because I think they meet an unaddressed need that falls somewhere between “give up my Saturday for free training” and “pay a lot of money for a multi-day weekday conference.” Only time will tell, though.

On the Horizon

Event though 2011 isn’t over yet, I’m slowing down on some of my activities save for SharePointBAC (my new extracurricular pastime). 2012 is already looking like it’s going to be a big year for SharePoint community activities. In January I’ll be heading down to Texas for SharePoint Saturday Austin, and in February I’ll be heading to San Francisco for SPTechCon. I’ll certainly cover those activities (and others) as we approach 2012.

Additional Reading and Resources

  1. Event: SharePoint Saturday Columbus
  2. Company: Idera
  3. Webcast: “Caching-In” for SharePoint Performance
  4. Webcast Slides: “Caching-In” for SharePoint Performance
  5. Location: My blog’s Resources section
  6. Event: SharePoint Saturday Cincinnati
  7. Blog: Stacy Deere and Stephanie Donahue’s “Not Just SharePoint”
  8. SPS Cincinnati Slides: “Caching-In” for SharePoint Performance
  9. SPS Cincinnati Slides: SharePoint 2010 Disaster Recovery: New Capabilities, New Possibilities!
  10. Blog: John Ferringer’s “My Central Admin”
  11. Whitepaper: New Features in SharePoint 2010: A Disaster Recovery Love Story
  12. CodePlex: SharePoint Backup Augmentation Cmdlets (SharePointBAC)
  13. Event: SharePoint Saturday Denver
  14. Tool: Prezi
  15. SPS Denver Slides: The Essentials of SharePoint Disaster Recovery
  16. SPS Denver Slides: “Caching-In” for SharePoint Performance
  17. Event: SharePoint Saturday The Conference
  18. Event: SharePoint Saturday Austin
  19. Event: SPTechCon 2012 San Francisco