Number of VLFs and Autogrowth Settings Colour Coded to Excel with PowerShell

So you have read up on VLFs

No doubt you will have read this post by Kimberly Tripp and this one and maybe this one too and you want to identify the databases in your environment which have a large number of VLFs and also the initial size and the autogrowth settings of the log files.

There are several posts about this and doing this with PowerShell like this one or this one. As is my wont I chose to output to Excel and colour code the cells depending on the number of VLFs or the type of Autogrowth.

There is not a pure SMO way of identifying the number of VLFs in a log file that I am aware of and it is simple to use DBCC LOGINFO to get that info.

I also wanted to input the autogrowth settings, size, space used, the logical name and the file path. I started by getting all of my servers into a $Servers Array as follows

$Servers = Get-Content 'PATHTO\sqlservers.txt'

Whilst presenting at the Newcastle User Group, Chris Taylor b | t asked a good question. He asked if that was the only way to do this or if you could use your DBA database.

It is much better to make use of the system you already use to record your databases. It will also make it much easier for you to be able to run scripts against more specific groups of databases without needing to keep multiple text files up to date. You can accomplish this as follows

$Query = 'SELECT Name FROM dbo.databases WHERE CONDITION meets your needs'
$Servers = Invoke-Sqlcmd -ServerInstance MANAGEMENTSERVER -Database DBADATABASE -Query $query

I then create a foreach loop and a server SMO object (Did you read my blog post about snippets? the code for a SMO Server snippet is there) returned the number of rows for DBCC LOGINFO and the information I wanted.

foreach ($Server in $Servers)
    {
      $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
      foreach ($db in $srv.Databases|Where-Object {$_.isAccessible -eq $True})
      {
        $DB.ExecuteWithResults('DBCC LOGINFO').Tables[0].Rows.Count
         $db.LogFiles | Select Growth,GrowthType,Size, UsedSpace,Name,FileName
       }
    }

It’s not very pretty or particularly user friendly so I decided to put it into Excel

I did this by using my Excel Snippet

$snippet = @{
      Title = 'Excel Object';
      Description = 'Creates a Excel Workbook and Sheet';
      Text = @'
      # Create a .com object for Excel
    `$xl = new-object -comobject excel.application
    `$xl.Visible = `$true # Set this to False when you run in production
    `$wb = `$xl.Workbooks.Add() # Add a workbook
    `$ws = `$wb.Worksheets.Item(1) # Add a worksheet
    `$cells=`$ws.Cells
    #Do Some Stuff - perhaps -
      `$cells.item(`$row,`$col)=`'Server`'
      `$cells.item(`$row,`$col).font.size=16
      `$Cells.item(`$row,`$col).Columnwidth = 10
      `$col++
    `$wb.Saveas(`'C:\temp\Test`$filename.xlsx`')
    `$xl.quit()
    Stop-Process -Name EXCEL
    '@
    }
    New-IseSnippet @snippet

and placed the relevant bits into the foreach loop

foreach ($Server in $Servers)
    {
      $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
      foreach ($db in $srv.Databases|Where-Object {$_.isAccessible -eq $True})
      {
        $VLF = $DB.ExecuteWithResults('DBCC LOGINFO').Tables[0].Rows.Count
        $logFile = $db.LogFiles | Select Growth,GrowthType,Size, UsedSpace,Name,FileName
        $Name = $DB.name
        $cells.item($row,$col)=$Server
        $col++
        $cells.item($row,$col)=$Name
        $col++
        $cells.item($row,$col)=$VLF
        $col++
        $col++
        $Type = $logFile.GrowthType.ToString()
        $cells.item($row,$col)=$Type
        $col++
        $cells.item($row,$col)=($logFile.Size)
        $col++
        $cells.item($row,$col)=($logFile.UsedSpace)
        $col++
        $cells.item($row,$col)=$logFile.Name
        $col++
        $cells.item($row,$col)=$logFile.FileName

I had to use the ToString() method on the Type property to get Excel to display the text. I wanted to set the colour for the VLF cells to yellow or red dependant on their value and the colour of the growth type cell to red if the value was Percent. This was achieved like this

if($VLF -gt $TooMany)
    {
      $cells.item($row,$col).Interior.ColorIndex = 6 # Yellow
    }
    if($VLF -gt $WayTooMany)
    {
      $cells.item($row,$col).Interior.ColorIndex = 3 # Red
    }
    if($Type -eq 'Percent')
    {
      $cells.item($row,$col).Interior.ColorIndex = 3 #Red
    }

I also found this excellent post by which has many many snippets of code to work with excel sheets.

I used

$cells.item($row,$col).HorizontalAlignment = 3 #center
$cells.item($row,$col).HorizontalAlignment = 4 #right
$ws.UsedRange.EntireColumn.AutoFit()

although I had to move the Title so that it was after the above line so that it looked ok.


image

You can find the script here. As always test it somewhere safe first, understand what it is doing and any questions get in touch.

PowerShell Snippets A Great Learning Tool

 

When I talk to people about Powershell they often ask how can they easily learn the syntax. Here’s a good tip

Open PowerShell ISE and press CTRL + J

image

You will find a number of snippets that will enable you to write your scripts easily.  Johnathan Medd PowerShell MVP has written a good post about snippets on the Hey, Scripting Guy! blog so I will not repeat that but suggest that you go and read that post.

Not only are there default snippets for you to use but you can create your own snippets. However there isn’t a snippet for creating a new snippet so here is the code to do that

 
$snippet1 = @{
    Title = "New-Snippet";
    Description = "Create a New Snippet";
    Text = @"
`$snippet = @{
    Title = `"Put Title Here`";
    Description = `"Description Here`";
    Text = @`"
    Code in Here 
`"@
}
New-IseSnippet @snippet
"@
}
New-IseSnippet @snippet1 –Force

I frequently use the SQL Server SMO Object in my code so I created this snippet

 
$snippet = @{
    Title = "SMO-Server";
    Description = "Creates a SQL Server SMO Object";
    Text = @"
    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
"@
}
New-IseSnippet @snippet

I also use Data Tables a lot so I created a snippet for that too

 
$snippet = @{
     Title = "New-DataTable";
     Description = "Creates a Data Table Object";
     Text = @"
      # Create Table Object
 $table = New-Object system.Data.DataTable $TableName
 
 
 # Create Columns
 $col1 = New-Object system.Data.DataColumn NAME1,([string])
 $col2 = New-Object system.Data.DataColumn NAME2,([decimal])
 
 #Add the Columns to the table
 $table.columns.add($col1)
 $table.columns.add($col2)
 
 # Create a new Row
 $row = $table.NewRow() 
 
 # Add values to new row
 $row.Name1 = 'VALUE'
 $row.NAME2 = 'VALUE'
 
 #Add new row to table
 $table.Rows.Add($row)
"@
 }
 New-IseSnippet @snippet

Denniver Reining has created a Snippet Manager which you can use to further expand your snippets usage and it is free as well.

If you have further examples of useful snippets please feel free to post them in the comments below

Find Out Which Indexes are on which Filegroups using PowerShell And How To Find Other Information

 

A short post today to pass on a script I wrote to fulfil a requirement I had.

Which indexes are on which filegroups. I found a blog post showing how to do it with T-SQL but as is my wont I decided to see how easy it would be with PowerShell. I also thought that it would make a good post to show how I approach this sort of challenge.

I generally start by creating a SQL Server SMO Object You can use the SMO Object Model Diagram or Get-Member to work out what you need. As we are talking indexes and filegroups I will also create a Database object

 
$Server = "SQL2012Ser2012"
$DBName = "AdventureWorks2012"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
$DB = $srv.Databases[$DBName]

Then by piping the database object to Get-Member I can see the properties

image

Lets take a look at the table object in the same way

image

 

I can see the indexes object so I pipe that to Get-Member as well

image

Now I have enough to information to create the report. I will select the Name, Table, Type and Space Used of the Indexes and format them nicely

 
$Server = "SQL2012Ser2012"
$DBName = "AdventureWorks2012"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
$DB = $srv.Databases[$DBName]
$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed|Format-Table –AutoSize

and here are the results

image

However, you may want the results to be displayed in a different manner, maybe CSV,HTML or text file and you can do this as follows

 
$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed|ConvertTo-Csv c:\temp\filegroups.csv
Invoke-Item c:\temp\filegroups.csv

image

 

 
$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed| Out-File c:\temp\filegroups.txt
Invoke-Item c:\temp\filegroups.txt

image

 
$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed|ConvertTo-Html |Out-File c:\temp\filegroups.html
Invoke-Item c:\temp\filegroups.html

image

Hopefully this has shown you how easy it can be to use PowerShell to get all of the information that you need from your SQL Server and how to approach getting that information as well as several ways to display it

Refreshing Availability Group Database with PowerShell

Following last weeks post on Refreshing A Mirrored Database with PowerShell I thought I would write the script to refresh an Availability Group Database.

An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to eight sets of corresponding secondary databases.You can read more about Availability groups here

There are situations where you may need to refresh these databases. Disaster Recovery is an obvious one but also during development to provide testing or development environments to test your High Availability implementations, run through disaster scenarios, create run books or ensure that the code changes still work with AG. There are other scenarios but this post covers the automation of restoring an Availability Group Database from a backup.

The steps that you need to take to restore an Availability Group Database are

Remove Database from the Availability Group
Restore the Primary Replica Database
Backup the Primary Replica Database Transaction Log
Restore the Secondary and Tertiary Replica Databases with no recovery
Add the Database back into the Availability Group
Resolve Orphaned Users – Not covered in this script
Check the status

Here is my set up for this post

image

I have 3 servers SQL2012SER08AG1, SQL2012SER08AG2 and SQL2012SER08AG3 with 3 databases in an Availability Group called AG_THEBEARD1. SQL2012SER08AG2 is set up as a secondary replica using Synchronous-Commit Mode SQL2012SER08AG3 is set up as a read only replica using Asynchronous-Commit Mode. I have three databases in my Availability Group and today I shall use the database called TestDatabase (I have no imagination today!) to demonstrate the refresh

The script requires some variables to be set up at the beginning. You can easily change this and make the script into a function and call it if you desire, but for this post I shall consider the script as a standalone. The reasoning for this is that I imagine that it will be placed into a run book or stored for use in a repository for specific use and therefore reduces any pre-requisites for using it.

First we will remove the database from the Availability Group. This is achieved using the Remove-SqlAvailabilityDatabase CMDLet

 
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$SecondaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$TertiaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName 
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$PrimaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName

 

Next  Restore the Primary Replica Database, Backup the Primary Replica Database Transaction Log
and Restore the Secondary and Tertiary Replica Databases with no recovery using Restore-SqlDatabase and Backup-SqlDatabase (You can also use the SMO method in the previous post if you wish)

 

 
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile  -ServerInstance $PrimaryServer -ReplaceDatabase

# Backup Primary Database
Backup-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $PrimaryServer -BackupAction 'Log'


# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $SecondaryServer
$srv.KillAllProcesses($dbname)

# Restore Secondary Replica Database 
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $SecondaryServer -NoRecovery -ReplaceDatabase 
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $SecondaryServer -RestoreAction 'Log' -NoRecovery  -ReplaceDatabase

# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $TertiaryServer
$srv.KillAllProcesses($dbname)

# Restore Tertiary Replica Database 
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $TertiaryServer -NoRecovery -ReplaceDatabase
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $TertiaryServer -RestoreAction 'Log' -NoRecovery  -ReplaceDatabase

Then add the database back to the Availability Group

 
Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database $DBName 
Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database $DBName 
Add-SqlAvailabilityDatabase -Path $MyAgTertiaryPath -Database $DBName 

Finally test the status of the Availability Group

 
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $PrimaryServer
   $AG = $srv.AvailabilityGroups[$AGName]
   $AG.DatabaseReplicaStates|ft -AutoSize

I also like to add some output to show the progress of the script. This can be logged using Out-File or displayed on the screen using Out-Host.

 
$EndDate = Get-Date
$Time = $EndDate - $StartDate
Write-Host "
##########################################
Results of Script to refresh $DBName on
$PrimaryServer , $SecondaryServer , $TertiaryServer
on AG $AGName
Time Script anded at $EndDate and took
$Time
" -ForegroundColor Green

Here are the results of my script

image

You can get the script here

Refreshing A SQL Mirrored Database Using Powershell

 

SQL mirroring is a means of providing high availability for your SQL database. It is available in Standard Edition and although the feature is deprecated it is still widely utilised. You can read more about it on MSDN here and Jes Borland wrote a useful post answering many questions here

There are situations where you may need to refresh these databases. Disaster Recovery is an obvious one but also during development to provide testing or development environments to test your High Availability implementations, run through disaster scenarios, create run books or ensure that the code changes still work with mirroring. There are other scenarios but this post covers the automation of restoring a mirrored database from a backup.

I have mentioned before and no doubt I shall again, John Sansom wrote a great post about automation and I am a strong follower of that principle.

To refresh a SQL mirror the following steps are required, there are some gotchas that you need to be aware of which I will discuss later

remove mirroring
restore principle database from backup
perform a transaction log backup of the principle database
restore both backups on the mirror server with no recovery
recreate mirroring
resolve orphaned users
check mirroring status

Regular blog followers will know that I prefer to use Powershell when I can (and where it is relevant to do so) and so I have used Powershell to automate all of the steps above

The script requires some variables to be set up at the beginning. You can easily change this and make the script into a function and call it if you desire, but for this post I shall consider the script as a standalone. The reasoning for this is that I imagine that it will be placed into a run book or stored for use in a repository for specific use and therefore reduces any pre-requisites for using it.

Set variables as follows, the last three variables set the types for the backup action type and device type and do not need to be altered.

# Set up some variables

"$PrincipalServer = '' # Enter Principal Server Name
$MirrorServer = '' # Enter Mirror Server Name
$DBName = '' # Enter Database Name
$FileShare = '' # Enter FileShare with trailing slash
$LocationReplace = $FileShare + $DBName + 'Refresh.bak'
$LocationTran = $FileShare + $DBName + 'formirroring.trn'

$PrincipalEndPoint = 'TCP://SERVERNAME:5022' # Change as required
$MirrorEndpoint = 'TCP://SERVERNAME:5022' # Change as required
$WitnessEndpoint = 'TCP://SERVERNAME:5022' # Change as required

$Full = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
$Tran = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Log
$File = [Microsoft.SqlServer.Management.Smo.DeviceType]::File</PRE></PRE>

 

After some error checking the first thing is to create server and database SMO objects

"# Create Server objects
$Principal = New-Object Microsoft.SQLServer.Management.SMO.Server $PrincipalServer
$Mirror = New-Object Microsoft.SQLServer.Management.Smo.server $MirrorServer

#Create Database Objects
$DatabaseMirror = $Mirror.Databases[$DBName]
$DatabasePrincipal = $Principal.Databases[$DBName]</PRE></PRE>

(Added Extra – Use New-ISESnippet to create a SMO Server Snippet and use CTRL + J to find it

"New-IseSnippet -Title SMO-Server -Description "Create A SQL Server SMO Object"
-Text "`$srv = New-Object Microsoft.SqlServer.Management.Smo.Server `$server"

)

Remove Mirroring

Before we can restore the database we need to remove mirroring

"$DatabasePrincipal.ChangeMirroringState([Microsoft.SqlServer.Management.Smo.MirroringOption]::Off)

restore principle database from backup

Once mirroring has been removed we can restore the database. Stuart Moore’s Great Series provides all the code you need to backup and restore databases with Powershell. There is however a bug which can catch you out. Here’s the code

"$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore|Out-Null
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationReplace,$File)|Out-Null
$restore.Database = $DBName
$restore.ReplaceDatabase = $True
$restore.Devices.add($restoredevice)
#Perform Restore
$restore.sqlrestore($PrincipalServer)
$restore.Devices.Remove($restoredevice)

The bug is as follows, if your restore is going to take longer than 10 minutes and you are using an earlier version of SQL than SQL 2012 SP1 CU8 then you will find that the restore fails after 10 minutes. This is the default timeout. You may try to set the

"$srv.ConnectionContext.StatementTimeout

Value to a larger value or 0 and this will work after SQL 2012 SP1 CU8 but prior to that you will still face the same error. The simple workaround is to use Invoke-SQLCmd2 and to script the restore as follows

"#Set up Restore using refresh backup

$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationReplace,$File)|Out-Null
$restore.Database = $DBName
$restore.ReplaceDatabase = $True
$restore.Devices.add($restoredevice)
#Perform Restore
$restore.sqlrestore($PrincipalServer) # if query time < 600 seconds
# $query = $restore.Script($PrincipalServer) # if using Invoke-SQLCMD2
$restore.Devices.Remove($restoredevice)

perform a transaction backup of the principle database

We need to have a full and transaction log backup to set up mirroring. Again you may need to use the script method if your backup will take longer than 600 seconds.

"
#Setup Trans Backup
$Backup = New-Object Microsoft.SqlServer.Management.Smo.Backup|Out-Null
$Full = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
$Tran = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Log
$File = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
$Backup.Action = $Tran
$Backup.BackupSetDescription = “Log Backup of “ + $DBName
$Backup.Database = $DBName
$BackupDevice = New-Object –TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationTran,$File)|Out-Null
$Backup.Devices.Add($BackupDevice)
# Perform Backup
$Backup.SqlBackup($PrincipalServer)
# $query = $Backup.Script($PrincipalServer) # if query time < 600 seconds
$Backup.Devices.Remove($BackupDevice)

# Invoke-Sqlcmd2 –ServerInstance $PrincipalServer –Database master –Query $query –ConnectionTimeout 0 # comment out if not used

Restore both backups on the mirror server with no recovery

To complete the mirroring set up we need to restore the backups onto the mirror server with no recovery as follows

"#Set up Restore of Full Backup on Mirror Server
$restore = New-Object -TypeName Microsoft.SqlServe r.Management.Smo.Restore|Out-Null
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationReplace,$File)|Out-Null
$restore.Database = $DBName
$restore.ReplaceDatabase = $True
$restore.NoRecovery = $true
$restore.Devices.add($restoredevice)
$restore.sqlrestore($MirrorServer) # if query time < 600 seconds
# $query = $restore.Script($MirrorServer) # if using Invoke-SQLCMD2
$restore.Devices.Remove($restoredevice)

# Invoke-Sqlcmd2 -ServerInstance $MirrorServer -Database master -Query $query -ConnectionTimeout 0 # comment out if not used

# Set up Restore of Log Backup on Mirror Server
$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore|Out-Null
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationTran,$File)|Out-Null
$restore.Database = $DBName
$restore.ReplaceDatabase = $True
$restore.NoRecovery = $true
$restore.Devices.add($restoredevice)
$restore.sqlrestore($MirrorServer)
$restore.Devices.Remove($restoredevice)

Recreate mirroring

You recreate mirroring in the same way as you would if you were using T-SQL simply add the principal endpoint to the mirror, and the mirror and witness endpoints to the principal

"#Recreate Mirroring
$DatabaseMirror.MirroringPartner = $PrincipalEndPoint
$DatabaseMirror.Alter()
$DatabasePrincipal.MirroringPartner = $MirrorEndpoint
$DatabasePrincipal.MirroringWitness = $WitnessEndpoint
$DatabasePrincipal.Alter()

Resolve orphaned users

You will need to resolve any users and permissions on your destination servers. I do not know a way to do this with PowerShell and would be interested if anyone has found a way to replace the password or the SID on a user object, please contact me if you know.

Many people do this with the sp_rev_logins stored procedure which will create the T-SQL for recreating the logins. However, Powershell cannot read the outputs of the message window where the script prints the script. If you know that your logins are staying static then run sp_rev_logins and store the output in a sql file and call it with Invoke-SQLCmd2

"$SQL = ‘’ #Path to File
Invoke-Sqlcmd2 –ServerInstance $Server –Database master –InputFile $SQL

The other option is to set up a SSIS package following this blog post and call it from Powershell as follows

"Invoke-Command –ComputerName $Server –scriptblock {DTExec.exe /File “PATHTOPackage.dtsx”} 

This requires Powershell Remoting to have been set up on the server which may or may not be available to you in your environment.

IMPORTANT NOTE – The script does not include any methods for resolving orphaned users so you will need to test and then add your own solution to the script.

check mirroring status

Lastly you want to check that the script has run successfully and that mirroring is synchronised (I am from the UK!!) To do this I check that time and file used for the last database backup using this script

"#Check that correct file and backup date used

$query = "SELECT TOP 1 [rs].[destination_database_name] as 'database',
[rs].[restore_date] as 'restoredate',
[bs].[backup_finish_date] as 'backuptime',
[bmf].[physical_device_name] as 'Filename'
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC"

Invoke-Sqlcmd2 -ServerInstance $PrincipalServer -Database msdb -Query $query |Format-Table -AutoSize –Wrap

and that mirroring has synchronised using the following Powershell command

"$DatabasePrincipal | select Name, MirroringStatus, IsAccessible |Format-Table -AutoSize

Depending on your needs you may add some error checking using the results of the above scripts. As I said at the top of the post, you can turn this script into a function and call it at will or add it to an Agent Job for regular scheduling or just kept in a folder ready to be run when required. The choice is yours but all usual rules apply. Don’t believe anything you read on this blog post, don’t run any scripts on production, test before running any scripts, understand what the code is doing before you run it or I am not responsible if you break anything

You can find the template script here

Enable CLR with Powershell

I had an email last night from someone who attended my PowerShell Box of Tricks session at SQL Saturday Exeter

He was getting an error whilst trying to set CLR Enabled during an automatic install and asked if I had any ideas. The error he had was related to Invoke-SQLcmd and the method he was calling the PowerShell script

I was unable to replicate his problem on my servers so I looked at other methods that may assist as well as following up with him to try and understand what was causing his issue. In doing so I worked out the following method to change the CLR Enabled setting by SMO and thought it worth a blog post to share

One way around his issue is to define and then call Invoke-SQLCmd2 by Chad Miller within his script. So his script would look in part as follows

However, I prefer to use SMO so I examined the Server SMO as follows notice the “.” for local server

$srv New-Object Microsoft.SQLServer.Management.SMO.Server .
$srv |gm

And noticed the Configuration property

$Config $srv.Configuration
$Config |gm

Enabled me to see the IsCLREnabled Property and using Get-Member I could see that the config value was settable

With this information I could write a simple script to alter the settings.

Prior to running the script

We then run the following script

Line 1 creates a Server SMO object there is a “.” to denote local server at the end of the line although you can use the server name as well

Line 4 sets the configvalue for the IsCLREnabled property

And Line 5 Alters the Config object, essentially running the reconfigure

After running the script

Hopefully this short post shows how easy it is to set SQL Server configuration values with Powershell using SMO

Any questions or comments please feel free to ask

Listing the SQL Server SysAdmins With PowerShell

A very short blog today just to pass on this little script.

I was required to list all of the SysAdmins across a large estate. Obviously I turned to PowerShell :-)

I iterated through my server list collection and then created a server SMO object and used the EnumServerRoleMembers method to display all of the sysadmin members

2014-04-12_152433

This will work on SQL2000 – SQL2012. You can see how you can easily change the rolename in the script to enumerate other server roles.

Another way you could do it is to use the query

“SELECT c.name AS Sysadmin_Server_Role_Members
FROM sys.server_principals a
INNER JOIN sys.server_role_members b
ON a.principal_id = b.role_principal_id AND a.type = ‘R’ AND a.name =’sysadmin’
INNER JOIN sys.server_principals c
ON b.member_principal_id = c.principal_id”

and pass that with Invoke-SQLCMD through to every server (if you had to use Powershell :-) ). That query won’t work with SQL 2000 though