PowerShell – The Automator (T-SQL Tuesday #15)

I read about T-SQL Tuesday #15 this evening while riding the train on my way home; I need to get in the scoop of these events when they first come out.

How to Review SQL Logs with PoweShell describes one of the many scripts I created in PoweShell to automate my daily tasks. I have also created the CodePlex project iSQLPS which is a collection of scripts for a SQL Server inventory system -it is a work in progress. PowerShell is by far, the greatest tool created for administrators of Microsoft systems and I’m surprise on how many administrators still resist the force or are unwilling to see the light!

We don’t need no stinking DBAs! (T-SQL Tuesday #12)

During a smoke break, Mike and Pat (two suits for a global consulting firm) were having one of those vainglorious conversations about who spends more on what, who has the larger whatever and who has the latest gadget. Their conversation was interrupted as the CTO and smoking pal, Jeff showed up and joined the two suits to smoke a few more brain cells for the benefit of the firm. Jeff then proceeded to ask “We are really going through rough times and we need to cut payroll. Out of these names, who would you let go?” Needless to say, the most senior DBA was among the ones chopped.

It is undeniable that without the suits and their making mula brains, there will be no business and not much work for DBAs or anybody else for that matter. They seem to be very clear on what they want, “dinero” and lots of it. But sometimes while their brains are fixed on their goals and times are tough, they tend to lose the ability to make good decisions. Recognize where the company real assets are –their people.

Like any other species, DBAs come in different sizes, colors, broken, newbies, with and without baggage, lazy, and if you get lucky you may find a wiz here and there. In other words, like a box of chocolates. One thing is for sure, we are all highly caffeinated –it is our cool aid.

Accidental or by choice, if you are a DBA and don’t like what you do, I say to you, “GET OUT, RUN, RUN FOR THE HILLS before you become road kill or destroy the hard work of many people before you. I’m sure it has taken many people blood, sweat and tears to get the company to where it is today.

I have met DBAs with all different sorts of backgrounds. On the good side, I had the pleasure to work with the type of DBA who is a walking SQL Server encyclopedia with photographic memory. I have also met with the Gandalf DBA whose wisdom pours out every time he participates in discussions. On the bad side, I had the misfortune to work with several closed minded DBAs who were afraid to share their knowledge. Somehow I believe it had to do with job security.

In any case, the skillful DBA will differentiate himself from the rest by making sure the production environment is running without a hitch. If a hitch happens, he will make sure data loss is minimal or null. He will automate daily and mundane DBA activities such as backups in order to make time for skillset building activities like studying and research to further his career. The skillful DBA will do all that and then some by using the best tool for the job.

In the area of database administration, the MS SQL Server DBAs is at a crossroads where he must choose a specialty and stick with it. The product has grown to cover many technologies and each one goes deeper than ever before. For example, we now have access to a wealth of information about what is happening on the server via DMVs and new system functions. In addition to that, we have new features that help us manage large environments:

PowerShell

Policy Base Management

DDL triggers (server/database level)

SQLCMD

Central Management Server

Since SQL 2005, a myriad of operators, system objects and the .NET CLR have been added to the list of tools available to the SQL developer/DBA. A certain skill level is necessary in order to know the when, why and how to apply these new features in order to squeeze up to the last cycle out of each CPU. Some of these skills can only be acquired through experience in the field.

Database administration and development are two of the oldest areas under the MS SQL Server umbrella. You still have replication, SSIS, SSAS, performance tuning, security, BI, architecture, and many other areas one can choose to specialize on. Choose one, hit it hard and learn as much as you possibly can before moving to the next area. Keep your skills sharp by reading something new to you about the area you are developing. And always, be prepared to move on a moment’s notice; you never know when you’ll encounter Mike, Pat and Jeff.

Oh, here’s the end of the story from the top Priceless!
Cutting corners and buying the cheapest hard drive at Fry’s – $59

Promoting developer to accidental DBA because you fired the previous skillful DBA in order to cut payroll -$85,000

Opening up your cheap open box Fry’s hard drive and finding it scratched like a Nazareth record after playing Hair of the Dog for 1000 times – PRICELESS

How to Script All Stored Procedures From a Database with PowerShell

PROBLEM: You need to script all stored procedures from a database. Each stored procedure needs to be on its own file so you can add them to a source control repository.

SOLUTION: The solution here is a variation of what we did for “How to Script All Tables From a Database with PowerShell”. The only difference in this solution is the use of the Get-SQLStoredProcedure function from the SQL Server PowerShell Extensions (SQLPSX).

CLEAR
$sServer = "TUIRA"
$sDatabase = "AdventureWorks"

$oSO = New-SqlScriptingOptions
$oSO.Permissions = $true
$oProcs = Get-SqlDatabase $sServer $sDatabase|Get-SqlStoredProcedure

foreach ($oProc in $oProcs){
    $oP = $oProc.Name
    $oS = $oProc.Schema
    $DDL = Get-SqlScripter $oProc -scriptingOptions $oSO
    Out-File -FilePath "E:AdventureWorks$oS`_$oP.sql" -InputObject $DDL
}

How to Script All Tables From a Database with PowerShell

PROBLEM: You need to script all the tables from a database. Each table needs to be on its own file so they can be added to a source control repository.

SOLUTION: The use of SQL Server PowerShell Extensions (SQLPSX) facilitates the solution I’m about to show. The first example is a straight to the point, just DDL for the table without indexes, permissions, or any other scripting options. I used the Get-SQLDatabase, Get-SQLTable and Get-SQLScripter functions from SQLPSX to script out each table from the database into their own file.

CLEAR
$sServer = "TUIRA" 
$sDatabase = "AdventureWorks"
$oTables = Get-SqlDatabase $sServer $sDatabase|Get-SqlTable

foreach ($oTable in $oTables){
    $oT = $oTable.Name
    $oS = $oTable.Schema
    $DDL = Get-SqlScripter $oTable
    Out-File -FilePath "E:AdventureWorks$oS`_$oT.sql" `
        -InputObject $DDL
}

Next, I extended the initial script by adding a reference to the SMO scripting class SQLScriptionOptions. Now, I can script all primary key, indexes, foreign keys, default values and more. Look for “ScriptingOptions Members” in Books Online or the Internet and you will find under public properties all the possible things you can include or exclude from your DDL scripts.

Here, I chose to NOT include permissions and to include the clustered index, regular indexes, triggers, and schemas.

CLEAR
$sServer = "TUIRA"
$sDatabase = "AdventureWorks"

$oSO = New-SqlScriptingOptions
$oSO.Permissions = $false
$oSO.ClusteredIndexes = $true
$oSO.Indexes = $true
$oSO.Triggers = $true
$oSO.NonClusteredIndexes = $true

$oTables = Get-SqlDatabase $sServer $sDatabase|Get-SqlTable

foreach ($oTable in $oTables){
    $oT = $oTable.Name
    $oS = $oTable.Schema
    $DDL = Get-SqlScripter $oTable -scriptingOptions $oSO
    Out-File -FilePath "E:AdventureWorks$oS`_$oT.sql" `
        -InputObject $DDL
}

Review SQL Server 2008 Administration in Action

I got a copy of this book back in September of 2009 because I wanted to know more about the hardware configuration of production database servers. Specifically, I wanted to know about best practices and recommendations in the areas of storage, memory and CPU. While at the store, I read chapter 2 “Storage and System Sizing” which covers from what SATA means to areas such as RAID, SAN, solid state drives, and best practice considerations. I purchased this book just for the content in chapter 2 and had zero expectations for the rest.

After reading the remainder of the book, I commend Rod College and the team behind this volume for their fantastic job. The material is accurate, current and invaluable to DBAs at any level. You’ll get a wealth of information from new features found in MS SQL 2008 to concepts you should already know but you are not familiar with in MS SQL 2008.

Review Quest Software and Virtual Training for SQL

SESSION I – BLITZ! 60 Minute SQL Server Takeovers

Quest Virtual Training for SQL Server - Performance Monitor and Wait Events

This past Friday I received an email from a co-worker about the virtual event hosted by Brent Ozar, Buck Woody, Kevin Kline and Ari Weil. I have to admit I deleted the message because I get tons of emails during the day and did not have the time to look into it. Today I just came across a blog from Buck Woody about the same event and decided to check it out.

Wow! I have no regrets so far. I watched the first session “Blitz! SQL Server Takeovers” and it has tons of very good information. This session is 2.5 hours long so find a place where you can watch this thing comfortably. I really liked the relaxed and conversational way the speakers presented the material – lots of humor. Like always, questions from the audience (chat room), time of recording, lack of coffee or medication (ha!) carried some of the speakers (except Ari alias the water hose) away from the subjects currently discussed.

The meat for this session is the T-SQL code Brent uses when inheriting a new SQL server. The speakers provide a great deal of information in the video while discussing each chunk of code from the T-SQL script. The code has been available in SQLServerPedia.com since April this year but I recommend watching the video and not just getting the code; you will not regret it. The script covers the following areas: Backups, CHECKDB, job failures, security, SQL server (advanced) options, startup stored procedures, general server configuration, database mail, set up notifications for alerts, Windows event logs, system databases checks, user databases checks, database encryption, check for Enterprise edition features, location of data files, check for triggers, wait stats, and index information.

Towards the end of this session, Ari gives a demo (sales pitch) of the Foglight Performance Analysis product from Quest Software. We are currently evaluating this product at work and when used properly, it can save tons of time and money. The way it is designed and the information that it provides will help the DBA understand what is happening, why it is happening and sometimes, how to fix the problem.

I also watched the “Opening Remarks” and it is a quick rundown of what is covered in all four sessions. I give this event a thumbs up for its content and style -it is a must see for every DBA.

Review SQL Antipatterns

Bill Karwin style and choice of arranging the material within the chapters made it an easy read. It includes good and accurate examples the reader can test in almost any database system. Overall, the book will save the inexperience from many failures and embarrassment.

I purchased this book hoping to find something new. Unfortunately, I have been in the trenches long enough to live through and see most of these
patterns. This book will be a great addition and a must read for application developers, new database professionals including DBA, developers and architects. I say “new” because it takes time to see all these patterns.

Review SQL Server 2008 Administration with Windows Powershell

I recommend this book to all database administrators who still have not jumped in and executed their first PowerShell command. I know of several DBAs who for one ~~excuse~~ reason or another, keep postponing their exposure to the next generation command line shell. This volume makes a good first PowerShell book for DBAs. It covers the basics of the language with enough examples to get you going and good coverage of what is possible when combined with MS SQL 2008.

The first ten chapters go over specifics of the language, programming syntax, and how to interact with different areas of the operating system. The following eleven chapters demonstrate with code samples, how integrated are MS SQL 2008 and PowerShell. I have found that many of the examples in this book also apply to MS SQL 2005. My favorite chapter is 15 “Building SQL Server Inventory” because the authors provide a working solution to a common problems DBA have when managing many database servers.

To those DBAs who still have not jumped in, do yourself a favor and read this book.

Monitoring MS SQL Jobs with PowerShell

PROBLEM: You manage many MS SQL servers and need an easy way to review jobs that have failed.

SOLUTION: I will show you one way to accomplish this task with results displaying on the screen or to a file. To make your life easier, I recommend you download & install PowerGUI from Quest Software. This application comes with a script editor that will enhance your PowerShell development experience.

Go ahead and open a new session of PowerShell. You can type or copy and paste the code from our initial three lines of code. We will start by loading the SMO assembly & creating an instance of the server object. On the third and final line of code, we get a collection of all jobs by accessing the SQL Server agent on the server. We then pipe the resulting object and look for all jobs which are enabled that have failed.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "TUIRA"
$srv.jobserver.jobs|where-object {$_.IsEnabled -eq $TRUE -and `
    $_.LastRunOutcome -eq "Failed"
}

You will notice the result not being in a desirable format, especially when you have many jobs. We will use the information from one of the jobs to pick the specific properties we want in our report and display them in a table format. Lets pick the Parent, Category, Description, LastRunDate, LastRunOutcome, & Name properties for our next result.
On your PS session screen, hit the up key and you should get the last line of commands we executed. Hit the left arrow key to modify the command as follow

$srv.jobserver.jobs|where-object {$_.isenabled -eq $TRUE}|Format-Table `
    Parent, Category, Name, LastRunDate, LastRunOutcome, Description `
    -Autosize

Now we have a result we can save to a file or print. But before doing that, we’ll expand this script to retrieve the jobs from multiples servers. Lets go ahead and open a Notepad session and enter the name of a few MS SQL servers you manage, one name per line, and save it with the file name serverlist.txt. I saved mine in C: for simplicity. Now, we just need to enclose our previously executed commands in a loop.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null
foreach($svr in get-content C:serverlist_prd.txt) {
    $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
    $srv.jobserver.jobs|where-object {$_.isenabled -eq $TRUE}| `
        Format-Table Parent, Category, Name, LastRunDate, `
        LastRunOutcome, Description -Autosize
}

I find this looping syntax very helpful whenever I need to execute code against multiple servers. The result from this modified version of the original code will leave a blank space between each server or iteration of the code. We’ll solve that with a variable in the next and final version of the script which includes the code to save the report to a file.

CLEAR
Set-Content -Path "C:TempMonitorJobs_LowerEnvironments.txt" -Value " "
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null

foreach($svr in get-content C:tempserverlist.txt) {
    $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
    $a += $srv.jobserver.jobs
}

$a|where-object {$_.isenabled -eq $TRUE -and $_.LastRunOutcome `
    -eq "Failed"}|Sort-Object -Descending LastRunDate| `
    Format-Table Parent, Category, Name, LastRunDate, `
    LastRunOutcome, Description -Autosize|Out-File `
    -FilePath C:TempMonitorJobs.txt -Append -Encoding ASCII

Scan network for instances of MS SQL with Powershell

PROBLEM: You need to find out which computers and servers in my network have an instance of MS SQL installed.

SOLUTION: In this solution we’ll create an instance of the Windows Management Instrumentation (WMI) for each computer in a list. We’ll then query the Win32_Service class for the existence of a service which name starts with “MSSQL”. Go ahead and open a Notepad session and enter the name of several computes you manage in your network, one name per line, and save it with the file name isSQLInstalled.txt. I saved mine in C: for simplicity.

foreach($svr in get-content "C:isSQLInstalled.txt") {
    $filter = "name LIKE 'MSSQL%'"
    $result = get-wmiobject win32_Service -f $filter -computername $svr
    $resulttxt = [bool] $result
    write-host "$svr`: $resulttxt"
}

You need to be member of the Administrator role in each computer in the list for this script to execute successfully. This is a very basic way to search for instances of MS SQL installed in your network. One way you can expand this solution is by using a variable to capture the computer names in your network via Active Directory. You can then use this variable in the loop instead of reading the computer names from a file as we did in this solution.

Tag: DBA