Verifying SharePoint Mirroring Database Status

SharePoint mirroring links SharePoint 2010 to the status of mirrored databases in SQL Server 2008 and above. Database mirroring is a high availability strategy where two SQL Servers are configured to remain in sync so that if the master server fails, the mirror server is able to pick up with either minimal or no lost data. You can learn more about SQL Server high availability strategies, including mirroring, in this TechNet article or Professional SQL Server 2012 Administration (Wrox, 2012).

SharePoint Server 2010, unlike previous versions, is “mirror aware” – prior to SharePoint 2010, when a database failover event happened, you had to manually reconfigure the SharePoint servers to point to the mirror instances of the databases. With SharePoint 2010, once configured, if the primary database fails over to the mirror, SharePoint will automatically detect the failure and connect to the mirror database instance. You can read more about SharePoint availability with SQL Server database mirroring in this TechNet article.

I recently had 18 SharePoint farms and 135 servers to configure and verify, so checking over the status of all the mirrors for failover was looking pretty daunting, until I figured out this little trick.

SQL Server 2008 R2: List SQL Databases

The first step is to open the SQL PowerShell provider on the Farm’s database server. Start –> Run –> sqlps will open the PowerShell window. Alternatively, open SQL Server Management Studio (SSMS), connect to an instance, then right-click on the “Databases” folder in the Object Explorer and select “Start PowerShell.”

If you open PowerShell with “sqlps” you will start in

PS SQLSERVER:>

You’ll have to navigate down to the Databases node, using the CD command – first change to the SQL folder:

PS SQLSERVER:> CD SQL

Then you’ll want to change to the server and instance. You can either CD to the server, then to the instance, or both at once:

PS SQLSERVER\SQL:> CD Server\Instance

(If you only have one instance, or you want the default instance, the instance is “Default”)

Finally we’ll change to the databases folder:

PS SQLSERVER\SQL\Server\Instance > CD Databases

(Note that at any time you can see what folders are available using the Dir command, or the PowerShell Get-ChildItem or GCI for short)

Now that we’re on the databases folder, we want to list all the databases on the server, with some specific attributes.

gci |
SELECT Parent,
    Name,
    CreateDate,
    Size,
    SpaceAvailable,
    MirroringSafetyLevel,
    MirroringStatus |
Sort-Object Name |
Export-CSV Database_List_$env:computername.csv

We’ll use the GCI command and pipe it to a SELECT statement to choose the attributes we want. Then we pipe that result to a Sort-Object cmdlet so that our databases are sorted in alphabetical order.

Finally we pipe that to Export-CSV to create a CSV file with our database list, which we’re going to use in a minute. (Note the use of the env:computername variable to generate a unique filename in case you’re collecting these from a number of servers)

Once we have that csv file, we’ll head over to our SharePoint 2010 farm to check on the status of all the SharePoint databases, and compare them to what we have on our SQL Server.

SharePoint 2010: List SharePoint Mirror Status for Databases

On any of the application servers in your SharePoint farm, open the SharePoint Management Shell (Start –> All Programs –> Microsoft SharePoint 2010 Products –> SharePoint 2010 Management Shell). The script we want to run here is similar to the script we ran on the SQL Server:

Get-SPDatabase |
SELECT Farm, Name, Type, FailoverServer |
Sort-Object Name |
Export-CSV Database_List_$env:computername.csv

The cmdlet “Get-SPDatabase” lists all the SharePoint databases, or “all the databases SharePoint knows about.” We’re selecting the farm name (just in case we need help keeping the files straight), the database name, the database type (similar to TypeName), and the FailoverServer property, which indicates which SQL Server has been configured as the mirror server (the database names must be the same). Then we sort by database name and export the lot to a csv file.

Now we have a csv file listing the databases on the SQL Server, and another file listing the databases for our farm.

Comparing the Two

Open both csv files into Excel (just double-clicking on them will do). Then click the “View” tab, and click “View Side by Side” – a dialog will open asking which spreadsheet to compare with – select the other csv file. You want the two spreadsheets next to each other horizontally, as shown below. If you end up with them tiled vertically, click the “Arrange All” button on the View tab and select “Vertical” then click “OK” – you should end up with the two spreadsheets looking like the below:

image

This next part is important – the list of databases in SQL Server will most likely be longer than the databases from SharePoint (since all the SharePoint databases are in SQL Server, but not vice versa).

Next, open up the “Name” column in each spreadsheet – you’ll see several databases with the same name. The next step is to line up the matching databases by inserting blank rows in the SharePoint database list so the SharePoint databases line up with their matching SQL Server databases, as shown below.

image

Now that we have the two sets of databases aligned, we’re going to combine them into a single table with a bit of Excel magic. First let’s delete the first row of each file – the #TYPE directive from our PowerShell output. Next, on our SQL Server database list, click in cell A1 (or select the whole table, but clicking in the top left cell is easier). Then on the Home tab of the ribbon click “Format as Table” and choose a table format. You’ll get the Format As Table dialog indicating the range (if you just had A1 selected, it should automatically select the whole populated range). Make sure “My table has headers” is selected, then click OK.

SNAGHTML31ca59b8

You should now have a formatted table of your SQL Server databases that looks similar to this:

image

Now select the data from your SharePoint table – click and drag to select the full range, then Ctrl-C to copy it. Next click in the top cell next to the table in the SQL Server database list:

image

Ctrl-V to paste the range from the SharePoint database list – the table formatting should expand to include the pasted data:

image

Expand the columns so you can read the data – now you have a full report of the status of the databases in your farm, as well as the status on SQL Server. If you’re mirroring SharePoint databases, this is a great way to check a full status of your farm databases (shown below with several columns narrowed for display purposes)

image

  • SharePoint databases that don’t have a Failover Server haven’t had mirroring set up.
  • SQL Server databases that don’t have a matching SharePoint database may be orphans from removed services or site groups
  • Verify the mirror status of SQL Server databases that you need to set SharePoint mirroring on.

When you have a number of farms you’re configuring, and especially if you have to rely on those wily DBAs to set up mirroring, this is a quick and dirty way to review a complex configuration issue.

SharePoint 2010: Create a Pretty Table of Farm Services

Once you’ve built a SharePoint 2010 Farm, you usually need to document it. Part of the documentation should be a list of what services are running on which server. Most folks will probably either screen shot the Central Admin page showing the services running on a server, but that only shows one server at a time, so you need a screen shot for each server, and these can be tough to read.

Or you might use a PowerShell script to list the services running on each server, which is somewhat better, but still tricky to read critically.

Wouldn’t you rather have a chart like this in your documentation?

SharePoint Services Chart in Excel

 

Well follow along and find out how!

Listing the Services

First we’ll get the list of services by server. Start the SharePoint 2010 Management Shell (Start –> All Programs –> Microsoft SharePoint 2010 Products ). The PowerShell command to list running services by server is as follows:

Get-SPServer |
Foreach-Object {
    $_.ServicesInstances |
    Where-Object{
        $_.Status -eq "Online"
    }
} |
Select TypeName, Parent |
Export-csv c:\path\Services_List_$env:computername.csv

This will produce a list like this:

#TYPE Selected.Microsoft.SharePoint.Administration.SPDatabaseServiceInstance
"TypeName","Parent"
"SharePoint Foundation Search","SPServer Name=MyFarmServer01"
"Information Management Policy Configuration Service","SPServer Name=MyFarmServer01"
"SharePoint Server Search","SPServer Name=MyFarmServer01"
"Managed Metadata Web Service","SPServer Name=MyFarmServer01"
"Microsoft SharePoint Foundation Usage","SPServer Name=MyFarmServer01"
"Business Data Connectivity Service","SPServer Name=MyFarmServer01"
"Secure Store Service","SPServer Name=MyFarmServer01"

…as a csv file. If you open it in Excel, you’ll have a sheet which looks like this:

Services In Excel

 

Uh, okay – now what? Now it’s Excel magic time!

First we can open out the columns to see all our info:

Columns Opened

 

Of course the server names look icky (technical term), so we run a quick find/replace – select the column, then click the “Find & Select” button in the ribbon. Search for “SPServer Name=” and replace it with nothing, then do a Replace All:

Find and Replace

 

This should give you something that looks like this:

Fixed Server Names

(Note: You may also have the mail server and database servers listed. I had already deleted them from this list, since they don’t add much. It’s up to you whether to leave them or not)

You will need to delete the first line (#TYPE Selected.Microsoft.SharePoint.Administration…)

Now you can either click & drag to select all the rows, or just click any cell in your list. Then click “Format as Table” and select a table format (I’m partial to blue with alternating rows). You’ll get a little dialog with the range selected (it will have automatically selected the full range). Check “My table has headers” then click “OK” You should get a formatted table as shown:

Formatted Table

 

Click on “TypeName” and type “Services,” then replace “Parent” with “Servers.” Now click in C1, the column next to Servers, and type “Count” – once you leave the cell, Excel should expand the table to include the new column:

Added Count

Click in C2 and type a “1” then the enter key. Then click back in the C2 cell, then click the little “handle” in the lower right of the cell highlight and drag it down the length of the table:

Click Handle

 

This should fill the column with number 1’s – you’ll see why in a moment.

Now click the Table Tools / Design tab on the ribbon, and click the “Summarize with PivotTable” button. Keep the defaults – the selected table and to insert the table in a new spreadsheet, then click OK. This will open the PivotTable editor in a new sheet:

PivotTable Editor

In the field list on the right you should see “Services,” “Servers,” and “Count.” Drag “Services” down to where it says “Row Labels.” Then drag “Servers” to “Column Labels” and “Count” to “Values”:

Pivot Table Fields

 

Your pivot table should now look like this:

Pivot Table

 

You can type over “Row Labels,” “Column Labels” and “Sum of Count” to more intelligent names like “Services,” “Servers,” and the Farm Name. Let’s also get rid of those Totals – right-click anywhere in the Pivot Table and select “PivotTable Options” to open the options dialog. Select the “Totals & Filters” tab, then uncheck “Show grand totals for rows” and “Show grand totals for columns”:

Removing Totals

Click “OK”.

Finally, click on any of the “1” values, then on the Home tab in the Ribbon click “Conditional Formatting” then “Icon Sets” and the icon set with the check, exclamation, and red “X.” This will format the single cell, but you’ll see a small icon next to it – click on it:

Selecting Pivot Table values

From the menu, select “All cells showing ‘MyFarm’ values” – this should cover the whole range with the symbols you selected:

All Selected Symbols

One step left – let’s get rid of those ones digits! Click on the “Conditional Formatting” button and select “Manage Rules.” In the Conditional Formatting Rules Manager, select your rule, then click the “Edit Rule” button. In the “Edit Formatting Rule” dialog, check “Show Icon Only.” Then click “OK” and “OK” again. Select all the check marks, click the formatting button to center them, and voila!

Final Table

I’m working on macros to make this easier, and after that comes an Add-In to copy the table to an editable range and export it to XML, and finally a PowerShell script to take that XML script and use it to flip services on and off… Hopefully…