Setting SCOM console polling rate

It is known that having a number of consoles open slows down the SCOM environment. This is because, each console places a burden on the RMS server as all consoles (including web console sessions) connect to the SDK service on the RMS server.

So if you have more than a few consoles open continuously you might consider lowering the refresh rate of the consoles.

The registry key that controls this behavior is: “HKCU\Software\Microsoft\Microsoft Operations Manager\3.0\console\CacheParameters\ PollingInterval”

The values it accepts are in the range 0 – 10, where 0 turns off automatic refreshes and requires manual refresh via F5. The values 1 through 10 increment the refresh interval by 15 seconds.

PollingInterval	RefreshedEvery_Secs
1	15
2	30
3	45
4	60
5	75
6	90
7	105
8	120
9	135
10	150

Setting this property in powershell is easy

#check current setting
Get-ItemProperty -LiteralPath 'HKCU:\Software\Microsoft\Microsoft Operations Manager\3.0\Console\CacheParameters' -Name PollingInterval

#change current setting
Set-ItemProperty -LiteralPath 'HKCU:\Software\Microsoft\Microsoft Operations Manager\3.0\Console\CacheParameters' -Name PollingInterval -Value 5

T-SQL Tuesday #040: File and Filegroup Wisdom (Quick Introduction to FILESTREAM)

T-SQL Tuesday #040: hosted by MidNightDBA

This month the T-SQL Tuesday invitation is from Jen&Sean McCown (B|T), asking us to talk about FileGroups or a related area. I thought FILESTREAMs was a good choice to talk about because it needs its own file group. Let’s get rolling then:

What is FILESTREAM?

FILESTREAM was a new SQL Server feature (in SQL2008) that lets you store unstructured BLOB data directly in the file system in a set of folders, access to which is provided via SQL*Server and a special file system driver.

Thumbs-Up:

• The files created as part of the FILESTREAM are managed by the SQL Server itself in their own file group which can be backed up and restored along with other SQL Server data.
• Reading and writing these files is part of the database transaction.

Bleh!:

• FILESTREAM data can be stored only on the local server drive or a local mount point.
• Database snapshopts are not supported for the FILESTREAM containers (folders).
• Database Mirroring is not supported.
• Transparent Data Encryption does not encrypt the FILESTREAM data.

When do I use it?

“(…) objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem. Between 256K and 1M, the read:write ratio and rate of object overwrite or replacement are important factors.”^[1]

So, the general recommendation is to use it when your BLOBs are greater than 1MB and you have additional logic that needs to be performed on the BLOB (like scanning the barcodes in the document before sending the document out) and fast read access is required. If all the fore mentioned conditions are not met, you are better off storing the BLOB in the database.

How does it work?

To put it very simply:

• Binary data stored as individual files outside the database.
• These individual binary files are accessed through WIN32 API for file operations.
• T-SQL will be used to query database to get file handle using PathName().
• T-SQL will be used to get current transaction token using GET_FILESTREAM_TRANSACTION_CONTEXT().
• Both the details are passed to ASP.Net application for read/write to files.

Wait, how did they do this before filestream?

The first method was to use the file system i.e. store the paths in the DB and the files on the NTFS shares. Some of the problems with approach were that the backup of the files is not always synchronized with the backup of the database, transactional consistency issues, support for FT Search is hard and finally creating a consistent DR plan was a nightmare.

The second method was to store the file into a BLOB, now called a VarBinary(max) where you have the benefits of the backups, the full text search but you are limited to 2GB per file. Operations on the database (backups and other maintenance tasks) take more time.

How do I set it up?

First things first, you need to enable FILESTREAM at windows level. You do this on the ‘FILESTREAM’ tab in the SQL*Server configuration manager for the SQL instances whose properties you want to change.

Then at the SQL*Server instance level: By running sp_configure with ‘filestream_access_level’ configuration option.

Then you create a new FileGroup for the FILESTREAM container. There is a 1:1 mapping between the FILESTREAM FileGroups and the FILESTREAM container. You can validate this by using the below query.

SELECT file_id AS fileid,
type_desc AS filetype,
name AS name,
physical_name AS physicalname
FROM sys.database_files
WHERE type_desc = 'FILESTREAM'
GO

Example:

CREATE DATABASE DiscountItems ON
PRIMARY (
NAME = DiscountItemsData1,
FILENAME = 'D:\Sql\MSSQL\Data\DiscountItemsData1.mdf'),
FILEGROUP DiscountItemsDB2(
NAME = DiscountItemsData2,
FILENAME = 'D:\Sql\MSSQL\Data\DiscountItemsData2.ndf'),
FILEGROUP DiscountItemsFS1 CONTAINS FILESTREAM DEFAULT(
NAME = DiscountItemsFS1,
FILENAME = 'D:\Sql\MSSQL\Data\DiscountItemsFS1'),
LOG ON (
NAME = DiscountItemsLOG,
FILENAME = 'D:\Sql\MSSQL\Data\DiscountItemsLOG.ldf')
GO

Once the database has a FILESTREAM filegroup, tables can be created that contain FILESTREAM columns. As mentioned earlier, a FILESTREAM column is defined as a varbinary (max) column that has the FILESTREAM attribute.^[2]

Example:

CREATE TABLE [ForSaleItems](
[ItemID] [int] IDENTITY(1,1) PRIMARY KEY
[ItemGuid] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE ON [PRIMARY],
[ItemDescription] [varchar](50) NULL,
[ItemImage] [varbinary](max) FILESTREAM NULL
)
FILESTREAM_ON DiscountItemsFS1
GO

As a requirement, you need to have a ROWGUID column like the ItemID column. If you try to create a plain old Identity column, the table won’t be created and you will get an error (Msg 5505, Level 16, State 1, Line 1 – A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column).

The column that will be used as a pointer to the real file on the disk needs to be created as a VarBinary(max) with the FileStream attribute. In our case this is the ItemImage.

The following query will insert a new row into our table and it will also create a file into our folder.

INSERT INTO ForSaleItems([ItemGuid], [ItemDescription],  [ItemImage])
VALUES(NEWID(), 'Something random somebody said', CAST('Hope is a heuristicSearch' AS VARBINARY(MAX)))

The path to the file is not immediately available, you need to use Pathname() function to get the path. But, remember that PathName() returns only a file handle so you cannot navigate to the path.

SELECT [ItemDescription], pathname = [ItemImage].PathName() FROM ForSaleItems

If you really want to see the files, you need to goto the FILESTREAM container (the folder we specified for FILESTREAM when creating the DB). Also, when you delete files that are no longer needed, they are removed by a garbage collection process (an automatic background process).

What about Backups?

All backup and recovery methods are available with FILESTREAM data. If you already have a backup process in place then remember that the FILESTREAM data is backed up with the structured data in the database.
If you do not want to back up FILESTREAM data with relational data, you can use a partial backup to exclude FILESTREAM filegroups.

Conclusion:

So, hopefully this has given you a very quick introduction to FILESTREAMs in SQL*Server.

Most of the stuff I presented in the post came from the following WhitePaper (Or at least, what I understood from the paper): FILESTREAMStorage by Paul Randal.

If there are any mistakes, they are my own and should not be attributed to anyone else. Comments, suggestions and telling off is always welcome.

Shuffle the deck using PowerShell

Sunny Chakraborty (B|T), started a page about top 12 things on his mind and since the last one looked a little easy I took a shot at it. 

12. Better Get-Random for small ranges (0-1000). Test for randomness of Get-Random.

Oh boy! was I wrong. What started as an exercise to generate small random sets led me around Wikipedia pages and finally showed me the Fisher-Yates Shuffle.

This was my first solution. And it absolutely sucks, generates loads of zeros and the reason why this happens is not due to the lack of the randomness of the running ticks but the fact that we modulus the result with 1000 to get the value into our range of interest. The basic problem with this is that we can fix it but; at a cost to speed and we may also need a cache the intermediate states to guarantee uniqueness in the set.

Function Get-SmallRandom
{
$procArch = $env:PROCESSOR_ARCHITECTURE
if($env:PROCESSOR_ARCHITECTURE -match “\d{2}$”){$procArch = $Matches[0]}
if($procArch -eq 64){
[Int](((Get-Date).Second * (Get-Date).Millisecond * (Get-Date).Ticks) / ([Int64]::MaxValue + 1))
}elseif($procArch -eq 32){
[Int]((((Get-Date).Ticks/(Get-Date).Millisecond)/([Int32]::MaxValue + 1.0))/1000)
}else{
[Int]((((Get-Date).Ticks/(Get-Date).Millisecond)/([Int32]::MaxValue + 1.0))/1000)
}
}

$a = 1..1000 | %{Get-SmallRandom}
$b = 1..1000 | %{Get-SmallRandom}
Compare-Object -ReferenceObject $a -DifferenceObject $b -ExcludeDifferent

Then it struck me that I may have approached the problem in a wrong direction. Given a set of 1000 numbers, how do I shuffle the set in an unbiased way so that it has the appearance of a randomly generated set? Cool, so how the hell do I do it; the obvious solution was to pick two random numbers less than size of the array the swap the numbers. But, I was not sure if this was such a good idea and started looking if there were any algorithms that did this and surely there is, the ‘Fisher Yates shuffle’. It is easy to implement and works great. So, here is my crack at it:

Function Get-ShuffledArray
{
param(
[Array]$gnArr
)
$len = $gnArr.Length;
while($len)
{
$i = Get-Random ($len --);
$tmp = $gnArr[$len];
$gnArr[$len] = $gnArr[$i];
$gnArr[$i] = $tmp;
}
return $gnArr
}

Not sure if this is an acceptable solution for what Sunny was looking for but, it was a wonderful learning experience. As always, working with PowerShell is fun.

An attempt at Conway’s Game of Life using PowerShell

Why did I get this itch?

Intermediate state in the game of life

While watching ‘Stephen Hawking’s Grand Design’ couple of weeks ago on Discovery channel where he talks about how conscience evolves and how simple cells could evolve into complex beings. As an example, I saw the game of life come up and show how simple starting states can evolve into complex self-sustaining groups. This got me thinking, and while investigating how we code this game of life; I found that the rules are pretty simple. So, I started looking at doing this in PowerShell.

The Origins

The Game of Life, also known simply as Life, is a cellular automaton devised by the British mathematician John Horton Conway in 1970.
Origins of the game are very interesting, according to Wikipedia, Conway was interested in a problem presented by mathematician John von Neumann, who attempted to find a hypothetical machine that could build copies of itself. The Game of Life emerged as Conway’s successful attempt to drastically simplify von Neumann’s ideas.
The “game” is a zero-player game, meaning that its evolution is determined by its initial state, requiring no further input. One interacts with the Game of Life by creating an initial configuration and observing how it evolves.

The Rules

The universe of the Game of Life is an infinite two-dimensional grid of square cells, each of which is in one of two possible states, alive or dead. Every cell interacts with its eight neighbours, which are the cells that are horizontally, vertically, or diagonally adjacent.
In each generation, the following transitions occur:

1. Any live cell with fewer than two live neighbours dies, as if caused by under-population.
2. Any live cell with two or three live neighbours lives on to the next generation.
3. Any live cell with more than three live neighbours dies, as if by overcrowding.
4. Any dead cell with exactly three live neighbours becomes a live cell, as if by reproduction.

The above rules cause births and deaths at each generation and will set the seed for the next generation. And the preceding generation is a function of the previous one.

Based on the above rules each cell can have one of the following happen to it in each generation:

1. STASIS : If, for a given cell, the number of on neighbours is exactly two, the cell maintains its status quo into the next generation. If the cell is on, it stays on, if it is off, it stays off.
2. GROWTH : If the number of on neighbours is exactly three, the cell will be on in the next generation. This is regardless of the cell’s current state.
3. DEATH : If the number of on neighbours is 0, 1, 4-8, the cell will be off in the next generation.

The Code

First let us look at the code that generates the next generation of cells based on the states of the current one.

#Currently Taking 10.5 Secs to generate next generation.
#consumes around 20-25% cpu.
#need to find a better way to do this.
Function Get-NextGeneration
{
	param(
	 [Int[,]]$GameMatrix
	)
	BEGIN
	{
		$tmpGameMatrix = $GameMatrix;
		#The game board for game of life is infinite. So, we simulate this by wrapping the
		#width and height.
		Function Get-WrappedWidth
		{
			param(
			    [Int]$x,
			    [Int]$xEdge
			)
			$x += $xEdge;
			while($x -lt 0){$x += $SCRIPT:BoardWidth;}
			while($x -ge $SCRIPT:BoardWidth){$x -= $SCRIPT:BoardWidth;}
			return $x;
		}

		Function Get-WrappedHeight
		{
			param(
				[Int]$y,
				[Int]$yEdge
			)
			$y += $yEdge;
			while($y -lt 0){$y += $SCRIPT:BoardHeight;}
			while($y -ge $SCRIPT:BoardHeight){$y -= $SCRIPT:BoardHeight}
			return $y;
		}

		Function Get-Neighbours
		{
			param(
				[Int[,]]$ArrayMatrix,
				[Int]$coordX,
				[Int]$coordY
			)
			[Int]$nx = 0;
			[Int]$ny = 0;
			[Int]$count = 0;
			for($nx = -1; $nx -le 1; $nx++)
			{
				for($ny = -1; $ny -le 1; $ny++)
				{
					if($nx -or $ny)
					{
						#$placeX = Get-WrappedWidth $coordX $nx
						#$placeY = Get-WrappedHeight $coordY $ny
						#We can put the function calls to get co-ords directly
						#when getting the positions in the matrix.
						#if($GameMatrix[$placeX, $placeY])
						if($ArrayMatrix[$(Get-WrappedWidth $coordX $nx), $(Get-WrappedHeight $coordY $ny)])
						{
							$count += 1;
						}
					}
				}
			}
			return $count;
		}

	}
	PROCESS
	{

		for($x = 0; $x -lt $SCRIPT:BoardWidth; $x++)
		{
			for($y = 0; $y -lt $SCRIPT:BoardHeight; $y++)
			{
				$neighbors = Get-Neighbours $tmpGameMatrix $x $y
				switch($neighbors)
				{
					{($neighbors -lt 2) -or ($neighbors -gt 3)}{$tmpGameMatrix[$x, $y] = 0;}
					{($neighbors -eq 3)}{$tmpGameMatrix[$x, $y] = 1;}
				}
			}
		}

	}
	END
	{
		$GameMatrix = $tmpGameMatrix;
		#should we even do this? : return ,$GameMatrix
		return ,$GameMatrix;
	}
}

Since the code is too long to post here, I put the code on github and poshcode:
Link to github

Link to poshcode

If you look at the code, please let me know how I can improve the code. Thank you, very much.

Made a few enhancements to the code last night after I posted code. Both the codes on github and poshcode have been updated with the new changes the code  now runs a little bit faster. I still believe that there is room for improvement. So, please provide any feedback you may have.

Here are some more screeshots with the new code running.

The new code populates a complex starting state.

This is about 5-10minutes into the code

About 15-20 minutes into the code run.

After about 30minutes into the code run

T-SQL Tuesday #39: Getting Installed Instances, querying logs

This week’s T-SQL Tuesday is being hosted by Wayne Sheffield ( blog | twitter ), and the topic is to blog about anything PowerShell related to SQL Server.

As a DBA, I get to use PowerShell rarely because most of the automation in our shop has been done in Perl. We are slowly moving towards PowerShell. One thing, I keep doing is fiddling with Perl scripts and to see if PowerShell can do it better.

Today I am going to write about a few things:

1. Getting a list of installed instances on a particular box.
2. Reading the SQL*Server error logs using SMO.
3. Querying the Windows event logs for errors.

Getting a list of installed instances on a box

So, what the below function does is, it looks in the ‘HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\’ registry path to see which SQL*Server instances are installed and for each installed instance returns the Instance name, Instance Id and path to sqlservr.exe.

Function Get-SQLInstanceList
{

    try
    {
		$msSqlKey = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\'
        $instNameKey = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\'
        $sqlInstLst = @()

        $instNames = (Get-Item -Path $instNameKey).GetValueNames()
        foreach($inst in $instNames)
        {
            $instIDKey = $instNameKey
            $instID = (Get-ItemProperty -Path $instIDKey -Name $inst).$inst
            $sqlBin = (Get-ItemProperty -Path ($msSqlKey + $instID + '\Setup') -Name SQLBinRoot).SQLBinRoot

            #HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.D11\Setup
            $objInstDtls = New-Object System.Object|
                            Add-Member NoteProperty InstName $inst -PassThru|
                            Add-Member NoteProperty InstID $instID -PassThru |
                            Add-Member NoteProperty SQLBin $sqlBin -PassThru

            $sqlInstLst += $objInstDtls
        }
        return $sqlInstLst
    }
    catch
    {
        $(throw "Unable to get SQL Server instances on: $($env:COMPUTERNAME)")
    }
}

Reading the SQL*Server error logs using SMO

If you have seen the SMO documentation; you will see that the ReadErrorLog() and ReadErrorLog(Int32) methods return a DataTable and I like that quite a bit because you can filter out a lot of noise.  By the way, for the overload acceptable values are only 0 through 9

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$SqlServerInstName"
$sqlServer.ReadErrorLog() | ? { $_.Text -match 'failed' -AND $_.ProcessInfo -eq 'backup'}

Querying the Windows event logs for Errors

I like ‘wmic’ a lot; one of my favourite commands is:
‘wmic service get name, caption, state, status | findstr /I /C:”sql”‘

So, when I got to know about the Get-WmiObject; I immediately feel in love. The below function will query the windows event logs and return the highest occurring error events along with their counts. In my personal experience I have found that using Win32_NTLogEvent with a filter is much faster returning results than Get-WinEvent and Get-EventLog; when running against remote computers.

$logsToCheck = @('Application', 'Setup' , 'System')
try
{
	$yesterday = (Get-WmiObject -Query "SELECT LocalDateTime FROM Win32_OperatingSystem").ConvertFromDateTime((Get-Date).AddDays(-1))
	foreach($evtLog in $logsToCheck)
	{
		Write-Host "Currently processing event log: $evtLog"

		$errCount = 0
		$filter = "LogFile='$evtLog' AND TimeGenerated >= '$yesterday' AND EventType=1"
		$evtWMIParams = @{
					'Class' 				= 'Win32_NTLogEvent';
					'NameSpace'				= 'ROOT\cimv2';
					'ComputerName'			= $env:COMPUTERNAME;
					'Filter'				= $filter;
					'Authentication'		= 'PacketPrivacy';
					'Impersonation'			= 'Impersonate';
					'EnableAllPrivileges'	= 1
				}

		$filteredEvnts = Get-WmiObject @evtWMIParams | Group-Object EventCode
		if($filteredEvnts){
			$errCount = ($filteredEvnts | Measure-Object -Property Count -Sum).Sum
			Write-Host "Number of errors in '$evtLog' log is: $errCount"
			$filteredEvnts | Select-Object -ExpandProperty Group | Select-Object @{n="Message";e={$_.Message}} |
			Group-Object Message | Sort-Object -Property Count -Descending | Select-Object Count, Name | Format-Table -AutoSize -Wrap
		}

	}
}
catch
{
	$(throw "Error getting data from event log")
}

Any comments or suggestions are welcome! Thank you, for your time.

Working with INI style files in PowerShell

We love PERL at the place where I work. I love it too; it has its own set of advantages. One of the major advantages PERL had was that PowerShell was not around when the solutions were automated. We have the whole SQL*Server installation, configuration and deployment of jobs patted down to putting relevant values in a configuration file and running a PERL script. I wrote parts of the script and love how it turned out.

Anyways, about year ago I was working on moving some parts of our deployment scripts to PowerShell. One of the things that was on the to-do list was parsing and building hashes out of .ini style files. It was not uncommon to find these files used for persisting configuration information and also for automation so, I assumed we would have a built-in cmdlet to cover this kind of a file. We do not have it.

It is a little disappointing but, since the structure of the file is almost static; we can code very easily around it. I had a different issue because our configuration files were semi-structured and had some xml style information as well; we can talk about that later. However, for the .ini style files the basic structure is simple and is setup like a hash where a key has a value i.e. Key = Value.

Once we get the content of the file using Get-Content cmdlet; we can then start parsing the file line by line. The trickiest part for me was getting rid of the comment lines. For example, if we assume ‘#’ is the escape character, we can have it appear anywhere in the line. If the line starts with ‘#’ we can ignore the line. But, if a line is something like ‘Key1 = Value25 #But value25 is not real {o_0}’; we need the key and its corresponding value and need to exclude the comments. Fortunately for us after we get the content of the file; we only need two lines to get this job done for us:

 $CfgFileContent = Get-Content .\sampleconfig.ini

 #Line1: Remove comment Lines, those that start with '#'
 $CfgFileContent = $CfgFileContent -replace "^`#.*$", ""

 #Line2: Remove comments anywhere else on line
 $CfgFileContent = $CfgFileContent -replace "`#.*$", ""

See the below image to get a feel of what is happening in the variable into which we read the file.

changes in ini files as we go through regex operations.

As you can see we have eliminated the comments from the array (Get-Content; returns the result as an array so to speak). But, we do see that we have an element that has no values in it and yes, that was the comment line that we deleted earlier. So, we need to be mindful of this when we process the array to build our hash.

Now all we have left to-do is to move through each line of the file and build the hash which can be used later on.

foreach ($line in $CfgFileContent)
{
  <#Region-Begin: Process configuration file that is setup like a hash where a key has a value#>
  $lineContent = [regex]::split($line, '=')

  #ignore empty keys
  if(($lineContent[0].CompareTo("") -ne 0) -and (-not($lineContent[1] -eq $null))){
	#work through the config file.
	$lineContent[0] = ($lineContent[0].Trim()).TrimEnd()
	$lineContent[1] = ($lineContent[1].Trim()).TrimEnd() -replace "`#.*$", "" 
	$ConfigValues[$lineContent[0]] = $lineContent[1] 
  }
 <#Region-End: Process configuration file that is setup like a hash where a key has a value #>
}

Kindly, let me know what you think.

T-SQL Tuesday #38 – Standing Firm

T-SQL Tuesday

This month’s T-SQL Tuesday is hosted by Jason Brimhall (B | T). The topic this month is Standing Firm.

This is my first T-SQL Tuesday post, yay!

My first taste of T-SQL Tuesday was from Erin Stellato’s (B|T) wonderful post for ‘T-SQL Tuesday #36: What Does Community Mean?’ I believe that was her first blog post after joining SQL Skills and it summarized very beautifully why #sqlfamily is so wonderful and I couldn’t agree more. I will tell you why.

Initially (around 2007), for me the community meant www.sqlservercentral.com; kudos to Steve Jones (B|T) and all the contributors on that site for putting their personal time and experience to help the people. I still remember the first thread I created on the site for a database corruption issue and I was like oh! Sh!t that did not just happen. I had none other than Paul Randall (B|T) come to my rescue and even Gail Shaw (B|T) came to my aid; not only did they help me resolve the issue but also helped me understand the fallacy of my move. I was trying to shrink a DB when I noticed the DB corruption; and yes I am not the brightest bulb in the batch. After, that I started to look around and noticed that there was a this wonderful community of database professionals who were ready to share their knowledge and were doing so setting aside their personal time.

Ahem! So back to this month’s topic ‘Standing Firm’; I am going to be writing about resolution.

To me the word means, ‘firmness of purpose’.  That dogged resolve to see through an issue to the end is something I miss dearly but, I would like to make a very conscious effort towards giving the best I can when working on something.

Being proactive and industrialization of existing solutions are some of the goals I set this year.

To me being proactive would actually mean interacting with developers a little more i.e. getting involved in the initial stages of design and development so that life becomes a little easier for us when we finally ‘finish’ the product (*evil laugh*). It also means that we focus on analysis of existing problems and work towards resolving them so that we do not see a recurrence.

Industrialization of existing solutions would mean re-writing PowerShell|T-SQL scripts to automate existing solutions. One of the key things that we miss when looking industrialization is innovation; and I would like to try and find at least innovative solution to a long running issue per quarter.

One last word about ‘Innovation’, this is a word that we hear a lot and sometimes feel stressed hearing it. To me innovation is not always something new; it could just be a new way of doing something old. So, let your imagination fly and innovate.

Adding Extended Properties to SQL*Server Databases Using PowerShell

When you are working as part of a team that manages a few hundred SQL*Server instances; sometimes, it becomes hard to keep track of all the people who need to be notified when you plan for database migrations and such. Also, if there is an issue that needs their input ($’s); we do not want to send it to the wrong person now, would we. Maintaining the information in excel sheets is good but, wouldn’t it be nice to have the information available on the DB itself? We can get all the information in one shot and send out e-mails to the concerned (correct) individuals.

To work around this problem, we added extended properties to all the user databases that we have on the instances. After I did this on about 5 databases in an instance; I was bored and wanted to see if there was any other way of doing this other T-SQL. Sure, there is and it’s in PowerShell, I couldn’t ask for more. It was good to walk off the beaten path and see explore new things but, it did not take me too long to figure out why documentation is boring; even PowerShell could not help me out much here.

The script is pretty straightforward though:

“Connect to the SQL*Server instance, Loop through all users databases and if a database does not have extended properties; Create a new extended properties object, Attach the object to the current DB and type in the values required.”

# Load required assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null;
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")| Out-Null;

try
{
	$smoConn = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
	$smoConn.ConnectionContext.ConnectTimeout = $commandTimeout
	$smoConn.ConnectionContext.Connect()

	$sqlServerDB = $smoConn.Databases

	foreach($db in $sqlServerDB){
		#Write-Host "Processing DB: " $db -Fore Magenta
		[String]$dbName = $db.Name
		if(  "master", "model", "msdb", "tempdb", "pubs", "NorthWind", "AdventureWorks" -notcontains  $dbName  )
		{
			Write-Host "Working on DB ->" $db  -Fore Green
			$xPropCount = $db.ExtendedProperties.Count
			if($xPropCount  -eq 0){
				$ownrExtProp = New-Object Microsoft.SqlServer.Management.Smo.ExtendedProperty
				$ownrExtProp.Parent = $db
				$ownrExtProp.Name = 'Owned By'
				$ownrExtProp.Value =  Read-Host "Enter the Name of the team that owns the DB: "
				$ownrExtProp.Create()

				$cntctExtProp = New-Object Microsoft.SqlServer.Management.Smo.ExtendedProperty
				$cntctExtProp.Parent = $db
				$cntctExtProp.Name = "Contact"
				$cntctExtProp.Value =  Read-Host "Enter the E-mail address of the group that owns the DB: "
				$cntctExtProp.Create()
			}

			foreach ($xProperty in $db.ExtendedProperties){
					Write-Host  " " $xProperty "->" $xProperty.Value -Fore Yellow
			}
		}
	}
}
catch
{
	$_ | fl * -Force
}

<# 	name	value 	OwnedBy	SqlChow 	Contact onechow@twopows.com #>

Since, we put in so much work documenting this stuff I would have liked to add a nightly job that would read these properties and keeps a central repository updated with the latest changes to the DB. Maybe…that is an overkill.

2012 in review

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

The new Boeing 787 Dreamliner can carry about 250 passengers. This blog was viewed about 1,800 times in 2012. If it were a Dreamliner, it would take about 7 trips to carry that many people.

Click here to see the complete report.

Creating a logging framework in PowerShell – The Final Part

In Part-1, we looked at why logging was important, established a set of guidelines or targets that we need to keep in mind while designing the framework and defined few variables that we will be using.

In Part-2, we expanded on the basics created a few more variables and created a function ‘Write-Log’, which as the name suggests writes messages to the log file.

In Part-3, we saw how we can manage the multiple log files that get generated using ‘Switch-LogFile’ function.

In Part-4, we saw how we can tie all the components needed (and, then some) for the logger by creating a new object and adding methods that will help us use the newly created object. Also, remember that it is the caller that should instantiate the Logger object and set file properties based on script name.

In this final part we will be seeing how we can use this new module to log our script actions. Before, we start off you need the resources. You can download the zip file containing the scripts here.

Our example, consists of 3 files ‘GrandParent_RunMe.ps1′, ‘Parent.ps1′, ‘Child.ps1′ and of course, the module file ‘PS-Log.psm1′. Let us see, what GrandParent_Runme.ps1 is doing:

	#You need to change the below to match the path where PS-Log module is stored.
	Import-Module D:\Programming\PS-Modules\PS-Log\PS-Log.psm1 -Verbose

	#Get the caller info.
	$scriptInfo = Get-ScriptInfo
	$logFileName = $scriptInfo.Path + '\' + $scriptInfo.Name + '.log'

	#manage your log files before you start writing to them.
	Switch-LogFile -Name $logFileName

	#if you do not call the Get-ScriptInfo method, we can set the log file name
	#using the SetLogFileName method for the object. This way we can have each
	#script called from here have its own log file.
	$gplog = New-LogFile ($scriptInfo.Name, $logFileName)

	$gplog.WritePSInfo("ScriptName = $($scriptInfo.Name.ToString())")
	$gplog.WritePSInfo("Starting script: GrandParent.ps1")
	& .\Parent.ps1
	$gplog.WritePSInfo("Exiting script: GrandParent.ps1")
	Remove-Module PS-Log

As you can see, the script is pretty straight forward, it prints a couple of messages and calls another script in this case ‘Parent.ps1′. Similarly, ‘Parent.ps1′ calls ‘Child.ps1′. ‘Child.ps1′ prints a couple of messages and we pop off the stack returning to ‘GrandParent_RunMe.ps1′. For the Grandparent, we need to edit import-module statement to match the location where the module is.

Let us see what happens when we run ‘GrandParent_RunMe.ps1′…

GrandParent_RunMe First Run

As you can see in the image, we have a new file called ‘GrandParent_RunMe.log’ and all the scripts have logged their actions in this one log. Pretty cool!!

Let us see what happens when we run ‘GrandParent_RunMe.ps1′, again…

GrandParent_RunMe.ps1 second run

As you can see the Switch-LogFile statement did what it was supposed to. It renamed the ‘GrandParent_RunMe.log’ from the frist run to ‘GrandParent_RunMe.001.log’ and creates a new log with script name which is ‘GrandParent_RunMe.log’. Let us check what is written into the log files created in both the runs.

Content from both log files

As we worked through the whole series, we defined a framework and created a module that follows the guidelines set by the framework. Also, this modules allows for a consistent way to log your script actions. Until next time.