The scenario is as follows: You are administering a central SQL Server, which hosts SharePoint databases for many DEV/QA test farms. Those farms come and go as VMs with broken SharePoints are removed and new are set up. If your team is sloppy, this can happen even a few times per month (depending on the size of the team). Given that storage space is precious, and the more databases, the more SQL Server will want to keep in RAM and removing a farm by removing a VM does not delete those databases, we need to find a way to keep our SQL Server clean.
Before deciding to use this method, consider the following…
This method is only a finality. Before you choose it as your way of keeping order, try to enforce some good practices.
1. If you are rich on resources, or your team is small, consider putting each farm on a separate instance – it’s as simple as it sounds. You remove a VM, you remove the instance it’s owner was given permissions to.
2. Make your team use proper naming schema for the databases. Noone likes those pesky GUIDs, so just append the database name with it’s owner’s login, or machine name. To do that, you have to configure SharePoint via a script rather that psconfig and watch out when creating service applications, installing workflow manager and such. This requires heavy user training and discipline.
3. Make the owner remove their databases or ask for the list of databases prior to removing the machine. Of course, it won’t work if the SharePoint farm that is being removed is simply broken. You can discover databases with the following one-liner run on SharePoint server:
Get-SPDatabase | select -expand DatabaseConnectionString > databaseReport.txt
It will dump connection strings. The “Data Source” parameter is the instance, on which the DB is located, while “Initial Catalog” holds the name of the database.
If all fails…
You find yourself in a situation where several farms were already removed, but the databases not. What now? Let’s write a script!
But before we do that, we have to learn some stuff. There’s a handy little tool shipped with SQL Server, called sqlps.exe. It’s essentially powershell with goodies for SQL Server. Let’s start up our standard powershell.exe on the SQL box and enter sqlps. Pow as the prompt (the text in the front of where you type) indicates, something changed. That “SQLSERVER:\” indicates that we are on some weird drive with the letter “SQLSERVER”, right? To understand this, just run Get-PSProvider.
Those are providers. The standard one that you use, FileSystem, is just one of them. Yes, that means we can navigate our SQL Server structure just like files and folders. To move on a bit quicker navigate to where the real fun starts:
cd SQL cd $env:COMPUTERNAME dir
See the output? Those are your instances, listed right here in your powershell! Navigate to one of them and then to Databases. Run dir again. What’s that?
Are those your databases? Cool!
To explain things a little further, those are Microsoft.SqlServer.Management.Smo.Database objects. You can learn more about the Microsoft.SqlServer.Management.Smo API here: http://technet.microsoft.com/en-us/library/hh248032.aspx
Now what can we do with that? Here comes that part…
The script!
sqlps -NoLogo -NoProfile -Command {
###########################################################################################################################
function Test-ConfigDatabase($db)
{
$tables = @('Classes', 'Objects')
$procedures = @('proc_GetObject', 'proc_getObjectsByBaseClass')
$hasTables = @($db.Tables | select -expand Name | ? {$tables -contains $_}).Count -eq $tables.Count
$hasProcedures = @($db.StoredProcedures | select -expand Name | ? {$procedures -contains $_}).Count -eq $procedures.Count
$hasTables -and $hasProcedures
}
function Get-ObjectById($configDB, $id)
{
$GetObjectSql = @'
DECLARE @RequestGuid uniqueidentifier
EXEC [dbo].[proc_getObject]
@Id = '{0}',
@RequestGuid = @RequestGuid OUTPUT
'@
$configDB.ExecuteWithResults($GetObjectSql -F $id).Tables | % {$_.Rows}
}
function Get-ObjectsByClass($configDB, $className)
{
$GetClassIdSql = @'
SELECT TOP 1 [Id], [BaseClassId]
FROM [{0}].[dbo].[Classes]
WHERE FullName LIKE '{1},%'
'@
$GetObjectsSql = @'
DECLARE @RequestGuid uniqueidentifier
EXEC [dbo].[proc_getObjectsByBaseClass]
@BaseClassId = '{0}',
@ParentId = NULL,
@RequestGuid = @RequestGuid OUTPUT
'@
$configDB.ExecuteWithResults(($GetClassIdSql -F $configDB.Name, $className)).Tables | % {$_.Rows} | % {$_.Id.ToString('D').ToUpper()} |
% {$configDB.ExecuteWithResults($GetObjectsSql -F $_).Tables} | % {$_.Rows} | % {$_.Id.ToString('D').ToUpper()} |
% {Get-ObjectById $configDB $_}
}
cd SQL
cd $env:COMPUTERNAME
dir | % {$_.Databases} | ? {$_.IsAccessible -and (Test-ConfigDatabase $_)} |
% {
$db = $_
New-Object PSObject -Property @{
ServerInstance = $_.Parent.Name
FarmName = $_.Name
Servers = Get-ObjectsByClass $_ Microsoft.SharePoint.Administration.SPServer | % {$_.Name}
Databases = Get-ObjectsByClass $_ Microsoft.SharePoint.Administration.SPDatabase |
% {
$dbname = $_.Name
$dbService = Get-ObjectById $db $_.ParentId.ToString('D').ToUpper()
$dbServer = Get-ObjectById $db $dbService.ParentId.ToString('D').ToUpper()
New-Object PSObject -Property @{Name=$dbname;Server=$dbServer.Name;Instance=$dbService.Name}
}
}
}
###########################################################################################################################
}
It’s as simple as it looks. Read on and I’ll explain it a little. To discover a farm and it’s databases, we need to know how SharePoint stores this information. As a practice run the following query in SQL Server Management Studio against your Config DB:
SELECT Id ,BaseClassId ,FullName FROM Classes WHERE FullName LIKE 'Microsoft.SharePoint.Administration.SPDatabase,%'
now pick up the Id of the record it gave you and run this:
DECLARE @return_value int, @RequestGuid uniqueidentifier EXEC @return_value = [dbo].[proc_getObjectsByBaseClass] @BaseClassId = '<<THE ID YOU GOT>>', @ParentId = NULL, @RequestGuid = @RequestGuid OUTPUT SELECT @RequestGuid as N'@RequestGuid' SELECT 'Return Value' = @return_value
now pick up one of the GUIDs and run this:
DECLARE @return_value int, @RequestGuid uniqueidentifier EXEC @return_value = [dbo].[proc_getObject] @Id = '<<ONE OF THE IDS YOU COPIED FROM PREVIOUS PROCEDURE'S OUTPUT>>', @RequestGuid = @RequestGuid OUTPUT SELECT @RequestGuid as N'@RequestGuid' SELECT 'Return Value' = @return_value
Now look at the Name column’s value. Yes, that’s the SPDatabase object in the database. To make things faster I’ll add that the SQL instance name where this database exists is the name of the parent object (object with ID = ParentId of this object), and that object’s parent is the SPServer, where this instance (SPDatabaseService, as SharePoint calls it) resides. Now look at the script I showed you. It’s exactly the same thing, just automated.
And that’s it, but since it just dumps some objects, we would like to format them a bit to make it more usable:
$farms = .\GetFarms.ps1 | group ServerInstance
$farms | % {
$_.Group | % {
$configDB = $_.FarmName
$server = $_.ServerInstance
$path = Join-Path $pwd $server
if (-not (Test-Path $path))
{
mkdir $path | out-null
}
$path = Join-Path $path "$configDB.txt"
'Servers:' > $path
$_.Servers | % {' {0}' -F $_} >> $path
'Databases:' >> $path
$_.Databases | % {' {0} on {1}\{2}' -F $_.Name, $_.Server, $_.Instance} >> $path
}
}
The result? A directory structure, where the folders are your instances and file (the name is the same as that farm’s config database name) contains a list of servers and databases on that farm.
Happy PowerShelling!