Skip to main content

Posts

Showing posts from 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!

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 Ma

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: Kendal Van Dyke's "Performance Tuning With

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

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 c

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]::Genera

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: $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 } 1

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"

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" $s

#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 pos

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 recommendat

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.