Powershell and SQL for getting Database info

Part of monitoring a SharePoint farm is to make sure there is enough space available for the content databases.

If your organization is anything like mine, segregation of duties are very important. Meaning the SQL DBA's do the SQL stuff, and we the SharePoint Admin, do the SharePoint stuff. As a SharePoint Administrator, I must say, I need to know a bit more about the SQL part of the SharePoint Farm.

So, I don't have access to the SQL Server, and I do not really need it. I just need to pull some stats and that is it. I used PowerShell and SQL Queries to get the info I need.

The info I require is:
 - All Databases on the server
 - Stats specific to each database: Size of DB, used space, and free space available. On which Drive the database files reside as well as the Log files.

The method I chose works in my environment and I am sure there are tools and other/better ways to do the same thing.


1. The PowerShell Session would need to be started with an account access to the SQL Server instance.
2. I want to save the info to CSV files for analysis in Excel.

##########################################################################
# Login to farm with Service Account
##########################################################################





$credential = New-Object System.Management.Automation.PsCredential("YourServiceAccount", (ConvertTo-SecureString "yourPassword" -AsPlainText -Force))




start-process powershell.exe -Credential $credential -NoNewWindow -ArgumentList "Start-Process powershell.exe .\SQLStats.ps1 -verb runas"









sqlFunctions.ps1
function Invoke-SQL {
      param(
      [string] $dataSource = ".\SQLEXPRESS",
      [string] $database = "MasterData",
      [string] $sqlCommand = $(throw "Please specify a query."),
      [bool] $export = $false,
      [string] $filename = "data.csv"
      )


      $connectionString =     "Data Source=$dataSource; " +
                              "Integrated Security=SSPI; " +
                              "Initial Catalog=$database"

      $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
      $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
      $connection.Open()

      $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
      $dataset = New-Object System.Data.DataSet
      $adapter.Fill($dataSet) | Out-Null

      $connection.Close()
      $dataSet.Tables

      if($export)
      {
            Remove-Item $filename
            foreach($table in $dataset.Tables)
            {
                  #$table | export-csv $filename -notypeinformation -append
                  $table | ConvertTo-Csv -NoTypeInformation `
                         | select -Skip 1 `
                         | Out-File $filename -Append
            }
      }

}


#I added these queries as functions. Not the right way, but anyway:

#This SQL Query runs a Stored Procedure on the SQL Server for each DB

function getDBStatsQuery()
{
      $query = "exec sp_msforeachdb
      'use [?];
      select DB_NAME() AS DbName,
      physical_name,
      CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) AS Status,
      CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')) AS Recovery,
      CONVERT(varchar(20),Type) AS Type,
      SUM(size)/128.0 AS File_Size_MB,
      SUM(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
      SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
      from sys.database_files group by type, physical_name' "
      $query
}




#This SQL Query runs a Stored Procedure to get the available free space for all Drives on the SQL Server

function getOSDriveQuery()
{
      $query = "EXEC master..xp_fixeddrives"
      $query
}







SQLStats.ps1

# Script to get Database and Log sizes

# Have to run script with user that has correct permissions on SQL
Write-host "Running as: " ([Security.Principal.WindowsIdentity]::GetCurrent().Name)



#Include SQL Functions
. .\sqlFunctions.ps1





### Start of Queries
$dataSource = "sqlsever/instance" #SQL Server and Instance
$database = "Master" #Database
$outDataFileName = "infoData.csv"
$outDriveFileName = "infoSQLDrives.csv"





#Run Query
Write-host "Invoking SQL Query to retrieve Database stats..."
$results = $(Invoke-SQL -DataSource $dataSource -Database $database -SQLCommand $(getDBStatsQuery) -Export $true -FileName $outDataFileName )





#Get Drive Space available on Server
Write-host "Invoking SQL Query to retrieve OS Drives free space..."
$drives = $(Invoke-Sql -DataSource $dataSource -Database $database -sqlCommand $(getOSDriveQuery) -Export $true -FileName $outDriveFileName)




Write-host "Done..."

This PowerShell Script creates two CSV Files. One for the Databases and one for the Drives on the SQL Server.


Hope it helps someone.

Copy/Move a Web Site to another Site Collection

Now I am sure all of us in the SharePoint community had to move or copy a site's library and/or lists to another site collection. There are various reasons for this, in our case it was because of governance.

Long story short:
- One content database
- One site collection
- Multitude of web sites.
- Limit on content database size of 100GB (now there is a whole discussion about what size a content database should be and could be, but let's not go there now).

Mission:
- Move a web site (subsite) out of the current site collection to a new site collection in it's own content database.

The easy part is to create the Content Database, add it to the Web Application, create the new blank Site Collection in the Content Database, set the properties of the Content Database to only 1 Site Collection.

If you are lucky, the owners of the Web Site did not implement Workflow, Lookup lists or something else(Features) that can complicate the move.

Then you would probably use STSADM command line and do your stuff.

In my case the users implemented Lookup lists/fields. While this is nice to use in SharePoint, and you should try do design your site with this in mind. It reallllly makes it difficult to move. So that is why governance/design at a higher level is important. Then you *don't* have to move the site.

Anyway, no use crying over spilled milk.

A lookup field is a field that gets a value from another field in another list. You could have multiple lookup fields that uses multiple other lists.

Say you have a MAIN List:

MAIN List
Field A, Field B, Lookup Field C (Lookup Value from LIST 1, Field Z)

LIST 1
ID Field A, Field B, Field Z
1: Value 1, Value 2, Value FFFF
2: Value 2, Value 5, Value AAAA
3: Value 6, Value 1, Value LLLL

You add a record to MAIN:
Value A, Value B, Value LLLL

The lookup field's value in the MAIN List is stored as such: "RowId;#Value", i.e "3;#Value LLLL".

So to move this is not simple. You have to create the Lookup Lists in the Target Site. Import the Values from the Source site Lookup List. Do this for every lookup field. Try to resolve circular Look Ups.

When your Lookup Lists are created and populated then you can start to copy a list with the lookup FIELDS to the target site.

1. Open Site
2. Open Web Site
3. Open List
4. Iterate over List Items
5. Iterate over Item Versions
5. Iterate over Item Fields and copy across to -> TargetSite/WebSite/List/Item/Field
6. If the Field is a Lookup,
6.1 Try to get the Row ID from the Target Lookup List by matching the Source field Value to the arget List Field
6.2 Update the Item[FIELD] to the New ID, and Same Value.

Easy huh? Not by a long shot. It is complicated and it is easy to get confused. Maybe if I have time I will develop my program to be user friendly and so that it can be used for any site.

Don't hold your breath though.