Saturday, December 5, 2009

Ola Hallengren’s Maintenance Solution Updated!

Over the past week, I have traded e-mails with Ola Hallengren in Sweden regarding his highly regarded SQL Server maintenance scripts. 

I made a suggestion that he include a parameter for the Litespeed @compressionlevel parameter.

Today, he updated his scripts to add this parameter! 

His announcement to me follows.

Hi Ronald,

I have now released a version with this feature.

EXECUTE dbo.DatabaseBackup @Databases = 'AdventureWorks',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@BackupSoftware = 'LITESPEED',
@Compress = 'Y',
@CompressionLevel = 7,
@Verify = 'Y'

http://ola.hallengren.com/Versions.html

Please try it out.

Best regards

Ola

My intention is to use Ola’s scripts as the core of our updated maintenance routines. 

Our current routines were written back in 2002 and aren’t using the latest syntax.

Many thanks to Ola Hallengren for implementing my suggestion so quickly!

Monday, November 9, 2009

Didn’t attend SQL PASS? Twitter was the next best thing.

I didn’t attend the 2009 SQL PASS Summit in Seattle  but I am following a bunch of people on Twitter who did.

There tweeting and twitpics were the next best thing. 

The Twitter feed was like a stock ticker that kept me current on what was happening at SQL PASS.

What did I learn via Twitter regarding SQL PASS?

I should get the conference DVDs.

The webcast "Simplify SQL Server Management with DMVs - the Experts' Perspective"  was hilarious.  I attended.  Buck Woody’s reign of humor continued throughout the week.

Louis Davidson (@drsql) loves sys.dm_io_virtual_stats.

I learned a simple query to find all the DMVs on a server from Buck Woody.

I can hover over the columns in Activity Monitor and see what DMV they are from.

Tom LaRock said "You don’t just get me, you get my network."  A great way to justify SQL PASS attendance to a manager.

The Quest Twitter T-shirts were great. 

Never ever walk out of a Buck Woody presentation.

Never bring a MacBook into a room with Buck Woody.

The keynote speeches will be on the DVDs.

One demo had a 192 CPU machine on stage. Wow.

Tweets to the SQL tweeps’ blogs further detailing their experiences.

Go to SQLServerPedia.com for a list of SQL Tweeple or check out my Follow list @RonDBA.

Next year's PASS Summit will be in Seattle from November 8th - 11th, 2010.  $995 rate if you register soon.

Thanks to all the SQL Tweeps for your coverage of SQL PASS last week!.

Tuesday, October 20, 2009

What a week of training!

Last week was a very good week for me from a SQL Server training perspective.

Kevin Kline of Quest Software spoke on Tuesday 10/13 at the Tampa SQL User Group on "Disk I/O Tuning for SQL Server". 40-50 people turned out. Kevin commented appreciatively on the attendance.

Friday 10/16, I attended Buck Woody's pre-SQL Saturday seminar on a performance tuning methodology he calls "Application Path Analysis". An educational and entertaining seminar.  Buck Woody's presentation style seemed to compress the time space continuum.  The day flew by because he was he was so interesting.

Finally, SQL Saturday!  Andy Warren, Jack Corbett and many volunteers pulled off another very successful SQL Saturday. 
I don't think there is a better value for SQL Server training.
We had Microsoft MVPs, regional, and local speakers. 

I attended five sessions and spoke at one session.

The session I attended were:
  1. Kendal Van Dyke's "Performance Tuning With DMVs"
  2. Joe Webb's "Locking and Blocking Made Simple"
  3. Ken Simmons' "Automating Routine Maintenance"
  4. Rodney Landrum "Taking Control of SQL Server Error Logs"
  5. Buck Woody's "SQL Server Resource Governor"
My session was "Database Hardening: Standardization, Optimization, and Automation.

They were all excellent sessions.  I'm talking about the other guys, not bragging about mine.

What typically happens at SQL Saturdays are there are multiple sessions at the same time and I wish that I could be in two places at once.  Thankfully, all speakers upload their decks and scripts to the SQL Saturday web site for the given event.

The highlights for me were Joe Webb's laptop configuration that was called "Daring." by Kendal Van Dyke.  I was very impressed by it. I have a similar configuration on my home machine.

Buck Woody did a great job on Friday and Saturday with his presentations. I've been a fan of his blog for a while but meeting him in person was a real treat.

Rodney Landrum's presentation on errorlog wrangling may solve some audit issues at my own company.

Kendal Van Dyke helped to solidify my knowledge of DMVs.

Ken Simmons' presentation gave me some ideas for the modernization we need to do on our internal DBA automation and that I need to include some pictures in my presentation to lighten things up.

I had a great time meeting with all the SQL Saturday speakers at the Speaker's party on Friday night.

It was a fun week.  Thank you to all the sponsors and volunteers that made it possible.

See you in Tampa in January 2010 for another SQL Saturday!

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
5:   
6:  [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
7:   
8:  $FilePath = "C:\Output"
9:  $OutFile = Join-Path -path $FilePath -childPath ("SQLVersions_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".log")
10:   
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
16:   
17:  })  | export-csv -noType $OutFile
18:   
19:  $end = get-date    
20:  write-host "End: "  $end
21:   

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.

http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3073&EvtSrc=MSSQLServer&EvtID=11001&LinkId=20476

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
3:      
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:

Server1
Server2
#Server3
Server4
...

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"
10:   
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:  }
25:   
26:  $sum | measure-object -property Column2 -sum
27:   

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


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 servers
2:  #.\get-full
3:   
4:  [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
5:   
6:  $start = get-date
7:  write-host "Start: " $start
8:   
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
15:   
16:  $end = get-date
17:  write-host "End: " $end

Thursday, August 20, 2009

#Active August Morning Routine

I tweeted my typical morning fitness routine earlier this month as part of #ActiveAugust.
Thought it would be a good idea to further define it in a blog post in case it might inspire someone to start their own morning fitness routine.
I'm big on exercise that doesn't require a gym membership and a minimum amount of equipment.

Most mornings: 20 minutes Nordic Track, 20 minutes Tai Chi and a bit of yoga.

NordicTrack cross-country ski machine for 20 - 30 minutes.

Tai Chi - 3 rounds.
I learned the Yang short form (37 moves) in college from Robert Smith.
I'm currently working on the Chen 38 form from Ren Guang Yi's book Taijiquan Chen Taiji 38 Form and Applications and watching relevant YouTube videos. I've figured out the first third of it at this point.

Yoga postures between Tai Chi rounds were learned from DVDs Beginning Yoga by Patricia Walden and Power Yoga, Total Fitness by Rodney Yee.
The postures I typically do include Triangle Pose, Warrior poses, and Sun Salutations.
Also, I use a sequence that was defined in the JUST CHILL column in the January 2007 Outside magazine entitled cheat on your yoga teacher.

Total workout time typically 30 - 45 minutes depending on the mix of the above.

I use the following videos for reference from YouTube.com:
  1. Chen Manching Yang Tai Chi short form (full set) http://bit.ly/2Hdiw
  2. Chen Style Taijiquan 38 Form http://bit.ly/PgDXr
  3. Dirty Jobs : Mike's Mail : Mike's Prison Workout http://bit.ly/zRGXg
Also, I work on the third floor of my building and park on third floor of the parking garage.
I use the steps 99% of the time.

In closing, Robert Smith introduces Tai Chi in his book this way, "Man cannot live fully without exercise. The I Ching (Book of Changes) says: "Nature is always in motion. Man also should strengthen himself without interruption."

Time to get moving.

Wednesday, August 5, 2009

Convincing DBAs to Learn PowerShell

Currently, I am the sole DBA at my employer to dive deep into Microsoft Windows PowerShell.

It has become my most important tool for discovering the state of our database server inventory as we work towards our standardization goals.

It is also the main tool I use to answer questions about the inventory that require querying multiple servers.

As I have learned how to use PowerShell, I have shared scripts and one-liners with my co-workers for the past year or more in an effort to convince them it is worthwhile to learn.
I've written a couple of articles at Simple Talk describing some of my scripting experience.
I've shared links to blog posts, articles, and tips on how to use PowerShell.
It got me thinking about what is the best way to get someone started with PowerShell.

In my efforts to learn PowerShell, I was always looking for examples.

In my opinion, this is the best way to start after some initial readings on the PowerShell basics.

So, my latest recommendations to my fellow DBAs to learn Windows Powershell are:

  1. PowerGUI
  2. Keith Hill's Effective Windows PowerShell PDF
  3. Dr. Tobias Weltner's Mastering PowerShell eBook

The best part about the above recommendations are they are all FREE.

My example-driven book recommendation would have to be Lee Holmes' Windows PowerShell Cookbook.

Finally, there are many PowerShell examples available on the web to help you become proficient.

Use your favorite search engine to find them.

Friday, July 31, 2009

Environment Hardening?


I am a Senior DBA at a Fortune 50 company in the United States.

A recent reorganization has placed me onto a new team called "Environment Hardening".

Our mission: "Standardization, Optimization, and Automation".

The four member team contains a Senior DBA from each RDBMS in use at my company.

I am the SQL Server representative. In the SQL Server domain, we have over 500 servers running just over 3600 databases. I intend to blog about what it takes to keep this huge inventory under control.

I think I will have plenty to write about.

In the beginning, expect posts about PowerShell, Policy Based Management, Enterprise Policy Based Management Framework, Central Management Server, and anything else that falls into the mission profile.

I hope you find it interesting and worth your time.