Category Archives: SQLServerPedia Syndication

Parsing Glenn Berry’s Diagnostic Information Queries With PowerShell

Do It In Bulk

I usually don’t run queries on just one server. Most of the time I need to get some piece of information from dozens if not hundreds of servers at a time. I also really like Glenn’s diagnostic queries and use them regularly with some modifications and additions of my own. I’m also really digging PowerShell too.

You Got Your Queries In My PowerShell

PowerShell is a very powerful way to gather information across lots of servers. I’ve got a set of templating files that drive my PowerShell scripts and thought I’d apply some of that to Glenn’s queries. The result is a function that takes a the file name of Glenn’s queries and outputs just the server level or the database level queries as an array that you can then loop through to query any number of servers or databases! It currently works with all three sets of files.

<#
.SYNOPSIS
Parse Glenn  Berry's DMV Diagnostic Queries into arrays http://sqlserverperformance.wordpress.com/
.DESCRIPTION
Does what it says so you can then loop through the array and execute the queries against a server ro database!
.PARAMETER FileName
FileName the name of the file you wish to process
.PARAMETER QueryType
QueryType ether server level or database level queries

.EXAMPLE
parseGABDMV '<path to sql file>\SQL Server 2005 Diagnostic Information Queries(December 2012).sql' server
#>
function parseGBDMV{
	[cmdletbinding(
		DefaultParameterSetName = '',
		ConfirmImpact = "low"
	)]
	Param(
		[Parameter(
			Mandatory = $True,
			Position = 0,
			ParameterSetName = '',
			ValueFromPipeline = $True)]
		[string]$FileName,
		[Parameter(
			Mandatory = $True,
			Position = 1,
			ParameterSetName = '',
			ValueFromPipeline = $True)]
		[string]$QueryType
	)
	Begin {
		$ErrorActionPreference = "SilentlyContinue"
		$found = $false
		$endquery = $false
		$startDatabaseQueries = $false
		$ServerQueries = @()
		$DatabaseQueries = @()
		$Query = "";
	}
	Process {
		Get-Content $File | foreach-Object {
			$_ = $_ -replace '\r\n$', "" #strip out empty lines
			$_ = $_ -replace '\t+', ""
			if ($_.Length -ne 0){
				if ($_.trim() -ne ""){
					if ($_.trim() -ne "`t"){
						$String = $_.toString();
					}
					else{
						$String = ""
					}
				}
				else{
					$String = ""
				}
			}
			else{
				$String = ""
			}

			if($found) {
				if($String -notlike "--*"){
					if($String.Trim() -ne ""){
						if($String.Length -ne 0){
							$Query += $String + "`r`n"
						}
					}
				}
			}

			if($String -like "--*"){
				if($String -cmatch '\(Query'){
					$found = $true
					$endquery = $true
					$Query = $Query -replace '\r\n$', ""
					if ($String.trim() -ne ""){
						if ($startDatabaseQueries){
							if (!$Query.Contains("USE YourDatabaseName")){
								$DatabaseQueries += $Query
							}
						}
						else{
							$ServerQueries += $Query
						}
					}
					$Query = ""
				}
			}

			if ($endqery) {
				$found = $false
			}
			if ($String -ne $null){
				if ($String.Contains("-- Database specific queries")){
					$startDatabaseQueries = $true
				}
				if ($String.Contains("-- Switch to user database *******************")){
					$startDatabaseQueries = $true
				}
			}
		}
		#dunno why always have a blank line on the first server query list
		$ServerQueries = $ServerQueries[1 .. ($ServerQueries.count-1)]
		if ($QueryType.ToUpper() -eq "SERVER"){
			return $ServerQueries
		}
		if ($QueryType.ToUpper() -eq "DATABASE"){
			return $DatabaseQueries
		}
	}
}

It may not be the cleanest PowerShell file parser on the planet but it seems to work and speed really isn’t an issue since the files are so small.

Enjoy!

Finding SQL Server Installs Using Powershell

Old Dog, New Tricks

I’ve been writing tools for SQL Server for a lot of years. Some of these tools were never completely released. Some of them were just for me. Some of them overlapped other tools already on the market and free for all. Recently, I started updating my bag of tricks and tools. This seemed like a great time to get back into PowerShell. I decided to pull out a bit of C# code I cobbled together nine years ago as part of a tool to find SQL Server instances on a network. I never really got around to making it a “production” ready tool since there was already a most excellent one on the scene in the form of SQLPing from Chip Andrews. SQLPing is a fantastic scan tool and can scan many more things than the method covered here.

Hello Operator?

When Microsoft implemented named instances with SQL Server 2000 they had to have a way to direct incoming traffic pointed to a single IP to the correct TCP port. So, they built in a traffic cop. When SQL Server 2005 came around it was pulled from the core network listener and put into its own service, the browser service. There was little documentation on how the browser worked. Luckily for poor sods like me using a network packet sniffing tool it was pretty easy to figure out how to talk to the browser. Once I figured out how to get the browser service to tell me what instances it knew about it was trivial to implement. These days Microsoft is being much more open about these kinds of things and actually have released documentation on how the browser service and the SQL Server Resolution Protocol works.

The Basic Mechanics.

As most of you know SQL Server’s default instance is on 1433 and the browser service is on 1434. Our goal is to send a UDP packet to port 1434. According to the docs we only need to send a single byte containing the number two. This prompts the listener to give us a list of instances and what port they are bound to. When I wrote my implementation it really was that simple. I dug around and figured out how to get PowerShell to send a UDP packet. I tested it and lo’ it worked, on my machine….

It’s Never So Easy.

When I tested it on my lab VM cluster with multiple nodes and multiple instances it would fail! I just didn’t get it. My C# code from the stone age worked just fine. My PowerShell code was a hit or miss. I started troubleshooting the problem just as I had in the beginning. I fired up my network sniffer and watched the traffic flow back and fourth. Again, I saw exactly what I expected, or more accurately what I wanted to see. A single packet with the hex number two in the first byte position. I ran the test several times over the next hour or so. Eventually, I just had to walk away from it. The next day I started over again. I read the documentation, it still said the same thing. I ran the test, still a two in the first byte position. Then I spotted it. The packet wasn’t one byte long. I went back and read the document again. It gives an upper boundary of 32 bytes but no lower limit. The packet I saw come through wasn’t one byte long it was always more than that. Armed with that I started big and worked my way down until I got errors. Now I know that a packet of three bytes always triggers a response. a two with two zeros.

Meet QuerySQLListener.

Here is the function I put together. It takes a server name and returns an array of strings with the fun bits in it.

function QuerySQLListener{
    [cmdletbinding(
        DefaultParameterSetName = '',
        ConfirmImpact = &quot;low&quot;
    )]
    Param(
        [Parameter(
            Mandatory = $True,
            Position = 0,
            ParameterSetName = '',
            ValueFromPipeline = $True)]
        [string]$Computer
    )
    Begin {
        $ErrorActionPreference = &quot;SilentlyContinue&quot;
        $Port = 1434
        $ConnectionTimeout = 1000
        $Responses  = @();
    }
    Process {
        $UDPClient = new-Object system.Net.Sockets.Udpclient
        $UDPClient.client.ReceiveTimeout = $ConnectionTimeout
        $IPAddress = [System.Net.Dns]::GetHostEntry($Computer).AddressList[0].IPAddressToString
        $UDPClient.Connect($IPAddress,$Port)
        $ToASCII = new-object system.text.asciiencoding
        $UDPPacket = 0x02,0x00,0x00
        Try {
            $UDPEndpoint = New-Object system.net.ipendpoint([system.net.ipaddress]::Any,0)
            $UDPClient.Client.Blocking = $True
            [void]$UDPClient.Send($UDPPacket,UDPPacket.length)
            $BytesRecived = $UDPClient.Receive([ref]$UDPEndpoint)
            [string]$Response = $ToASCII.GetString($BytesRecived)
            $res = &quot;&quot;
            If ($Response) {
                $Response = $Response.Substring(3,$Response.Length-3).Replace(&quot;;;&quot;,&quot;~&quot;)
                #$i = 0;
                $Response.Split(&quot;~&quot;) | ForEach {
                $Responses += $_
            }
            $socket = $null;
            $UDPClient.close()
        }
        }
        Catch {
            $Error[0].ToString()
            $UDPClient.Close()
        }
    }
    End {
        return ,$Responses
    }
}

It Isn’t Perfect But It Works.

I”m sure there is a cleaner way to implement it but I’m really just getting into PowerShell again after several months of tinkering with it last time. If you have any suggestions or improvements I’ll gladly take them!

New Toy: The Brydge iPad Keyboard – Updated

Finally a REAL keyboard for my iPad!

I can’t tell you how long I’ve wanted a real keyboard that my iPad could dock with easily. I have always hated lugging around a laptop everywhere to do any real typing. When I got the first iPad I really thought it was going to allow me to cut the need for a laptop way down but without a solid portable keyboard and the smaller 1024×768 screen it basically became a gaming and reading machine. I found myself using my then Galaxy with the sliding keyboard to do almost all my real typing for emails.

Redoubling my effort.

When the New-now old, but not 3rd generation but the third release, iPad came out with an incredible screen resolution I vowed to try to cut my usage down again. I could use VPN and RDP into any box and see the whole screen but typing out T-SQL commands was still very painful on the touchscreen. So, I started testing every bluetooth keyboard or keyboard case out there. I found a few like the Logitech and the apple keyboard more than adequate for the job but they were bulky and I still needed a stand and a case of some sort. Oh, and they aren’t cheap ether. I eventually found a keyboard/case that the keyboard was wafer thin and detached from the crappy case so I carried that when I HAD to have a keyboard and left my laptop behind. At the end of April I found out about Brydge on Kickstarter. It looked like exactly what I’ve always wanted in a keyboard dock for my iPad. I decided to take a risk and support the project. It was funded with an expected ship date in October, just in time for the PASS Summit! WOO HOO! Well, the project was really successful and they had to delay a bit as they tooled up for a much larger production run. That meant I didn’t get it until the 28th of November.

So, Was It Worth It?

Well, I think so. Let me break down what I like and what I’m not so fond of.

Likes

The Keyboard is generally OK I love the fact it has solid tactile feedback. I LOVE buckling key keyboards so having a keyboard with some feedback and that takes a bit of force to actually type a letter suits me just fine. The keyboard is also recessed into the frame so you don’t have smudges on your beautiful retina display. I really hated the fact that my 50 dollar official iPad magnetic cover always left lines and helped spread my oily fingerprints evenly over the screen. It also has a pretty full complement of keys on it and generally are pretty easy to reach without too many typo’s. I also love the full alt, shift and arrow keys so I don’t have to touch my iPad screen to select text to cut and paste.

I’m also good with the hinging system. They altered it a bit from the initial prototype to make it easier to future-proof it. Out of the gate it works very well with the iPad 2,3 and 4 body styles. The hinges are also stiff enough to position the screen at any angle and you don’t have to worry about it tilting on its own. It will also lay ~~completely~~ almost completely flat.

It has some weight to it. At first I was mildly put off with the extra heft but quickly realized it helped stabilize the whole setup nicely. Unlike a laptop where the screen is the lightest part of the device here the iPad has some heft to it and on a couple of other keyboard/case combos it would flip over onto it’s back if the angle was bad.

It is about the size of a Macbook air which is also nice. It doesn’t taper to that razors edge but I can live with that.

Dislikes

The keyboard is a little cramped and if you have big hands it may be difficult to type accurately and fast at the same time. The space bar is also a bit touchy. I use just one thumb on my right hand to trigger it and sometimes it doesn’t respond. I think the problem is they have two springs holding up the space bar but only one switch under it. It also seems to be worse under the left thumb than the right.

There are also two rubber pads for the iPad to rest on when it is fully closed that kind of rub on my wrists if I’m laying my hands flat.

It doesn’t seem to have the magnetic trigger. When I open it up I have to push the home button on the iPad or on the keypad to wake up my iPad. Not a huge thing but I really like that feature on the newer iPads. Well, it does when you set it up right. I had aligned the home button on the iPad with the home button on the keyboard. As a lefty it looked good to me.

If you look the back facing camera now is unrestricted and usable.

Landscape only. I know, it is mimicking a laptop experience but it would have been cool to rotate the iPad between portrait and landscape. It isn’t hard to pull the iPad from the keyboard just a little wishful thinking on my part.

The hinge pads. Since you can use the iPad 2,3 or 4 the hinges use rubber pads to grip your iPad. They have little sticky pads on the back and the Brydge ships with the iPad 2 shims already attached. When I removed them it completely ruined the sticky pads and cleaning up the hinges was a bit of a pain.

The hinge stickers. That’s right, stickers… The hinges themselves are aluminium and silver accordingly if you have a black iPad they are stark against the frame so the black stickers help correct that. I don’t have the best skills when applying stickers so mine are just a smidge off. They do ship two sets and I will probably redo mine again or get someone with steamer hands to do it.

~~The hinge partially covers the rear facing camera. Again, not a huge deal just an observation if you plan on using the camera you will have to remove it from the Brydge.~~ Not if you rotate it see above.

It is made out of aluminium but doesn’t have the smooth finish like the rest of the Apple products which was a disappointment aesthetically.
I have revived several comments that it matches the iPad well and several people asked if it was a new Macbook Air.

It can be difficult to open the hinges are stiff but they did put a groove to help with that.

It also as a small wobble. When the iPad isn’t attached it sits flat on a level surface. When I put the iPad in though the right front is just a little raised so it will move just a bit. I shaved the left hand side rubber foot down about the width of two business cards and it fixed the issue.

You can see there are four rubber feet on the bottom.

It isn’t cheap. I got in the early bird at 150.00 dollars but it will be selling for more than that at retail. That is about a 50.00 dollar premium over any other keyboard I’ve bought so far.

The Oddities

Since it feels like a netbook or a little laptop I keep going down under the keyboard to touch the scroll pad to move the mouse around. It will take a bit of time but I’m sure I’ll get past that.

As with any new keyboard, it takes time to get use to the layout and feel of it. Since it feels like a netbook I also want to use my keyboard shortcuts to do things like cut and paste. There may be a way to set this up and I’ll dig into it a bit more and report back.

Initial Verdict: WIN!

Even though it looks like my dislikes out number the likes I really do dig it so far. I even wrote this blog post using Blogsy for iPad. I’ve NEVER written this much text on my iPad in one setting. It is pricy, but if I use it as much as I think I will I’m not sweating it. The Brydge was made for people like me and I’m glad I backed the project.

Go check them out at http://www.thebrydge.com if you buy one drop me a line and let me know what you think about it.

Quick Tip: SSIS and SAP BW Round 2

Again, frustration.

After almost breaking my arm patting myself on the back getting past my last SAP BW issue I found that SAP BW Connector and SQL Server 2012 were punishing me again. I was building a second package against the same SAP instance on the same SQL Server 2012 instance when I hit something more than flip this field from 3 to 1 kind of thing.

NO SOUP FOR YOU!

“Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: Index”

Oh mister SSIS this means war! Looking at the complete output log I saw that SAP BW had actually delivered the data but the SAP BW source component had thrown a hard error. It was a general error and searching Bing yielded next to nothing. I did find posts like this one that pointed to some bugs inside and outside the connector. This, of course, builds a ton of confidence that its going to work in the future.

Desperation Leads To A Fix.

After searching dozens of KB articles around the SAP BW 1.1 and 1.0 connector I finally just pulled the trigger and installed service pack 1 for SQL Server 2012 and the 2012 SP1 feature pack SAP BW 1.1 connector. I hate when the standard tech support advice works…. The scary thing is there are still outstanding bugs that are fixed in CU3 and CU4 that aren’t included in SP1. The worst thing is I never could find an exact KB that matched my error codes. I don’t know why it was broken and I don’t know if there was a specific patch or CU that addressed my bug. This isn’t the first time something like this has happened ether. Sometimes bug fixes don’t get listed when a CU or service pack is made available.

Quick Tip: SSIS and SAP BW

Frustration.

If you have ever had to work with Integration Services and data sources other than the Microsoft variety you know how frustrating it can be. I recently started a project using SQL Server 2012 and SAP BW 7. The customer had already decided that the other commercial options weren’t viable and settled on the Microsoft Connector 1.1 for SAP BW. A test package was built and worked.

I had already read through Using SQL Server 2008 Integration Services with SAP BI 7.0 and felt comfortable with the instructions. Even though we were using SSIS 2012 I knew there was very little differences between the 1.0 connector and the 1.1 connector. I built out our new package and all seemed well. The SAP BW connection in the connection manager hooked up and tested out. The SAP BW source also tested out pulling the metadata and even delivered preview data just fine. Next, I attempted to run the package. Just as I expected it ran and I got the data I expected. The next three attempts all resulted in SAP BW connector timeouts. I kept poking and prodding it. The SAP admin didn’t see any issues ether.

So, I did what I always do. I stopped tussling with the package and walked through all the steps again. I went back and read the doc again. I made a few notes and shot an email off to the SAP administrator. I settled on one thing pointed out in the doc:

“We want to keep the number of parallel processes to a reasonable value for the overall DTP process type DTP_LOAD, but this parallelism can lead to a timeout error during the Open Hub DTP extraction through Microsoft Connector for SAP BI”

Sure enough, it was set to something other than 1.

VICTORY!

Now, we get all our records and don’t have the timeout issues. The downside seems to be a bit slower performance. If the choice is a faster data pump that only runs once every five or so attempts or one that runs slower but runs every time I’ll slow up a bit. The fact that everything verified and I even got preview data on every attempt was hugely frustrating and time consuming. Knowing when to stop and reevaluate the problem and having a good relationship with the SAP administrator probably saved me hours if not days of cursing and shaking my fist at both Microsoft and SAP.