THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

  • Speaking - Tech Ed, Tech Ed, SSWUG, PASS

    I feel like I've hit the speaker's lottery this year, starting with SQL Cruise Miami, and it just keeps getting better! After a great trip to SQL Bits I'm now preparing for my travel to New Orleans for more great SQL fun!

    In early June I'll be speaking at Tech Ed North America, this year in New Orleans. I'll be presenting the same session later in June at Tech Ed Europe, in Madrid. Here's the session details:

    Maintain SQL Server System and Performance Data with PowerShell
    Maintaining a solid set of information about our servers and their performance is critical when issues arise, and often help us see a problem before it occurs. Building a baseline of performance metrics allows us to know when something is wrong and help us to track it down and fix the problem. This session will walk you through a series of PowerShell scripts you can schedule which will capture the most important data and a set of reports to show you how to use that data to keep your server running smoothly.

    I've also been selected to be part of the Summer Camp 2013 Conference and will present multiple sessions that you can view online, starting July 9.

    Today the announcements were made regarding the PASS Summit 2013 presentations. I'm happy to say that I was selected again this year, and this time I'm presenting a brand new session, in the BI Dev Track!

    Automate Your ETL Infrastructure with SSIS and PowerShell
    Much of your ETL process flow consists of packages that are very similar in structure, capturing data from a single source and transferring that to a single destination. Creating the individual packages can be tedious and it's easy to miss something in the process of generating the same basic package over and again. BI Markup Language makes it easy to build new packages, and PowerShell makes creating the BIML scripts easy. In this session we'll show you how to use PowerShell to generate dozens of SSIS packages doing similar tasks from a defined set of ETL sources.

    I have a special fondness for the PASS Summit. My first Summit was in November of 2003, and there I met many amazing and wonderful people, including Johan Bijnens, Morten Baden Rohde, Brian Knight, Steve Jones, Andy Warren, Kevin Kline and many more. Each year I've gone back I've renewed those friendships and made many more. I've been fortunate to have been selected to speak again this year and am excited to see everyone again this year in Charlotte.

    I look forward to seeing you there.


  • New Article on the SQL Server 2012 Backup and Restore PowerShell Cmdlets

    While I was on vacation last week in Scotland Simple Talk published a new article I wrote called Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets.

    Hope you have as much fun with it as I did writing it.



  • T-SQL Tuesday #41 - Presenting and Loving it!

    For this T-SQL TuesdayT-SQL Tuesday Bob Pusateri asked us to share how we came to love presenting.

    Before I ever got involved in computing technology I had (and still have) a love for the theatre, specifically musical theatre. When I was little the majority of albums (this was the 1950s, kids) we had were cast albums from Broadway shows my parents had seen at Musicarnival. I performed in shows all through school, and was a Theatre Major at Kent State University before I realized I needed to make a living.

    It was this love of "performing" that had employers sending me to the trade shows to talk with customers. I wasn't the typical programmer, I could talk with people, even people I didn't know! One company was so impressed in my performance when I'd played Harold Hill in the show The Music Man that they made me a salesman. That failed miserably.

    About eight years ago I started attending meetings of the Cleveland SQL Server group, at the Microsoft office in Independence, Ohio. As people had questions I'd pipe up and answer when I could, and as they needed someone to present I offered to put together my materials as a presentation. The first few times were a bit rough - ok, they were very rough - but the group was gracious and I learned to organize the material better. In addition to the user group presentations I'd been giving training presentations to the staff at work, getting them to understand SQL Server better to make my job as the DBA easier. This helped me develop my skills a lot.

    I was first selected to speak at the PASS Summit in 2006, and I presented a session on SMO (Server Management Objects) and my demos all used Visual Basic. After the presentation a number of people came up to me and said they were administrators and weren't allowed to have Visual Studio on their desktop. PowerShell had just been introduced and I adjusted my material to use PowerShell.

    Also in 2006 I became a Microsoft Certified Trainer (MCT). In teaching the official Microsoft courses I learned how to work with material I hadn't created myself, which then helped me build better presentations of my own material. I also learned that having to teach material forced me to learn it better myself. Someone will always ask questions about an aspect of the topic I'd never encountered. I found the best way to learn any topic is to teach it.

    In the course of events I became the leader of the Ohio North SQL Server Users Group and at each meeting I ask everyone there to think about putting together a presentation for the group, so we can learn from them, and they can learn it better. We've got a great group of people who now present not just at our group but at others in the area and at SQL Saturdays and even the PASS Summit as well! I can't tell you how pleased I am at how many from our group are regular presenters in the SQL Server community now. These people include Erin Stellato ( b | t ), Sarah Dutkiewicz ( b | t ), Brian Davis ( b | t ), Adam Belebczuk ( b | t ), Craig Purnell ( b | t ), and Colleen Morrow ( b | t ), but more are stepping up regularly, and for that I thank each one of them.

    Presenting is one of those magic activities in which everyone benefits. I'm fortunate that I have a natural inclination towards it, but love to see new people stepping up and sharing their experience and knowledge with the rest of the community.

    See you at the next event!


  • T-SQL Tuesday #39: Managing your SQL Server Services with PowerShell

    T-SQL TuesdayThis T-SQL Tuesday is about using PowerShell to do something with SQL Server. Now, if you've read any of my blog posts you probably know I've been using PowerShell to do things with SQL Server for a while now, but I'm glad Wayne decided on this topic for his T-SQL Tuesday topic, because everyone has different ways to use PowerShell, and you can learn from all of them, as I do.

    (When I started to write this post I'd intended to share how I convert a PerfMon binary log file into SQL Server data for baseline analysis, but found I'd already done that here. Then, I thought I'd share how I save SQL Agent jobs and move them to another server, but did that one, too!)

    One of the interesting aspects of SMO (Server Management Objects) is the Managed Computer object. It doesn't get a lot of attention because, well, that goes to the SQL Server instance and the various database objects. Administrators, though, need to pay attention to managing the instance itself. SQL Server 2008 introduced the Configuration Manager, a GUI application that allows administrators to view the SQL Server services installed, including their current state, the service account they use, etc. It also allows them to manage the external access to the instances via the network protocols supported, the TCP/IP ports, etc.

    Here's a diagram of the Managed Computer object:

    Managed Computer Object

    Now, if you're familiar with the Configuration Manager you should see some parallels there, and that makes sense, because these objects are the ones Configuration Manager is working with.

    Let's say it's time for you to change the service account and password for your SQL Server instance. Using this model, we have our guide. We need to create a new ManagedComputer object, connect to the server, connect to the service, use the SetServiceAccount() method to set the new values, then restart the service. In this example I'll also restart the Agent service since I'm resetting the SQL Server instance service account.

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
    $mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer localhost
    $sqlinst = $mc.Services['MSSQLSERVER']
    $sqlagnt = $mc.Services['SQLSERVERAGENT']
    start-sleep -s 10

    Let's say I just want to see the services on my local instance, like I do in Configuration Manager. Again, that's pretty easy.

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
    $mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer localhost
    $mc.Services | select Name, ServiceState, DisplayName, ServiceAccount | format-table

    There are times you'll need to work with the network protocols, and I've actually used the following code to change the IP port for an instance after an install, because it HAD to match a particular value.

    $mc = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') localhost
    $ip.IPAddressProperties['TcpDynamicPorts'].Value = '1099'

    Now, why is this important, if I can do all this in Configuration Manager? Well, SQL Server 2012 supports installation on Windows Server Core, and there's no GUI on a Core server, so this becomes the best way to access the objects you normally manage with Configuration Manager. More importantly, it allows you to build a set of scripts to "just handle" any number of problems, without trying to remember what submenu gives you access to what property to change to solve your problem.

    Good luck, and thanks, Wayne, for hosting this month's T-SQL Tuesday!


  • Speaking - SQL Saturday 173, Washington DC

    After a great time at the PASS Summit in Seattle I'll be once again presenting on PowerShell for SQL Server at SQL Saturday #173 in Chevy Chase, Maryland.  On Friday, December 7 I'll be presenting my full day session Automate and Manage SQL Server with PowerShell. Here's the abstract:

    This soup-to-nuts all day session will first introduce you to PowerShell, after which you'll learn the basic SMO object model, how to manipulate data with PowerShell and how to use SMO to manage objects. We'll then move on to creating Policy-Based Management policies, work with the Central Management Server, manage your system inventory and gather performance data with PowerShell.  We'll wrap up with a look at the new PowerShell cmdlets introduced for SQL Server 2012 and how you can use PowerShell to manage SQL Server 2012 in server environments including the new Always On technology and Windows Server Core. After this one day you'll be ready to go to work and able to use PowerShell to make you truly effective.

    On Saturday, for those who can't make Friday's session I'll present PowerShell 101 for the SQL Server DBA, the introductory module from the all day session.

    If you've taken a look at Windows Server 2012 - and if you haven't yet, you should - you know that PowerShell is the tool Microsoft has chosen to allow you to manage hundreds, even thousands, of servers.  Using the graphical tools available will only slow you down, making you more easily replaceable.  Learning how to automate and manage lots of servers improves your efficiency and your value to your company.  There's a real advantage to understanding how to use PowerShell to get things done quickly.

    So I look forward to seeing you on Friday, December 7 and Saturday December 8 in Chevy Chase, for SQL Saturday #173!


  • PASS Summit 2012 Women In Technology Luncheon

    My final stint at the Summit Blogger's Table(tm) is for the annual WIT luncheon. I do appreciate the honor that PASS conferred on me by inviting me to the "table" for the event, it's been a lot of fun (even if there were some moments that weren't.)

    Newly-elected board member Wendy Pastrick is the MC for this year's luncheon, and the panel consists of Stefanie Higgins, Denise McInerny, Kevin Kline, Jen Stirrup and Kendra Little.  I'm pleased to say that I know each one of them except Stefanie Higgins, and I'll try to rectify that soon.

    Bill Graziano welcomes the crowd and points out that at the 2004 PASS Summit in Orlando Denise had invited Bill to attend the luncheon and he found he was the only male in the room, so he beat a hasty retreat.  He's attended every one since then.

    Wendy introduced this year's topic, "Where Have We Been, and Where Are We Going?"  

    Stefanie Higgins starts off, as a former board member and the founder of the WIT luncheon, and addressed how amazed she is at the growth of the luncheon since its inception.  It was started based on her experiences as a woman in a technology dominated by men.  When she took certification classes in the late 90s she had an instructor who wouldn't acknowledge her presence in the room.  She's thrilled with how far we've come supporting women in technology, and acknowledges that we have quite a ways yet to go.

    Denise McInerny says that at her first Summit in 2002 she had to work to find another woman to talk to.  At the 2003 Summit she saw the WIT luncheon and that confirmed her confidence that PASS supports women and has been an active volunteer since.  By having these events an environment exists to have the kind of conversations that encourage more women to participate and helps the community get stronger.  Not coincidentally we've encouraged women to take a bigger role in the organization, as volunteers and as speakers.  In 2011 15% of the attendees were women, and that'll be the base metric for how it grows into the future.

    The concerning statistic is that women leave the techology industry at twice the rate of men.  Women are the users and consumers of technology, but they're not taking part in creating that technology.  Studies show that diverse groups produce better results.  It's important to encourage more women in the technology fields.  The rest of the world is starting to recognize this and take steps to correct it, but there's a long way to go.  Denise identified a number of organizations that have programs to counter the trend, and some academic institutions are addressing it, but not in sufficient numbers at this point.

    Kevin Kline told two stories - one from the past, and one about the future.  When PASS was formed they realized they didn't have the kind of resources that other organizations had, They found that while they didn't have the money other large conferences had, but could make it a warm, friendly place to be.  What we had to say is "Welcome, come on in, sit next to me and let's talk about things." Kevin is the father of one son, and six daughters (not as the result of any smart decisions, by the way.) Men turn a group into a hierarchy and a power game, where are women are more interested in how the other person feels. It's hard to get girls to understand that being interested in technology is ok, even if their friends aren't interested.

    Jen Stirrup is the new PASSion Award Winner for 2012, and has been instrumental in opening up the European market to WIT.  IT is 5% of Europe's GDP, but only 25% of jobs in science in technology are women and only 17% of IT jobs are held by women.  Women are becoming more disengaged from IT and more disengaged from data.  The government of Scotland is putting together programs to reverse this growth but more needs to be done.  PASS allows women to support and encourage each other.  Every person who attended the SQL Saturday in Portugal attended the WIT lunch, and this trend is continuing in Europe.  Having homogenous groups working on problems tends to promote "group think", which isn't very effective at solving problems, where diverse groups tend to not fall into that trap.

    Finally Kendra Little talked about what's changed in the last ten years and how that affected her.  She always liked working with data and tried to find her way, but always saw herself as "an employee". Over the years she found opportunities to find the jobs that help her grow, but it still wasn't the whole picture.  PASS, and SQL Saturday, gave her opportunities to talk and be a mentor and a leader, and the people that came up to her and told her how that helped them taught her that she could be that leader, consultant and entrepeneur.  The opportunities PASS provides provides that level of confidence that wouldn't otherwise exist.

    Wendy points out to all the women that they are role models, and they can help young people make good decisions about their future. She then opened up the floor for questions.

    This is always a great event and continues to grow each year.


  • PASS Summit 2012 PreCon - DBA-298-P Automate and Manage SQL Server with PowerShell

    On Tuesday I presented an all-day pre-conference session on using PowerShell to automate and manage SQL Server.  It was a very full day and we had a lot of great questions.  One discussion in Module 6 was around scripting all the objects in a database, and I'd mentioned the script I wrote for the book The Red Gate Guide to SQL Server Team-based Development.  When putting together the demos for the attendees to download I realized I'd placed that script in the Module 6 folder, so you don't need to go anywhere special to get it, it's there.

    I've attached the demo material to this blog post, and those of you who stayed with me until the end of the day know the password for the file, and to the rest of you, please respect that these people paid to attend the session and the material is exclusively for them.

    Thanks to everyone who attended!


  • PASS Summit 2012 Day Two Keynote

    Summit day two is upon us and we're anticipating another exciting keynote.  Nice video of international attendees at the start of the keynote focusing on the global community.  Doug McDowell came out and talked about how much great content is going on simultaneously at the Summit.  In the last five years PASS has incorporated SQL Saturdays, 24 Hours of PASS, SQL Rally, Virtual Chapters, and doubled and maybe even quadrupled the membership numbers.  Doug talked about applying the revenues into these programs that support these activities.  The single largest fund raiser for PASS is the Community Summit.

    Doug talked about adding full time staff as community evangelists, and additional fulltime IT staff to keep the systems running that drive the organization. He also introduced Wendy Pastrick, James Rowland-Jones and Sri Sridharan as newly elected board members with two year terms starting January 1.

    Tom LaRock, VP of Marketing took the stage and talked about the community of volunteers.  After noting the wonderful work done by the volunteers across the board, Tom cited the monthly PASS Volunteer of the Month award and the Volunteer Honorable Mentions, Amy Lewis and Jesus Gil.  He then introduced the 2012 PASS Volunteer of the Year award winner Jen Stirrup. Jen took the microphone and thanked the Women-in-Technology organizations throughout Europe for their help.

    Next year the PASS Summit will be in Charlotte, NC October 15-18, 2013, and shared the special PASS alumni code for discount on next year's Summit. He recognized the companies that have sent 5 or more employees to this year's Summit.  The WIT Luncheon will be today, led by Jen Stirrup.

    Tom introduced Quentin Clark, Corporate Vice President for Microsoft, talking about turning data into business value.  "The opportunity we have is to rewire business around information."  The election results analysis is really a big data problem.  Using RFID in hotel keys, hotels can determine what services guests use even if they're not "buying" those services, such as the gym or the free breakfast, and they can tailor their business based on those trends.  Big Box retailers can use similar methods to change the music playing in the background based on the tastes of the customers in the store. It's all based on aggregating large amounts of data gathered through new methods of providing that data.

    Quentin invited Julie Strauss on stage to show a demonstration they called PASS Cinemas. They look at the tweets of the current movies running to analyze the marketing of the movies to maximize interest.  Like yesterday, they pulled data from various sources they used relational data sources, Hadoop and loaded it into PDW to analyze comments on twitter about the movies.  (I do find it a bit creepy that companies are tracking this level of information, though.) Unfortunately, the presentation was focused on a lot of details, but didn't really show us anything that wasn't already covered yesterday, and the details weren't enough for me to know truly how to provide these results. The whole demo experience was underwhelming.

    Looking forward to some great sessions today and dinner with Red Gate this evening!


  • PASS Summit 2012 Day One Keynote

    Today is the official start to the 2012 PASS Summit and I'm honored to have a seat at the Blogger's Table again. This is a set of tables set up in the back of the keynote room for people who blog frequently (I know) to share their thoughts on the keynote with the public, and appreciate the invitation from PASS to participate again.

    The lights go down and they showed a video of PASS board members talking about what the PASS Summit means to each of them. It was well put together and I know that the PASS Summit has changed my life.  Bill Graziano, the president of PASS comes out to address the crowd and talks about how the event has grown over the last 14 years.  (This is my 10th consecutive Summit.)  He asked all the people who have ever spoken at a PASS Summit, SQL Saturday, user group meeting, 24 hour of PASS or other like event to stand, and it was an impressive number of people who stood.

    New developments over the last year include a simpler way to find local PASS chapters from the website, and there are 20 Virtual Chapters available to take part in. He shared what PASS brings to individuals via local events, virtual events and and hands-on training. The number of events have grown over 80% this year, and have delivered over 543,000 hours of training in the last year.

    The PASS Business Analytics conference will take place in Chicago April 10-12, 2013. PASS is expanding to encompass the expanding focus on data, especially the focus on 'big data' throughout the world. Registration for this event is open now.

    PASS is also expanding its focus on the international community ( and is gathering community feedback from around the world to better serve the global community. PASS is considering bylaw changes to allow appointment to board seats based on geographic location to help support this community.

    Microsoft has sent 300 engineers to the PASS Summit. (This is why I love the fact that the Summit is held in Seattle so regularly.) If you have ANY questions about using SQL Server in any configuration, the CSS team has probably seen it, and can help solve the problems.

    On Twitter you can follow the events this week using the #sqlpass or the #summit12 hashtags.

    Ted Kummert, Corporate Vice President of Microsoft takes the stage to talk about Accelerating insight on any data.  He talks about the SQL Server family and how the Summit is like the family reunion, and welcomed all the first-timers to the family.

    He showed a picture of the people involved in the release of SQL Server 2012 on the day they signed off on the release.  He said that we (the PASS community) is part of the release as well, as they built it to meet our needs and requests.  He announced the release of Service Pack 1 for SQL Server 2012.

    The world of data is changing. Data is becoming a principle focus for business and organizations are realizing this. There are new types and new sources of data. Storage volumes increase to allow us to store more and more data.  Architectural assumptions have to change to allow processing that data in ways that were never before possible.  The cloud is a way to build infrastructure, along with new hardware and software that can handle incredible volumes to provide new insights on business.  Services powered by data analytics allow companies to improve customer service because they can see the trends that were never visible before.

    In-memory databases are one focus. Queries against a database in memory are naturally much faster, and Microsoft is building those types of solutions into the product (such as the tabular model in SSAS, and columnstore indexes.) They're bringing in-memory transactional processing engine (known as 'hekaton') to SQL Server.  Hekaton will be part of the next release of SQL Server.  They demonstrated an app that improved performance by almost 30 times by using in-memory tables and optimizing stored procedures to use those in-memory tables, without changing the application. In the next release of SQL Server, the columnstore indexes will be updateable and can be defined as the clustered index, so the performance gains will be significant.

    Another key part of the strategy is to manage any data, any size, anywhere, so they brought in Hadoop and other technologies. Microsoft HDInsight Server is in CTP at this point, and in preview they're providing Windows Azure HDinsight Service.  Parallel Data Warehouse has been out for a while now and is another key component in this strategy.  The next release of PDW will be in the first half of calendar year 2012 (SQL Server 2012 Parallel Data Warehouse) with up to 50x performance gains and optimized architecture.

    Another announcement is a product called PolyBase, which will unify queries between relational, PDW and Hadoop data, and query data in place regardless of it's location. Using PolyBase you can use Transact-SQL, create an 'external table' to the non-relational data, and query the non-relational data using the query syntax you already know.

    Microsoft is focusing on providing "BI for Everyone", integrating BI into everyday solutions that end users and professionals can use comfortably.  The next step in self-service BI includes Office 2013 and SQL Server 2012 SP1. Power View and Power Pivot are now fully integrated into Excel 2013.  With that integration you simply have to enable Power View and with one click you can integrate a map view of your data into your worksheet.  And now, Power View can access MOLAP cubes in Analysis Services via DAX queries.

    It was a good keynote and I'm excited about these new ways to get at disparate data quickly and easily.


  • [Speaking] PowerShell at the PASS Summit

    Next week is the annual PASS Summit, the event of the year for those of us in the SQL Server community. We get to see our old friends, make new friends, and learn an amazing amount about SQL Server, and it'll be in Seattle, so it's close to the mother ship. I love having Microsoft close, because it's easier to get to know the people who actually make this amazing product we spend our lives working with.

    This year I'm fortunate to have been selected to present three sessions. One is a regular session called Manage SQL Server 2012 on Windows Server Core with PowerShell, where I'll be showing you how to set up and install SQL Server 2012 on Windows Server 2008 R2 and Windows Server 2012 Server Core, with some cool things that make that setup easy to manage. I'm also doing a Spotlight session called Maintain SQL Server System and Performance Data with PowerShell, where I'll show you how to create a database to contain the inventory of the servers you manage, and how to gather performance metrics, all with PowerShell.

    On Tuesday, I'll also be doing a pre-conference session all day called Automate and Manage SQL Server with PowerShell. I truly enjoy spending the day with this session. We start out with an introduction to PowerShell, because you really need some basics before the more advanced topics make sense. This is not a comprehensive PowerShell course, because that would take days, but the introduction will give you enough to truly get started.

    Once we get the basics down, we'll dive into Server Management Objects (SMO), because the SQL Server team built this interface specifically for managing SQL Server. You may love SMO or you may hate it, but understanding its structure will help you automate your tasks as you manage your environment. We'll get into the various activities a DBA is expected to manage and walk through scripts that solve those problems. As the day goes on we'll get into managing Central Management Server and Policy-Based Management, and we'll cover the topics in the other sessions I'll be doing on Wednesday. We'll also see how much more important PowerShell is in Windows Server 2012 than ever before.

    If you haven't sensed the excitement about the upcoming PASS Summit, check it out, and I hope to see you there!


  • Search For a Query in RDL Files with PowerShell

    In tracking down poorly performing queries for clients I often encounter the query text in a trace file I've captured, but don't know the source of the query. I've found that many of the poorest performing queries are those written into the reports the business users need to make their decisions. If I can't figure out where they came from, usually years after the queries were written, I can't fix them.

    First thing I did was find a great utility called RSScripter, which opens up a Windows dialog that allows you to connect to a Reporting Services server and script the reports to RDL source files in a directory you specify. By exporting the RDL files from the Reporting Services server I can search through and find the queries I need to find.

    The next thing I do is set a string variable to contain the query in question, which I do here using a here-string:

    $q = @'SELECT  d.SalesOrderID,
    FROM    Sales.SalesOrderDetail d
            INNER JOIN Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
            INNER JOIN Sales.SpecialOffer o ON d.SpecialOfferID = o.SpecialOfferID
    WHERE   d.SpecialOfferID <> 1'@

    Now, when RSScripter runs it pulls out everything from RS, and I'm only interested in the RDL files so I use the Get-ChildItem cmdlet and pipe the output through the Where-Object cmdlet to select just those files whose extension is '.rdl', and assign that to a variable.

    $rdls = Get-ChildItem | where-object {$_.Extension -eq '.rdl'}

    Now I have a collection of RDL file objects, which I can iterate through with the foreach command. For each one I'm going to display the file name, then use the Get-Content cmdlet to read the contents of the RDL file, and pipe that to the Select-String cmdlet to look for the query string. If a match is found it'll display that line after the file name.

    foreach ($rdl in $rdls) { write-output $rdl.Name; get-content $rdl.FullName | select-string $q }

    While this may be a little crude, it's kind of an ad-hoc way of finding where a query comes from. There are recursive options for Get-ChildItem that'll search the entire subdirectory tree, but for now I didn't need that.

    I hope this helps you find some of those troublesome queries you're struggling with.


  • Scanning the Error Log with PowerShell

    One of the most important things you can do as a DBA is to keep tabs on the errors reported in the error log, but there's a lot of information there and sometimes it's hard to find the 'good stuff'. You can open the errorlog file directly in a text editor and search for errors but that gets tedious, and string searches generally return just the lines with the error message numbers, and in the error log the real information you want is in the line after that.

    PowerShell 2.0 introduced a new cmdlet called Select-String which searches through a text file and returns the lines matching the target string. What it adds is an command-line parameter [-context] which allows you to specify the number of lines before and after the match is found. Bingo! By specifying the parameter -context 0,1 with the Select-String cmdlet it returns the line with the error, and the line afterwards.

    To see what this does, first navigate to the errorlog directory, then issue the following commands:

    $errlog = Get-Content '.\ERRORLOG'
    $errlog | Select-String  -pattern 'Error:' -context 0,1

    Now that's really useful, but it does mean that I have to log on to each server to get the errors, or I have to somehow know where every server's errorlog directory is and connect through the network to get to the files. Not so nice if you want to work from a client and 'just get the errors'.

    Using SMO, there's a method under the Server object called ReadErrorLog() which reads the error log and (if assigned to a variable) creates a DataRow object containing the LogDate, ProcessInfo and Text properties from the errorlog. The ProcessInfo property contains the spid or other identifying value to indicate the source of the problem. By connecting to an instance using the Server object and loading the error log into the DataRow object, we can then get similar results, but we have to add an additional parameter, -inputobject, which lets us specify the property to search for our string.

    $svr = new-object ('Microsoft.SqlServer.Management.SMO.Server') MyServer
    $err = $svr.ReadErrorLog()
    $err | Select-String -inputobject {$_.Text} -pattern 'Error:' -context 0,1

    The advantage of this solution is that I don't log into the target server and I don't have to know where the log file is. The downside is that we lose the LogDate and ProcessInfo properties altogether, and there's no way to piece the parts back together. (Now, as sure as I say this Chad Miller will pipe up with a cleaner way to do this. This is good, and it's how I learn.)

    What I found that works, though it's a tad clunky for my taste, is to concatenate the properties back together before sending it to the Select-String cmdlet, like this:

    $errlog = @()
    $err | foreach { $errlog += [string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text }

    The problem with this approach, of course, is you have to pass through the errorlog twice. To minimize this, I added an option to only report the errors starting with a particular date.

    $errlog = @()
    $err | where {$_.LogDate -ge $startdt} | foreach {
    	$errlog += [string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text

    This minimizes the issue, and if you're properly cycling the error log (normally once a week) the amount of log data it has to cycle through twice isn't too bad. With the properties concatenated we then return to the original search command to get our results.

    $errlog | Select-String  -pattern 'Error:' -context 0,1

    But wait, there's more! After playing with it for a bit I realized I'd like an option to just return DBCC results. Unlike errors, DBCC results are reported on the same line as the DBCC reports it was run, so I don't need the line afterward. In my script I added a variable called $srch to the command line arguments, and if it's equal to 'DBCC' it just returns the DBCC lines.

    # Search the errorlog and return any error and the subsequent detailed message
    if ($srch -eq 'DBCC') {
    	$errlog | select-string -pattern 'DBCC' -context 0,0
    else {
    	$errlog | select-string -pattern 'Error:' -context 0,1

    And that's it! I named the script scan-errorlog.ps1, and it takes the instance name, start date and (optionally) 'DBCC' as parameters, and returns either errors for that server after the start date, or DBCC results since the start date.

    ./scan-errorlog.ps1 MyServer 9/25/2012

    That command line will return any errors reported in the errorlog since midnight last night.

    It's really fun to find gems like this that make our lives as a DBA much easier.


  • 24 Hours of PASS - PowerShell 101 for the SQL Server DBA

    Thanks to everyone who came out for the session today.  It was a lot of fun and I hope you had a great experience as well.  I've attached the demo scripts and slide deck to this post, and I look forward to seeing you in November at the PASS Summit!


  • Speaking - 24 Hours of PASS, Summit Preview Edition

    There's so much to learn to be effective with SQL Server, and you have an opportunity to immerse yourselves in 24 hours of free technical training this week from PASS, via the 24 Hours of PASS event.

    I'll be presenting an introductory session on PowerShell called PowerShell 101 for the SQL Server DBA. Here's the abstract:

    The more you have to manage, the more likely you'll want to automate your processes. PowerShell is the scripting language that will make you truly effective at managing lots of servers. But it's more than just a scripting language - it's an interactive shell that stores data for you and allows you to implement ad-hoc solutions quickly and easily. Within the PowerShell environment you can easily manage both SQL Server instances and the Windows servers themselves, giving you a 'best of both worlds' environment that puts you in control. This session will introduce you to PowerShell and show you how to use it to manage SQL Server across many instances.

    I look forward to seeing you there.


  • Speaking - Red Gate's SQL in the City

    The great folks at Red Gate have invited me to join the festivities at the SQL in the City events in both Chicago on October 5, and in Seattle on November 5.

    In both cities I'll be presenting a session entitled Automated Deployment: Application And Database Releases Without The Headache. Here's the abstract:

    Ever since applications were first created, the deployment of updates and changes has been a headache, with the potential of disruption of the application at best and data corruption at worst. Getting the steps of any deployment right are critical to the success, and if there's a problem it's even harder. Data structures depend on code, and code depends on data structures. In this session we'll look at how deployment has been done in the past, the problems encountered, and we'll look at some ways to mitigate the risk inherent in application deployment, including Red Gate's new Deployment Manager tool, which helps you streamline the whole process.

    There is a great lineup of speakers at all the events, including my good friends Steve Jones (b | t) and Grant Fritchey (b | t), who both work for Red Gate, but also people like Brent Ozar (b | t), Jes Borland (b | t), Buck Woody (b | t) and Adam Machanic (b | t), all good friends and great speakers.

    You can sign up for the Chicago event here and for the Seattle event here. I look forward to seeing you!


This Blog


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement