Attention DBA’s – Don’t forget the Network as a Bottleneck !

These days when looking at performance issues with SQL Server , the root cause is generally DB concurrency  , CPU , I/O , Memory related or some combination thereof. This is especially true of OLTP style systems where you aren’t expecting to move large amounts of data around. Also the Client end of the connection tends to be on a good fast link to SQL Server , as most of the time its on either an App or Web Server*

I once resolved a Performance issue on a business critical SQL Server where Network queuing played a key part in the problem , but it was a SQL related issue.

Key facts (Abridged) 

  • Access Front End to SQL Server** application with about 1000 connections, around 100 active users.
  • Most users were using Virtual Desktops , hosted on Servers in same datacentre as SQL Server.
  • Performance had degraded gradually over a period of a week , whereby a SELECT / UPDATE operation in the app had gone from a fairly consistent 5 second response time , to around 50-120 seconds mainly during the busiest time of day (10am to 12pm)
  • New version of Access software / SQL components released about 3 weeks before performance started to degrade (Alarm Bells !).
  • SQL Server 2005 x64 , Server 2003, 32 GByte RAM DL380 – (cant remember the G number !
  • Consolidated*** SQL Server with 4 other active instances. sp_configure ‘max server memory’  set to 4 Gbytes on each instance. CPU not throttled / affinitised in the instances in any way.
  • Database approx 2Gbytes used space , so theoretically almost all of it would cached in memory (as 4Gbytes allocated to SQL).

Troubleshooting (Abridged)

(see here for an excellent post on the art of troubleshooting – http://blogs.msdn.com/b/lucascan/archive/2009/04/14/troubleshooting-101.aspx)

  • As soon as I got the call (around about 11am) , I kicked off a pre-Configured SQLDiag on the Server to capture Perfmon, Profiler, Blocking data for later analysis.
  • In parallel started looking interactively at the issue . Given the consolidated nature of this server,  I went straight for an interactive Perfmon to check OS counters like Processor Utilisation , I/O (Logical Disk / Avg. Disk/sec Transfer), Memory and a SQL Counters like Processes Blocked (was 0) , Wait Statistics etc. 
  • These all looked OK apart from Wait Statistics Network IO waits which I had never seen at any value apart from 0. Looked at Network Interface//Bytes Sent and Bytes Received for (Teamed) Public Facing Network Card. Sent Bytes was averaging around 12 Mbytes a sec (high for OLTP server) and more importantly Output Queue Length was averaging around 50-60 confirming some queuing happening at the network layer
  • A Conference call was convened
    • The Networks guy confirmed that they were seeing similar throughput (12 Mbytes / sec x 8 = 96 Mbits a sec).
    • He also checked that the Duplex settings were set correctly (FULL) and that there was no errors on the switch / for that port. 
    • Backup guy confirmed that there was no active backup running and that the dedicated Network Card was configured correctly.
    • Windows guy confirmed that the Chimney / RSS Patch was installed. Also that teaming was redundant but not load balanced as per company standard / hadn’t been changed recently.
    • (I briefly tried to find out which process was causing Network I/O – Server 2003 so no Resource Monitor , Proc Explorer from sysinternals output didn’t match Perfmon of 12 mbytes / sec , so I did a cheeky Network Monitor trace for about 1 minute to look at later – obviously a tiny snapshot but better than nout)
  • By then we had managed to organise getting an interactive filtered (by user) Profiler trace of a standard SELECT / UPDATE statement with a user who joined the conf call.  Unfortunately by then it was after the busy time mentioned above so took about 15 seconds (not bad for the app), but at least it gave us a baseline.
  • I eyeballed the trace quickly and noticed that the longest duration statement at around 10 seconds was something like

  SELECT IdField FROM 2MillionRowTable

(did you spot the missing WHERE clause ?)

  • Developer confirmed that this gets executed every time .. So that 4 bytes (int field) x 2 million rows = approx 8 Mbytes for each iteration ! This was a lovely Silver Bullet and was changed immediately and the performance problem went away !
  • Later analysis from the Profiler in the SQLDiag I’d kicked off using ReadTrace part of SQLNexus, revealed that above problem code above was executed 3500 times between 11:00am and 12:20pm (when i stopped the SQLDiag as after busy time). And that it was about 90% by Duration of all queries in the trace.

Key Take-Aways

  • Even though the root cause of this issue was above code, it is interesting Network Queueing was the first indication that there was something server side. Its always worth looking at the Network related Perfmon counters as a quick check at the start of the troubleshooting.
  • The fact that there was a Application / SQL code release 2 weeks before was always near the front of my mind. The 2millionrow table mentioned above was part of this release went from 0 to 2 million rows in about 4 weeks, hence the gradual degradation of performance. Timebomb / Snowball Effect
  • I had 4 things on the go in parallel at various points.  If I didn’t have the SQLDiag ready to go it probably wouldn’t have got done
    • Kicked off the pre-configured SQLDiag (a simple batch file). Obviously this only took about 5 minutes, including checking was working properly , not filling the disk.
    • Interactive Monitoring using Perfmon / DMvs etc.
    • Attending a 2 hour phone conference call whilst the problem was happening with about 10 people across 2 continents.
    • Dealing with incoming emails on the issue from multitude of people . After a Replied All after while saying “Sorry, I won’t respond to any mails for the next 30 minutes as I’m trying to diagnose the issue”.
  • Scoping and information gathering as always a very important part of the troubleshooting process.
  • For most SQL performance problems I will normally look at sysprocesses / DMV’s to look for blocking / aggregating wait types first. Because I knew this was a consolidated server, I used Perfmon first , as I was guessing that it would perhaps be another instance monopolising a shared resource. Because SQL Perfmon counters have Wait Statistics object and also Processes Blocked counter under General Statistics, you can get similar (not as detailed) info there.

To Do

  • Understand why we were seeing high Output Queue Length when running at about 12mbytes / sec and what the realistic maximum you can expect on a 1Gbps switch (I know there lots of other things that can come into play in between). I know an expert in this area , so hopefully this will cost me 1 coffee or beer. Good ROI I think.
  • Understand why Process Explorer Network I/O figures differed so much from Perfmon / Network Switch I/O data. I ran it really quickly , so I reckon I stuffed something up. This is important because the Network I/O*** is still quite high (around 5mbytes /sec) for a SQL Server box , so got a sneaking suspicion there is some other network hungry process that could be tuned . More important because it is a consolidate server.
  • Small caveat re the pre-configured SQLDiag. I hadn’t used this on a multi-instance box before , so I had to edit the XML file to change from default * / all instance, to just my specific instance. Need to variabilse my batch file to have optional instance parameter.
  • Might do a follow up post explaining how I used the various tools mentioned.

* Unlike the good old 90’s where Corporate Networks were slower , more unreliable and Client Server was the default.

** Don’t shoot the messenger..

*** Each Application has its own instance.

**** I used the TopUsers Expert add in for Network Monitor from Codeplex to see traffic by conversation – awesome

Advertisements

Using .UDL Data Link OLEDB File to perform a basic SQL Connectivity test from App / Web Server

Not an original post by any means , but used this yesterday as was reminded that not many DBA’s seem to know about this.

Background

Quite often we are confronted with App / Web site / team where they are claiming connectivity problems to SQL Server. This technique allows you to do a quick connection test to SQL Server from a Wkstn / Server without the SQL client tools .

I like this method as you can generate the file yourself and send it to an App / Dev guy who say doesn’t know what he is doing and / or doesn’t have the same first language as you*

In our scenario we are trying to test connection to :

SQL Server Name** = KneeDeep, Instance = Clunge , TCPPort Listening on = 1678, DatabaseName=ShonkyDB

(** Remember if your app is using DNS Alias , use this here)

1. Create a new UDL file somewhere

by doing Start / Run / Notepad c:\somedir\somefile.udl . Obviously you will be prompted by the usual “Cannot find file / do you want to create message” . Say Yes and Save and Exit. Note it has to be a .udl file.

2. Open the file.

In Windows Explorer double click on the File . You should get the standard OLEDB Connection Dialogue.

Click on the Provider tab and change it driver to “Microsoft OLEDB Provider for SQL Server”.

Click either Next or back or the Connection tab.

3. Fill in your Server details as per below

image

N.B in my example I am using Windows Authentication – obviously this will run under the credentials of the user logged into the relevant Wkstn / Server. If you want to truly represent the application , you would need to do a RunAs with what ever credentials it uses.

4. Optionally fill in other details on “All” Tab

A nice side effect of this technique is that it will enumerate all of the Connection properties for the relevant driver here. i.e. you could change the Connection Timeout (default 15 seconds) or Application Name properties

5. Click “Test Connection” button on the Connection tab

and thats it

Click on OK will exit and save the file. Below is my example

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ShonkyDB;Data Source=KNEEDEEP\CLUNGE;Application Name=Shonky App Test

You can then send this to whoever to save on to App / Web server and get them to double click.

Notes and Caveats (Not complete)

– Your app might not be using SQL / OLEDB – i.e. could be .Net / SQLClient so this might not be a 100% test.

– In my example I’m using SQL OLEDB driver ,but obviously you could use this technique to connect to a legacy DB – like Oracle – that has an OLEDB Provider

– Also can help , as it enumerate the OLEDB drivers on the relevant Wkstn / Server.

– Could use ODBC to do this (ODBCAD32) but not as portable , bit manual.

– Can force TCP or named pipes in the usual way i.e. :

np:KneeDeep\Clunge

tcp:KneeDeep,1678

MSDTC Troubleshooting and the PREEMPTIVE_OS_DTCOPS wait type

Short Version : If you see above wait type in sysprocesses* for your Distributed Transaction check that DNS is correctly configured  / working / pingable for that server (and Cluster Network name if applicable)

This week had that classic MSDTC problem , whereby customer was just getting MSDTC transaction failed when running a custom Transaction Replication setup routine between 2 SQL 2005 Clusters – From a server I will call Publisher to a server I will call Subscriber

It would just hang for around 10 mins and then produce the error message.

Hadn’t looked at MSDTC for a while , so Binged it and came up with couple of really good troubleshooting articles (see bottom). But had a couple of key learnings too. Process :

1. Checked Event Logs and Restarted MSDTC Resource on both Clusters

it was a UAT / Staging environment so could be a brutal. No joy.

(I actually failed here – because i should have checked the Event Log after restart as had a 9017 DNS Bad Key entry with EventId = 1119 , Source = ClusSvc)

2. Used DTCPing

to test the Network connectivity / Security settings between the 2 Servers

This worked ok. Running DTCPing from my client workstation to both Servers actually failed , but I parked this fact as I thought it must be some Firewall / Port restriction

3. Used DTCTester 

in both directions (ie creating DSN / running from Publisher –> Subscriber and vice versa)

Note : You have to create a User (not System) DSN and if using Windows Authentication you can just spoof the user and password (2nd and 3rd params)

DTCTester DSNName beer beer

This failed with similar hanging behaviour for both scenarios – although the hang period was about 5 mins.

I then ran DTCTester from my Client workstation and interestingly it worked against the Publisher but failed against the Subscriber.

4. Distributed Transaction from SQL Server.

As we already had Linked Servers set up, I ran below from the Publisher. With hindsight I should have done this first/instead of step 3 to save faffing around with DSN’s

BEGIN DISTRIBUTED TRANSACTION

SELECT * FROM Subscriber.master.dbo.sysobjects

(where Subscriber was the linked server name for the , er , Subsciber) Again this was hanging as per the problem / DTC Tester .

I then connected to Subscriber and did below to see if I could see anything

SELECT * FROM master.dbo.sysprocesses WHERE hostname = ‘PUBLISHER’ and loginame = ‘andyjball’

interestingly this showed my spid from the publisher  a wait type of 0x01B3 / PREEMPTIVE_OS_DTCOPS which I had never seen before . Bing search came up more or less blank.

5. Examining the Subscriber Configuration.

As per point 3 it seamed to be the culprit. I found 2 major things wrong

a) Pinging Cluster Network Name failed

resolved to a different IP address that was configured on the Cluster IP Address and didn’t reply

b) Had Status 9017 against DNS Status for the Parameters tab on the Cluster Network name.

9016

(note this shows 9016 as I was repro’ing on another VM / Cluster by removing write permission from the DNS entry for the Cluster Name from the Cluster Service Account)

I don’t recall ever seeing these at anything other than 0. Below (confirmed with some other research) resolved to DNS BAD KEY

NET HELPMSG 9017

which made sense given a). So contacted our Windows team who fixed the DNS / name resolution issue , restarted MSDTC Resource on the Subscriber Cluster , retested and All working !

Network / Firewall / MSDTC Permissions http://blogs.msdn.com/b/distributedservices/archive/2011/11/22/troubleshooting-msdtc-communication-checklist.aspx

* Yeah I know about DMV’s

Shonky way to enumerate SQL Instances and TCP Ports on a Server

A lot of places I work either turn off SQL Browser and/or block UDP Port 1434 (Google Slammer virus !) and so some times you are face with situation that you can’t connect to a named instance as you don’t have the port.  As this is stored in the registry, I knocked up quick batch file below to query a given server . Tested against SQL 2000/5/8 from an XP client.

Save as (say) GetTCPPort.cmd and then run by

GETTCPPort.cmd Computername

where Servername is the ComputerName of the given server (ie without instnace). Once retrieved you can connect using below

tcp:computername,portnumber

ie.

tcp:lops2001,1545

Obviously can also try focing named pipes connection by doing npConfused smileervername\instance

There is a cleaner SMO / Powershell option to do this that I will write / post when the sun is not shining

@echo off

SET SQLSERVER=%COMPUTERNAME%
IF NOT !%1==! SET SQLSERVER=%1

@echo *** Getting Ports for %SQLSERVER% ***

reg query “\\%SQLSERVER%\HKLM\SOFTWARE\Microsoft\Microsoft SQL Server”  /s /v TcpPort
IF %ERRORLEVEL%==1 goto notfound

@echo.
@echo *** Getting Listen On for %SQLSERVER% ***
reg query “\\%SQLSERVER%\HKLM\SOFTWARE\Microsoft\Microsoft SQL Server” /s /v ProtocolList
goto end

:: ie not found , try other key
:notfound
@echo Not found in Microsoft SQL Server, checking MSSQLServer
reg query “\\%SQLSERVER%\HKLM\SOFTWARE\Microsoft\MSSQLServer” /s /v TcpPort

@echo.
@echo *** Getting Listen On for %SQLSERVER% ***
reg query “\\%SQLSERVER%\HKLM\SOFTWARE\Microsoft\MSSQLServer” /s /v ProtocolList

:end

Adding Log To Table to SQL Agent Job

Had to do this for a whole load of SQL servers with same job which was intermittently failing on random servers where we need more info / output and the “Log to Table” option* hadn’t been set.  Used a Powershell script to register all the Servers in Management Studio Server Group and then ran below query on all Servers in that Server Group

Also expanded msdb on all above servers to make a bit of space.

I like this method cos now we have the Server Group registered with all the servers we can then easily run another query against all the Servers in that Server Group to get the results / output.

(*only available in >=2005 so should really check .

See sp_update_jobstep for details on the flags for various advanced options

)


/*
SQLAgentAddLogToTable.sql

Adds “Log to table” / Append option on SQL Job step in named job

*/
SET NOCOUNT ON

DECLARE @JobName    sysname
DECLARE @Flags int
DECLARE @NewFlags int
DECLARE @ScheduleName sysname
DECLARE @job_name    sysname
DECLARE @job_stepname sysname
DECLARE @step_id int
DECLARE @SchedulesStartTime int
DECLARE @ScheduleId int

SET @JobName = ‘Some shonky job’

DECLARE STEPCURSOR CURSOR FOR
SELECT
    Jobs.name,
    Steps.step_name,
    Steps.step_id ,
    Steps.flags,
    Schedules.name,
    Schedules.schedule_id ,
    Schedules.active_start_time
FROM msdb.dbo.sysjobs as Jobs
LEFT OUTER JOIN
    msdb.dbo.sysjobschedules as JobSchedules
ON Jobs.job_id = JobSchedules.job_id
LEFT OUTER JOIN
    msdb.dbo.sysschedules  as Schedules
ON JobSchedules.schedule_id = Schedules.schedule_id
LEFT OUTER JOIN msdb.dbo.sysjobsteps Steps
    ON Jobs.job_id = Steps.job_id
WHERE Jobs.name = @JobName
AND Jobs.Enabled = 1
AND ( (next_run_date IS NOT NULL) AND (next_run_time IS NOT NULL))
AND ((next_run_date <> 0) AND (next_run_time <> 0))

OPEN STEPCURSOR
FETCH NEXT FROM STEPCURSOR INTO
    @job_name,
    @job_stepname,
    @step_id,
    @Flags,
    @ScheduleName,
    @ScheduleId,
    @SchedulesStartTime

   
PRINT @@Servername
   
WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT ‘*** Processing Step ‘ + @job_stepname
        — ie if not log to table / append
        IF (@Flags & 16) <> 16
            BEGIN
                SET @NewFlags = @Flags + 16
                PRINT ‘Updating stepname  = ‘ + @job_stepname + ‘ to log to table (NewFlags = ‘ + CAST(@NewFlags as varchar(25)) + ‘)’
                EXEC msdb.dbo.sp_update_jobstep @job_name = @job_name, @step_id = @step_id, @flags = @NewFlags
            END
        ELSE
            BEGIN
                PRINT ‘stepname = ‘ + @job_stepname + ‘ already has log to table / append set’
            END

    FETCH NEXT FROM STEPCURSOR INTO
    @job_name,
    @job_stepname,
    @step_id,
    @Flags,
    @ScheduleName,
    @ScheduleId,
    @SchedulesStartTime
END
CLOSE STEPCURSOR
DEALLOCATE STEPCURSOR

Another MS Exam – Oasis or Blur

Just convinced myself to start studying for the 2008 MCDBA 70-453 (or wotever it’s called now) upgrade exam. Every time I start the process I question the ROI in time and money terms. This debate somehow reminded me of the Brit Pop band wars of the mid 90’s when I sat my first exam.

Mainly positive, the only real negative I came up with is cost. Gone from 30 quid to 120 quid in 16 years – if I get a chance I will plot this against CPI /cost of beer.

1. It gives you a good grounding in most areas of the relevant technology.

This is especially relevant now that products have so many features , it is very likely that your current role doesn’t utilise all those technologies. Exams gives you the opportunity to learn / play with most aspects of the chosen product.

(Ed: SQL on 4 floppys anecdote removed)

2. No one got sacked / not employed for doing Exams

It shows your current and future employer that you are motivated enough to study / geek outside of normal work hours. That must be positive.

3. Self Esteem and motivation and laziness

Obviously when you spend some time working on something and achieve the desired result it feels good 😉 . This can help counter war weariness a Production DBA sometimes feels after too many days / on call nights in the trenches.

Personally I find it quite hard these days to pick up a geek book / articles /web cast whatever unless there is a measurable end goal.

4. CDI.

Whilst it doesn’t apply to me as I am not on the scene any more, But if you are , next time a boy/ girl u like asks u what u do – whip out your MS Certification card – you’ll be amazed at the results.

Populating Management Studio from SCOM

Technorati Tags: ,

Wrote this at last job with the help of SCOM Gun David Murphy.  Does what it says on the tin. Having all your SQL Servers populated in Management Studio isn’t as important in >=2005 as it was prior, because you can connect / browse etc with having to register servers which wasn’t possible <=2000.

But it is useful to have servers registered / grouped together by version / environment especially given the >= 2008 Management Studio feature that allows to run the same query against multiple registered servers in the same group.

Its a bit rough and ready (a bit like) , have to change params manually (“Change these values”) section, for the environment (Prod, Dev etc) it depends on the naming standard , but wanted to get something out there.

Got the idea from the excellent post below from Kevin Holman that documents some useful SCOM queries / Schema.

http://blogs.technet.com/b/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx

# SSMSFromSCOM.ps1
# Populates SSMS Registered Servers from SCOM query
# See “Change these values” section for variablisation
# Assumes : SQL Server 2008 Management Studio installed
#           Perms to SCOM Database
#           Read Access to AD
#        Windows 7 Admin tools installed

# Add Libs required for the SQLServer Provider
Import-Module ActiveDirectory
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

set-psdebug –strict

  #1. Function to get AD Description for Computer Account using ADSI /LDAP
  Function GetADDescription
    {
    Param (
                $ServerName,
                $LDAPSearchOU
           )
          
        $Connection = $LDAPSearchOU
        $AD = [adsi] $Connection
        $searcher = new-object System.DirectoryServices.DirectorySearcher($AD)
        $SearchFilterString = “(&(objectClass=Computer)(cn=” + $ServerName + “))”

        $searcher.filter = $SearchFilterString
        $Computers = $searcher.findall()
       
        If ($Computers.Count -eq 0)
           {
                $RetString =  “AD Description = ” + $ServerName + ” not found in ” + $Connection
                Return $RetString
        Exit
           }
        Else
            {
                     ForEach($Server in $Computers)
                {
                   
                    $RetString = “AD Description = ” + $Server.Properties.description
                    Return $RetString
                }
            }       
    }

# Change these values    
    $SCOMServer = “SCOMSQLServer”
    $SCOMDB = “OperationsManager”
    $GlobalLDAPSearch = “LDAP://OU=ComputerAccount,DC=SomeDomain,DC=com,DC=au”
    $TopLevelFolder = “AllServersSCOM”
    $CSVOutputFile = “c:\temp\SCOMServerlist.csv”
    $ServerListFileName = “c:\temp\SCOMServerList.txt”
# End Change These Values

# Change to the default Folder (Local Server Groups)
CD ‘SQLSERVER:\sqlregistration\Database Engine Server Group’

# ie all the Folders / Items under the root
$Folders = get-childitem
$FolderExists = 0

# Roll through all Folders
ForEach($Item in $Folders)
{
    # If found delete it
    If ($Item.Name -eq $TopLevelFolder)
    {
        Write-Host “!!! ” $TopLevelFolder ” already exists, Deleting …”
        remove-item $TopLevelFolder -Recurse -Force
        $FolderExists = 1
        Break
    }
}

#Create the folder and set description change into it
new-item $TopLevelFolder
cd $TopLevelFolder

#Environment Folders
new-item “Environment”
    cd “Environment”
    new-item “Production”
    new-item “Development”
    new-item “Staging”
Set-Location ..

# Version specific folders
new-item “SQLVersion”
Set-Location “SQLVersion”
    new-item “SQL2000”
    new-item “SQL2005”
    new-item “SQL2008”
    new-item “SQL2008R2”
Set-Location ..

# Get List of SQL Servers from SCOM in a Datatable – returns 2 fields ServerName and Version
$SQLQuery = “SELECT
            ServerName=[ConnectionString_B87E82BC_1A85_4DE3_330A_13133CF5F9C3],
            Version = Version_B87E82BC_1A85_4DE3_330A_13133CF5F9C3,
            ServicePack = CASE [ServicePackVersion_B87E82BC_1A85_4DE3_330A_13133CF5F9C3]
                                WHEN ‘8.00.2039’ THEN ‘SP4’
                                WHEN ‘0’ THEN ‘RTM’
                                ELSE ‘SP’ + [ServicePackVersion_B87E82BC_1A85_4DE3_330A_13133CF5F9C3]
                            END,
            DBCount = DatabaseCount.DBCount               
            FROM OperationsManager.dbo.[MTV_DBENGINE] WITH(NOLOCK)
            LEFT OUTER JOIN
            (
            — Crazyness to get rid of instance name if default instance
            SELECT     PrincipalName = CASE PATINDEX(‘%;MSSQLServer%’, bme.Path)
                                        WHEN 0 THEN SUBSTRING (bme.Path, 1, PATINDEX( ‘%.%’, bme.Path)-1) + ‘\’ + SUBSTRING(bme.Path, PATINDEX(‘%;%’, bme.Path) + 1, 1000)
                                        ELSE SUBSTRING (bme.Path, 1, PATINDEX( ‘%.%’, bme.Path)-1)
                                       
                                    END,
                    DBCount = COUNT(*)
            FROM BaseManagedEntity bme WITH(NOLOCK)
                        LEFT JOIN ManagedType mt WITH(NOLOCK) ON mt.ManagedTypeID = bme.BaseManagedTypeID
            WHERE bme.IsDeleted = 0
            AND mt.TypeName = ‘Microsoft.SQLServer.Database’
            — Assume we don’t want to count ms supplied / system dbs
            AND Name NOT IN (‘DBA’, ‘model’, ‘master’, ‘msdb’, ‘tempdb’,’ReportServerTempDB’, ‘pubs’, ‘Northwind’, ‘AdventureWorks’, ‘AdventureWorksDW’ )
            — GROUP BY SUBSTRING (bme.Path, 1, PATINDEX( ‘%.%’, bme.Path)-1)
            GROUP BY  CASE PATINDEX(‘%;MSSQLServer%’, bme.Path)
                                        WHEN 0 THEN SUBSTRING (bme.Path, 1, PATINDEX( ‘%.%’, bme.Path)-1) + ‘\’ + SUBSTRING(bme.Path, PATINDEX(‘%;%’, bme.Path) + 1, 1000)
                                        ELSE SUBSTRING (bme.Path, 1, PATINDEX( ‘%.%’, bme.Path)-1)
                                       
                                    END
            ) AS DatabaseCount
            ON [ConnectionString_B87E82BC_1A85_4DE3_330A_13133CF5F9C3]  = DatabaseCount.PrincipalName
            WHERE Edition_B87E82BC_1A85_4DE3_330A_13133CF5F9C3 NOT IN (‘Express Edition’, ‘Windows Internal Database’, ‘Windows Internal Database (64-bit)’)
            AND ([ConnectionString_B87E82BC_1A85_4DE3_330A_13133CF5F9C3] NOT IN (‘ShonkyServer1′,’ShonkyServer2’) )

# Build Query and Connection String
$ConnectionString = “Server=” + $SCOMServer + “;Database=” + $SCOMDB + “;Integrated Security=True;Application Name=ShonkyPowershell”
#Create Dataset and Adapter
$dataset = new-object “System.Data.DataSet” “MyDataSet”
$erroractionpreference = “Stop”
Write-Host “Connecting to ” $SCOMServer ” SQL Server”
$DataAdapter = new-object “System.Data.SqlClient.SqlDataAdapter” ($SQLQuery, $ConnectionString)
$RowCount = $DataAdapter.Fill($dataset)

Write-Host “Processing ” $RowCount ” Servers”
Write-Host “”

#ie each SQL Server
$arrServerDetails = @()
ForEach($Row in $dataset.Tables[0])
    {
        # ie just the first column as we only have one in our dataset poindexter
        $RegSQLServerName = $Row[“ServerName”].ToUpper()
        $SQLVersionNumber = $Row[“Version”].ToUpper()
       
        # need to do this cos i think powershel sql server provider doesn’t like instances names as the delimeter is \ !       
        # and 2 \\ cos its a delimiter in Powershell
        $RegSQLDisplayName = $RegSQLServerName -replace (“\\”, “_”)
        $RegConnectionString = “Server=” + $RegSQLServerName  + “;integrated security=true”
      
        # Trim off Instance name to get NETBIOS name if a named instance
        If ($RegSQLServerName.contains(“\”) -eq $True)
            {
                $NETBIOSName = $RegSQLServerName.Substring(0, $RegSQLServerName.IndexOf(“\”))
            }
        Else
            {
                $NETBIOSName = $RegSQLServerName
            }
       
        # Get last 2 chars, ie DE = Dev , ST = Staging
        $EnvironmentSuffix = $NETBIOSName.substring($NETBIOSName.Length – 2, 2)
       
        Switch ($EnvironmentSuffix)
        {
            “DE” {$EnvironmentFolder=”Development”}
            “ST” {$EnvironmentFolder=”Staging”}
            Default {$EnvironmentFolder=”Production”}
        }
               
        # Change to environment Folder (assumes created in earlier step)
        Get-Location       
        cd “Environment”
        cd $EnvironmentFolder
       
        $MajorVersion = $SQLVersionNumber.substring(0, $SQLVersionNumber.IndexOf(“.”))
       
        # Get friendly name for SQL Version Number
        Switch ($MajorVersion)
        {
            “8” {$VersionFolder=”SQL2000″}
            “9” {$VersionFolder=”SQL2005″}
            “10” {
                    Switch ($SQLVersionNumber.SubString(0, 5) )
                        {
                            “10.50” {$VersionFolder=”SQL2008R2″}
                            default {$VersionFolder=”SQL2008″}
                        }
                  }
           
            default {$VersionFolder=”Unknown”}
        }
       
       
        # 1. Do Environment Stuff
        $ConnectionString = “Server=” + $RegSQLServerName + “;integrated security=true”
        Write-Host “Registering ” $RegSQLServerName ” (NETBIOS Name = ” $NETBIOSName “, in Folder = Environment\” $EnvironmentFolder “)”
        new-item $RegSQLDisplayName   -itemtype registration -Value $ConnectionString
       
        #Get it back so we can add description (seems a bit shonky)
        # $ADDescription = GetADDescription ($NETBiosName, $GlobalLDAPSearch)
        $ADDescription = GetADDescription $NETBIOSName $GlobalLDAPSearch
        # Write-Host “`t” $ADDescription       
        $Item = Get-ChildItem  $RegSQLDisplayName 
        $Item.Description=$ADDescription
       
        #Back to root
        Set-Location ..
        Set-Location ..
       
       
        # 2. Do Version stuff
        cd “SQLVersion”
        cd $VersionFolder

        Write-Host “Registering ” $RegSQLServerName ” (NETBIOS Name = ” $NETBIOSName “, in Folder = SQLVersion\” $VersionFolder “)”
        new-item $RegSQLDisplayName   -itemtype registration -Value $ConnectionString
       
        $Item = Get-ChildItem  $RegSQLDisplayName 
        $Item.Description=$ADDescription
       
        #Back to root
        Set-Location ..
        Set-Location ..
       
            
        # For CSV output Summary
        $CSVrow = “” | Select  SQLServerName,         `
                            ADDescription ,         `
                            MajorVersion, 
                            ServicePack,  `
                            Environment, `
                            DBCount

        $CSVRow.SQLServerName = $RegSQLServerName
        $CSVRow.ADDescription = $ADDescription
        $CSVRow.MajorVersion = $VersionFolder
        $CSVRow.ServicePack = $row[“ServicePack”].ToUpper()
        $CSVRow.Environment =  $EnvironmentFolder
        $CSVRow.DBCount = $row[“DBCount”]
       
        $arrServerDetails+=$CSVRow
                       
    } # ForEach($Row in $dataset.Tables[0])
   

Write-Host “Writing List of SQL Servers to file = ” $ServerListFileName
$dataset.Tables[0] | Select-Object ServerName | Out-File $ServerListFileName

Write-Host “Writing CSV summary to file = ” $CSVOutputFile
$arrServerDetails | Export-CSV -Path $CSVOutputFile -NoTypeInformation