Skip to main content


Showing posts from September, 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.ps13: $start = get-date4: write-host "Start: " $start5:  6: [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null7:  8: $FilePath = "C:\Output"9: $OutFile = Join-Path -path $FilePath -childPath ("SQLVersions_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".log")10:  11: # Version inventory12: @(foreach ($svr in get-content "C:\Input\AllLOBServers.txt")13: {14: $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr15: $s | select Name, Version16:  17: }) | export-csv -noType $OutFile18:  19: $end = get-date 2…

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 …

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 long3:    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,…

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.

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.

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: $svr4: $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.DataTable9: trap {"Oops! $_"; continue } $da.fill($dt) | out-null10: if ($dt.Rows.Count -gt 0) { $dt | SELECT Server, Name, Loginname, DBname, Password, Accdate | export-csv -noTypeInformation $ExFile }11: }

Put a …

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.ps12: [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null3: # Count log shipped databases4: foreach ($svr in get-content "C:\Input\ProdInstances.txt" | where {$_ -notmatch "^#"})5: {6: $svr7: $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"8: $ver = $s.Information.Version.Major9: $con = "server=$svr;database=master;Integrated Security=sspi"10:  11: …

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...

Server1 Server2 Server3

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 servers2: #.\get-full3:  4: [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null5:  6: $start = get-date7: write-host "Start: " $start8:  9: @(foreach ($svr in get-content "C:\Input\DevServers.txt")10: {11: $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr12: trap {$s; continue } $s.databa…