Quick Tips for Managing the SharePoint 2010 Office Web Applications Cache

I presented remotely to the Boston Area SharePoint User Group (BASPUG) tonight (7/13/2016), and I referenced an article that I had written that is no longer available online. This post originally appeared as a “SharePoint Smarts” article from Idera. Idera is out of the SharePoint business nowadays, but the information I shared in that article is still relevant to those who use SharePoint 2010. So if you have a SharePoint 2010 environment and use the Office Web Apps, this post (and more specifically, the scripts contained within) is for you.

One of the hotly anticipated items in SharePoint 2010’s feature set is the introduction of the Microsoft Office Web Applications, or “Office Web Apps” for short. The release of the Office Web Apps opens up new possibilities for those who work with documents and files that are tied to Microsoft Word and other applications in the Microsoft Office Family.

What Are the Office Web Apps?

In prior versions of SharePoint, viewing and editing Office documents that existed in SharePoint document libraries normally required a client computer possessing the Microsoft Office suite of applications. If you wanted to view or edit a Word document that existed in SharePoint, for example, you needed Microsoft Word (or an equivalent application) installed on your computer.

That situation changes with the arrival of the Office Web Apps. When a SharePoint 2010 farm is properly set up and configured with the Office Web Apps, it becomes possible to view and edit several different Office document types directly from within a browser as shown in Figure 1 below.

Open Document

Figure 1: Browser-based editing of a Microsoft Word document

The Office Web Apps provide browser-based viewing and editing support for Microsoft Excel, OneNote, PowerPoint, and Word document types, and this support extends to more than just Internet Explorer. Firefox 3.x, Safari 4.x, and Google Chrome browser types are also supported for viewing and editing – making the Office Web Apps an enabler of cross-platform collaboration that centers on Office documents.

A Word about the Plumbing

As you might imagine, browser-based rendering and editing of Office documents involves a number of complex processes that engage a variety of front-end, middle-tier, and back-end components. The front-end and middle-tier tasks that are tied to document viewing and editing are handled primarily by a new set of service applications that appear when the Office Web Apps are installed. These service applications (and their associated pages, handlers, and worker processes) take care of the business of document conversion, load-balancing, and rendering for browser consumption.

Document conversion and rendering typically generate a combination of images, HTML, JavaScript, and XAML (or eXtensible Application Markup Language) that are sent to consuming browsers. The creation of these document resources is an expensive process, both in terms of CPU cycles and storage. To improve performance levels, it makes sense to generate these document resources only as needed and reuse them whenever possible. That’s where the Office Web Apps cache comes in.

The Office Web Apps cache is the back-end store that is responsible for housing images, HTML, JavaScript, and XAML resources once they have been created for a document. Each time a document is converted into a set of these resources, the resources are stored in the Office Web Apps cache. When a request for a document comes into SharePoint, the cache is checked to see if the document had been previously requested and rendered. If it had, and the cached document resources are up-to-date for the document, then the document request is served from the cache instead of engaging the Office Web Apps to convert and re-render it. Serving document resources from the Office Web Apps cache can yield significant performance improvements over scenarios where no cache is employed.

Quick side note before going too far: the Office Web Apps cache is only employed for Word and PowerPoint document types. It is not used for OneNote or Excel documents.

Inside the Office Web Apps Cache

The Office Web Apps cache takes the form of a single site collection for each Web application within a SharePoint farm. When the Office Web Apps are installed and configured in a SharePoint environment, a couple of new timer jobs are installed and run regularly within the farm. One of those timer jobs, the Office Web Apps Cache Creation timer job, ensures that each Web application where the Office Web Apps are running has a site collection like the one shown below in Figure 2.

Site Collection

Figure 2: The Office_Viewing_Service_Cache site collection

The Office_Viewing_Service_Cache site collection is a standard Team Site, and it is the location where resources are stored following the conversion and rendering of either a Word or PowerPoint document by the Office Web Apps.

The Team Site can be accessed just like any other SharePoint Team Site, and a glimpse inside the All Documents library (showing a number of document resources) appears below in Figure 3.

Cache Library

Figure 3: All Documents library in an Office Web Apps cache site collection

Managing the Cache

For such a complex system, the Office Web Apps components do a pretty good job of maintaining themselves without external intervention. This extends to the site collections that are used by Office Web Apps for caching purposes, as well. For example, the Office Web Apps Expiration timer job that is installed with the Office Web Apps removes old document resources from cache site collections once they’ve hit a certain age. The timer job also ensures that each of the site collections responsible for caching has adequate space to serve its purpose.

This doesn’t mean that there aren’t opportunities for tuning and maintenance, though. In fact, there are a couple of things that every administrator should do and review when it comes to the Office Web Apps cache.

Tip #1: Relocate the Cache to a New Database

By default, the Office Web Apps Cache Creation timer job creates an Office_Viewing_Service_Cache site collection in a content database that is collocated with one or more of the “real” site collections within each of your content Web applications. Since the cache site collection is allowed to grow to a beefy 100GB by default, it makes sense to relocate the cache site collection to its own (new) content database. By relocating the cache site collection to its own content database, it becomes easy to exclude it from other maintenance such as backups.

Relocating the cache site collection is pretty straightforward, and it can be accomplished pretty easily with following RelocateOwaCache.ps1 PowerShell script. Simply save the script, execute it, and supply the URL of a Web application within your farm where the Office Web Apps are running. The script will take care of creating a new content database within the Web application, and it will then move the Web application’s Office Web Apps cache site collection to the newly created content database.

<#
.SYNOPSIS
   RelocateOwaCache.ps1
.DESCRIPTION
   Relocates the Office Web Apps cache for a specified Web application to a new content database that is created by the script
.NOTES
   Author: Sean McDonough
   Last Revision: 07-June-2011
.PARAMETER targetUrl
   A Web application where Office Web Apps are in use
.EXAMPLE
   RelocateOwaCache.ps1 http://www.TargetWebApplication.com
#>
param 
(
	[string]$targetUrl = "$(Read-Host 'Target Web application URL [e.g. http://hostname]')"
)

function RelocateCache($targetUrl)
{
	# Ensure that the SharePoint cmdlets are loaded before continuing
	$spCmdlets = Get-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction silentlycontinue
	if ($spCmdlets -eq $Null)
	{ Add-PSSnapin Microsoft.SharePoint.PowerShell }
	
	# Get the name of the current database where the cache is located; it
	# will serve as the basis for a new content database name.
	$cacheSite = Get-SPOfficeWebAppsCache -WebApplication $targetUrl -ErrorAction stop
	$newDbName = $cacheSite.ContentDatabase.Name + "_OWACache"
	
	# Create a new content database and relocate the cache to it.  Make sure the
	# user knows what's happening each step of the way.
	Write-Host "- creating a new content database ..."
	$cacheDb = New-SPContentDatabase -Name $newDbName -WebApplication $targetUrl -ErrorAction stop
	Write-Host "- moving the Office Web Apps cache ..."
	Move-SPSite $cacheSite -DestinationDatabase $cacheDb -Confirm:$false -ErrorAction stop
	Write-Host "- performing required IISRESET ..."
	iisreset | Out-Null
	
	# Let the user know where the cache is now located
	Write-Host "Cache successfully relocated to the '$newDbName' database."

	# Abort script processing in the event an exception occurs.
	trap
	{
		Write-Warning "`n*** Script execution aborting. See below for problem encountered during execution. ***"
		$_.Message
		break
	}
}

# Launch script
RelocateCache $targetUrl

Tip #2: Review Size and Expiration Settings

When an Office_Viewing_Service_Cache site collection is provisioned within a Web application by the Office Web Apps Cache Creation timer job, it is initially configured to hold cached document resources for 30 days. As mentioned in Tip #1, a cache site collection can also grow to a maximum of 100GB by default.

Whether or not these default settings are appropriate for a Web application depends primarily upon the nature of the site collections housed within the Web application. When site collections contain primarily static documents or content that changes infrequently, it makes sense to allow the cache to grow larger and expire content less often than normal. This maximizes the benefit obtained from caching since document content turns over less frequently.

On the other hand, site collections that experience frequent document turnover and heavy collaboration traffic tend to benefit very little from large cache sizes and long expiration periods. In site collections of this nature, cached content tends to become stale quickly. Little benefit is derived from holding onto document resources that may only be good for days or even hours, so maximum cache size is reduced and expiration periods are shortened.

Tip #3: Give Yourself Some Warning

Since each Office Web App cache is a Team Site and like any other site collection, you can leverage standard SharePoint site collection features and capabilities to help you out. One such mechanism that can be of assistance is the ability to have an e-mail warning sent to site collection owners once a site collection’s size hits a predefined threshold. In the case of the Office Web Apps cache, such a warning could be a cue to increase the maximum size of the cache site collection or perhaps lower the expiration period for document resources housed within the site collection.

Like the maximum cache size setting described in Tip #2, the ability to send e-mail warnings once the cache reaches a threshold is actually tied to SharePoint’s site collection quota capabilities. The maximum size of the cache site collection is handled as a storage quota, and the warning threshold maps directly to the quota’s warning threshold as shown below in Figure 4. In the case of Figure 4, a maximum cache size of 50GB is in effect for the cache site collection, and the e-mail warning threshold is set for 25GB.

Quota

Figure 4: Quota settings for an Office Web Apps cache site collection

Knobs and Dials

Tips #2 and #3 discussed some of the more straightforward Office Web Apps cache settings that are available to you, but you might be wondering how you actually go about changing them.

The AdjustOwaCache.ps1 PowerShell script that appears below provides you with an easy way to review and change the settings discussed. Simply save the script, execute it, and supply the URL of the Web application containing the Office Web Apps cache you’d like to adjust. The script will show you the cache’s current settings and give you the opportunity to modify them.

<#
.SYNOPSIS
   AdjustOwaCache.ps1
.DESCRIPTION
   Dumps several common OWA cache settings to the console for a selected Web application and provides a mechanism for altering the those values
.NOTES
   Author: Sean McDonough
   Last Revision: 08-June-2011
.PARAMETER targetUrl
   A Web application where Office Web Apps are in use
.EXAMPLE
   AdjustOwaCache.ps1 http://www.TargetWebApplication.com
#>
param 
(
	[string]$targetUrl = "$(Read-Host 'Target Web application URL [e.g. http://hostname]')"
)

function AdjustCache($targetUrl)
{
	# Ensure that the SharePoint cmdlets are loaded before continuing
	$spCmdlets = Get-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction silentlycontinue
	if ($spCmdlets -eq $Null)
	{ Add-PSSnapin Microsoft.SharePoint.PowerShell }
	
	# Create an easy converter for GB to bytes
	$GBtoBytes = 1024 * 1024 * 1024
	
	# Get a reference to the cache site collection and extract the values we'll be
	# working with and (potentially) altering.
	$cacheSite = Get-SPOfficeWebAppsCache -WebApplication $targetUrl -ErrorAction stop
	$wacSize = $cacheSite.Quota.StorageMaximumLevel / $GBtoBytes
	$wacWarn = $cacheSite.Quota.StorageWarningLevel / $GBtoBytes
	$wacExpire = 30
	if ($cacheSite.RootWeb.Properties.ContainsKey("waccacheexpirationperiod"))
	{ $wacExpire = $cacheSite.RootWeb.Properties["waccacheexpirationperiod"] }
	Write-Host "Current OWA cache values for '$targetUrl'"
	Write-Host "-  Maximum Cache Size (GB): $wacSize"
	Write-Host "-   Warning Threshold (GB): $wacWarn"
	Write-Host "- Expiration Period (Days): $wacExpire"
	
	# Give the user the option to make changes.
	$yesOrNo = Read-Host "Would you like to change one or more values? [y/n]"
	if ($yesOrNo -eq "y")
	{
		[Int64]$newWacSize = Read-Host "-  Maximum Cache Size (GB)"
		Write-Host  "-   Warning Threshold (GB)"
		[Int64]$newWacWarn = Read-Host " (supply 0 for no warning)"
		[int]$newWacExpire = Read-Host "- Expiration Period (Days)"
		
		# Convert GB values to bytes and set the cache
		$newWacSize = ($newWacSize * $GBtoBytes)
		$newWacWarn = ($newWacWarn * $GBtoBytes)
		Set-SPOfficeWebAppsCache -WebApplication $targetUrl -ExpirationPeriodInDays $newWacExpire -MaxSizeInBytes $newWacSize -WarningSizeInBytes $newWacWarn -ErrorAction stop
	}

	# Abort script processing in the event an exception occurs.
	trap
	{
		Write-Warning "`n*** Script execution aborting. See below for problem encountered during execution. ***"
		$_.Message
		break
	}
}

# Launch script
AdjustCache $targetUrl

Conclusion

The Office Web Apps are a powerful addition to SharePoint 2010 and pave the way for greater collaboration on Office documents without the need for the Microsoft Office suite of client applications. The Office Web Apps cache is an important part of the larger Office Web Apps equation, and the cache is generally pretty good about taking care of itself. As shown in this article, though, it is still a good idea to relocate the cache from its default location. At the same time, a little bit of tuning and e-mail alerting can go a long way towards ensuring that the cache operates optimally for you in your environment.

Wrap-Up, Roll-Up, and Move-On!

My time with Idera has come to an end, so I wanted to aggregate some of the resources I assembled with them. I also wanted to share some information about my new company, Bitstream Foundry LLC.

Train Series Sometimes it’s hard to believe just how quickly time flies by. At the tail end of last December, I announced that some big changes were coming for me – namely that I would be transitioning into something new from an employment perspective. Today is the last day of normal business in March 2013, and that means my time with Idera is at an end.

My last three years with Idera have been quite a whirlwind of activity. I feel very fortunate and am extremely thankful to Idera for the opportunities they’ve afforded me – especially over the last year in my role as their Chief SharePoint Evangelist. In that role, I was given the latitude to spend a significant chunk of my time focusing on an area that is very important to me personally: the SharePoint Community.

The Roll-Up

In thinking about my role and some of what I’ve done over the last three years, it occurred to me that it might be nice to summarize and link to some of the materials I assembled while at Idera. I’ve occasionally referenced these items in the past, but I don’t think I’ve ever tried to aggregate them into one post or in one place.

Blog Posts

In the last half a year or so, my regular content generation efforts were being funneled to Idera’s SharePoint “Geek Stuff” blog. Here’s a table (with associated links) to the posts I’ve written:

March 19, 2013 Maskthumb Plan Your SharePoint Farm Right with a SQL Server Alias
February 8, 2013 Strategythumb Do You Have a SharePoint Backup Strategy?
January 17, 2013 Cheating on a Test The Five Minute Cheat-Sheet on SharePoint 2013’s Distributed Cache Service
December 20, 2012 smart girlfriends smiling and looking at the laptop Why Administrators Will Giggle Like Schoolgirls About SharePoint 2013’s New App Model
November 20, 2012 IderaIceThumb1 Sean’s Thoughts on the Microsoft SharePoint Conference 2012
October 19, 2012 Broken electric cable. Getting the Permissions Wired-Up Properly When Attaching a Content Database to a SharePoint Farm
September 21, 2012   Okay, Really – What Can I Do With a SharePoint Farm Configuration Database Backup?
August 24, 2012 roots Do I Really Need to Backup Up the SharePoint Root?
June 20, 2012 Talking with John Ferringer Interview with John Ferringer
June 8, 2012 TechEd '99 Baseball Cap TechEd – Why Should You Care?

SharePoint Smarts

There was a point in the past when Idera was publishing a sort of newsletter called “SharePoint Smarts,” and I wrote a couple of articles for the newsletter before it eventually rode off into the sunset:

Whitepapers

Over the years, I’ve also written or co-authored a handful of whitepapers for Idera. At the time I’m writing this post, it appears that a couple of those whitepapers are still available:

And although it isn’t available just yet, sometime soon Idera will be releasing another whitepaper I wrote that had the working title of “SharePoint Caching Implementation Guide.” If that sounds at all interesting, keep an eye on the Whitepapers section of Idera’s Resources page.

Moving-On

Bitstream Foundry LLC Although I’m going to miss my friends at Idera and wish them the best of luck going forward, I’m very excited about some things I’ve got cooking – particularly with my new company!

A couple of months back, I launched Bitstream Foundry, LLC, with the intention of getting back into more hands-on SharePoint work. My intention is to focus initially on a combination of custom SharePoint development work and SharePoint App Store product development. In the past, I’ve been a “switch hitter” when it comes to SharePoint, and I’ve gone back and forth between development and administration roles fairly regularly. Although I’m not abandoning my admin “comrades in arms,” I have to admit that I tend to get the greatest enjoyment out of development work. Between custom solutions and App Model development, I’m pretty sure I’ll be able to keep myself busy.

Microsoft BizSpark Things are falling into place with the new company, as well. I applied for membership in Microsoft’s BizSpark program yesterday, and within hours I was accepted – much to my surprise. Why was I surprised? Well, my company website is (at the moment) being redirected to a “coming soon” page I put together on the new Microsoft Azure Web Sites offering. I’ve been waiting for an Office 365 tenant upgrade so that I can build out a proper site on SharePoint 2013, but the upgrade seems to be taking much longer than originally expected …

I also learned today that my application to get Bitstream Foundry listed in the SharePoint App Store was approved, so the way is paved for me to roll out Apps. Now I just need to write them!

One Thing That Won’t Change

Despite all of the recent changes, one aspect of my professional life that won’t be changing is my commitment to sharing with (and giving back to) the SharePoint community. My confidence in my current situation would probably be substantially lower if it weren’t for all of you – my (SharePoint) friends. Over the last several months, my belief in “professional karma” has been strongly reinforced. I’ve always tried to help those who’ve asked for my time and assistance, and I’ve seen that goodwill return to me as I’ve sought input and worked to figure out “what’s next.” To those of you who have offered advice, provided feedback, written endorsements/recommendations, and more, you have my most heartfelt thanks.

I love interacting with all of you, and I still get tremendous enjoyment out of blogging, speaking, teaching, and sharing with everyone in the SharePoint space. My “official” days as a full-time evangelist may be behind me, but that won’t really change anything for me going forward as far as community involvement goes. I’ll continue to answer emails, blog when I have information worth sharing, assemble tools/widgets, help organize events, and generally do what I can to help all of you as you’ve helped me. I’m also honored to be a part of several upcoming events, and I hope to see some of you when I’m “on tour.” If we haven’t met, please say hi and introduce yourself. Making new friends and connections is one of the most rewarding aspects of being out-and-about :-)

References and Resources

  1. Blog Post: Big Changes and Resolutions for 2013
  2. Company: Idera
  3. Yahoo! Finance: Press Release
  4. Idera: SharePoint Geek Stuff Blog
  5. Idera: Resources Page
  6. Microsoft: BizSpark
  7. Company Site: Bitstream Foundry, LLC
  8. Microsoft: Azure Web Sites
  9. Microsoft: Office 365 Enterprise E3
  10. Microsoft: SharePoint App Store
  11. SharePoint Interface: Events and Activities

Big Changes and Resolutions for 2013

2013 promises to be a year of big changes. In this post, I cover career changes and some official resolutions I’m making for the new year.

Happy 2013 Fortune Cookie

2012 is coming to a close, and 2013 is just around the corner. I’ve been thinking about the year that has gone by, but I’ve been thinking even more about the year to come. 2013 promises to be a year of great personal change – for reasons that will become clear with a little more reading.

But first: I’ve got this friend, and many of you probably know him. His name is Brian Jackett, and nowadays he works for Microsoft as a member of their premier field engineering (PFE) team. For the last couple of years, I’ve watched (with envy, I might add) as Brian has blogged about his year-gone-by and assembled a list of goals for the coming year. He even challenged me (directly) to do the same at one point in the past, but sadly I didn’t rise to the challenge.

I’ve decided that year-end 2012 is going to be different. 2012 was a very busy year for me, and a lot of great things happened throughout the year. Despite these great things, I’m going into 2013 knowing that a lot is going to change (and frankly has to change).

Biggest Things First

The End ... Or Is It?Let me start with the most impactful change-up: my full-time role as Chief SharePoint Evangelist for Idera is coming to a close by the end of March 2013. I’ve been with Idera for over two and a half years now, and I’m sad to be moving on from such a great group of folks.

I’m leaving because Idera is undergoing some changes, and the company is in the process of adjusting its strategy on a few different levels. One of the resultant changes brought about by the shift in strategy involves the company getting back to more of an Internet/direct sales-based approach. Since a large part of my role involves community based activities and activities that don’t necessarily align with the strategy change, it doesn’t make a whole lot of sense for me to remain – at least in the full-time capacity that I currently operate in.

To be honest, I didn’t expect my role or position to be around forever. As many of you heard me declare publicly, though: I wanted to make the most of it while I had the role and the backing. I got a lot out of working with my friends at Idera, and I greatly appreciate the opportunities they afforded me. I hope it’s been as much fun for them as it has been for me.

What’s Next?

Even after my full-time role comes to a close, I’ve already had a couple of conversations around continuing to do some work with/for Idera. Despite my full-time focus on Idera over the last 2+ years, I have actually been operating as a contractor/consultant – not a full-time employee. This has left me free to take on other SharePoint work when it made sense (and when my schedule permitted). Going forward, my situation will probably just do a flip-flop: Idera will become the “side work” (if it makes sense), and something else will take center stage.

I don’t yet know what will be “showing on the main screen,” though. That’s been on my mind quite a bit recently, and I’ve been spending a lot of time trying to figure out what I really want to do next. Take a full-time role with a local organization? Do contract development work and continue to work from home? Wiggle my way into becoming the first Starbucks SharePoint barista? Something else entirely? If my preliminary assessment of what’s out there is accurate, there are quite a few different options. I’ll certainly be busy evaluating them and comparing them against my ever-evolving “what I want to do” checklist.

Can You Help Me Out?

Linked In Connection to Sean McDonough Many of you know that I do a lot of speaking, blogging, answering of questions/emails, etc. Giving back to the community and sharing what I’ve learned are a part of my DNA, and I’ll continue to do those things to the extent that I can going forward. I normally don’t ask for anything in return; I just like to know that I’m helping others.

As I try to figure out what’s next, I’d like to ask a favor: if you feel that I’ve helped you in some significant or meaningful way (through one of my sessions, in an email I’ve answered, etc.) over the last few years, would you be willing to endorse my skills or recommend me on LinkedIn? I see a wealth of opportunities “out there,” and sometimes an endorsement or recommendation can make the difference when it comes to employment or landing a client.

Resolutions

Employment and the ability to support my family aside, this is the first year (in quite a few) that I’ve made some resolutions for the new year. Although it’s an artificial break-point, I’ve separated my resolutions into “work-related” and “non-work” categories. And although I can think of lots of things I want to change, I’ve picked only three in each category to focus on.

Work-Related

Resolutions for a New Year1. Manage Distractions More Effectively. Working at home can be a dual-edged sword. If I were single, unmarried, and better-disciplined, I’d see working at home as the ability to do whatever I wanted without distraction. That’s not the reality in my world, though. Where I can remove distractions, I intend to.

Some of you chimed-in (positively) when I recently made a comment on Facebook about unsubscribing to a lot of junk email. Over time, I’ve come to realize that all of the extra email I’ve been getting is just a distraction. I can do something about that.

The same goes for email in general. I have multiple email accounts, and mail streams into those accounts throughout the day. Rather than constantly trying to stay on top of my inbox, I’m going to shift to a “let it sit” mentality. If I’m honest with myself, 95% of the email I receive can go unanswered for a while. I’ll attend to those items that require my attention, but some of the quasi real-time email discussions I’m known to have don’t really matter in the greater scheme of getting real work done.

Social networking tools are another great example. I think they can be a very positive and helpful force (especially for someone who’s at home all day, like me), but they can very easily become a full-time distraction. I cut down my Twitter use dramatically a couple of years back. I won’t even set foot “on” Yammer because of the huge, sucking, time-consuming noise it appears to make. Going forward, I’m going to attempt to use other tools (Facebook, LinkedIn, etc.) during specific windows rather than having them open all-day, everyday – even if I’m not “actively” on them.

For distractions that can’t be removed (e.g., children running around), my only option is to better manage the distractions. My home office has doors; I’ve already begun using them more. I’ll be wearing headphones more often. These are the sorts of things I can do to ensure that I remain better focused.

2. Thoughtfully Choose Work. I had to come clean with myself on this one, and that’s why I chose to word the resolution the way I did. Work is important to me, and it’s in my nature to always be working on something – even if that work is “for fun.” While I’d like to be the type of person who could cut back and work less, I don’t know that I’d be able to do so without incurring substantial anxiety.

Knowing this about myself, I’ve settled on trying to be more thoughtful about doing work. Make it a choice, not the default. Being a workaholic who labors from home, work became my default mode rather quickly and naturally. I remember a time when weekends were filled with fun activities – and leaving work meant “leaving” in both the physical and mental sense. Even if I can’t maintain boundaries that are quite that clear nowadays, I can be more conscientious about my choices and actually making work a conscious choice. That may sound like nothing more than semantics or babble, but I suspect other work-at-home types will get what I’m saying.

For me, this mentality needs to extend to “extracurricular” work-like activities, as well. I just went back through my 2012 calendar, and I counted 19 weekends where I was traveling or engaged in (SharePoint) community activities. That’s over a third of the weekends for the year. Many of those events are things I just sort of “fell” into without thinking too much about it. Perhaps I’d choose to do them all anyway, but again – it needs to be a choice, not the default course of action.

3. Spend Time on Impactful Efforts. Of all my work-related resolutions, this is the one that’s been on my mind the most. As I already mentioned (and many of you know), I spend a lot of time answering questions in email, speaking at and organizing SharePoint events, writing, blogging, etc. Although I originally viewed all of these activities as equally “good things,” in the past year or so I’ve begun to see that some of those activities are more impactful (and thus “more good”) to a wider audience than others.

In 2013, I intend to focus more of my time on efforts that are going to help “the many” rather than “the few.” No, that doesn’t mean I’m going to stop answering email and cease meaningful one-on-one interactions, but I do intend to choose where I spend my time more carefully.

In broader terms, I also intend to focus my capabilities on topics and areas that are generally more meaningful in nature. For example, my wife and her co-worker started a project a while back that has been gaining a lot of traction at a regional level – and the scope of the project is growing. Their effort, The Schizophrenia Oral History Project, profoundly impacts the lives of people living with schizophrenia and those caring for them, providing services to them, and others. I’ve been providing “technical support” (via an introduction to Prezi, registering domain names, etc.) for the project for a while, and I’m currently building a web site for the project using SharePoint and the Office 365 Preview. This sort of work is much more meaningful and fulfilling than some of the other things I’ve spent my time on, and so I want to do more of it.

Non-Work

1. Lose Another Ten Pounds. My weight has gone up and down a few times in the past. At the beginning of 2012, I was pretty heavy … and I felt it. I was out of shape, lethargic, and pretty miserable. Over the course of 2012, I lost close to 30 pounds through a combination of diet (I have Mark Rackley to thank for the plan) and exercise. Now at the end of the year, I’ve been bouncing around at roughly the same weight for a month or two – something I attribute primarily to the holidays and all the good food that’s been around. In 2013, I plan to lose another ten pounds to get down to (what I feel) is an optimal weight.

2. Take Up a Martial Art Once Again. This will undoubtedly help with #1 directly above. I practiced a couple of different martial arts in the past. Before and during college, I practiced Tae Kwon Do. A few years back, I had to reluctantly cease learning Hapkido after only a couple of years in. Martial arts are something I’ve always enjoyed (well, except when I was doing something like separating a shoulder), and I’ve found that life generally feels more balanced when I’m practicing. With the recent enrollment of my five year-old son into a martial arts program, I’m once again feeling the pull. I’ve wanted to learn more about Krav Maga for a while; since there’s a school nearby, I intend to check it out.

3. Prioritize My Home Life. This may be last on my list, but it’s certainly not least. With everything I’ve described so far, it’s probably no surprise to read that I do a pretty poor job of prioritizing home life and family activities. That’s going to change in 2013. Provided I make some headway with my other resolutions, it will become easier to focus on my wife, my kids, and my own interests without feelings of guilt.

Wrap-Up

I’ve written these resolutions down on a Post-It, and that Post-It has been placed on one of my monitors. That’ll ensure that it stays “in my face.”

Do you have any resolutions you’re making? Big changes?

References and Resources

  1. Blog: Brian Jackett
  2. Microsoft: Premier Field Engineering (PFE) Team
  3. Blog Post: Brian Jackett – Goals for 2010
  4. Company: Idera
  5. Company: Starbucks
  6. LinkedIn: Sean McDonough
  7. Facebook: Sean McDonough
  8. LinkedIn: Dr. Tracy McDonough
  9. LinkedIn: Dr. Lynda Crane
  10. Prezi: The Schizophrenia Oral History Project
  11. Prezi: Home Page
  12. Microsoft: Office 365 Preview
  13. Blog: Mark Rackley (The SharePoint Hillbilly)
  14. Wikipedia: Taekwondo
  15. Wikipedia: Hapkido
  16. Wikipedia: Krav Maga

Whaddaya Mean I Can’t Deploy My SharePoint App?

After applying some recently-released patches for SharePoint 2013, my farm’s App infrastructure went belly-up. This post describes my troubleshooting and resolution.

ULS Viewer Showing the Problem I’ve been doing a lot of work with the new SharePoint 2013 App Model in the last few months. Specifically, I’ve been working on a free tool (for Idera) that will be going into the SharePoint App Marketplace sometime soon. The tool itself is not too terribly complicated – just a SharePoint-hosted app that will allow users to analyze library properties, compare library configuration settings, etc.

The development environment that I was using to put the new application together had been humming along just fine … until today. It seems that I tempted fate today by applying a handful of RTM patches to my environment.

What Happened?

I’d heard that some patches for SharePoint 2013 RTM had been released, so I pulled them down and applied them to my development environment. Those patches were:

After all binaries had been installed and a reboot was performed, I ran the SharePoint 2013 Products Configuration Wizard. The wizard ran and completed without issue, Central Administration popped-up afterwards, and life seemed to be going pretty well.

I went back to working on my SharePoint-hosted app, and that’s when things went south. When I tried to deploy the application to my development site collection from Visual Studio 2012, it failed with the following error message:

Error occurred in deployment step ‘Install app for SharePoint’: We’re sorry, we weren’t able to complete the operation, please try again in a few minutes. If you see this message repeatedly, contact your administrator.

Okay, I thought, that’s odd. Let’s give it a second.

Three failed redeploys later, I rebooted the VM to see if that might fix things. No luck.

Troubleshooting

My development wasn’t moving forward until I figured out what was going on, so I did a quick hunt online to see if anyone had encountered this problem. The few entries I found indicated that I should verify my App settings in Central Administration, so I tried that. Strangely, I couldn’t even get those settings to come up – just error pages.

All of this was puzzling. Remember: my farm was doing just fine with the entire app infrastructure just a day earlier, and all of a sudden things were dead in the water. Something had to have happened as a result of the patches that were applied.

Not finding any help on the Internet, I fired-up ULSViewer to see what was happening as I attempted to access the farm App settings from Central Administration. These were the errors I was seeing:

Insufficient SQL database permissions for user ‘Name: SPDC\svcSpServices SID: S-1-5-21-1522874658-601840234-4276112424-1115 ImpersonationLevel: None’ in database ‘SP2013_AppManagement’ on SQL Server instance ‘SpSqlAlias’. Additional error information from SQL Server is included below.  The EXECUTE permission was denied on the object ‘proc_GetDataRange’, database ‘SP2013_AppManagement’, schema ‘dbo’.

Seeing that my service account (SPDC\svcSpServices) didn’t have the access it needed to run the proc_GetDataRange stored procedure left me scratching my head. I didn’t know what sort of permissions the service account actually required or how they were specifically granted. So, I hopped over to my SQL Server to see if anything struck me as odd or out-of-place.

Looking at the SP2013_AppManagement database, I saw that members in the SPDataAccess role had rights to execute the proc_GetDataRange stored procedure. SPDC\svcSPServices didn’t appear to be a direct member of that group (that I could tell), so I added it. Bazinga! Adding the account to the role permitted me to once again review the App settings in Central Administration.

Unfortunately, I still couldn’t deploy my Apps from Visual Studio. Going back to the ULS logs, I found the following:

Insufficient SQL database permissions for user ‘Name: NT AUTHORITY\IUSR SID: S-1-5-17 ImpersonationLevel: Impersonation’ in database ‘SP2013_AppManagement’ on SQL Server instance ‘SpSqlAlias’. Additional error information from SQL Server is included below.  The EXECUTE permission was denied on the object ‘proc_AM_PutAppPrincipal’, database ‘SP2013_AppManagement’, schema ‘dbo’.

It was obvious to me that more than just a single account was out of whack since the proc_AM_PutAppPrincipal stored procedure was now in-play. Rather than try to manually correct all possible permission issues, I decided to try and get SharePoint to do the heavy lifting for me.

Resolution

Service Applications in Central Administration Knowing that the problem was tied to the Application Management Service, I figured that one (possible) easy way to resolve the problem was to simply have SharePoint reprovision the Application Management Service service application. To do this, I carried out the following:

  1. Deleted my App Management Service Application instance (which I happened to call “Application Management Service”) in Central Administration. I checked the box for Delete data associated with the Service Applications when it appeared to ensure that I got a new app management database.
  2. Once the service application was deleted, I created a new App Management Service service application. I named it the same thing I had called it before (“Application Management Service”) and re-used the same database name I had been using (“SP2013_AppManagement”). I re-used the shared services application pool I had been using previously, too.

After completing these steps, I was able to successfully deploy my application to the development site collection through Visual Studio. I no longer saw the stored procedure access errors appearing in the ULS logs.

What Happened?

App Management Database Roles I don’t know what happened exactly, but what I observed seems to suggest that one of the patches I applied messed with the App Management service application database. Specifically, rights and permissions that one or more accounts possessed were somehow revoked by removing those accounts from the SPDataAccess role. Additional role and/or permission changes could have been made, as well – I just don’t know.

Once everything was running again, I went back into my SQL Server and had a look at the (new) SP2013_AppManagement database. Examining the role membership for SPDC\svcSpServices (which was one of the accounts that was blocked from accessing stored procedures earlier), I saw that the account had been put (back) into the SPDataAccess role. This seemed to confirm my observation that somehow things became “unwired” during the patching and/or configuration wizard run process.

 

References and Resources

  1. MSDN: Apps for SharePoint overview
  2. Company: Idera
  3. Microsoft: SharePoint App Marketplace
  4. MSDN: How to: Create a basic SharePoint-hosted app
  5. SharePoint 2013 Update: KB2737983
  6. SharePoint 2013 Update: KB2752001
  7. SharePoint 2013 Update: KB2752058
  8. SharePoint 2013 Update: KB2760355
  9. MSDN: ULSViewer

Kicking-Off 2012: SharePoint Style

My SharePoint community activities are off to a roaring start in 2012. In this post, I’ll be recapping a couple of events from the end of 2011, as well as covering new activities taking place during the first couple of months of 2012.

HighSpeedI don’t know how 2011 ended for most of you, but the year closed without much of a bang for me. I’m not complaining about that; the general slow-down gave me an opportunity to get caught up on a few things, and it was nice to spend some quality time with my friends and family.

While 2011 went out relatively quietly, 2012 seems to have arrived with a vengeance. In fact, I was doing some joking on Twitter with Brian Jackett and Rob Collie shortly after the start of the year about #NYN, or “New Year’s Nitrous.” It’s been nothing but pedal-to-the-metal and then some since the start of the year, and there’s absolutely no sign of it letting up anytime soon. I like staying busy, but in some ways I’m wondering whether or not there will be enough time to fit everything in. One day at a time …

Here’s a recap of some stuff from the tail end of 2011, as well as what I’ve got going on for the first couple of months in 2012. After February, things actually get even crazier … but I’ll save events beyond February for a later post.

SPTV

SPTV logoDuring the latter part of 2011, I had a conversation with Michael Hiles and Jon Breyfogle of DSC Consulting, a technical consulting and media services company based here in Cincinnati, Ohio. Michael and Jon had an idea: they wanted to develop a high-quality, high-production-value television program that centered on SharePoint and the larger SharePoint ecosystem/community. The initial idea was that the show would feature an interview segment, coverage of community events, SharePoint news, and some other stuff thrown in.

It was all very preliminary stuff when they initially shared the idea with me, but I told them that I thought they might be on to something. The idea of a professional show that centered on SharePoint wasn’t something that was being done, and I was really curious to see how they would do it if they elected to move forward.

Just before Christmas, Jon contacted me to let me know that they were indeed moving forward with the idea … and he asked if I’d be the show’s first SharePoint guest. I told him I’d love to help out, and so the bulk of the pilot episode was shot at the Village Tavern in Montgomery one afternoon with host Mark Tiderman and co-host Craig Pereira. Mark and I shot some pool, discussed disaster recovery, and just talked SharePoint for a fair bit. It was really a lot of fun.

The pilot isn’t yet available (publicly), but a teaser for the show is available on the SPTV web site. All in all, I think the DSC folks have done a tremendous job creating a quality, professional program. Check out the SPTV site for a taste of what’s to come!

SharePoint Saturday Columbus Kick-Off

SharePoint Saturday Columbus logoAround the time of the SPTV shooting, the planning committee for SharePoint Saturday Columbus (Brian Jackett, Jennifer Mason, Nicola Young, and I) had a checkpoint conversation to figure out what, if anything, we were going to do about SharePoint Saturday Columbus in 2012. Were we going to try to do it again? If so, were we going to change anything? What was our plan?

Everything with SPSColumbus in 2012 is still very preliminary, of course, but I can tell you that we are looking forward to having the event once again! We expect that we’ll attempt to hold the event during roughly the same part of the year as we’ve had it in the past (i.e., late summer). As we start to nail things down and come up with concrete plans, I’ll share those. Until then, keep your eyes on the SharePoint Saturday site and the SPSColumbus account on Twitter!

SharePointCincy

Those of us who reside in and around Cincinnati, Ohio, are very fortunate when it comes to SharePoint events and opportunities. In the past we’ve had SharePoint Saturday Indianapolis just to the west of us, SharePoint Saturday Columbus to the northeast, and last year we had our first ever SharePoint Saturday Cincinnati (which was a huge success!) On top of that, last year was the first ever SharePointCincy event.

SharePointCincy was similar in some ways to a SharePoint Saturday, but it was different in others. It was a day full of SharePoint sessions, but we also had Fred Studer (the General Manager for the Information Worker product group at Microsoft) come out an speak. Kroger, a local company whose SharePoint implementation I’m very familiar with, also shared their experience with SharePoint. Rather than go into too much detail, though, I encourage you to check out the SharePointCincy site yourself to see what it was all about.

Of course, the whole reason I’m mentioning SharePointCincy is that it’s coming again in March of this year! Last year’s success (the event was attended by hundreds) pretty much guaranteed that the event would happen again.

I’m part of a planning team that includes Geoff Smith, Steve Caravajal of Microsoft, Mike Smith from MAX Technical Training, and the infamous Shane Young of SharePoint911 (which, in case you didn’t know it, is based here in Cincinnati). Four of the five of us met last Friday for a kick-off meeting and to discuss how the event might go this year. It was a good breakfast and a productive meeting. I don’t have much more to share at this point (other than the fact that, “yes, it’s happening”), but I will share information as it becomes available. Stay tuned!

Secrets of SharePoint Webcast

Secrets of SharePoint logoIt’s been a few months since my last webcast on SharePoint caching, so my co-workers at Idera approached me about doing another webcast. I guess I was due.

On this Wednesday, January 18th, I’ll be delivering a Secrets of SharePoint webcast titled “The Essentials of SharePoint Disaster Recovery.” Here’s the abstract:

“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.

For those of you who have heard me speak and/or attended my webcasts in the past, you’ll probably find this session to be a bit different than ones you’ve seen or heard. The main reason I say that is because the content is primarily business-centric rather than nuts-and-bolts admin content.

That doesn’t mean that SharePoint administrators shouldn’t attend, though; on the contrary, the webcast includes a number of very important messages for admins (e.g., why DR must be driven from the business angle rather than the technical/admin angle) that could really help them in their jobs. The session expands the scope of the DR discussion, though, to include the business aspects that are so tremendously important during the DR planning process.

If what I’ve shared sounds interesting, please sign-up! The webcast is free, and I’ll be doing Q&A after the session.

SharePoint Saturday Austin

SharePoint Saturday Austin logoThis upcoming weekend, I’ll be heading down to Austin, Texas, for the first SharePoint Saturday Austin event! The event is taking place on January 21st, and it is being coordinated by Jim Bob Howard (of Juniper Strategy) and Matthew Lathrop (of Rackspace). Boy oh boy – do they have an amazing line-up of speakers and contributors. It’s quite impressive; check out the site to see what I mean.

The guys are giving me the opportunity to present “The Essentials of SharePoint Disaster Recovery” session, and I’m looking forward to it. I’m also looking forward to catching up with many of my friends … and some of my Idera co-workers (who will be coming in from Houston, Texas).

If you’re in the Austin area and looking for something to do this upcoming Saturday, come to the event. It’s free, and it’s a great chance to take in some phenomenal sessions, win some prizes, and be a part of the larger SharePoint community!

SharePoint Pro Demo Booth Session

SharePoint Pro logoOn Monday, February 20th at 12pm EST, I’m going to be doing a “demo booth” session through SharePoint Pro Magazine. The demo booth is titled “Backup Basics: SharePoint’s Backup and Restore Capabilities and Beyond.” Here’s the description for the demo booth:

SharePoint ships with a number of tools and capabilities that are geared toward protecting content and configuration. These tools provide basic coverage for your SharePoint environment and the content it contains, but they can quickly become cumbersome in real world scenarios. In this session, we will look at SharePoint’s backup and restore capabilities, discuss how they work, and identify where they fall short in common usage scenarios. We will also highlight how Idera’s SharePoint backup solution picks up where the SharePoint platform tools leave off in order to provide complete protection that is cost-effective and easy to use.

The “demo booth” concept is something new for me; it’s part “platform education” (which is where I normally spend the majority of my time and energy) and part “product education” – in this case, education about Idera’s SharePoint backup product. Being both the product manager for Idera SharePoint backup and a co-author for the SharePoint 2010 Disaster Recovery Guide leaves me in something of a unique position to talk about SharePoint’s built-in backup/restore capabilities, where gaps exist, and how Idera SharePoint backup can pick up where the SharePoint platform tools leave off.

If you’re interested in learning more about Idera’s SharePoint backup product and/or how far you can reasonably push SharePoint’s built-in capabilities, check out the demo booth.

SPTechCon 2012 San Francisco

SPTechConFebruary comes to close with a big bang when SPTechCon rolls into San Francisco for the first of two stops in 2012. For those of you who check my blog now and again, you may have noticed the SPTechCon “I’ll be speaking at” badge and link on the right-hand side of the page. Yes, that means I’ll be delivering a session at the event! The BZ Media folks always put on a great show, and I’m certainly proud to be a part of SPTechCon and presenting again this time around.

At this point, I know that I’ll be presenting “The Essentials of SharePoint Disaster Recovery.” I think I’m also going to be doing another lightning talk; I need to check up on that, though, to confirm it.

I also found out that John Ferringer (my co-author and partner-in-crime) and I are also going to have the opportunity to do an SPTechCon-sponsored book signing (for our SharePoint 2010 Disaster Recovery Guide) on the morning of Wednesday the 29th.

If you’re at SPTechCon, please swing by to say hello – either at my session, at the Idera booth, the book signing, or wherever you see me!

Additional Reading and Resources

  1. Blog: Brian Jackett’s Frog Pond of Technology
  2. Blog: Rob Collie’s PowerPivotPro
  3. Company: DSC Consulting
  4. Site: SPTV
  5. LinkedIn: Mark Tiderman
  6. LinkedIn: Craig Pereira
  7. Event: SharePoint Saturday Columbus
  8. Blog: Jennifer Mason
  9. Twitter: Nicola Young
  10. Site: SharePoint Saturday
  11. Twitter: SharePoint Saturday Columbus
  12. Event: SharePoint Saturday Cincinnati
  13. Event: SharePointCincy
  14. LinkedIn: Geoff Smith
  15. Blog: Steve Caravajal’s Ramblings
  16. Blog: Mike Smith’s Tech Training Notes
  17. Company: MAX Technical Training
  18. Blog: Shane Young’s SharePoint Farmer’s Almanac
  19. Company: SharePoint911
  20. Webcast: “Caching-In” for SharePoint Performance
  21. Site: Secrets of SharePoint
  22. Webcast: The Essentials of SharePoint Disaster Recovery
  23. Event: SharePoint Saturday Austin
  24. Blog: Jim Bob Howard
  25. Company: Juniper Strategy
  26. LinkedIn: Matthew Lathrop
  27. Company: Rackspace
  28. Company: Idera
  29. Event: SharePoint Pro Demo Booth Session
  30. Site: SharePoint Pro Magazine
  31. Product: Idera SharePoint backup
  32. Book: SharePoint 2010 Disaster Recovery Guide
  33. Event: SPTechCon 2012 San Francisco
  34. Company: BZ Media
  35. Blog: John Ferringer’s My Central Admin

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

Recent and Upcoming SharePoint Activities

In this post I talk about the recent SharePoint Cincy event. I also cover some of my upcoming activities for the next month, including a trip to Denver and SharePoint Saturday Twin Cities.

There have been some great SharePoint events recently, and quite a few more are coming up.  Here are some of the events I have been (or will be) involved in recently/soon:

SharePoint Cincy

SharePoint Cincy EventOn March 18th, Cincinnati saw it’s first (arguably) “major” SharePoint event.  SharePoint Cincy was put together by Geoff Smith, the Cincinnati CIO Roundtable, and MAX Training … and it was a huge success by any measure.  I took the picture on the right during the introductory speech by Geoff Smith, and it should give you an idea of well-attended the event was.

I was fortunate enough to deliver a talk on disaster recovery during the day, and I also helped Geoff and the organizing committee in advance of the event with some of the speaker round-up for the IT professional / administrator track.

I enjoyed the event because the audience composition was different than one would typically find at a SharePoint Saturday event.  Many of those in attendance were IT decision makers and managers rather than implementers and developers.  I attribute the high numbers in my DR session (typically not a big pull with technical crowds) to that demographic difference.

The next SharePoint Cincy event is already planned for next year (on March 12th, I believe), so keep your eyes peeled at the beginning of next year!

SharePoint Saturday Twin Cities

SharePoint Saturday Twin CitiesSome fine folks in the Minneapolis and St. Paul area (Jim Ferguson, Sarah Haase, and Wes Preston)  have worked to assemble the upcoming SharePoint Saturday Twin Cities on April 9, 2011.  Like all SharePoint Saturdays, the event is free for attendees.  There’s plenty of good education and giveaways to make it worth your while to spend a Saturday with other SharePoint enthusiasts.

I’ll be heading out to the event to deliver my IT pro caching talk titled “’Caching-In’ for SharePoint Performance”  The abstract for the session appears below

Caching is a critical variable in the SharePoint scalability and performance equation, but it’s one that’s oftentimes misunderstood or dismissed as being needed only in Internet-facing scenarios.  In this session, we’ll build an understanding of the caching options that exist within the SharePoint platform and how they can be leveraged to inject some pep into most SharePoint sites.  We’ll also cover some sample scenarios, caching pitfalls, and watch-outs that every administrator should know.

If you’re in the Twin Cities area and available on Saturday April 9th, come on by the following address …

Normandale Community College
9700 France Avenue South
Bloomington, MN 55431

… for a day of high-quality and free training.  You can register here on Eventbrite!

Lunch and Learn with Prinomic

Idera partners with a number of different companies in order to make its software more available, and one of those companies is Prinomic Technologies.  Prinomic is a consulting company based out of Denver, Colorado, and they focus on the creation of solutions that employ and target SharePoint.  They are somewhat unique in that they offer a combination of both services and products, and it affords them a great deal of flexibility when addressing customer needs.

I’ll actually be traveling out to Denver to deliver a lunch-and-learn in conjunction with Prinomic titled “SharePoint Disaster Recovery Options” on Wednesday, April 13th, 2011.  The lunch and learn is open to the public; simply follow the link (above) to register if you’re interested.

Prinomic is located at the following address:

4600 S Syracuse
9th floor
Denver, CO 80237

Colorado Springs SharePoint User Group

Knowing that I’d be out in the Denver area on April 13th, I reached out to some of the folks I know there to see if I might coordinate something with one of the local user groups.  I love speaking, and it was my hope that someone would actually grant me some more time with additional SharePoint geeks!

I was very fortunate to get a reply back from Dave Milner.  Dave and Gary Lapointe run the Colorado Springs SharePoint User Group, and they mentioned that it would be okay for me to come by and present to their group on the evening of the 13th.  So, it looks like I’ll be heading down to Colorado Springs after the lunch and learn with Prinomic!

I’ll be presenting my 2010 DR talk titled “SharePoint 2010 and Your DR Plan: New Capabilities, New Possibilities!”

Disaster recovery planning for a SharePoint 2010 environment is something that must be performed to insure your data and the continuity of business operations. Microsoft made significant enhancements to the disaster recovery landscape with SharePoint 2010, and we’ll be taking a good look at how the platform has evolved in this session. We’ll dive inside the improvements to the native backup and restore capabilities that are present in the SharePoint 2007 platform to see what has been changed and enhanced. We’ll also look at the array of exciting new capabilities that have been integrated into the SharePoint 2010 platform, such as unattended content database recovery, SQL Server snapshot integration, and configuration-only backup and restore. By the time we’re done, you will possess a solid understanding of how the disaster recovery landscape has changed with SharePoint 2010.

If you’re in the Colorado Springs area on Wednesday, April 13th, come on by the user group!  The user group meets at the following address:

Cobham Analytics
985 Space Center Drive
Suite 100
Colorado Springs, CO

Meet-and-greet activities are from 5:30pm until 6pm, and the session begins at 6pm!

TechNet Events: Transforming IT from Virtualization to the Cloud

Finally, I wanted to mention a series of events that are both going on right now and coming soon.  My good friend Matt Hester, our region’s IT Pro Evangelist with Microsoft, is traveling around putting on a series of Technet events titled “Transforming IT from Virtualization to the Cloud.”  These events are free training and center on cloud computing, why it is important, private vs. public cloud options, etc.

The event looks really cool, and it’s being offered in a number of different cities in the region.  I’ve already signed up for the Cincinnati event on April 6th (a Wednesday).  Check out Matt’s blog post (linked above) for additional details.  If you want to sign up for the Cincinnati event on April 6th, you can use this link directly.

Additional Reading and References

  1. Event: SharePoint Cincy
  2. People: Geoff Smith
  3. Blog: Jim Ferguson
  4. Twitter: Sarah Haase
  5. Blog: Wes Preston
  6. Event: SharePoint Saturday Twin Cities
  7. Registration: SharePoint Saturday Twin Cities on Eventbrite
  8. Company: Idera
  9. Company: Prinomic Technologies
  10. Lunch and Learn: “SharePoint Disaster Recovery Options”
  11. LinkedIn: Dave Milner
  12. LinkedIn: Gary Lapointe
  13. Technet Event: “Transforming IT from Virtualization to the Cloud”
  14. Technet Event: Cincinnati cloud event