Thursday, September 29, 2016

SharePoint Content Database PowerShell Scripts
There are a number of PowerShell database cmdlets provided by SharePoint; in this tip I will cover the ones that deal with content databases.  The following is the outline for this tip:
If you choose to use SharePoint Central Administration to manage content databases, you'll use the Manage content databases option in the screenshot below.  I will cover using Central Administration to manage content databases in a future tip.
SharePoint 2010 Central Administrator

PowerShell 101

There are quite a few tips on using PowerShell to perform various SQL Server tasks; take a look at the PowerShell tips category onMSSQLTips.com for some great content.   For managing SharePoint content databases, you have two options available in Windows Server 2008:
  • SharePoint 2010 Management Shell
  • Windows PowerShell ISE
The SharePoint 2010 Management Shell is installed automatically by SharePoint; you can find it in the Microsoft SharePoint 2010 Products program group.  The user interface is pretty much like the Command Prompt; it is simple and effective.  The Windows PowerShell ISE is what I prefer; it is a GUI tool that's a little more polished than the SharePoint 2010 Management Shell.  A good SQL Server analogy would be SQLCMD versus SQL Server Management Studio.
Windows PowerShell ISE is not installed by default on your Windows 2008 server; follow these steps to install it:
  • Go to Server Manager, Add Features
  • Check Windows PowerShell Integrated Scripting Environment (ISE)
After installing Windows PowerShell ISE, you can launch it from the Accessories / Windows PowerShell program group.  There is one additional step that you need to perform in order to use it with SharePoint; you need to load the Microsoft.SharePoint.PowerShell snap-in.  There are two ways to do this:
The following is an example of the code you can put in the profile to add the snap-in:
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
  }
There is one more thing you need and that is proper permission to use PowerShell to update a content database and the farm configuration database; you need to be a member of the SharePoint_Shell_Access database role in the respective databases.  You will need a SharePoint farm admin to add you to this role using the Add-SPShellAdmin cmdlet.
Add a user to the SharePoint_Shell_Access role in the farm configuration database only:
Add-SPShellAdmin-UserName domain\username
Add a user to the SharePoint_Shell_Access role in the farm configuration database and content database specified:
Add-SPShellAdmin -UserName DOMAIN\USERNAME -Database DATABASENAME
Add a user to the SharePoint_Shell_Access role in the farm configuration database and every content database:
Get-SPContentDatabase | Add-SPShellAdmin -UserName DOMAIN\USERNAME

Introduction

Here are a couple of points before we get in to the details:
  • If you are new to SharePoint 2010, take a look at our earlier tip Introduction to SharePoint 2010 for DBAs.  In the sections that follow the concepts presented in the earlier tip are fundamental to your understanding.
  • A content database can either be attached to a web application or detached; where a cmdlet works with a content database that is detached you will see the -Name parameter; when a cmdlet works with a content database that is attached to a web application you will see the Identity parameter; the Identity parameter can be the database name, its Id (GUID or UNIQUEIDENTIFIER as returned by Get-SPContentDatabase), or a SPContentDatabase object in the PowerShell pipeline.
  • Along the same lines you don't specify the DatabaseServer parameter when a content database is attached to a web application; SharePoint knows where it is.
  • In the sample code when I use contentdb I mean the database name
  • I'm running the PowerShell cmdlets on a Windows 2008 server that is connected to a SharePoint farm; I'm not going in to running the cmdlets remotely.

Get-SPContentDatabase

Get-SPContentDatabase returns information about one or more content databases in the SharePoint farm.  The server where you run the command is connected to a particular SharePoint farm so the available databases include only those in the farm. 
To show information about all databases in the farm, run the Get-SPContentDatabase cmdlet without any parameters.  The output is (only one database shown):
Id : 2fb2c72c-e098-4405-9d39-fefd3686fb67
Name : WSS_Content
WebApplication : SPWebApplication Name=SharePoint - 80
Server : DEV-FARM
CurrentSiteCount : 2
Server is the name of the database server; the current site count is the number of site collections in the content database.  To get information about a particular database you can add the -Identity contentdb parameter.

New-SPContentDatabase

To create a new content database run the New-SPContentDatabase cmdlet.  I would contend that this is the most important of the database cmdlets as it allows you to create content databases that you can then use for specific site collections.  A typical command to create a new content database would be:
New-SPContentDatabase -Name contentdb -DatabaseServer dbserver -WebApplication webappname `
  -MaxSiteCount 1 -WarningSiteCount 0
The funny character at the end of the first line above is the one to the left of the number 1 on your keyboard; it is the PowerShell line continuation character (a very poor choice in my opinion).  Note that the content database is associated with or owned by a web application.  You will likely need to get the web application name from your SharePoint administrator.  An example web application name would be http://intranet.yourdomainname.com. 
You can (and probably should) specify the MaxSiteCount parameter to limit the number of site collections that can be stored in this content database; the default value is 15,000.  I prefer to set the MaxSiteCount to 1 so that someone using SharePoint Central Administration to create a new site collection doesn't inadvertently put in in this content database.  The MaxSiteCount can be changed with the Set-SPContentDatabase cmdlet (described later) or in Central Administration.
Creating a content database is the first step in proactively managing your content databases.  The next step is to specify which site collection(s) are to be stored in the content database.  Your SharePoint administrator should use the New-SPSite cmdlet along with the ContentDatabase parameter when creating a new site collection in order to specify the content database for the site collection.  If you are using Central Administration to create site collections you cannot directly specify the content database for the site collection; you can make sure that only one content database is available by setting the MaxSiteCount or Database Status (described later).

Dismount-SPContentDatabase

Dismount-SPContentDatabase detaches a content database from the web application.  You would do this if you wanted to move the content database to a different web application and/or a different database server.  The operation updates the farm configuration database but otherwise leaves the content database intact in the SQL Server instance.   
A typical command to detach a database would be:
Dismount-SPContentDatabase -Identity contentdb

Mount-SPContentDatabase

Mount-SPContentDatabase is the inverse of Dismount-SPContentDatabase.  In this case you are attaching a content database to a web application.  This would be the case if you ran Dismount-SPContentDatabase and are now ready to attach the content database to a different web application or you moved a detached content database from another database server. 
A typical command to attach a content database would be:
Mount-SPContentDatabase -Name contentdb -DatabaseServer dbserver -WebApplication webappname
Mount-SPContentDatabase can also be used when you upgrade a SharePoint 2007 database to SharePoint 2010; this is referred to on TechNet as the database attach upgrade method  The database will be upgraded to the SharePoint 2010 schema and attached to the web application.  You will get a list of any errors or warnings generated by the upgrade.  Typical errors have to do with customizations referenced in the content database that are not in the web application.  I will not cover those issues here as the DBA isn't usually (hopefully) the person who needs to resolve them.

Remove-SPContentDatabase

Remove-SPContentDatabase detaches a content database from the web application and drops it from the SQL Server instance.  As a general rule I don't use this one.  Even if I do want to drop the database I use Dismount-SPContentDatabase then go to SQL Server Management Studio and drop the database. 
A typical command to detach a content database and drop it would be:
Remove-SPContentDatabase -Identity contentdb

Set-SPContentDatabase

Set-SPContentDatabase is used to set the properties of the content database.  Typical properties to set are:
  • MaxSiteCount - the maximum number of site collections that can be stored in the content database
  • WarningSiteCount - the number of site collections when a warning notification is sent
  • Status - Disabled (no site collections can be added) or OnLine (site collections can be added as long as the MaxSiteCount hasn't been reached)
A typical command to disable new site collections from being added to the content database would be:
Set-SPContentDatabase -Identity contentdb -Status Disabled
A typical command to enable new site collections to be added to the content database would be:
Set-SPContentDatabase -Identity contentdb -Status OnLine
As an aside there is a little bit on inconsistency with the content database Status in Central Administration versus the Set-SPContentDatabase cmdlet.  If you select the Manage content databases option from Central administration, you will see a list of content databases with a Database Status column that has the values Started or Stopped.  When you select a database to view or modify its properties, you will see a Database Status dropdown with the options Ready and Offline.

Test-SPContentDatabase

Test-SPContentDatabase will check a content database against a web application to determine whether all of the customizations referenced in the content database are available in the web application.  This cmdlet can check a content database that is attached to a web application or one that is detached.  In either case the output of the command is a list of errors and warnings which hopefully will be resolved by someone other than the DBA.  This is the same list that Mount-SPContentDatabase generates when you are attaching a SharePoint 2007 content database to SharePoint 2010.
A typical command to test a content database that is attached to a web application would be:
Test-SPContentDatabase -Identity contentdb
A typical command to test a content database that is not attached to a web application would be:
Test-SPContentDatabase -Name contentdb -WebApplication webappname
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)