Lead Database Administrator

SQL Saturday #85

2011-09-21T12:56:00.000-04:00

I'll be speaking at SQL Saturday #85 this weekend in Orlando.
Here's another example we'll review during my session "PowerShell by Example".
This example uses the SQLPSX 1.6 function get-sqlserver to return server properties.

This example was inspired by @PaulRandal's Ponderings on the instance-wide fillfactor setting in his SQLskills Insider Tips newsletter of 9/3/2011.
To become a SQLskills insider, go here.

Looking forward to seeing everyone at SQL Saturday #85!

   1:  # In response to Paul Randal's Ponderings of Sep 3, 2011 regarding fill factor set at the instance level...

   2:  # His Call To Action is to verify it is set to 100. This script provides the basic means to check it on multiple instances.

   3:  # The results are sent to a csv file

4:

   5:  $FilePath = "C:\Output"

   6:  $OutFile = Join-Path -path $FilePath -childPath ("ServerWide_FillFactor" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")

7:

   8:  # Provide a list of servers one way or another...

   9:  $Servers = 'RED50\SQLEXPRESS'

  10:  #$Servers = get-content 'C:\Input\Servers.txt'

11:

  12:  @(

  13:  foreach ($svr in $Servers)

  14:  {

  15:      $s = get-sqlserver $svr

  16:      $s.Configuration | select parent,{$_.Fillfactor.RunValue}

17:

  18:  }

  19:  ) | export-csv -noType $OutFile

Execute Guardium SQL Server Permissions Script on Multiple Servers

2011-09-02T12:51:00.000-04:00

# SQLSaturday#85 in Orlando is September 24, 2011

# I'll be presenting "PowerShell by Example"

# Here's a typical example we will review.

# Want more?  Come to my session at 11 AM.

# Need more info on SQL Saturday Orlando, go here.

# Set permissions on multiple servers for GUARDIUM Vulnerability Assessment 
# Pre-requisites:
# PowerShell V1
# SQLPSX 1.6.1
# add-pssnapin sqlserverprovidersnapin100
# add-pssnapin sqlservercmdletsnapin100


1:  $serverName = "SQL999"

   2:  $databaseName = "InventoryDB"

3:

   4:  $FilePath = "C:\GUARDIUM\DataSources\Output"

   5:  $OutFile = Join-Path -path $FilePath -childPath ("MSSQL_GDM_Output" + (get-date).toString('yyyyMMdd_hhmmtt') + ".txt")

6:

   7:  # here string contains SQL query

   8:  $qry = @"

   9:  SELECT  DISTINCT

  10:          ISNULL(A.MACH_LPAR_NM, '') + CASE a.INST_NM

  11:                                         WHEN 'DEFAULT' THEN ''

  12:                                         ELSE '\'

  13:                                       END + CASE a.INST_NM

  14:                                               WHEN 'DEFAULT' THEN ''

  15:                                               ELSE ISNULL(a.INST_NM, '')

  16:                                             END AS InstanceName

  17:  FROM    InventoryDatabase

  23:   "@

24:

  25:  $Servers = Get-SqlData $serverName $databaseName $qry

26:

  27:  Start-Transcript $OutFile

28:

  29:  $ScriptToExecute =  "C:\GUARDIUM\Scripts\gdmmonitor-mss.sql"

30:

  31:  foreach ($svr in $Servers)

  32:  {

  33:      $serverName = $svr.InstanceName

  34:      Write-Host $serverName

  35:      trap {"Oops! It failed. $_"; continue } Invoke-Sqlcmd -InputFile $ScriptToExecute  -verbose -ServerInstance $serverName

  36:  }

37:

  38:  Stop-Transcript

How cluttered is MSDB?

2011-07-06T06:35:00.000-04:00

# My quick and dirty PowerShell script to check for the oldest entry in MSDB.

# I'm using a SQLPSX 1.6 function Get-SqlData to execute queries.

# Runs against multiple servers

   1:  $start = get-date

   2:  write-host "Start: "  $start

3:

   4:  $serverName = "SERVER\INSTANCE"

   5:  $databaseName = "dbINVENTORY"

6:

   7:  $FilePath = "C:\Output"

   8:  $OutFile = Join-Path -path $FilePath -childPath ("MSDB_MIN_Date_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")

9:

  10:  # here string contains SQL query

  11:  $qry = @"

  12:  SELECT DISTINCT

  13:          ISNULL(A.MACH_LPAR_NM, '') + CASE INST_NM

  14:                                         WHEN 'DEFAULT' THEN ''

  15:                                         ELSE '\'

  16:                                       END + CASE INST_NM

  17:                                               WHEN 'DEFAULT' THEN ''

  18:                                               ELSE ISNULL(INST_NM, '')

  19:                                             END AS InstanceName

  20:  FROM    dbInventory A

  24:  ORDER BY InstanceName

  25:  "@

26:

  27:  $Servers = Get-SqlData $serverName $databaseName $qry

28:

  29:  $qry = @"

  30:  SELECT  @@SERVERNAME AS ServerName, MIN(backup_finish_date) OldestMsdbBackupDate FROM  msdb.dbo.backupset

  31:  "@

  32:  $databaseName = "msdb"

33:

  34:  # Version inventory

  35:  @(

  36:  foreach ($svr in $Servers)

  37:  {

  38:      $serverName = $svr.InstanceName

  39:      trap {"Oops! Query failed. $_"; continue } Get-SqlData $serverName $databaseName $qry

40:

  41:  }

  42:  )  | export-csv -noType $OutFile

43:

  44:  $end = get-date

  45:  write-host "End: "  $end

Meme Monday

2011-04-04T11:15:00.000-04:00

Thomas LaRock (blog|twitter) has started Meme Monday and challenged folks to write a blog post in 11 words or less. Here’s mine…

The Scripting Games have begun! I will be participating this year!

When does a practice become BEST?

2011-02-27T07:38:00.000-05:00

How many good references does an idea need before it becomes a best practice?
For starters, Ola Hallengren's script for Backup, Index Maintenance, and DBCC checks.

I've done the research and spoke to Ola in person at the 2010 PASS Summit.

Ola's good references come from no less than Microsoft, Red Gate Software, Quest Software, and Microsoft MVPs.

I think Ola Hallengren's maintenance solution is a BEST PRACTICE.
I have a few questions for you.
Do you agree?
Do you use Ola's maintenance solution?
Do you use Ola's solution as is or have you modified it?
Please respond in the comments.

SQL Rally abstracts submitted

2011-01-26T21:49:00.000-05:00

I've submitted two abstracts for the SQLRally.
If you're interested in learning PowerShell by example or want to hear how I automated our login provisioning and compliance reporting. Vote for my sessions.
It's a win-win situation in the PowerShell track for attendees no matter who you vote for.
Another great bunch of abstracts in the DBA track.
Get out and vote.

SQLskills Free Immersion

2011-01-23T11:44:00.000-05:00

Why do I want to attend a SQLskills class taught by Paul Randal and Kimberly Tripp?

I revere competence.

To be a master, you must train with the masters.

This Zen proverb sums up my reasons for wanting to attend.
To follow the path:

look to the master,
follow the master,
walk with the master,
see through the master,
become the master.

True masters are humble and approachable. SQLskills is.

Why I'd make the best use of the knowledge I'd get from being in the class.
I consider myself well-informed regarding SQL Server but I’d never say I’m an expert. I’ll let my peers decide that. Officially, I'm on the Level 3 Support Team at my company. It implies that I am my company’s SQL Server Subject Matter Expert. We only get the difficult questions.
This class would make me a more competent SME and be the foundation for my MCM effort.

I would love to attend the SQLskills Master Immersion Event on Internals and Performance in Dallas, TX February 21-25, 2010.

Class info here: http://goo.gl/mZp46

Contest info here: http://goo.gl/Or6Qv Entries must be submitted by 1/25/2011 23:59.

Finally, another chance to ogle Paul's sweet ORANGE Dell laptop would be a wonderful thing.

Opinion: SQL Rally Abstract Process

2011-01-21T10:12:00.004-05:00

I'm really enjoying the process that the SQL Rally is using to select speakers.
I've submitted abstracts to several SQL Saturdays and the PASS Summit but this has been the most interesting process so far. What I really liked about the SQL Rally submission process:

Blind submission
Two abstract limit
Community votes for the final slate of speakers.

The blind submission process eliminates being concerned about what others will do. Submit what you have, if it's worthy, it will be selected. If not, move on.
The two abstract limit levels the playing field for less experienced speakers who haven't compiled as much content but have gained enough speaking experience at the local level to move up to the regional level. I think this limit also has produced better abstracts. I think the candidates spent more time sharpening their abstracts because they only had two chances to get a slot.

Finally, letting the PASS community vote was a great idea but stretching the voting out over a month is brilliant from a marketing perspective. The organizers have developed a very community focused method of creating maximum interest in this event. Well done!

I'd like to see all or parts of this process used for future PASS events.

NEVER CHANGE RESOLUTION BEFORE A PRESENTATION!

2011-01-18T13:27:00.000-05:00

I had the honor of speaking at SQL Saturday #62 this past weekend in Tampa.
It's only the second time I've presented using my Windows 7 laptop and the first time I presented using the Presenter View in Powerpoint 2010. We still run Windows XP at work so I don't use Windows 7 on a daily basis. I arrived early to setup for my presentation, connected the projector and booted the laptop. The laptop auto set the resolution to 800x600. I didn't like the setting so I increased the resolution to a higher level and started up the presentation and went into Presenter View. Cue foreboding music...

By this time, the first attendees started rolling in. I looked at the first few slides to check everything was OK and then went back to the first slide in the deck. I started my presentation, got to the fourth slide on the deck and the picture was missing! The next slide was blank! WTF? The slides looked good in the ribbon at the bottom of Presenter View but weren't rendering correctly on the screen or in the Presenter View's left pane.

Needless to say, this really threw me off. Thankfully, one of the attendees suggested I switch to the editor view and present from that. So, I muddled through the rest of the presentation and my demos. Despite my PowerPoint woes, I did receive decent reviews via Twitter but I was not happy. I had to figure out what went wrong. I kept thinking and talking about it with other people for the next couple of hours.

Finally, I thought changing the resolution might have been the crux of the issue. I went back in the room during the lunch break and plugged my laptop into the projector. This time, I did not touch the resolution. The deck displayed correctly. Head slap!

So, the sole purpose of this post is to save some other speaker newbie from repeating this mistake.

Thanks to @BrentO for his post on Presenter View and @mrdenny and @brianwmitchell for their input while I was trying to figure this out.

SQL PASS Summit Helium Talks

2011-01-04T06:54:00.000-05:00

I've started listening to the PASS Summit DVD sessions during my commute. By accident, I discovered that my Sansa Clip+ mp3 player has a playback speed option while listening to the Lightning Talks.

Buck Woody, Brent Ozar, Adam Machanic are great speakers but the fun begins when you change the playback speed option to FAST. It really sounds like Helium has been introduced into the room. It's really entertaining, you can still understand the speakers and you get through the content quicker.

One final note, Michelle Ufford must have been talking REALLY fast.

SQL Saturday #62

2011-01-03T09:27:00.000-05:00

I'll be speaking at SQL Saturday #62 in Tampa, FL on January 15, 2011.

I'm presenting a session called "Why Learn PowerShell" because there was a request for a beginning PowerShell session. Tons of speaker submissions for this SQL Saturday so the organizers gave most speakers one slot to give the maximum amount of people the chance to speak. They are still trying to secure additional space to expand the schedule.

If you have any interest in learning PowerShell, I encourage you attend this session.
I'll explain why it's worth learning and provide REAL WORLD examples that I use regularly.
To further convince you that SQL Saturday is worth your time consider the following:

The variety of topics is pretty amazing for a FREE event.
Local, regional, and national speakers will present.
Several 2010 PASS Summit presenters will speak.
The Microsoft Scripting Guy and The Scripting Wife will be attending
A local Microsoft Certified Master will be presenting on the Parallel Data Warehouse.
The best lunch of any SQL Saturday will be served.

I've also registered for the Day of Data session "Virtualization and Storage for the DBA" by Denny Cherry being held the day before at the Italian Club in Ybor City.
I encourage all IT Pros to attend this event.

PASS Summit Final Thoughts

2010-12-07T07:30:00.000-05:00

I've got to figure out how to get to the PASS Summit every year! It was AWESOME!

It was great to meet more of the tweeps I follow and reconnect with those that I've met before.
It was my first time to Seattle also. The weather was better than expected. I had a great time walking around the city when time allowed. I also had the opportunity to try the local restaurants and visit the Space Needle. Everything was great. Especially the coffee at Top Pot Doughnuts.

Every time slot had several sessions I wanted to attend. You can't get to all the sessions you would like so buying the Conference DVDs is a must.

Participating in my local PASS user group, SQL Saturdays, and Twitter made being a FIRST TIMER at this year's PASS Summit a non-issue. I did not feel like a FIRST TIMER. I knew a lot of people because of participating locally. I just blended right in, had fun, and learned a ton.

Being a FIRST TIMER, I didn't realize I could download the slide decks before the DVDs arrive. A prompt from a co-worker made me aware of this benefit. I downloaded over a dozen decks so far and reviewed them. Great benefit. Still tons more to go through.

So, the most significant takeaways for me:

Meeting Ola Hallengren and Paul Randal. Talking to them about Ola's Maintenance Solution was invaluable.
The changes to the MCM programming have me thinking about making it a 2011 New Year's resolution. I'll have a post on that later.
The Separation of Duties Framework that Lara Rubbelke and ll Sung Lee debuted has me thinking how to use it back in the real world.

Suggestions for next year:

Any possibility of getting Youtube videos on the conference DVDs for the Top 10 highest rated sessions?
Put Twitter handles on the conference badges.
Select one of my speaker abstracts. ;-)

I've told my manager that we should be sending a representative every year. No excuses.
I had a great time at my First PASS Summit!

PASS Summit Day 3

2010-12-03T07:15:00.000-05:00

The final day of the PASS Summit started off with a great keynote speech by Dr. David DeWitt Ph.D talking about SQL Query Optimization.
Dr. DeWitt is currently a technical fellow at Microsoft leading the Jim Gray Systems Lab. He said "Rocket Science is easier than query optimization." So, if you fail at query optimization, they send you to build rockets. This was one of those presentations that will need to be watched multiple times before all the non-Ph.Ds like me assimilate everything. He did a great job explaining an unbelievably complex topic in terms most people can understand. It'll just take me a few passes to totally understand. I hope.

DBA Mythbusters
A fun interactive session with Paul Randal from SQLSkills.com with Buck Woody playing a supporting role. Buck said the interaction with Paul was not rehearsed. Too bad video doesn't make it on the conference DVDs.

The PowerShell Cookbook for the DBA by Joe Webb
Joe did an excellent overview of how a DBA can use PowerShell to automate repetitive tasks. You can download the scripts from the session here.

Consolidating data collection with SQLDIAG and analysing it all with SQLNexus by Chris Bolton
I went to this session because I need to get more comfortable with these tools.
Chris did a very thorough walkthrough of how to use SQLDIAG and SQLNexus.
I need to read his book next.

Business Intelligence in SharePoint 2010 by Brian Knight
A very well-rehearsed, entertaining and educational session. The presenters were supposedly doing tequila shots the entire 90 minutes but they were still standing at the end of the presentation so I think something else was in the bottle.

My hope for this series of blog posts is to highlight just a few sessions that I found worthwhile. I think the PASS Summit conference DVDs are a compulsory purchase when attending. There are too many excellent sessions to attend in three days. I don't think you get the full value of the conference unless you buy the DVDs.

PASS Summit Day 2

2010-12-01T07:13:00.000-05:00

Day 2 started off with Andy Warren's recommendation of Top Pot Doughnuts.
I normally exercise in the morning so the walk down to Top Pot was a nice equivalent.
Top Pot sits right in front of pylon 43 of the Space Needle monorail on 5th Street downtown.
They have the mother of all glazed donuts and excellent coffee. Coffee so smooth I could drink it black. That's significant. I don't like black coffee.

Day 2 was to be BI day for me at the PASS Summit but it quickly turned into the search for Ola Hallengren.

I attended or wanted to attend the following sessions:

Automate SQL Server Administration with PowerShell by Allen White
I've read a bunch of Allen's blog posts so I wanted to see him in person. He did a broad overview of how to automate DBA tasks using PowerShell. If you're a DBA wanting to learn PowerShell, Allen should be on your list of people of blog posts to read and one of your Follows on Twitter. I did see all of this presentation and introduced myself to Allen afterwards.

50 Surprising Features of SQL Server Business Intelligence by Amir Netz, Donald Farmer
I wanted to see this session after hearing Amir Netz speaking during the morning keynote but I really wanted to meet Ola Hallengren at the Summit. So, I went searching for him. The power of Twitter helped me to get a description and picture of him.

Data Warehouse Design and Architecture Best Practices by Erik Veerman
I sat for the beginning of this presentation but then I heard Ola was in the Experts Area in the Expo hall so I bailed out. I did find Ola in the Experts Area and we spoke for over an hour about his Maintenance Solution script. We had a great conversation and I'm all the more convinced that we need to be using his script at my employer.

Business Intelligence End to End by Donald Farmer
This session was cancelled. I'm hoping it ends up on the DVDs somehow.

Another excellent day at the Summit. It was great to meet Ola Hallengren in person!

PASS Summit Day 1

2010-11-10T13:30:00.001-05:00

Started the day off great with breakfast in the Daily Grill with @BrentO after he tweeted that he had three open spots at his table. Can't let the dude eat breakfast alone.

I attended these sessions on Day 1 of SQL PASS. Made it through the day with minimal impact from jet lag.

DBA283S "Virtualization and SAN Basics for DBAs" with Brent Ozar.
This man has a gift for imparting information in a funny and entertaining way.
Great stuff I can use to better understand what is happening at my workplace with links to even more information at his blog under his SAN and Virtual tags.
This one was voted most popular yesterday so it's today's Second Chance session.

AD314 "Database Testing Overview" with Buck Woody and Alex Kuznetsov.
Buck and Alex gave an overview for properly testing a database before deploying new features. Buck said Microsoft got one of its biggest black eyes from a one line change that had only one word changed. Wow! You have been warned, test before your production deployments.

DBA448M "Si Se Puede! Achieving Separation of Duties with SQL Server"
Great session by Lara Rubbelke and Il-Sung Lee that introduced the new Codeplex project SQL Server Separation of Duties Framework. This will be the first thing, I start exploring when I get back to the office. Downloaded it last night to my laptop.

PD193S "You're Not Attractive - But Your Presentations Can Be"
My favorite session of the day! This session was probably in a room that was the farthest from the main hub of the conference. Commented on that when I entered the room and @BuckWoody called me an old man for the rest of the hour. If he insults you, it's only because he loves you.

I'm a FIRST TIMER at the PASS Summit but I don't feel like one because of my participation in SQL Saturdays and Twitter. Glad I ordered the conference DVDs. Too many great sessions to attend all of them. I think you don't get the FULL value of this conference without purchasing the DVDs.
A great start to my week.

SQL PASS Summit - a SQL geek vacation?

2010-11-04T06:50:00.000-04:00

I'll be attending my first PASS Summit next week in Seattle, WA!

First, I'd like to thank my managers for sending me. Woohoo!

Second, mere words can't express how much I am looking forward to it but maybe this does...

I asked if I had to take my work laptop with me to the Summit.
The reply, "You're not going on vacation."
To this geek, it's damn near a vacation.
I think it's one of those events that I will need a vacation to recover from given how much is planned.

I attended virtually last year. I watched all the available streaming events, followed the #sqlpass Twitter hashtag and convinced my manager to buy the conference DVDs.

I and many others are again using the #sqlpass hashtag to prepare for this Summit. I still need to look at the schedule and plan my days but I do know I will definitely want to see Buck Woody and Brent Ozar in the same room doing their presentation.

I want to attend the session on how to write a speaker abstract, also. I submitted two sessions for this Summit on PowerShell that weren't accepted. I've enjoyed speaking at several PASS SQL Saturdays and I've set a goal to some day speak at the PASS Summit. I've learned so much preparing for and participating in SQL Saturdays. Also, I've gotten to know a great group of people in the process. Participating in SQL Saturday and Twitter has prepared me to get the most from the PASS Summit because I already know many attendees and they've shared past experiences via these mediums to help the rookies get the most out of the Summit.

Kendal Van Dyke @SQLDBA has some great advice for first time attendees. He tipped me off to the Light Rail option from the airport to downtown when I asked the SQL tweeps about getting to/from the airport. He then wrote a blog post to cover a lot of other relevant topics. Tom LaRock @SQLRockstar has put together the OC (Orientation Committee) and a preview to help the rookies have the best experience. We meet on Monday before the Welcome Reception.

I'll be arriving Monday afternoon and leaving Friday morning. I'll be tweeting like everyone else so follow @RonDBA and #sqlpass for the live feed.

I'll be participating in "Where your SQL Saturday Shirt Tuesday". Look for the Florida SQL Saturday participants in their "Florida Print" shirts. Hard to miss.

Looking forward to meeting you all! Remember, YOU ARE NOT ON VACATION. LOL.

SQL Saturday #49 - Orlando

2010-10-20T22:19:00.001-04:00

My THIRD SQL Saturday of the year is behind me.

I spoke in Tampa #32 and South Florida #40 earlier this year.

Jack Corbett(@unclebiguns), Andy Warren(@sqlandy) and their volunteers did another EXCELLENT job on this event.

What I liked:

The coverage via the #sqlsat49 Twitter feed.

The constant updates from Jack on the event via e-mail and twitter.

The large schedules on the wall in the hallways even though I stayed in the PowerShell room most of the day.

Liam Fitzpatrick’s Bangers & Mash with Sam Adams OctoberFest beer at Friday night’s speaker party sponsored by Confio Software

Meeting the ScriptingGuy and ScriptingWife. Nice people. Funny guy.

The ScriptingGuy statue that @MaxTrinidad got as a gift. He wouldn’t let me touch it.

I REALLY liked that statue Max. (c;

The tweet from the @ScriptingGuys while I was doing my session “Why Learn PowerShell” asking if I wanted to do a guest blog post for the Scripting Guy blog.

How about “If the Original Cylons Had Learned PowerShell, Would They Have Been replaced by CGI?”.

@z_williamson’s tweeting the color commentary for the sessions he attended. Beware don’t make a mistake when he’s in the room. One of the presenters had an incident with a lack of power to his laptop and blurts out “Don’t tweet that!”. Too late, z_williamson had pulled the twitter trigger well before. I hope he’s going to the PASS Summit to provide coverage. I’d also like to thank him for taking pictures during my sessions.

A few quibbles:

The sponsor area. Need to find another table arrangement that reduces the bottleneck in the hallway.

Not enough Ninja T-shirts to go around. Seriously. LOL. No, I’m kidding.

Moving on…

Hey, Tampa. Let’s do a Kung-fu SQL Saturday in 2011. Orlando is going Ninja next year.

The 2010 PASS Summit in Seattle is next for me. @RonDBA will be there.

I can’t say enough how valuable the SQL Saturday concept is for networking and staying current on SQL Server. If you want to get ahead, you need to attend.

Is PowerShell a Springboard?

2010-09-17T19:50:00.000-04:00

I think so.

I've used PowerShell to learn about Automation, SMO, WMI and object-oriented programming for starters. Also, I've automated compliance reporting and ID administration tasks with it.

On one occasion, I was troubleshooting a connectivity issue involving an app that used C#.
I was able to read the C# code and troubleshoot the issue because PowerShell's syntax is very similar to C#.

If I've piqued your interest, come to my session at SQL Saturday #49 in Orlando on October 16, 2010.

#sqlmovies

2010-09-10T06:59:00.000-04:00

Yesterday's Twitter meme #sqlmovies was a lot of fun.

My entries and the movie they were derived from follow.

I, DBA is a play on the movie I, Robot.
The Hurt Blocker obviously from The Hurt Locker.
@WesBrownSQL's RT The Dead Locker was perfect.
Lost In SAN from Lost In Space.
And finally "what a query wants" from Amanda Byne's movie "what a girl wants"

Buy Lunch, Get Training FREE - SQLSaturday #49

2010-08-23T06:17:00.001-04:00

I'll be speaking at my THIRD SQLSaturday of the year in Orlando.

I'm doing a mini session and a regular session.

The mini session is a condensed version of my Why a DBA Should Learn PowerShell talk. In 15 minutes, I hope to convince you that learning PowerShell is worth your time.
Powershell is the newest scripting language from Microsoft and it goes across the Windows platform, including SQL Server. So it's new and exciting, but is it ready for prime time? Is it worth the effort to learn? How will it help you? Join PowerShell advocate and user Ron Dameron for a quick discussion of why he thinks knowing Powershell is a key skill for a SQL Server DBA.

My full session for the day is

Automate Login Administration & Compliance Reports

SQLSaturday is the best training value for the money.

Buy a lunch, Get a FULL day of SQL training for FREE!

Learn by sharing

2010-08-02T09:00:00.002-04:00

SQL Saturday #40 in South Florida recap.

Speaker dinner was great at Longhorn Steakhouse. Thanks to Confio Software and SQLSkills.com for picking up the dinner and bar tab. Great to meet more SQL Tweeps in person and catch up with previous acquaintances.

Evidently, the organizers did a fantastic job marketing this event because there were a large number of attendees. The numbers I heard were in the 400 - 500 range. Rooms were full for the talks I saw and the Commons was busy.

Check-in was very smooth. Speaker room was great. Plenty of water, refreshments, and food for the attendees. Technical support from DeVry University staff was very helpful.

The PowerShell room was packed for my first session at 8:30 am on "Why DBAs Should Learn PowerShell". I think I may retitle this "Why ANYONE Can Learn PowerShell".

My second session on how to automate database login administration and compliance reporting was also well attended.

Had a great time eating a super lunch from Jason's Deli in the speaker room. Showed @BrentO and Tim Ford(@sqlagentman) my slides from my first deck. I was inspired by Brent's post on how to find great photos for your slide decks. Got to see Brent speak in person for the first time. Smooth as silk he was. Even when the power blinked out at the perfect moment in his talk on DR.

I still picked up a few tips on DR and Virtualization despite him saying it was a 100 level talk. He provided guidance on size thresholds for virtualization candidates and he confirmed my thinking on the best HA/DR options for SQL Server.

What makes a SQL Saturday great? Everyone who pitches in from the community.

Great job by the organizers, sponsors, and speakers for this event! Awesomesauce all over this event.

SQL Saturday #40 South Florida. Here I come!

2010-07-27T20:30:00.001-04:00

Need a few reasons to come to this event?

It’s FREE.

You’ll learn from a master. A Microsoft Certified Master.

Brent Ozar will do two sessions. His first at 0945 conflicts with my first session. I hope they gave me a small room for my session. I predict most attendees will be in his session, not mine. He’s guaranteed to keep you awake. No cover charge at the door. Costumes optional.

I’ll get to see Brent’s second session on Disaster Recovery in Hurricane Alley.

College football hasn’t started. So, you won’t miss UF, UCF, USF, FSU or The U.

It’s FREE.

Another Microsoft MVP, Tim Ford, will be there to talk about indexes, DMVs, and mistakes to avoid. BTW, great post from him recently on how being involved in the SQL community allowed him to land softly at a new job when he wasn’t looking.

More MVPs, Twitterati and local experts galore.

Did I say the whole thing is FREE!

I’m on the schedule for two sessions first thing in the morning:

Why DBAs Should Learn PowerShell

I will detail my reasoning why a DBA would benefit from learning PowerShell.

A DBA can use PowerShell in conjunction with SQL, WMI, and SMO to automate repetitive tasks and better manage their workload.

Automating SQL Server Login Administration and Compliance Audits with PowerShell

Are your internal auditors asking that the administration of SQL Server logins be handled by a third party other than the DBAs? Is your Compliance and Audit departments asking you to determine if password policies are being enforced, who are sysadmins on your servers, who owns databases? If so, come to this session to learn how to complete these tasks quickly and efficiently using PowerShell and SQL PowerShell Extensions (SQLPSX). This automation is capable of adding or dropping logins and adding or dropping members in database or server roles on any server. Also, I will demonstrate how to automate compliance reporting via PowerShell and SMO to report on the usage of password complexity/expiration policies, database owner, sysadmin role members, and other security related information defined in the SMO object model.

I don’t think there is a better training or networking value than attending a SQL Saturday.

See you on Saturday bright and early.

In the works…

2010-07-01T22:00:00.001-04:00

Speaker abstracts I've submitted...

SQL Saturday #40 in Miramar, FL.

Automating ID Administration with PowerShell and SQLPSX

I will demonstrate how I automated ID provisioning using PowerShell, SQLPSX, and my company's standard job scheduler.

Database Hardening via PowerShell

I will demonstrate how I use PowerShell to handle the issues encountered in a Fortune 50 corporate environment that has over 500 database servers and 3600 databases.

Why DBAs Should Learn PowerShell

I will detail my reasoning why a DBA would benefit from learning PowerShell.

A DBA can use PowerShell in conjunction with SQL, WMI, and SMO to automate repetitive tasks and better manage their workload.

The bonus for attending SQL Saturday #40, meeting Brent Ozar and Tim Ford in person.

SQL PASS Summit 2010

Why DBAs Should Learn PowerShell

Described above

Automating SQL Server Login Administration and Compliance Audits with PowerShell

MCITP Database Administrator 2008!

2010-05-04T22:34:00.001-04:00

I passed the 70-450 exam last week and completed the 70-432 exam earlier in April.

I have to say this certification is a much better test of your real world DBA skills then the MCDBA exams.

A lot less memorization required and a lot more critical thinking needed.

I started with Buck Woody's notes for the 70-432 exam and used the book he recommended.

There are no self-study kits for the 70-450 exam. I prepared for this exam by reviewing the exam's Skills Being Measured outline along with SQL Server Books Online.

For both exams, I will say that practical work experience does help a great deal especially with the 70-450 exam.

I answered a number of questions on the 70-450 exam just thinking about real world issues I have dealt with.

I will also say attendance at my local SQL User Group meetings, SQL Saturdays, Quest Software Virtual Events, 24 Hours of PASS, reading blogs, and twitter contributed to my MCITP success.

While it's always gratifying to earn the certification, I think the most valuable aspect of certification is that it pushes you to learn other parts of the product that you may not get a chance to use regularly.

Also, this success confirms for me the value of the social media, blogs, twitter, and community resources.

I don't think I would have passed the tests without using all of these resources.

Finally, I thank all of you who blogged about your certification experience while I was preparing for my exams.

February Orlando PASS Meeting

2010-02-12T14:37:00.003-05:00

Jack Corbett (@unclebiguns) and Andy Warren(@sqlAndy) invited me to speak at this month’s Orlando PASS meeting. First, I want to thank them for the opportunity. I enjoyed it.

For the benefit of those who couldn’t make the trip to Tampa last month for SQL Saturday #32, I did my talk on “Database Hardening via PowerShell”.

I worked a half day and then headed to Orlando around 12:30 PM. The plan was to speak and to visit my daughters at the University of Central Florida.
I visited with the youngest daughter prior to the meeting. When I asked if she was available, she asked “Does this mean I get a free lunch?”. Yes, we had a late lunch
.
After lunch, I headed over to the meeting location around 4 PM hoping to beat the rush hour traffic. Boy, was I wrong. The constant rain that day slowed I-4 Eastbound traffic to a crawl. Plus, I was wondering who let all these people off work early???

I arrived at the meeting location at 5 PM, a full hour before the meeting started.
I’m glad because it took the full hour to get ready and sort out the LiveMeeting setup. It was the first time I had done a LiveMeeting.
Even with the hour prep time, I forgot to do one thing to prep for my demos. Did you catch it?
I forgot to import the SQLPSX 2.0 modules prior to starting the first demo.

I should have run the following in the PowerShell IDE prior to starting:
import-module ShowMbrs
import-module SQLServer
import module Agent
import-module Repl
import-module SSIS
import-module SQLParser
I was able to recover pretty quickly because I had a version of the script that didn’t use the SQLPSX modules.

My demos demonstrated:
- How to check the SQL Server version on 600 servers in less than 10 minutes.
- How to check which Windows groups have access to your database servers.
- How to check for the correct Recovery Model on multiple servers.
- How to list the sysadmins and database owners on multiple servers.
- A script to report on issues raised by our internal Compliance and Audit departments.

If any of this is of interest to you, I’ve provided my deck and scripts to OPASS to share.

As I said, I enjoyed the meeting. Andy and Jack do a great job getting people involved and talking to each other. The icebreakers were effective and the attendees were friendly and attentive.
As usual, I always learn something new when I speak. On this occasion, it was attending LiveMeeting as a speaker. I also appreciated the feedback afterwards of my talk via blogs, Twitter, and e-mail regarding my performance.

After the meeting, I visited my other daughter and made it home to Tampa around Midnight.
It was a fun day.

SQL Saturday #32 Recap

2010-01-25T06:57:00.001-05:00

SQL Saturday #32 was a great success this past weekend!

Pam Shaw (@pamshaw) and Jorge Segarra(@SQLChicken) did a great job organizing this event!

Thanks to KForce for hosting the event and to all the sponsors for picking up the costs.

If you missed this event, you also missed out on some of the best food ever at a SQL Saturday! Spaghetti Warehouse in Ybor City did a great job with the speaker/volunteer dinner Friday night. The day of the event all attendees got a taste of Ybor from “Latam at the Centro” for lunch. BEST LUNCH I HAVE EVER HAD AT A SQL SATURDAY!

Hmmm. Best lunch I’ve had in a while.

It was great to meet the following SQL Tweeps in person: @adam_jorgensen, Argenis Fernandez(@afernandez), David Taylor(@dyfhid), @GarethSwan, Jason Strate(@StrateSQL), Aaron Nelson(@SQLvariant),and Jeff Truman(@jtruman0917). I hope I didn’t miss anybody.

Great to see these tweeps again: @brianknight, @cmille19, @GratefulDBA, @patrickdba, @unclebigguns, @leighfreijo,

Thank you @devfish for REAL Cafe Con Leche in Latam’s Room B during the PowerShell sessions!

Met my first Father/Son DBA duo, Mike Nelson and Aaron Nelson (@SQLVariant). The father is an Oracle DBA, the son does SQL Server. I wonder if they ever have any Oracle vs. SQL Server debates at home???

My presentation “Database Hardening With PowerShell” went well using my recently won Dell Mini 10v. No performances issues at all while running PowerPoint Viewer, PowerShell V2, and SQL Server 2008 for my demos. I was the third of three presenters on PowerShell.

I put a plug in for the Tampa PowerShell User Group starting up in March 2010 at the end of my talk.

Why do I go to SQL Saturday events? Because of the people I meet, the variety of sessions, the chance to learn something new, and I enjoy volunteering as a speaker.

If you had attended SQL Saturday #32, a few things you would have learned are:

The MVP Deep Dives book has a PowerShell chapter worth the price of admission.

Ed Wilson’s PowerShell V2 Best Practices book is available.

It’s a good idea for a DBA to learn SSRS for their own reporting purposes.

I had to leave early so I missed some sessions I wanted to attend but all attendees will upload their slide decks in the new few days to the SQL Saturday web site.

I did snag a T-shirt from my favorite vendor Red Gate Software!

My FIFTH SQL Saturday is complete.

If you missed SQL Saturday #32 and my presentation, I will be at Orlando PASS on February 9, 2010 to do it again.

List Windows Groups With Access to SQL Servers

2010-01-06T12:21:00.001-05:00

A manager posed the question “Can we list all the Windows groups that have access to all of our database servers?” The answer is “Yes.”

It is very easy to do with a short PowerShell script via SMO.

The results are written to a CSV file.

   1:  ## List Windows Groups on a server ##

   2:  ## ./get-WinGrps.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 ("WindowsGroupsOnServers_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")

10:

  11:  # Version inventory

  12:  @(foreach ($svr in get-content "C:\Input\TestServers.txt")

  13:  {

14:

  15:      $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr

  16:      $s.Logins | ? {$_.LoginType -eq "WindowsGroup"} | select Parent, Name, LoginType

17:

  18:  })  | export-csv -noType $OutFile

19:

  20:  $end = get-date

  21:  write-host "End: "  $end

Ola Hallengren’s Maintenance Solution Updated!

2009-12-05T13:55:00.001-05:00

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.

2009-11-09T12:47:00.001-05:00

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

What a week of training!

2009-10-20T18:27:00.020-04:00

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 DMVs"
Joe Webb's "Locking and Blocking Made Simple"
Ken Simmons' "Automating Routine Maintenance"
Rodney Landrum "Taking Control of SQL Server Error Logs"
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!

PowerShell: Quick SQL Server Version Check

2009-09-29T22:39:00.001-04:00

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.

2009-09-29T22:05:00.007-04:00

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.

Two Lines of Powershell = Random Passwords

2009-09-28T23:53:00.004-04:00

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.

Search for the error message!

2009-09-27T09:49:00.001-04:00

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

2009-09-23T22:29:00.002-04:00

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

2009-09-17T07:11:00.006-04:00

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

2009-09-17T07:04:00.003-04:00

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:

Discover DEVELOPMENT databases set to FULL Recovery Model

2009-09-09T21:56:00.010-04:00

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

#Active August Morning Routine

2009-08-20T06:46:00.022-04:00

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:

Chen Manching Yang Tai Chi short form (full set) http://bit.ly/2Hdiw
Chen Style Taijiquan 38 Form http://bit.ly/PgDXr
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.

Convincing DBAs to Learn PowerShell

2009-08-05T06:25:00.023-04:00

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:

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.

Environment Hardening?

2009-07-31T06:58:00.011-04:00

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.