Tuesday, September 29, 2009

PowerShell: Quick SQL Server Version Check

I have to keep track of our SQL Server version inventory.  The goal is to reduce the SQL Server 2000 population as fast as possible.

The following PowerShell script will produce a csv file containing the database server name and the version of SQL Server it's running.

1:  ## Get SQL Version installed on multiple servers ##
2:  ## ./sqlver.ps1
3:  $start = get-date
4:  write-host "Start: "  $start
6:  [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
8:  $FilePath = "C:\Output"
9:  $OutFile = Join-Path -path $FilePath -childPath ("SQLVersions_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".log")
11:  # Version inventory
12:  @(foreach ($svr in get-content "C:\Input\AllLOBServers.txt")
13:  {
14:      $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr
15:      $s | select Name, Version
17:  })  | export-csv -noType $OutFile
19:  $end = get-date    
20:  write-host "End: "  $end

The csv file format gives you the option of using Excel or importing to a database table for review.

PowerShell is a great way to automate simple tasks that become tedious when you need to execute that task on hundreds of servers.

Linked server problem? No, connectivity issue.

In my new role as a Database Hardener, I also function as Level 2 Production Support.

If the Production Support Team can't resolve an issue, they pass it to me.

Recently, I was given the task to troubleshoot a linked server issue that had stumped other DBAs.

Both instances were SQL Server 2005. The DBAs tried multiple configurations but the linked server kept failing with either a timeout error or a network interface error.

I started from the beginning and tried to defined the linked server myself.  Same results.

The one thing I think I did different is that I clicked on the For Help: link at the bottom of the error dialog.


After reading this entry, I took the recommended user action and pinged the target server via xp_cmdshell from the Query window on the originating server.   No response.

Tada! It was a network connectivity issue and had nothing to do with how the DBA was configuring the linked server.  I advised the DBA to call in a ticket for the network team.

The lesson here is try not to let your focus get too narrow when troubleshooting and use all the help that is offered.

Monday, September 28, 2009

Two Lines of Powershell = Random Passwords

One of the first things I translated from a previous script language to PowerShell was a random password generator.  The previous script was 57 lines long.

So, when I HAD to create a SQL Authenticated login I could provide a strong password.

I googled (Bing wasn't out yet.) on random password PowerShell and found the TWO lines below in the comments to a post on Dimitry Sotnikov's blog dated July 2007. 

The first line loads the assembly you need to obtain the GeneratePassword method.
The second line actually produces the password.  You can change the length of  the password by modifying the first number inside the parentheses.  Examples below.
1: [Reflection.Assembly]::LoadWithPartialName(”System.Web” ;)
2:  [System.Web.Security.Membership]::GeneratePassword(8,2)  # 8 bytes long
4:  [System.Web.Security.Membership]::GeneratePassword(10,2)   #  10 bytes long

For a password, that would make Commander Data proud...
1: [System.Web.Security.Membership]::GeneratePassword(100,2) # 100 bytes long

My education in PowerShell had humble beginnings. I started learning PowerShell by translating previous scripts.  Examples found on the web provided ample guidance.

The only caveat about this method is that is sometimes produces passwords with characters not allowed in SQL Server passwords. So, verify the password is valid before proceeding.  In most cases, it is.

Sunday, September 27, 2009

Search for the error message!

Honestly, I can't tell you how many times searching on the text of an error message has led me to a solution.

I had a DBA call me who needed help with SQL Server replication.  Ummm.  Let's just say I'm not a SQL Server replication expert.

The DBA said he couldn't create a publication on a new server.

Asked the DBA to send me an e-mail detailing the issue. It contained the error message thrown.

I searched on the text of the error message.

It led me to a post on Pinal Dave's blog.

We took action based on this blog post.  Which led us to another error message.  Searched again, took action, made progress.

In the end, the issue was resolved after going through this loop a few times.

This works for me so much but I run into so many people who don't do it.

So, search on the text of the error message when a problem arises.

Most of the time you will find an answer and you will likely find a resource that you can use to resolve future problems.

Wednesday, September 23, 2009

SQL Saturday #21 Orlando

I'm scheduled to present a session on Database Hardening: Standardization, Optimization, and Automation in the afternoon of October 17, 2009 at SQL Saturday #21 in Orlando, FL.

You can expect coverage of how I use PowerShell, Policy Based Management, the EPM Framework and an implementation of a DBA utility database and inventory system to manage our database server inventory.

I will also talk about how I have helped our Compliance and IT Audit department keep our database servers secure.

If interested, register for SQL Saturday #21

50+ sessions from a variety of local and national speakers including SQL Server MVPs.

SQL Saturday is FREE! There is a nominal $10 charge for an excellent lunch from Jason's Deli.

Finally, I'll be attending Buck Woody's pre-conference seminar "Performance Tuning Methodology" on Friday, October 16, 2009.

Thursday, September 17, 2009

Find NULL Passwords with PowerShell/T-SQL

DBAs: Here's a small script that will find SQL Logins with NULL passwords on multiple servers.
1:  foreach ($svr in get-content "C:\Input\ProdInstances.txt" | where {$_ -notmatch "^#"})
2:  {    
3:      $svr
4:      $ExFile = 'C:\Audit\NULL_SQL_Passwords_' + $svr.Replace('\','_') + '.csv'
5:        $con = "server=$svr;database=master;Integrated Security=sspi" 
6:        $cmd = "SELECT @@SERVERNAME AS Server, name, loginname, dbname, password, accdate FROM master..syslogins WHERE password IS NULL AND isntgroup = 0 AND isntname = 0 AND loginname NOT LIKE '##%'"
7:        $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
8:        $dt = new-object System.Data.DataTable
9:        trap {"Oops! $_"; continue } $da.fill($dt) | out-null
10:        if ($dt.Rows.Count -gt 0) { $dt | SELECT  Server, Name, Loginname, DBname, Password, Accdate | export-csv -noTypeInformation  $ExFile }
11:  }

Put a list of servers in a text file like so:


I called mine ProdInstances.txt and put it in the folder C:\Input.

If you need to skip a server in the list, put a # at the beginning of that line and the where clause in line 1 will cause that line to be skipped.  This is helpful when you are testing.

Line 1:  ForEach loop begins and reads the file C:\Input\ProdInstances.txt to get the list of servers.

Line 2:  Opening ForEach brace

Line 3:  Displays contents of $svr variable to console.  I use this as a progress indicator.

Line 4:  Setup csv output file to contain results.

Line 5:  Setup connection to the database server.

Line 6:  Set SQL command to be executed.

SELECT @@SERVERNAME AS Server, name, loginname, dbname, password, accdate 
FROM master..syslogins 
WHERE password IS NULL 
AND isntgroup = 0 
AND isntname = 0 AND loginname NOT LIKE '##%' 

Line 7:  Setup SQLDataAdapter.

Line 8:  Setup DataTable to hold results of SQL query

Line 9:  Execute the query and load the DataTable.  Trap statement checks for errors.

Line 10: If the result set contains any rows, write the result set to the csv file.

Line 11: Closing ForEach brace

Count Log-shipped databases

As part of my job in the Environment Hardening group, I needed to discover the usage of log-shipping in our environment. The script below generates a count of log-shipped databases.  It demonstrates how to use SQL Server Management Objects to find the Major version of SQL Server, the Switch statement, running a SQL query and summarizing the results using the measure-object cmdlet.

We wanted to be sure log-shipping was in use where the Recovery Point Objective and Recovery Time Objective required it.

1:  # .\CountLogShippedDBs.ps1
2:  [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
3:  # Count log shipped databases
4:  foreach ($svr in get-content "C:\Input\ProdInstances.txt" | where {$_ -notmatch "^#"})
5:  {
6:  $svr
7:  $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
8:  $ver = $s.Information.Version.Major
9:  $con = "server=$svr;database=master;Integrated Security=sspi"
11:  switch($ver)
12:  {
13:  {$_ -eq '8'}
14:  {$cmd = "IF OBJECT_ID('msdb.dbo.log_shipping_databases') IS NOT NULL SELECT @@SERVERNAME, COUNT(*) FROM msdb.dbo.log_shipping_databases"}
15:  {$_ -eq '9'}
16:  {$cmd = "IF OBJECT_ID('msdb.dbo.log_shipping_primary_databases') IS NOT NULL SELECT @@SERVERNAME, COUNT(*) FROM msdb.dbo.log_shipping_primary_databases"}
17:  {$_ -eq '10'}
18:  {$cmd = "IF OBJECT_ID('msdb.dbo.log_shipping_primary_databases') IS NOT NULL SELECT @@SERVERNAME, COUNT(*) FROM msdb.dbo.log_shipping_primary_databases"}
19:  }
20:  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
21:  $dt = new-object System.Data.DataTable
22:  trap {"Oops! $_"; continue } $da.fill($dt) | out-null
23:  $sum += $dt
24:  }
26:  $sum | measure-object -property Column2 -sum

Wednesday, September 9, 2009

Discover DEVELOPMENT databases set to FULL Recovery Model

Sometimes DBAs forget to set databases on DEVELOPMENT servers to SIMPLE recovery after a restore from PRODUCTION.

Here's a short PowerShell script that uses Sql Server Management Objects (SMO) to discover this condition.

Save it as get-full.ps1 on your machine and execute using the example code on Line 2.

Load a text file with your list of DEVELOPMENT servers like so...


Save the file on your machine to C:\Input\DevServers.

A csv file will be produced listing the server, database, and recovery model of the databases set to FULL recovery.

1:  # find databases in full recovery on DEVELOPMENT servers
2:  #.\get-full
4:  [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
6:  $start = get-date
7:  write-host "Start: " $start
9:  @(foreach ($svr in get-content "C:\Input\DevServers.txt")
10:  {
11:  $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr
12:  trap {$s; continue } $s.databases | ? {$_.RecoveryModel.ToString() -eq 'Full'} | select parent, name, recoverymodel
13:  }
14:  ) | export-csv -noType C:\Output\DEVELOPMENT_DBs_in_FULL_RECOVERY.csv
16:  $end = get-date
17:  write-host "End: " $end