SharePoint Bit Me

A blog about the care and feeding of SharePoint

MS Ignite 2015 – Flying Cars Don’t Sound Cool Anymore

Day one of Ignite 2015 and I have to say I was a bit surprised at how low key the keynote presentations were. Don’t get me wrong. I wasn’t looking for a return of the patented Ballmer ‘Monkey Dance’. I know that current MS CEO, Satya Nadella, is inherently more reserved than Steve Ballmer. Really though, look at it, this is a BIG year. Microsoft just didn’t make me feel it.

This is some cool stuff! So why didn’t it make more impact? I think there are two reasons. First, Microsoft hasn’t been very quiet about these things. We knew about a lot of the coolest bits way before this week. Steve Jobs would have guarded this information like it was the U.S. Nuclear codes and sprung it on us with some fanfair, but Micrsoft has been too busy getting things done to bother with “controlling the message”. Second reason? Because flying cars don’t sound cool anymore.

Let me explain.

Flying cars have become a symbol of technologies that we’ve all thought about, that never seems to get to production. These concepts are always 5 to 10 years away and in the case of flying cars, everyone thought we would have them long before now. Every year you see a new story about someone who “finally” created a flying car and the news is met with…boredom. We’ve all seen the flying cars. On the morning news, on the Discovery channel, in 40 year old copies of Popular Mechanics, flying cars exist! They just suck, and we all know it. It isn’t the car that’s the problem. Take off and landing space, fuel economy, having drivers who wouldn’t kill each other, these are the real problems. Flying cars were an easy problem to solve; practical flying cars still elude us. But still we wonder.

Microsoft may be on the edge of giving us a boatload of flying cars. Phones that can legitimately be used as desktops. Management tools that work across cloud and on prem infrastructure. Tools for private cloud that can be scaled and managed elegantly. Useful (if still very limited) voice activated assistants for business. All cool stuff. We’ve just been so beaten down by the hype from past attempts we may be about to miss it when some of these things actually work outside of a demo.

I’m not a prophet, and I have never been a big Microsoft fanboy. I’m sure some of what they are bringing out this year won’t meet expectations. But everything I mentioned in the paragraph above has a real chance of actually being delivered within the next year. I just hope that when the first practical flying car comes out we actually notice.

May 5, 2015 Events, OffTopic

A Bag of Databases

A while ago a co-worker of mine had a little problem with attaching a LOT of databases to a customers web application. He needed to do it several times and wanted a way to easily get any errors produced as each database attached. I put together a Powershell script to do the job and it made the tedious job a lot easier.

Now I don’t think it is likely you will have exactly that same need, but it turns out that several parts of the script could be useful for a lot of things so let’s talk about some of the useful ideas.

Getting a list of items from a CSV file

To make life easier I set up the script so that it could pull the names of the databases from a common separated value (CSV) file. In this case there was only one value I wanted to put from the file, so there weren’t a lot of commas involved and the format of the file was simple, here is an example:

ContentDB  <-- The first line of the file contains column names.

Of course my co-worker was dealing with a couple of hundred files so the list was longer, but you get the idea. So here is how we used this file.

If you put the 3 lines above in a text file named InputDBs.txt in a folder on the C drive named MountData you can use it like this:

$SQLInstance = "SQLSERVER"
$WebApp = ""
$InputCSV = "C:\MountData\InputDBs.txt"
$DBNames = Import-csv $inputCSV

## Mount each DB in InputCSV
ForEach ($item in $DBNames){
Mount-SPContentDatabase –Name $item.ContentDB –WebApplication $WebApp -DatabaseServer $SQLInstance -Outvariable mountoutput -errorvariable mounterrors

The first two variables are obvious, we are setting the SQLINSTANCE to a variable so we can easily change it to use another instance in the future and the $WebApp is the Web application we are connecting the database to. $InputCSV is just the path to our file that contains all of the database names. Once we have that file we can read items out of it with

Import-csv $inputCSV

and pump them into our other variable


that we use in the next line

ForEach ($item in $DBNames){

Each line (except the first, which contains the column name) of the file is an “item” so if there are 2 database names the process would run twice. The first item would result with this line:

Mount-SPContentDatabase –Name $item.ContentDB –WebApplication $WebApp -DatabaseServer $SQLInstance -Outvariable mountoutput -errorvariable mounterrors

Would run with these values

Mount-SPContentDatabase –Name WSS_Content_DB1 –WebApplication -DatabaseServer SQLSERVER -Outvariable mountoutput -errorvariable mounterrors

The second pass would have these values:

Mount-SPContentDatabase –Name WSS_Content_DB2 –WebApplication -DatabaseServer SQLSERVER -Outvariable mountoutput -errorvariable mounterrors

Because the first “item” in the ContentDB column was WSS_Content_DB1 and the second was WSS_Content_DB2. Are you seeing the possibilities yet? For example we could eliminate the $Webapp variable and add a WebApp in our CSV file to attach each database to a different web app like this:

ContentDB,WebApp  <-- The first line of the file contains column names.

Mount-SPContentDatabase –Name $item.ContentDB –WebApplication $item.WebApp -DatabaseServer $SQLInstance -Outvariable mountoutput -errorvariable mounterrors

The same process can be used for lots of things, so get out there and find them.

March 5, 2015 SharePoint, SQL, Uncategorized

So You Say You Want an Upgrade?

Today I am going to upgrade my pre-upgrade script. Nothing too dramatic and there are still plenty of ugly constructions in it that I refuse to touch for fear that I will lose months of my life polishing the details.

The major changes are redirecting the output within the script (see my previous entry about that) and adding an option to list all the webs hosted in the farm and listing what template they use. This can be really helpful in finding where missing templates may cause things to break. Like the Fab 40 templates, still, in 2014, *sigh*.


# Please login to your SharePoint server using an account that has permissions to run PowerShell Commands.
# Place the file InfoScript.txt into a directory on the server that hosts SharePoint Central Administration and rename it to InfoScript.ps1
# Open the SharePoint 2010 Management Shell and type:
# c:\[directory where you placed the script]\InfoScript2010.ps1
# The "Out-File -width 200" part of the command is to make the output wider to fit more information on each line
# Once the script has completed, please forward the output to your Rackspace contact via email.

add-pssnapin microsoft.sharepoint.powershell -ea 0

function OutRedirect

$scriptuser = whoami
write-output "InfoScript was run under account name","------------------------------------", $scriptuser 

write-output "Servers in farm, their upgrade status and version number (version is just for comparison)"
# Added bit to weed out SQL and email servers
Get-SPServer | Where-Object { $_.role -ne "invalid" } | Select Name, Role, NeedsUpgrade, Version | out-string -width 200
write-output ""
write-output "--------------------------------------------------------------------------------------------------------"
write-output "Web Apps in Farm and if they are using claims auth or not"

Get-SPWebApplication |select DisplayName, URL, @{Label = "Claims Enabled?"; Expression = {$_.UseClaimsAuthentication}} |out-string -width 200

write-output "SharePoint Farm Build number"
Get-SPFarm | Select BuildVersion | out-string -width 200

write-output ""
write-output "--------------------------------------------------------------------------------------------------------"
write-output "Solutions in Farm"
Get-SPSolution | select Displayname, deployed, DeployedWebApplications | out-string -width 200

write-output ""
write-output "--------------------------------------------------------------------------------------------------------"
write-output "Site Collections by DB"
Get-SPContentDatabase | %{Write-Output "`nDatabase: $($_.Name)"; ForEach($site in $_.sites){write-Output ("`t " + $site.url)}}| out-string -width 200

write-output ""
write-output "--------------------------------------------------------------------------------------------------------"
write-output "Database sizes"
Get-SPDatabase |Sort Name| select Name, @{Label = "DB Size in MB"; Expression = {"{0:N2} MB" -f ($_.DiskSizeRequired/1MB)}}, Type, Id | out-string -width 200

write-output ""
write-output "--------------------------------------------------------------------------------------------------------"
write-output "SiteCollection size estimates (very rough)"
Get-SPSite | Select URL, @{Label="Storage"; Expression={"{0:N2} MB" -f ($_.Usage.Storage/1048576)}} | out-string -width 200

write-output ""
write-output "--------------------------------------------------------------------------------------------------------"
write-output "AAM Info"
write-output ""
write-output "--------------------------------------------------------------------------------------------------------"
write-output "Features in Farm"
Get-SPFeature -Limit all | out-string -width 200

write-output ""
write-output "--------------------------------------------------------------------------------------------------------"
write-output "Templates in Farm"

Get-SPWebTemplate | out-string -width 200

write-output ""
write-output "--------------------------------------------------------------------------------------------------------"
write-output "Template USED in farm by web" " "

Write-Host -foregroundcolor yellow "The final step in this script will list every web (site or subsite)
in your farm along with the template used by that web.
If you have a large number of sites it might take some time to complete and use a lot of resources. 

All the other information collected by this script has already been placed in a file,
so if you answer no, only this step will be skipped."

$continue = Read-Host "Would you like to continue? (Y/N)"
if($continue -eq "Y")
get-spsite -limit all | get-spweb -limit all |select URL, @{Label = "Template"; Expression = { $_.WebTemplate}}, @{Label = "TemplateID"; Expression = { $_.WebTemplateID}} | out-string -width 200

$OutFile = Read-Host "Please enter the path (name you want for the file included) (ex. C:\temp\InfoOutput.txt)"
outRedirect|Out-File -width 200 $OutFile

Enjoy and have a happy holiday season!

December 22, 2014 PowerShell, SharePoint

A Quick PowerShell Tip (redirecting the output of a PowerShell script)

If you have been to this blog before you may have seen my SharePoint 2013 preupgradecheck script. One thing that always bothered me about that script was that I had to ask the user to redirect the output when they ran the script by using the out-file cmdlet, like this:

Open the SharePoint 2010 Management Shell and type:
c:\[directory where you placed the script]\InfoScript2010.ps1 | Out-File -width 200 c:\InfoOutput.txt

I kept thinking there had to be a better way to redirect the output from within the script itself, but I never really took the time to come up with a solution until recently. After sitting and really thinking it over for a few minutes instead of just complaining I found a simple answer that I thought I would share here, even though it isn’t really about SharePoint.

A nice  feature in PowerShell is the function, simply put simply, a function is just a block of code that you have wrapped up so you can use it later in your script. For example:

function sayHello
write-output "Hello, how is your day?"

After creating this function in your script you can call it at any time by just using sayHello as a command and everything within the  brackets { } will execute. This is great for pieces of code that repeat, or that need to be run under different conditions, but it turns out it is also an easy way to redirect output. For example, if you have the function above in your code you can redirect the output to a file very simply.

sayHello|Out-File c:\hello.txt

So with my preupgrade check as an example, I could just enclose the entire script inside a function called outRedirect and then pipe that into the file I want.

function outRedirect
(the entire script found here)

outRedirect|Out-File -width 200 c:\InfoOutput.txt

As with most of my PowerShell advice, this isn’t exactly rocket science, but I like to share and I hope it is useful to you.

December 31, 2013 PowerShell

SQL and SharePoint

I have mentioned in previous blog entries that SQL is a pretty important part of SharePoint performance and stability. This is important because there are a few things about adjusting SQL for SharePoint that go counter to what I like to call “DBA Lore”. Database Administrators are professional specialists who have a spent a lot of time creating an unwritten set of recommendations for keeping servers running at peak efficiency. Much like wizards DBAs, are subtle and quick to anger when you try and meddle with their servers. SharePoint can be a little frustrating for these highly trained professionals because it requires a little special treatment and may break some of those unwritten rules.So let’s talk about some of those special needs.

SharePoint should have its own SQL server instance.

Most DBAs don’t argue against this in theory,  but in practice they don’t have a budget any larger than the rest of us. Economic concerns have made it a common practice to put as many databases as possible on a single instance, because each instance has a certain amount of overhead.Enter reason number one why SharePoint is a special case; it’s called the “Maxium Degree of Parallelism” or the MAXDOP option.The MAXDOP setting limits the number of processors that are used for the execution of a query with a parallel execution plan. In a system like SharePoint that tends to have a large number of queries, relative to the number of processors, running at the same time, a low MAXDOP setting is desirable. Essentially the value of 1 suppresses parallel plan generation making each query stay on the processor it starts with.  This SQL setting has been recommended with SharePoint for sometime now. In SharePoint 2013 it has gone from being a recommendation to a requirement. The MAXDOP setting can have a very negative impact on database queries that assume they will be allowed to run in parallel, so if they are hosted on the same instance as SharePoint performance will suffer.    If the MAXDOP setting isn’t a good reason for isolating SharePoint databases, then consider the fact that a SharePoint Server 2013 installation by default (basically just clicking through all the wizard options) creates 20 databases. These databases can be quite demanding on a server, and if they have to share with unrelated SQL data for resources in the same instance everything suffers.

SharePoint Databases Don’t Want Your Help

The rules are a little slippery in a few cases, but here is the general advice: Don’t run any SQL commands that query or manipulate data directly against SharePoint databases. That’s it. Don’t reorg, don’t reindex, don’t SHRINK DB’s, don’t do all the stuff normally associated with DB maintenance. Backups and DB checks are as far as you should go.

This goes double for changing the default permissions of the SQL service and admin accounts. Don’t take the db_creator and Security admin roles away from the account used to install SharePoint, don’t make the DBA’s account owner of all the databases and take the service accounts out of db_owner. DB checks and back it up, then walk away.

Finally, if your DBA’s have questions about the db types in SharePoint, how fast they grow, what recovery model they should use, all that stuff, then direct them to this link . This technet article is full of details they will hopefully find useful.

Remember SQL does all the hard work so SharePoint can make everything else look easy. It is a big part of your SharePoint equation so treat it right.


November 19, 2013 SharePoint, SQL

The Most Important PowerShell Snippet You Will See Today

Don’t worry about what it does, just cut and paste it.

New-Item -ItemType directory -Path $home\Documents\WindowsPowerShell
Write-Output "Start-Transcript" | Out-File $PROFILE

Just kidding! This is a little snippet of PowerShell code that will set it up so that every time you launch PowerShell it will automatically create a transcript of the session. That way if something goes wrong or unexpectedly right you can look at the transcript later. It also means if a co-worker goes in and breaks stuff with PowerShell without telling you, there is a transcript of what they did.

Here is what it does:

New-Item -ItemType directory -Path $home\Documents\WindowsPowerShell just creates a new directory in the users Documents folder called WindowsPowerShell.

Write-Output "Start-Transcript" | Out-File $PROFILE just writes the cmdlet Start-Transcript into a file named $PROFILE. PowerShell is hard wired to process this $PROFILE and run whatever commands are in it as a starts.

So that is it, easy but very useful.

October 31, 2013 PowerShell

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.

October 11, 2013 SQL

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.

August 18, 2013 PowerShell, SharePoint, Tools

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!




May 20, 2013 HowTo, OffTopic, PowerShell

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.

February 21, 2013 SharePoint, SQL, SSL, SSRS, Tools