SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Monitioring Suite - Powershell Profile & Functions


I like to run Powershell from the windows OS. You can run it through SSMS if you wish, but I want to concentrate on the OS version. It’s just personal preference. I’ll be honest with you – when researching Powershell for the first time I came across an article on how to use it from the OS and just ran with it, I haven’t attempted to use it from SSMS yet. If you prefer to run Powershell through SSMS then these concepts should apply, but I haven’t tested them.

Usually you will want to reuse some code you have written. But how then do we load it into our Powershell session? Profiles!

We can run powershell code automatically for all sessions (global) or just our own using a Powershell Profile. This is a file containing all the code you want run when you start a session. It basically saves you having to repeat code. You can use it for custom functions that you might reuse. This is very handy for what we are about to do. More information about the Powershell profile can be found here:



Firing up the ISE

Let’s start by getting acquainted with using Powershell. There are many articles on how to use Powershell with SQL Server, so I am just going to stick with how I do it specifically for use with my monitoring tools.

As I said in my last post, Powershell comes with its own interface – the Powershell ISE or Interactive Scripting Environment. It’s basically an app that allows you to write scripts, run them and view the output. I like it so I use it, but you can use any scripting tool you wish.

You can find the Powershell ISE here:


If we fire it up, we get an application split into 3 horizontal windows. The top window is used to write scripts and save them into PS1 files. The bottom is the shell prompt to run Powershell commands. The middle shows output.


Creating the ISE Profile

Running the ISE for the first time, we can see that there is no output, so obviously nothing has run. Let’s write some code in the top window – just a simple output line:

"global ISE profile - Loading"

And save the file as Microsoft.PowerShellISE_profile.ps1 in:


The file created is a profile for all users who run the Powershell ISE. If we restart the ISE, the middle window shows the output:

global ISE profile - Loading

Cool! So we know that the profile has been run for our session.

Note this profile is just for the PowershellISE, I like to use this profile for testing. I write all my code in the ISE – I can make changes my ISE profile, test them, and then copy the contents to the Powershell profile:


Creating the file in the %windir%\system32\windowspowershell\v1.0\ folder means that any user running Powershell will use this file. If you only want a specific user to use a profile then create it in:

%UserProfile%\My Documents\WindowsPowerShell\

We’ll get to the Powershell Profile later, for now lets just stick with the ISE profile.


Loading Functions

We can use .NET’s System.Data.SqlClient objects to connect to a SQL Server and run SQL Code. There are probably other ways to code this using SMO, or new 2008 cmdlets, but let’s just keep it simple for now.

For our particular task we need two functions – one to get data from the server we are monitoring, and one to write the data to the server we are using to collect monitoring data – I’ll call this the MGT (management) server from now on.


The First One

Ok, so the first function needs to collect data from a server. Since we are going to get data from SQL code such as DMVs etc, we can assume that we are going to write some SQL code to select data. So we need a function that will connect to a database and run some SQL code that selects data. We then need this data to be ready to load.

The System.Data.SqlClient namespace has everything we need for this task. We can create a connection, run a query, and store the results in a dataset ready to load.

So in our Microsoft.PowerShellISE_profile.ps1 file, let’s create this function. To make sure it’s unique we’ll call it SQLmon-rtn. It will take three parameters: The server name, a database, and SQL code. It will also return our data:


function SQLmon-rtn { 

	param ($ServerName, $DB, $SQL) 

	# To run some SQL code and store the results we need four objects: 
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection; 
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand; 
	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter; 
	$SqlDataSet = New-Object System.Data.DataSet; 

	# Next we need to establish where the connection will point to using a connection string: 
	$SqlConnection.ConnectionString = "Server=$ServerName;Database=$DB;Integrated Security=True"; 

	# We then need to establish what we are running and where. Add a suitable timeout just in case: 
	$SqlCmd.CommandText = $SQL; 
	$SqlCmd.CommandTimeout = 120; 
	$SqlCmd.Connection = $SqlConnection; 

	# We use the SQL adapter to run the command and fill the dataset: 
	$SqlAdapter.SelectCommand = $SqlCmd; 
	$SqlAdapter.Fill($SqlDataSet) | out-null; 

	# Finally we close the connection and return the dataset: 
	return $SqlDataSet.Tables[0];


Ok! Copy the above code into the ISE profile file, restart the ISE and let’s test it. In the bottom window type:

>SQLmon-rtn "<Server\Instance,port>" "master" "SELECT @@SERVERNAME"

Where <Server\Instance,port> Is a server you can connect to. In the middle window you should get the output:





The Second One

The second function needs to take the output of SQLmon-rtn and load it into our DBA server. For that you will require a table on the MGT server. Let’s create a test table for this. I’m going to use a database called Monitoring and a table called ServerNames. On the MGT server in the Monitoring database run this SQL:

CREATE TABLE ServerNames ( 
ServerName VARCHAR(256) 
We’re going to use the Data.SqlClient.SqlBulkCopy object to insert the data. Let’s call the function SQLmon-Bulkins. This function is going to take four parameters: the MGT server name, the database, table and a dataset:
function SQLmon-Bulkins { 

	param ($ServerName, $DB, $TBL, $DT) 

	# create a connection string: 
	$connectionString = "Data Source=$ServerName;Integrated Security=true;Initial Catalog=$DB;" 

	# create a bulkcopy object using the connection string: 
	$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString 

	# set the destination table: 
	$bulkCopy.DestinationTableName = $TBL 

	# load the dataset: 


Ok, copy the code above into your ISE profile, restart the ISE and we’re ready to test!

In the bottom window type:

>$DTout = SQLmon-rtn "<Server\Instance,port>" "master" "SELECT @@SERVERNAME"

>SQLmon-bulkins "<MGT Server>" "Monitoring" "ServerNames" $DTout

On the MGT server in the Monitoring database run this SQL:

SELECT * FROM ServerNames

The Server you entered in the first command should be returned.


We now have a profile and two functions we can reuse to extract data from a server we wish to monitor and load it into our management server. You can begin to see the “power” of powershell – it just makes things so easy!

My next post will get us a little dirtier and we’ll come up with something a little bit more useful than server names. We’ll grab some proper data from one server and load it into another. Data & log files are a great place to start. So until then…

Stay tuned!


Posted by chubbsm on 29 April 2011

Cool - so far so good!

Leave a Comment

Please register or log in to leave a comment.