T-SQL Tuesday #028 – Is it really specialization?

This is the first time I've participated in T-SQL Tuesday - I hope to do so more often...

I can recall several years ago being a jack of all trades kind of guy. I was doing development, security, server support, workstation support, database support, and even working with the physical time clocks. I was actually quite happy – I even moved my family close to the job so that I could be home more often.

While I was there, I realized something. There are a LOT of jack of all trades people out there. With this realization – I looked at the IT horizon and decided I would either specialize in Information Security or SQL Server. Both areas of specialization play to my natural personality traits – persistent pursuit of truth and knowledge combined with complex problem solving skills. For my next position, I decided I would put out my resume and whichever job I was hired for first (security or DBA) I would make that my specialization. Fortunately, I landed a full time DBA job. I have taken that role very seriously and have enjoyed the knowledge and experience gained.

That being said, the more “specialized” I get, the more I realize I’m not specialized. I’m a jack of all trades, it’s just that the trades that I’m a “jack of” are just less common. When I chose to be a database professional (or it chose me), I came to realize that there were developers, engineers, and DBAs. My desire for automation lent itself to the DBA role of those three, further specializing my career. As a DBA, I found myself doing SSIS, SSRS, SSAS, and the Database Engine. I decided to further specialize in the database engine. In the general IT space, this may seem specialized, but in the SQL Server space, this is not.

Even at this level, there is much more specialization to be had even within the database engine. There is HADR (mirroring, clustering, replication backups), Performance (indexes, OS, query tuning), Monitoring (Agent, Alerts, logs), Security (auditing, permissions), and Logical Design. I’ve met several professionals who I would consider to have been specialized even within this area as you start to dive into the storage engine, the query optimizer, memory management, connectivity, etc.

To me, specialization isn’t about finding a niche, it’s about recursion. It’s about chasing your passions to places your current peers may not travel to. Once there, it’s about recognizing where within this now reduced area of expertise you can further pursue your passions and distinguish yourself amongst the new peers only to find a whole new set of challenges and specialties to pursue.

I expect that one day, I will be forced to settle in an area whether it be by my own lack of ability to pursue the next level or by environmental factors, but until then, the journey continues.


About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

Finding the Missing Perfmon Counters in SQL Express

My post this weekend about slaying beasts with magical .dll incantations was my way of relieving some stress after having to work an entire Saturday unplanned. My goal with this post is to provide a little more insight into the problem and the solution that was discussed in a very abstract manner on Saturday.

First - to define the issue. I had a client who wanted me to investigate the performance of a server previously foreign to me. The reason for the analysis was to determine any value in upgrading the SQL Express instance to SQL Standard. This was a vendor supported environment that would move to the client's managed environment if they migrated to Standard edition. I gladly accepted the task and asked a co-worker to get the perfmon and sql trace set up to collect the data.

It wasn't long after I made the request that he called me and expressed that he couldn't find the performance counters. I had very little experience in SQL Express, so I immediately wondered if that was one of the features that was disabled. I could find nothing supporting that thought, so I logged onto the box and verified that indeed none of the SQL Server performance counters were available.

The counters were still loading into the sys.dm_os_performance_counters view, so I figured that the problem be between counters and the collector. I had seen this before on a cluster where one node would work and the other wouldn't, so I suggested we rebuild the counters using unlodctr and lodctr. I got a maintenance window (which was ironic considering that it was SQL Express, but I digress), unloaded and reloaded, and bounced the instance. Still nothing. Fortunately, I had not broken it worse - the values were still in the DMV.

I went through the very well documented troubleshooting procedures on the SQL Server CSS blog. I worked through Step 01 and decided to take a break to enjoy some of the new ice cream novelty that my wife had purchased for us all. Before moving on to Step 02, I had an epiphany - SQL Express is 32-bit only. That was a 64-bit box. I've experienced a similar problem before when setting up ODBC data sources - have to have both 32 and 64 bit set up for some applications to work.

So, I get back on the box and look and there is a perfmon.msc in the SysWow64 folder. I launch it and, like magic, the counters appear. I can't express the excitement - the kids wanted to know if I had discovered a new plant on Plants vs. Zombies. :-) I continued to build out the perfmon interactively and when I had everything in it I wanted, saved it out as a data collector. My excitement soon slipped into fear as I opened up the csv I was creating and realized that it was not collecting any SQL counters. Frustrated, I verified that the data collector definition was accurate. Everything seemed right...

I returned to my good friend Bing and now that I was sure it was a 32/64 bit issue (just as sure as I was that it was a corrupt counters issue, but I'll not linger there) and searched on "perfmon data collector x86 x64". The 2nd hit on Bing produced a winner - Creating a custom Data Collector Set with SQL x86 counters on an x64 OS. It's an article from a technical lead at Microsoft that appears to be in Romania. His issue was specifically with SBS, but the situation was similar. I was able to copy out the SQLCtrs.dll file from a 64-bit SQL Standard of the same build and place it into the C:\Windows\System32 folder on the Express box and now the counters for the data collector started to work.

The jury is still out on whether Standard will help this box perform, but I know one thing, we should now have the information necessary to determine that answer. I can only say I have the excellent SQL Server community to thank for getting this resolved. If it were not for the sharing nature of the world wide SQL Server community, I'm certain that this DBA Warrior would still be running from the Beast at the Well.

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

SQL Express and the Curse of the Perfmon Counters

On a dark and spooky night in the land of SQL Server make-believe, a young warrior was asked to slay a beast preventing those in the land of SQL Server Express from collecting water from the Well of the Perfmon Counters.

While this warrior was only vaguely familiar with SQL Server Express, he thought - "It's a beast still in the land of SQL Server, just an outlying province." This warrior was not unfamiliar with slaying beasts in the land of SQL Server. He had slain hundreds of Dragons of Performance and had silenced the Horrid Beast of SSPI, so how could this beast in such a small province pose any threat.

Confidently, he stepped into the magical transport at Port 3389 and was quickly whisked away to the land of SQL Express. Upon arrival, everything seemed normal. The absence of the familiar SQL Agent was disheartening, yet expected, so he continued to the Well of the Perfmon Counters.

Other warriors had gone before to survey the land. They reported on the size and viciousness of the beast in the well, but he foolishly thought those were nothing more than exaggerations. So, at the well, he started to pull out the water. On the rope were labels that read "% Processor" and "Avg Disk Sec / Transfer", and many others as the rope continued to pull from the well. However, when he expected to find the bucket marked SQLServer:BufferManager, the rope was frayed. Just then, out of the Well came a beast the warrior had never before seen in the land of SQL Server Enterprise or even SQL Server Standard. This beast would surely require special magic to defeat. Sword and spear alone would not be sufficient.

Before being bitten by the hundreds of razor sharp teeth in the beast's mouth, the warrior fled into the woods to collect his thoughts and ask the all knowing, Sir BingsALot. Sir BingsALot offered a suggestion - restring the rope in the well by using the bark from the magical trees of Unlodctr and Lodctr. So, the warrior toiled to take this bark and weave it into a rope. First, the bark of the Unlodctr, then the bark of the Lodctr. Day and night, for days on end, without rest, food or water - Ok, enough of that - for 15 minutes with Pepsi and McDouble in hand.

With the new rope in hand, he ran back to the Well and dropped the rope. Once again, several labels appeared on the rope as he pulled it up, but just as he thought he could see the SQLServer:BufferManager bucket, the beast slashed the rope with its razor sharp claws and leapt from the well with more ferocity than before, frustrating the warrior, and sending him back to the woods for some more advice from SirBingsAlot. Try and try again, suggestions from the wisdom of the masses would just not work. Every time the rope would get close, the beast would appear, each time with more teeth and sharper claws, ready to pounce on the warrior.

The warrior, not defeated, but feeling weakened, decided to journey back home, closing the door on Port 3389, and enjoy some ice cream with the SQL Server family. It was while enjoying the wonderful ice cream Mrs. Warrior had purchased for just an occasion that he had an epiphany. SQL Express has never signed the 64-bit treaty with the other lands of SQL Server. SQL Express was still running under the magical laws of the 32-bit treaty.

The warrior thought - "I wonder if using the 64-bit bucket to retrieve the water is making the beast angry? I shall use the 32-bit bucket!". With speed rivaled only by the great runners in the #SQLRun at #SQLPass, this warrior jumped to Port 3389 where he was once again transported away to the land of SQL Express. This time, armed with the 32-bit bucket, he tied the 32-bit Perfmon bucket he collected from the "C:\Windows\SysWow64" folder to the rope and dropped it into the well. With great trepidation, he started to pull the bucket up. Once again the collection of counters, which he started to collectively call "OS" for no apparent reason, started to appear. The SQLServer:BufferManager bucket appeared... no beast. Victory! The warrior had done it.

As the warrior started to dump the bucked into the "Data Collector" barrel to save the water for drinking later, the bucket disappeared. Instantly, the beast appeared and knocked the warrior off his feet. How could this be! As the warrior ran off to safety, he got a detailed glimpse of the beast. This beast was not the same. This beast was nothing more than a copy of the original. While the looks were deceiving, the original was vanquished, this one was indeed new.

Armed with new key words, err, details to ask of SirBingsAlot, the warrior received his answer from a warrior in Microsoft Romania. Yes, buried deep in a tome from 2010, a bold warrior had shared his experience in slaying a similar beast in the land of SBS. Armed with the magical SQLctr90.dll spell collected from the province of SQL Server Standard, the warrior collected the water from the Well of Perfmon and saved it into the Data Collector barrel. While he could still hear the beast breathing heavily in the background, he failed to raise his head and allowed the water to be carried away. The water from the Well of Perfmon can lives on to energize the other warriors as they seek to slay the Dragons of Performance!

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist.

Follow Me on Twitter

Delivering Three PTI SQL 2012 Firestarter Webcasts

I have been given the opportunity to deliver a few webcasts about some of the new features of SQL Server 2012 over the next couple of months for Perpetual Technologies.

You'll get to hear me share the greatness that is SQL Server during the following sessions:

2/16/2012
Throwing T-SQL 2012 Enhancements Against the Wall
Just like your grandma tested spaghetti to tell if it was done, we'll be throwing several of the new T-SQL enhancements against the wall to see what's good and what falls off. We'll silence the lion's RaiseRror by throwing some meaty exceptions. We'll bring together the old lovers "Coalesce" and "+" for one last jaunt before they are banished by the Concat function. We'll even open the window on some of the new aggregate and analytical functions as well as some other t-sql enhancements introduced in SQL 2012.

3/8/2012
SSIS 2012 – New and Improved
SQL Server Integration Services has been used by many a DBA through the "Import and Export Wizard" or through the use of Maintenance Plans. However, SSIS has been adopted by many organizations as the platform of choice for moving data to and from disparate data repositories. SQL 2012 introduces several enhancements to SSIS for both the administrator and the development team. These enhancements will be explored as we compare and contrast SSIS in previous versions with SQL 2012.

3/29/2012
Extended Events – The Death Knell of SQL Trace
Extended Events has been around for a couple of versions of SQL Server, but beginning in 2012 has an important role – replacing SQL Trace. We'll dive into how to use Extended Events just as you would use Profiler to create a trace. We'll also be exploring the new APIs and Powershell enhancements that make Extended Events a 2012 feature to be learned, loved, and utilized.

Over this same time frame, my colleagues will be delivering webcasts on topics such as MDS, DQS, Reporting Services, and Analysis services:

2/9/2012
Utilizing Knowledge with Data Quality Services in SQL Server 2012
Data operations are not only about getting data in and out of a given system but also ensuring that the data is suitable for usage. We've all heard of the phrase "Bad data in, bad data out" and what good is data to us if we cannot ensure that it is correct. Most of the time we write lots of code within stored procedures and functions to compensate for our lack of a system that can help us out with this part of the data food chain. Lucky for us, Microsoft SQL Server 2012 comes with a product known as Data Quality Services (DQS), whose main goals are not only improve your data quality but also to make it much easier to implement and maintain. In this session, we will be looking at what it takes to get DQS up and running in your environment, how to set up Knowledge Bases, and how to utilize the system with tools like SSIS and Master Data Services.
Presented By: Arie Jones

2/23/2012
Enhancements to Reporting Services in SQL Server 2012
Reporting is one of the best ROI features of any business intelligence project because it allows the end user to view the results of all of your hard work. In SQL Server 2012, Microsoft has provided a couple of useful upgrades to our tried and true Reporting Services framework. More importantly, is the release of Power View (formerly known as Project Crescent) which allows you end users to visualize, interact, and perform sophisticated analyses on their own. In this session, we will cover all of the upgrades involved in the new version of SSRS as well as demo some of the slickness of the Power View tool.
Presented By: Shaun Watts

3/1/2012
AlwaysOn – Way to get your 9's?
We are always being told 9's, 9's and more 9's, but when implementation time comes to take action, technology/budget limitations get in the way, especially in the High Availability and Disaster Recovery (HA/DR) technology space. Clustering and Mirroring are great and meet many of our needs, but there are some things we have wished for, such as a mirror database that is usable. AlwaysOn solves this limitation by merging both Mirroring and Multi-Node Multi-Subnet Clustering (True Geo-Clustering Support!!!). AlwaysOn also allows the replica database to be used in a Read-Only capacity, so we can use it for reporting purposes. (Imagine that!) Join us in providing a warm welcome to this new functionality with extensive demos of setting up, configuring and testing AlwaysOn and all of it in an effort to afford the 9's we are always being asked.
Presented By: Warren Sifre

3/15/2012
PowerPivot & BISM in SQL Server 2012 OH MY!
PowerPivot is one of Microsoft's self-service Business Intelligence tools released with R2 but it was a serious v 1.0 type of environment. Now with its second release Microsoft has ramped up the capabilities and given us a new toy known as the BI Semantic Model. In this presentation we will delve into all the new goodies and show you how these tools will change the way that you develop and implement your business intelligence offerings.
Presented By: Hope Foley

3/22/2012
SSAS 2012 – What BISM Means to You!
The data revolution continues and with revolutions come evolutions. Implementing and using SSAS has always been synonymous with cost and expertise. With the newest evolution of the SSAS platform from UDM to BISM there is an enhanced focus on data proliferation amongst the non-BI specialist of the world. The new BI Semantic Model introduces new components such as the Tabular Data Model and Vertipaq, which allows for quick SSAS DB creations with the intent on having widely available content originating from a Pivot source. We will discuss the impact the BISM will have in the BI architecture as well as review some of the new options within SSAS Configurations.
Presented By: Warren Sifre

You can click here or visit http://www.pti.net/Firestarter/ to sign up.

Verifying Last Successful CheckDB with Powershell and SMO - sorta

A few years ago, I wrote a script that was published on SQLServerCentral.com about how to verify the last successful CheckDB for all the databases on an instance. This was a tremendous help to me and other DBAs as we could now tell when the integrity was valid on our databases. If we kept record of this, we could know exactly to when we would have to restore to be guaranteed a consistent database.

I recently worked on a project where I needed to have Powershell collect this date. Much to my chagrin, SMO does not expose this property. I can get the LastBackupDate, LastLogBackupDate, and LastDifferentalBackupDate as properties of the database class, however LastSuccessfulIntegrityCheck is just not there.

I filed a Microsoft Connect in December, 2011 that requested to have this feature added to the SMO database class - - but that has yet to be responded to by Microsoft.

I knew I had to do something so that Lord Vader wouldn't smack me with the dark side of the force. I eventually just reverted to T-SQL and the ExecuteWithResults database method to return the DBCC DBINFO result set to Powershell for each database. The script below will give you the last successful checkdb for all databases in any instances that you provide in a text file. This script uses a very simple custom object - $iDatabase - and loads these objects up into an array for further processing later. This array could be piped to the ConvertTo-HTML or the ConvertTo-CSV cmdlet to create a more flexible output or even through the Where-Object to only report on those that were out of spec. In this example, however, it just sends all the rows unfiltered to the Format-Table cmdlet.

Be certain to vote for the Microsoft Connect suggestion if you think that this would be a valuable addition to the SMO database class.

#************************
#     LastSuccessfulCheckDB
#    Author:  Kyle Neier, Perpetual Technologies
#    Reports on the last successful checkdb for all databases within all instances provided
#    
#    $InstanceList file should be a file of SQL instances in either server, server,port or server\instancename
#    
#************************

param
(
    [string]$InstanceList = "C:\Users\kneier\Documents\Powershell\InstanceList.txt"
)

# Load SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null;

#Initialize Array to hold new database objects
$iDatabases = @()

#Loop over each instance provided
foreach ($instance in $(get-content $InstanceList))
{
     try
    {
        "Connecting to $instance" | Write-Host -ForegroundColor Blue
        
        $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $instance;
        #How many seconds to wait for instance to respond
        $srv.ConnectionContext.ConnectTimeout = 5
        $srv.get_BuildNumber() | out-Null
    }
    catch
    {
        "Instance Unavailable - Could Not connect to $instance." | Write-Host -ForegroundColor Red
        continue
    }
    
    $srv.ConnectionContext.StatementTimeout = $QueryTimeout
    
    foreach($Database in $srv.Databases)
    {
        #create object with all string properties
        $iDatabase = "" | SELECT InstanceName, DatabaseName, LastSuccessfulCheckDB
        #populate object with known values
        $iDatabase.InstanceName = $srv.Name
        $iDatabase.DatabaseName = $database.Name
        
        try
        {
            #Get date of last successful checkdb
            #executes dbcc dbinfo on database and narrows by dbi_dbcclastknowngood
            $database.ExecuteWithResults('dbcc dbinfo() with tableresults').Tables[0] | `
            ?{$_.Field -eq "dbi_dbccLastKnownGood"}| `
            %{$iDatabase.LastSuccessfulCheckDB = [System.DateTime]$_.Value} -ErrorAction Stop
        }
        catch
        {
            "CheckDB could not be determined for $instance.$database" | Write-Host -ForegroundColor Red
        }
        
        #add the iDatabase object to the array of iDatabase objects
        $iDatabases += $iDatabase
    }
}

#output all the databases as a table for viewing pleasure
$iDatabases | ft

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

Eliminating Successful Backup messages with Powershell and SMO

As a DBA, I enjoy knowing that the better job I do, the less likely it is that any of my customers know my name. In the same vein, I like my SQL Instances to behave in a similar fashion. I want the instance to remain silent unless I ask it a question or unless it has an issue – then I want it to scream loudly, but only so loud that the DBAs – not the customer – can hear it.

One setting of SQL Server I’ve grown to love is trace flag 3226. This trace flag has worked since SQL 2000, but was fully documented with SQL 2008R2 (http://msdn.microsoft.com/en-us/library/ms188396.aspx). With this flag enabled, gone are the countless messages that get in the way of seeing the real errors. While I understand logs can be filtered and software exists to parse logs, most of the time, these are unavailable. In environments with frequent log backups and numerous databases, this information can become overwhelming.

After enabling this flag globally, no longer is every successful backup recorded in the SQL Server error log nor is it placed in the Windows Application Event Log. That is all fine and dandy, however, how should I add this trace flag to 50+ instances? After weighing the options (clicking party, registry updates, WMI, SMO), I decided that SMO would be the most flexible given the situation.

Below is a Powershell script that will loop over all of the servers in a CMS group and add the 3226 trace flag to the StartupParameters property if it does not already exist.

$q = "`""

#timeout in seconds
#If query does not return in this interval
#timeout will fire and move to next object
#prevents locks and blocking over this interval
#set to zero to wait indefinitely
$QueryTimeout = 120

$CMSInstance = "CMSINSTANCE"
   
#Load SMO assemplies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.RegisteredServers') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Common') | out-null


$connectionString = "Data Source=$CMSINstance;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CMSStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)
$CMSDBStore = $CMSStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups["2005 + Instances"]


foreach($RegisteredServer in $CMSDBStore.RegisteredServers)
{
    $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $RegisteredServer.ServerName;
    Write-Host "Connected to $($srv.name)"  -ForegroundColor Blue
    
    try
    {
        
        #Get the managed computer object
        $SQLServerWMI = New-Object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $srv.ComputerNamePhysicalNetBIOS
        
        #$srv.ServiceName is not correct - derive service name here
        Write-Host $srv.ServiceName
        if($srv.InstanceName -eq "")
        {
            $ServiceName = "MSSQLSERVER"
        }
        else
        {
            $ServiceName = "MSSQL`$$($srv.InstanceName)"
        }
        #Get the info for this particular SQL Server Service
        $SQLServerServiceWMI = $SQLServerWMI.Services[$ServiceName]
        $StartupParameters = $SQLServerServiceWMI.StartupParameters
        #Get the startupparameters into an array
        $StartParamsArray = $StartupParameters.split(';')
        if($StartParamsArray -notcontains "-T3226")
        {
            #Add T3226 to the StartupParameters collection
            $SQLServerServiceWMI.StartupParameters += ';-T3226'
            $SQLServerServiceWMI.Alter()
            Write-Host "`tSuccessfully Added T3226 to $($srv.name)." -ForegroundColor Blue
        }
        else
        {
            Write-Host "`tT3226 already exists on $($srv.name)." -ForegroundColor Blue
        }
    
    }
    catch
    {
        Write-Host "Error Adding trace T3226 to $($srv.Name) on $($srv.ComputerNamePhysicalNetBios), "`
        "service name $($ServiceName)." -BackgroundColor Red -ForegroundColor Black
    }
    
}

Safely Restart SQL Agent with Powershell

I recently had to enable tokens in SQL Agent across about 50 instances. Unfortunately, enabling tokens requires a SQL Agent restart.

After weighing the options – look at Job Activity monitor for each instance, verify no running jobs, then restart the SQL Agent service or write a cool new Powershell script – I decided to write the Powershell. Growing up, it was explained to me that work isn’t supposed to be fun. However, scripts like this continue to prove that one can indeed get paid to have fun.

Using the power of SMO and Powershell, I was able to loop over all the instances in a particular CMS group and determine if any jobs were currently running. If no jobs were running, tokens were enabled and the SQL Agent cluster resource reset. In this case, all the instances were on Server 2008R2 clusters, but this could be modified to use the WMI Win32_Service class to restart the remote service on a standalone instance or cluster.exe for a cluster on an older OS.

$q = "`""

#timeout in seconds
#If query does not return in this interval
#timeout will fire and move to next object
#prevents locks and blocking over this interval
#set to zero to wait indefinitely
$QueryTimeout = 120

#The instance name of the CMS
$CMSInstance = "CMSInstanceName"
   
#Load SMO assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.RegisteredServers') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Common') | out-null


#Connect to the CMS instance
$connectionString = "Data Source=$CMSINstance;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
#The Registered Server store on the CMS
$CMSStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

#Define the group and sub group that we want to loop over
$CMSDBStore = $CMSStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups["2005 + Instances"]

#Get the cmdlets to manage 2008R2 clusters
Import-Module FailoverClusters

#Loop over each registered server and use SMO to interrogate the Agent
foreach($RegisteredServer in $CMSDBStore.RegisteredServers)
{
      $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $RegisteredServer.ServerName;

      #the SMO JobServer class represents the SQL Server Agent associated with the instance
      $SQLAgent = $srv.JobServer

      #If tokens are not enabled, try to enable them
      if($SQLAgent.ReplaceAlertTokensEnabled -eq $false)
      {
            $JobsExecuting = 0
            foreach($Job in $SQLAgent.Jobs)
            {
                  if($Job.CurrentRunStatus.ToString() -ne "Idle")
                  {
                        #Set the variable to 1 to respresent at least one job is running
                        #Break out of the loop
                        $JobsExecuting = 1
                        break
                  }
            }
            if($JobsExecuting -eq 0)
            {
                  #Only if no jobs are running
                  write-host "No Jobs Running on $($srv.Name)"
                  #Enable tokens and save the change
                  $SQLAgent.ReplaceAlertTokensEnabled = $true
                  $SQLAgent.Alter()
                  Write-Host "Alerts Token Enabled on $($srv.Name)"
                  #Use the cluster cmdlets to stop and start the resource
                  get-cluster $srv.ComputerNamePhysicalNetBIOS | get-clusterresource | 
                  ?{$_.ResourceType.Name -eq "SQL Server Agent" -and $_.Name -eq "SQL Server Agent ($($srv.InstanceName))"} | 
                  stop-clusterresource | start-clusterresource -ErrorAction Stop
            }
            else
            {
                  #If there are jobs running, alert the client with what instance they were running - try again later
                  Write-Host "$($srv.Name) has running jobs, try again later" -ForegroundColor Red
            }

      }
}