SharePoint 2010: Rename Content Databases and the Configuration Database

SharePoint 2010 Rename Content Database and the Configuration Database: This is part 2 of an attempt to create an omnibus resource for renaming things in SharePoint. Part 1 (and the index) can be found here:

How to Rename SharePoint 2010… Everything!

Configuration Database

Don’t rename the configuration database. If you think you want to rename it, then don’t. There are a few articles that discuss detaching the database to rename it. Personally I’ve tried this three times and failed three times; and the price of failure is rebuilding your farm from scratch.

(Note that there can be more to this than just the farm name – the farm also encrypts data based on the farm password, and I believe changing the name of the Configuration Database may affect this. Since renaming the configuration database isn’t supported by Microsoft, it’s pretty tough to dig out information on this)

Content Databases

Next on the list are the content databases. Again let me reiterate – back up your farm before embarking on these changes…

To rename a content database, we’re going to remove the database in SharePoint, then detach the database in SQL Server, and reattach it with the new name. Finally, we’ll reattach the database.

Take the Content Database offline

  1. Open Central Admin
  2. Application Management –> Databases / Manage content databases
  3. Make sure you have your web application selected in the top right, then click on the content database.
  4. In the Content Database Settings page, change the database status from “Ready” to “Offline” then click “OK”
  5. Go back to the Content Database Settings page again, and check the box for “Remove content database” – note this means to remove the association from SharePoint; it doesn’t delete the database.

Rename the Database in SQL Server

  1. Open SQL Server Management Studio and find the database.
  2. Right-click on the database, select “Tasks” then “Detach…”
  3. In the Detach Database dialog, check “Drop Connections” but do not check “Update Statistics.” Then click “OK”
  4. Open Explorer and find the mdf and ldf files for the database.
  5. Rename the two files to matching names complying with your naming standard (in my case I changed WSS_Content_{GUID}.mdf to WSS_Content_01.mdf)
  6. Right-click on the “Databases” folder in SSMS, and click “Attach”
  7. Click the “Add…” button, then select the (new) mdf file, then click “OK”
  8. You’ll have to change the “Attach As,” Data, and Log file paths:
  9. Click “OK”
  10. Your database should now show up in SSMS – refresh the Object Explorer pane if it doesn’t.

Add the Content Database Back to SharePoint 2010

  1. Now back to Central Admin, and “Manage Content Databases.”
  2. Click “Add a content database”
  3. Enter the Database Name you created, then click “OK”
  4. Once that’s complete, you’ll see the database listed, and if you run “get-spdatabase” in the SharePoint 2010 Management Shell, we should have one GUID less to deal with!

Renaming Central Admin Content Database

So now you may have two questions:

1) What was all that talk about using PowerShell? We did this in Central Admin!

2) And hey – how do I rename the Central Administration content database?

This section should address both of those concerns – we’ll cover renaming the Central Administration content database in PowerShell, which will also show you how to rename any other content database in PowerShell. (Which gets important when you have a large number of databases to rename…)

What we’re going to do is create a new content database with the name we want, then move the sites from the existing content database into the new one, and finally delete the old content database.

Create a new Content Database

This is simply one PowerShell cmdlet:

New-SPContentDatabase -Name SharePoint_CA
     -WebApplication http://server:CentralAdminPort

This command provisions a database in the Farm’s SQL Server, then mounts it as a content database in SharePoint for the Central Admin web application. Now we need to move the Central Admin site from the existing content database to the one we just created. To do this we need the IDs (GUIDs, not the names) of the respective databases.

You can see the databases with a Get-SPDatabase, but if you try Get-SPContentDatabase then you’ll see other content databases in the farm, but you won’t see the Central Admin content databases. Central Admin content databases are excluded from listing in the Get-SPContentDatabase cmdlet as a security measure.

However, you can use the –webapplication parameter to explicitly get the content databases for the specific (Central Admin) web application. So:

Get-SPContentDatabase –WebApplication http://[server]:[port]

Will return the existing content database and the one we just created, so we can run:

Get-SPSite –ContentDatabase {GUID of old database} |
    Move-SPSite -DestinationDatabase {GUID of new database}

The first cmdlet selects all the SharePoint sites which are in the content database with the “evil” name. The pipe sends those sites as the input for the Move-SPSite cmdlet, with the GUID of the new database. Once you’ve moved the sites to the new database and verified them, then you can take the old content database offline, delete it from SharePoint, and delete it in SQL Server.

How to rename SharePoint 2010 … Everything!

This is the first in a series of articles to pull together all the various guides around the web to rename SharePoint 2010 databases, servers, services, and other objects. Over the coming weeks I’ll publish these articles:

  • Introduction & Windows PowerShell (this article)
  • Rename the Configuration and Content Databases
  • Rename the State Service Database
  • Rename Alternate Access Mappings
  • Rename Servers
  • Rename SharePoint Site Groups and Sites and IIS Sites
  • Rename IIS Application Pools

Why Rename SharePoint 2010 Objects?

As SharePoint 2010 installations grow in size, admins and DBAs are facing increasing frustration in dealing with Microsoft’s propensity to guarantee unique names by gluing GUIDs on the back of every name. This has gotten to be such an issue that half the reason the AutoSPInstaller script was created was to eliminate the GUIDs where possible. (Note that aside from simply being unattractive, names with GUIDs are a royal pain when you have to write scripts)

A standard SharePoint 2010 installation leaves nasty database names.

However, you may still end up with awkward names for various reasons. There are instructions on how to rename these artifacts scattered around the web; this is my attempt to gather everything into one place.

Windows PowerShell to Rename Content Databases and other SharePoint Objects

If you’re working with SharePoint 2010 and you’re not working with Windows PowerShell, it’s time to start. Virtually all the guidance here is PowerShell-based. I’ll be working with the SharePoint PowerShell provider, which has the SharePoint extensions enabled as well as the path to the 14 hive defined – it makes a great command prompt for SharePoint as well.

Some Windows PowerShell tips before we proceed. You will definitely want to understand piping. The short, simple explanation is that when you use the pipe character ( | ) between commands, it redirects the output of the first command into the second command.

So this script:

Get-SPDatabase | SELECT Name, ID

Returns all the SharePoint databases (Services Databases, Configuration Databases, Content Databases) from the current farm. Think of this as a collection of objects. This collection is then sent to the SELECT command, which will select out the Name and ID properties and list those for all the objects piped to it. Understanding the “way of pipes” will make a huge difference in your productivity with Windows PowerShell.

The other little tidbit about PowerShell and SharePoint objects are how to get the one you want. When you type Get-SPDatabase in the SharePoint Management Shell, you’ll get something looking like this:

image

Using the SharePoint 2010 Management shell.

So how do you get the one you want? The most popular way to pick out a specific object is to use the SELECT-OBJECT cmdlet, like this:

Get-SPDatabase | WHERE-OBJECT { $_.Name -eq "SomeDatabase" }

This line takes that collection of SPDatabase objects output by the Get-SPDatabase cmdlet, and pipes it to the WHERE-OBJECT cmdlet. That takes the collection of objects and uses the statement in curly braces to filter out the database we’re looking for. (“$_” is shorthand for “the current object”)

[amazon_enhanced asin=”1118021983″ container=”AmazonTable” container_class=”alignleft” price=”New” background_color=”FFFFFF” link_color=”000000″ text_color=”0000FF” /]

The problem, of course, is that we have these nasty GUID names to deal with, so we’re going to be copying and pasting from the list of databases. If we have to copy and paste anyway, then we can cut to the chase with this command:

Get-SPDatabase -ID c3c72c94-513b-4ff1-9346-3646f1bdc7ff

This gets us one of the Content Databases – in this case the one for the Central Admin site. The ID is a GUID, and easily selectable to copy and paste from the list. This is the second way to return a specific database, and until we get the names trimmed down, it’s the most straightforward. In addition, the –ID parameter is available for virtually every SharePoint object type, and will obviously always be unique.

With that under your belt, next time we’ll tackle renaming the SharePoint Content Databases, and I’ll address why you should never rename a SharePoint Farm Configuration Database.

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.