SQL Tuning for SharePoint 2013

Special thanks to John Denman (@john_denman) for this list of links containing SQL Tuning tips for SharePoint 2013. Just in time for me to pass it on to my SharePoint 2013 Class.


SharePoint 2010 – 2013 preupgradecheck

For all of you who are considering an upgrade to SharePoint 2013 you may have noticed one feature that is missing from SharePoint 2010, the pre-upgrade check. In SharePoint 2007 (post service pack 2) there was an stsadm.exe -o preupgradecheck option that gave you information about possible issues upgrading your 2007 farm to 2010. In SharePoint 2010 that option is no longer available and instead the recommended upgrade tests are Test-SPContentdatabase , Test-SPSite , and Request-SPUpgradeEvaluationSite . These three PowerShell cmdlets can give you a lot of useful information, and they definitely tell you many of the things you need to know to successfully upgrade your 2010 farm to SharePoint 2013. These commands all come with one big problem, to use the above commands you have to have a SharePoint 2013 farm in which to run those commands. If you are just wondering about the upgrade to SharePoint 2013, but you aren’t yet serious enough to have installed a server you were out of options…until now!

To make some attempt at correcting this terrible injustice I have written a fairly basic PowerShell script that can give you some of the same information the old “preupgradecheck” option gave you. Information like what your farm’s build number is, what templates you have and what web uses which template, database sizes, solutions installed in the farm, etc. If you felt like it you would be able to get most of this information by digging around in Central Administration, SQL Management studio,  the SharePoint settings pages, or SharePoint designer, but this script may save you a little time. The script won’t tell you anything specific about the problems you may have upgrading to 2013, it just gives you all the information in one place to use as a reference. I hope you find it to useful, and you can find it HERE.


No Warranty Expressed or Implied. Use with caution (that applies to all PowerShell written by some random person on the internet). Do not drive or operate heavy equipment for at least 4 hours after using PowerShell. Side effects may include bleeding from the eyes (my code is not pretty), heart palpitations and severe migraine headache. Good luck.

Stupid PowerShell snippet of the week – List Installed Windows Updates.

OK, so this isn’t strictly SharePoint, but I have been bitten a few times lately by Microsoft patches, particularly those of the .NET variety. In both cases there were 2 problems that came up.

  1. Looking through a list of updates by the KBXXXXXXXX number is difficult in the Windows Update GUI
  2. There are a lot of updates, which makes it difficult to compare what is installed on two different machines.

My answer? It  isn’t exactly a quantum leap in PowerShell technology, but it is a script to list the installed Windows updates and the output can be easily directed to a text file so you can search it easily in notepad or compare it to the output from another machine.

$wu = new-object -com "Microsoft.Update.Searcher"
$updatecount = $wu.GetTotalHistoryCount()
$wu.QueryHistory(0,$updatecount)|select title


( add |out-file updatelist.txt  right after select title to dump all of the output into a text file for easy comparison.)

I hope this is a helpful snippet in your time of troubleshooting need!




Help! SharePoint 2013.

Lots of stuff is going on with SharePoint 2013. Not much has been going on with my blog. I need some topics people. My specialty is Infrastructure, Install, and technical issues around getting SharePoint up and running, but if I get a clear idea of what people want help with I will stretch to other topics. So help a guy out, let me know what information you are really dying to see content about.

SharePoint Saturday Michigan Presentation

Thanks to everyone who attended SharePoint Saturday Michigan, it was a great event with a great crowd and I hope I get to attend again next year. For those who attended my session I apologize for the missing content. I have added the slide about Service Packs back in and made some contents in the notes, since it doesn’t make a lot of sense if you just read it without me babbling on about each bullet point. For anyone interested in the rest of the slides without my babbling you can find them here.

I just got my first article on Technet Magazine published!


Quit Your Job!

SharePoint 2013 Preview has a lot of interesting and improved features and I am really interested to see how they will be used as 2013 starts to gain traction in the market. That being said, most SharePoint 2013 installations aren’t going to be fresh environments, they are going to be upgraded from SharePoint 2010. I look at SharePoint farms everyday, and frankly a lot of them aren’t ready for that upgrade. I am not blind to the fact that, as a consultant, I wouldn’t be looking at most of these farms if there weren’t some sort of problem, but there is one very common issue that I see over and over that could be solved simply.

Here is the issue. No one knows anything about the farm. Why are there 3 different names in the AAM for this web application? What is for? Why are 3 web applications attached to the same content database, using the same type of authentication? No one knows, or no one remembers, and in some cases, no one cares. What is the password to the farm account? What is the password to the account that  has farm/site collection/local machine and SQL administrator permissions? Which of the 37 farm solutions installed in the farm are actually used? Where are the wsp files and license codes for those purchased solutions?

So here is the “Quit Your Job!” part of the post. Imagine you just won the lottery. You are about to quit and move to your private island, but first you want to be nice to the person who has to take your place. Write all the accounts and passwords down, put them in a sealed envelope/password safe program and give them to your boss for safe keeping. Put a text file named info.txt on the root of the C: drive of the machine that has Central administration on it. Write down all the things that you know about the farm. All the little settings, all the reasons for unusual setups, anything that that is out of the ordinary about your farm. No passwords, no in-depth information about your network or firewall, just why and when little changes were made. In this little thought exercise, the notes are for the person taking your place, but in reality if you keep that little text file up to date the notes will be for you a year or two from now when you can’t remember why you created a web application, or host header. This one little text file will save you time, money, and possibly a few therapy sessions.

Upgrading SharePoint to 2013 (or 2010, or 2007) is a lot easier when you know what databases need to be available in the new farm and what settings are needed to make it work correctly in your environment. Right now it may look and sound like SharePoint 2013 is the hard part of the upgrade, but the new product is almost never the problem. It is the accumulation of truly necessary but unsupported customizations, day to day workarounds, and forgetfulness, that make the upgrade hard. Information is your best tool for working around a lot of obstacles caused by that accumulation.

So quit your job right now! Write down everything your replacement needs to know (or everything you need to remember in a year). Sure, you have a better chance of being hit by lightning than winning that lottery, but those notes might come in handy someday.

SharePoint Saturday Louisville presentation

First let me say thank you and congratulations to the organizing committee of the first SharePoint Saturday Louisville. The event was a huge success and went very smoothly. Second thanks to all the people who attended, what a great group! Good questions, excellent input and a positive attitude all day.

For those who attended my session (or anyone interested), here is my presentation, as promised, I hope it is useful.

System Reviews: A Check-up for SharePoint 2010

2013 Info Wave


I have been off the grid for a few days, but I am trying to catch up and here are some of the links I think might be helpful for anyone trying to get up-to-speed on Windows 8, SharePoint and Office 2013! I know it isn’t even close to a definitive list, so leave any other links you find useful in the comments.

Todd Klindt’s first SharePoint 2013 install
Introducing Excel 2013
Office and the Cloud
Rackspace Sharepoint Team Gets Early Look at Microsoft Office 2013
SharePoint 2013 Web Part: Content Search
SharePoint 2013: presentation: IT pro training
What’s new in SharePoint Server 2013 for Developers?
SharePoint 2013 Video Tutorials
SharePoint 2013 – Cross Site Collection Publishing Part 1
SharePoint 2013: 5 Reasons Why the New App Model Will Make Everyone Happy
Building Your First Power View report
Welcome to the World, SharePoint 2013 & Office 2013 !

When /Reports isn’t /Reports

Hold on tight, this one gets a little confusing.

Say you have a SharePoint 2007 farm with SQL Server Reporting Services (SSRS) integrated with it. Your reports site in SharePoint is You create a new 2010 farm and migrate your 2007 content to it. For the 2010 farm you install SQL Reporting Services 2008r2 taking mostly the defaults for a SharePoint integrated report server and then you load up the site to test the new SQL reporting install. Instead of seeing the SharePoint web you expect, you see this:

This operation not supported on a report server that is configured to run in SharePoint Integrated mode (rsOperationNotSupportedSharePointMode)

So what happened? To understand the cause of the problem you have to understand one key difference between SSRS 2008r2 and SSRS 2005. The 2005 version of SQL Reporting uses IIS to serve out information over http and https. SSRS 2008r2 (and just SQL 2008 for that matter) has its own web server component.

Typically on a SQL Reporting 2005 setup the /Reports directory for SSRS will end up in the “Default” IIS  web application, the one that exists as soon as you install IIS and has the *:80 host header binding. What this means is, any request on port 80 will go to the Default web application unless there is a specific host name requested that is bound to a different web application.

Let’s look at an example. You have a machine with the name SPWF1 and an additional fully qualified domain name (FQDN) assigned to it in DNS. With SSRS 2005 IIS is the only web server on the system. If 2 web applications are located on port 80, one the default with the *:80 host header binding, and the other a SharePoint web application with the host header binding, any http request (on port 80) that comes to IIS will have to pass one test before it gets connected to a web application. Is the request for (the name bound to the SharePoint web application) or something else , the machine name or the machine ip address for example? On such a machine you could have a SharePoint web at and a SQL reporting URL at http://SPWF1/Reports and IIS would automatically sort them out. Http:// would go to the SharePoint web application, any other request would go to the default web application.

That same server with SSRS 2008r2 would actually have 2 web servers, SQL Reporting Service’s own web server and IIS. The difference is, the web server built into SSRS doesn’t check the host header bindings in IIS. By default when you set up SSRS it will create http://<servername or FQDN> /Reports and /Reportserver URLs on port 80 with a *:80 binding. These URLs can be renamed ( /SSRS and /SSRSServer for example). SQL Reporting’s web server will completely ignore any requests that it doesn’t have a direct match for, so it shows no sign of itself if you browse to http://SPWF1 or but if it has a direct match such as http://SPWF1/Reports or it will grab the request before IIS gets to it. What this means for you is either you have to do one of two things. Make that SharePoint doesn’t have any URLs that would match the Report Server URLs, either by changing the default name of /Reports and /Reportserver,  or by changing the host header binding in the SQL Server Reporting configuration tool from *:80 to something more specific, like the machine name or a totally different FQDN and make sure that all requests to the report server use that specific name. I told you it was a little confusing 🙂 . Leave any questions in the comments and I will try to answer them to the best of my ability.