THE SQL Server Blog Spot on the Web

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

Allen White

  • Developing New Speakers

    There's a lot of discussion about the process of bringing new speakers to a level that allows them to be ready and able to present at major conferences like the PASS Summit.  Andy Warren (@sqlandy) wrote a blog post about a speaker challenge and Brent Ozar (@BrentO) wrote about Speaker 47.  Erin Stellato (@erinstellato) responded to An Open Letter To SQLSaturday & User Group Organizers by Nic Cain (@SirSQL) with a post about Helping First Time Presenters.

    The most important thing to remember when helping develop and improve the breadth, depth and range of presentations is that we all started someplace.  At every user group meeting of my group, the Ohio North SQL Server Users Group,  I share what others call my "spiel". I share it in every user group meeting of other groups I attend, and in every presentation I give, be it a SQL Saturday, the PASS Summit or any other event I've been invited to speak.  Here's what I say:

    There isn't a person in this room who doesn't have some knowledge that we can all learn from. In other words, every one of you has something that I can learn from, but the only way that can happen is if you get up here and share it with the rest of us. It does two things. One, we get to learn from you. Two, you get to learn more about something you're already passionate about. You have to know more about something to present it, than to just do it every day. By sharing it with us we learn from you and you learn it better.

    Now I don't mean for someone to get up the very first time and expect to be at a level that's ready for a major conference. That takes experience. That takes understanding that someone in the audience isn't really interested in your topic, and it's OK if they get up and leave. That also takes understanding that someone in the audience wants to prove that they know more than you know about your subject. I've seen this happen to both new speakers and to very experienced ones.  Those of us who have been on the speaking circuit for a while have dealt with those people, and I encourage this group to help the newbie by letting the offending audience member know that their comments can wait until after the presentation is over.  (There's no "good" way to handle this kind of heckler, and it's best to get them to shut up or leave.)

    I like Erin's idea about a "buddy" system, to help each other out. It allows us to provide new speakers the kind of feedback they won't get on an evaluation form, and it provides moral support.  I feel extremely proud that five people from our user group in Cleveland will be presenting at this year's PASS Summit, including both Erin and me. I think this stems from my "spiel" and the supportive approach we take during user group meetings where new speakers present.

    Brent has some good points about the PASS Summit requiring the best speakers. The rating system in place doesn't objectively allow for ratings to be used exclusively, though. Speakers often get bad ratings because of things out of the speaker's control, things like the temperature of the room, the random disturbances outside the room, poor audio or video projection systems, etc.  There also doesn't seem to be a way to let attendees know what to expect, and even when it does, attendees often pay little attention to prerequisites or session goals. Everyone has their own agenda, and that's the criteria by which the speaker is rated.  I don't know how to fix this, but it deserves some attention.

    Most importantly, while we need to see the speakers we know will "deliver the goods", we also need fresh faces and new ideas. My "spiel" is my way of encouraging new speakers, and I think we're successful. SQL Saturdays offer a great avenue for new and experienced speakers to learn from each other.  I ask my experienced colleagues to lend a hand and help new people wherever possible, and attend their sessions, even if it's a topic that you already know thoroughly. (I once attended a "Basic T-SQL Backup" session by my friend and SQL Server MVP/MCM Sean McCown and learned things about backup I hadn't known, after using backup for 20 years.) By attending these sessions you provide support to the new speaker, you can intervene in the case of a negative attendee scenario, and you also just might learn something.

    We're all in this together, and we all grow with each other's help.


  • Why Your Abstract Wasn't Selected

    We're anxiously waiting to hear from PASS which sessions were selected for the 2014 Summit in November.  It's a big job to go through the hundreds of submissions and pick the sessions that will appeal to the people who will be paying over $1,000 to attend this annual event.  As I am also waiting to hear the results, I saw this article addressed to actors who didn't get cast for the part they worked so hard to audition for, and it seemed appropriate to address the same issues for would-be Summit speakers.

    So, given that I've been a speaker at many events, and rejected from many events, and have been, as a PASS Chapter leader and SQL Saturday organizer, in a position to select the sessions for an event, I hope I bring a little bit of perspective to the process.  With that in mind, here's a list of reasons that may affect why your submission wasn't selected.  (Note that I am not and never have been on the selection committee for the PASS Summit, so nothing that I say here reflects discussions that have directly impacted the Summit selection proces.)

    1)  Your abstract was interesting, and your title was engaging, but Microsoft submitted a session almost identical to the one you submitted, and as the co-founding member of PASS, and the reason we're all able to attend this great event, they have some pull.  If they want to present the session in question, their session will take precedence over yours. There's nothing wrong with your submission, it just got bumped by another.

    2)  Your abstract was interesting, your title was engaging, and it seems like it'd be a good session, but another session almost identical to yours was submitted by an industry leader, someone who has strong name recognition and has a great reputation for delivering sessions that fill the session rooms and consistently rates high in evaluation scores.  Remember that the goal of the conference is to get as many paying attendees as possible, and having a person who's known to put "butts in the seats" is going to take precedence to your session.  This isn't about you, this is about what's best for the conference.

    3)  Your abstract was interesting, your title was engaging, but there were too many sessions submitted for that particular track, and since they only had so many slots to fill, they had to draw the line somewhere.  It may not seem fair, and it may seem to you that there should be more sessions in that particular track, but the conference organizers had made there determination before session submission how many sessions were to be selected for each track, and there were just too many sessions in that one.

    4)  Your abstract was good, the title was interesting, but you'd had some problems before with meeting what the attendees expected from the presentation.  Remember that they're paying a lot of money, both in conference fees and travel expenses, to be at this event.  The conference organizers have to know that the presentations will be at the top level to justify those expenses, and they chose another session that more closely aligns with that goal.  You can work on those problems at user group meetings and SQL Saturdays to correct those issues and that'll reflect well in future events.

    5)  Your abstract was good, but the title was dull.  This is hard. How do you come up with a title that'll grab people's attention, but without going over the top? The best thing to do is to look at the sessions over previous events and see what wording grabs your attention.  It has to reflect what you're planning to deliver, but a session title like "Improving Query Performance" just isn't going to attract many people to your session.  Remember, the title will attract people to your abstract, and that will bring people to your session.  (Unless you're Conor Cunningham, and then everyone will come to your session because you're Conor Cunningham regardless of the title or abstract.)

    6)  Your title was good, but your abstract was dull.  Dull is hard to define, but it could be uninteresting, or too long, or is written in a way that tells the attendee that this session may not live up to the title's promise.  It's important to be concise, but accurately convey what the attendee should expect to gain by attending your session. It's also important to be enthusiastic about your subject, because if you aren't, why should they be?

    7)  Your title was dull, your abstract was worse.  Sorry, but this happens, too.  Look at the sessions from Summits past and work on developing titles and abstracts that will appeal to the selection committee.  Remember, this conference isn't about you, it's about getting people using SQL Server to come to the biggest SQL Server conference on the planet, and it needs to be the best.  You have good ideas, you just need to work on presenting them in a way that's attractive.

    So, those are my thoughts. I hope that sessions I submitted will be selected this year.  One of the things I love to do is to share what I've learned with others, to help them grow as SQL Server professionals. Hopefully I'll get to do that again this year.

    Best of luck to all of you.


  • Just an Ack Will Do

    In the 1980s one of my principal responsibilities was enabling communications between retail point-of-sale systems and the host computer where we processed those transactions. Communications protocols were many and varied, and I had to figure out their nuances and get the registers to talk to the hosts.  Success was most often achieved when, after sending a message to the remote system, I received back a message called an Ack, an acknowledgement that the message had been received successfully.

    In recent attempts at communication (via email, mostly), I've been finding that the receiving party doesn't feel the overwhelming need to let me know that the communication was received, and this is extremely frustrating to me. I have taken to asking questions that need to be answered, just to ensure that the message is being delivered.  (I really already know the answer, but it gets the respondent to acknowledge the message.)

    Communication is key to success, whether it's a project, a business relationship (or any type of relationship, really), and without two-way communication assumptions can be made that could cause that relationship, or database servers, to break down, and that's generally a bad thing. I try to avoid bad things.

    So really, send an Ack. It's not hard and lets the sender know you're there, and the project is still on track.


  • [Speaking] SQL Saturday #295 - Las Vegas

    On Saturday I'll be presenting one of my favorite sessions, Manage SQLServer Efficiently w/PowerShell Remoting, at the Las Vegas SQL Saturday.  Here's the abstract:

    You have more and more servers to manage and less time to accomplish everything. You're writing scripts to automate those tasks but they still take time to run. PowerShell remoting allows you to manage servers without the overhead of Remote Desktop, and allows you to run processes on all your servers simultaneously. In this session we'll walk through how PowerShell remoting works, how to set it up, and how you can save time getting things done more quickly.

    I've been talking about using PowerShell to manage SQL Server for a long time now, but the ability to manage multiple servers simultaneously just feels right.  I even built a new set of VMs to demonstrate the scripts using SQL Server 2014, which just became available yesterday!

    I look forward to seeing you at SQLSaturday #295!


  • The 2013 PASS Summit - Day 2

    Good morning! It's Day 2 of the PASS Summit 2013 and it should be a busy one.

    Douglas McDowell, EVP Finance of PASS opened up the keynote to welcome people and talked about the financial status of the organization. Last year's Business Analytics Conference left the organization $100,000 ahead, and he went on to show the overall financial health, which is very good at this point.  Bill Graziano came out to thank Doug, Rob Farley and Rushabh Mehta for their service on the board, as they step down from their positions.

    Tom LaRock introduced the new executive board, including Adam Jorgenson as the Executive Vice President, and Denise McInerney as the VP of Marketing, and he introduced the new incoming board members, Jen Stirrup, Tim Ford and Amy Lewis.

    The PASS Business Analytics Conference will be in early May in San Jose, California, and next year's PASS Summit will be in Seattle from November 4-7.  Tom invited everyone to the WIT luncheon here in the Cisco Crown Ballroom, to the Birds of a Feather lunch tomorrow, and to the Community Appreciation Party tonight at the NASCAR Hall of Fame.

    Today's keynote speaker is David DeWitt, Technical Fellow at the Microsoft Jim Gray Systems Lab, to talk about Hekaton, What, Why and How.

    Dr. DeWitt seems to think we'll be board with his talk and he couldn't be further from the truth.  He always explains really complicated things in a way that the rest of us can really understand.

    He calls Hekaton an "OLTP Rocket Ship". It's memory-optimized, but durable, and fully integrated into SQL Server 2014. It's architected for modern CPUs. 

    OLTP performance has benefitted from CPU performance improvements, etc., but the hardware improvements have pretty much maxed out.  Hekaton essentially "means" they're going for 100X performance improvement.  How do we get there?

    Pinning tables in memory still has problems.  Performance still limited by latches and locks, and interpretation of query plans.  Latches must be used to protect data in the buffer pool, but cause contention for other processes attempting to read the same data.

    Hekaton uses lock-free data structures, it uses versions with timestamps and optimistic concurrency control, and it's compiled into a DLL to improve performance dramatically.  SQL Server now has three query engines under the hood: Apollo (the Column Store index processor), the relational query processor, and Hekaton.  Essentially uses versioned views of data using the optimistic model to provide high-speed throughput.  Dr. DeWitt's discussion was detailed and thorough, and it would greatly benefit you to view the recording if you didn't see it live.


  • Speaking - Automate Your ETL Infrastructure with SSIS and PowerShell

    Today at 4:45PM EDT I'm presenting a new session using PowerShell to auto-generate SSIS packages via the BIML language.  The really cool thing is that this session will be live broadcast on PASS TV! You can view the session by clicking on this link.

     If you have questions for me during the session, you can send them to me via Twitter using this hashtag:


     Brian Davis, my good friend from the Ohio North SQL Server Users Group, will be monitoring that hashtag and feeding me the questions that I can answer during the session.

    I look forward to hearing from you on this great topic.


    Update: The session (to me) went really well, and I appreciate everyone who attended. I've uploaded the slides and demo scripts to this post.  AW.


  • The 2013 PASS Summit - Day 1

    It's SQL Server Geek Week once again! Every year at the PASS Summit the SQL Server faithful descend on the city of choice for the annual Summit, and this year it's Charlotte, North Carolina.  Once again I've been given the privilege of sitting at the bloggers table, so my laptop is on a table!

    So far this week it's been great seeing people I get to see just once a year. I attended Red Gate's SQL in the City event on Monday, and saw some great sessions from Grant Fritchey, Steve Jones and Nigel Sammy.  On Tuesday I was invited to attend the Biml Workshop, put on by Varigence, and you'll see a lot of great things happening in the BI space in the near future from them.

    This morning started off with a 3.3 mile run, organized by Jes Borland, and sponsored by SQL Sentry, called #sqlrun, and that was a great way to start off the event.

    Bill Graziano pointed out that over 700,000 technical training hours have been provided by the PASS organization, including chapters, virtual chapters, SQL Saturday, 24 Hours of PASS and SQL Rally events. Without the volunteers who make these events happen, we couldn't reach nearly as many people.  He also introduced Amy Lewis as this year's PASSion award winner, for outstanding volunteer effort.  Amy was one of the people recently elected to the PASS Board of Directors for the coming year.  He also thanked Ryan Adams in a special "honorable mention" for his volunteer work, and thanked all the PASS volunteers for making these events happen.

    The keynote speaker this year is Quentin Clark, Corporate VP at Microsoft for the Data Platform Group.  He starts out by saying that today's talk is about "listening to you".  Instead of everything being about "the cloud", they're now talking hybrid solutions, and that's great, because not everything should be pushed to cloud solutions.  He announced the SQL Server 2014 CTP2 is now public and available for download.  He also confirmed that these are the final "production ready" bits, so it should be feature-complete.  (At last night's get-together I was told that it's "almost" ready, so I'm sure if there's anything glaring that's a problem, it's still fixable, but it better be critical.)

    With the new features in SQL Server 2014, including the Hekaton bits, SQL Server can provide up to 30X OLTP performance gains, up to 100X faster star join queries, and up to 90% disk space savings over previous offerings.  There's no need to rewrite existing apps, and it's incorporated into the core engine, not a special add-in.

    Tracy Daugherty, Program Manager at Microsoft, came on stage to demonstrate some of the new in-memory features of SQL Server 2014. He built a demo that simulated 20,000 users simultaneously performing the same actions he's performing during the demo.  The first steps is game recommendations generated in 6.2 seconds, and the purchase completed in 4.0 seconds.  In converting to use in-memory features, he got a 9x performance boost doing the same exact activity.  The recommendations came up in 0.7 seconds, and the purchase in 0.1 seconds.  The hot list generation baseline took 26 minutes to build, and after conversion to in-memory technology, it took 0.4 minutes.

    Quentin also announced the ability to back up SQL Server for all supported versions os SQL Server (2005-2014) to Windows Azure, so you've got instant off-site backup, without having to spin up your own off-site storage solution. AND they've added the ability to encrypt the backups when creating the backups! (This is really a great new feature, to protect your backups from unauthorized access.)  Tracy demonstrated a feature called smart backup, which automatically figures out whether or not a "significant" amount of change has occurred, and if so, automatically kicks off a backup.  They've provided a free download that allows you to backup databases in SQL 2005, 2008 and 2012 to Azure storage, with encryption.  Yes!

    Data warehousing and "big data" is also being targeted for hybrid solutions, with a focus on using HDInsight, data warehouse virtual machines and PDW spread across on-premises and in the cloud for better performance.  Using the new technology, one company has reduced DW load times from days to hours.

    The Power Suite (Power Query, Power Pivot, Power View and Power Map) provide "real-time" insights for everyone, according to Quentin. He said that "everyone can ask the question", so they're trying to simplify the ability to get answers to those questions.  Kamal Hathi, Program Manager, came out to demonstrate the Power BI features. The simplicity of the way he pulled data from the source with simple questions reminds me of what they tried to do with "English Query" in SQL Server 2000 days, but it looks effective.

    If you go to, you can participate in a Power BI contest, to show how you are pushing boundaries with Power BI. Top ten winners get the new XBox One.

  • Fall 2013 IT/Dev Connections Sessions

    This week I had the honor of presenting two sessions at the IT/Dev Connections conference in Las Vegas.  My two sessions were Manage SQL Server 2012 on Windows Server Core with PowerShell and Manage SQL Server Efficiently with PowerShell Remoting.  I think both sessions went well, and the attendees indicated that they will be able to use what I presented as soon as they get back to the office, and to me that's the best praise I could get.  I meant to post the session materials the next day, but I was busy taking advantage of the conference to attend other great sessions myself.

    So here's the slide deck and demo scripts from both sessions, and thank you for attending my sessions!


  • PASS Elections and Great Candidates

    The candidates for the PASS Board of Directors are a great group of people, and I consider each one of them friends. Each one of them, if elected, will do a great job bringing their talent and experience to the task of leading this organization that has helped all of us become better at everything surrounding SQL Server.  There are two candidates, specifically, that I believe are best suited to the demands (and there are incredible demands) and responsibilities of the board of directors.

    Allen Kinsel has proven that he understands the requirements and commitments of the PASS director.  He opened up the process of how the selection process worked as Program chair and as head of the Chapter program has helped countless user group chapters come into being and thrive.  I think this success warrants a return to the board.

    As founder of SQL Cruise Tim Ford has built an organization that provides both great SQL Server training and fun times with family, both personal family and #SQLFamily.  During these trips attendees spend time learning SQL Server, getting to know each other and the instructors, and building the relationships that make what we do so rewarding.  He's also taken the helm of the SQL track at SQLConnections. His volunteer experience with PASS goes back over 10 years and so brings a track record of service along with his leadership and dedication.

    As I mentioned before, the other candidates are friends as well, and each one of them will do well for PASS, but I felt it important to single out both Allen and Tim, and I hope you'll carefully consider your choices and cast your vote when the polls open.


  • Are We Losing a Standard (Edition) Data Recovery Technology?

    One of the coolest technologies Microsoft released with SQL Server 2005 was Database Mirroring, which provided the ability to have a failover copy of a database on another SQL Server instance, and have the ability to automatically failover to that copy should a problem occur with the primary database. What was even cooler was that this new technology was available on Standard Edition! Mom and Pop shops could afford to implement a high availability solution without paying an extra tens of thousands of dollars in license fees, and still have a service they could rely upon. This new technology was continued with SQL Server 2008 and SQL Server 2008 R2, with the same edition rules, and now lots of companies embrace it fully.

    When we first started discussing Availability Groups, the new "Always On" technology that was introduced with SQL Server 2012 with Microsoft, the development team told us that it would "leverage" the clustering services technology built into the Windows Operating System. I was extremely upset with this decision because at that time, clustering services was only supported in the Enterprise Edition of Windows Server. (I was vocal enough about it that Michael Coles (blog) started calling me "Mr. Standard Edition".)

    The good news about Windows Clustering Services is that it's now supported in Standard Edition on Windows Server 2012.

    The bad news about Availability Groups is that it's only supported in Enterprise Edition of SQL Server 2012. Oh, and by the way, Database Mirroring is now deprecated.

    Now, deprecated doesn't mean it's gone, just that it's scheduled to be removed from the product in a future release. (Isn't that comforting?)

    Personally, I think that in the interest in competing with Oracle and DB2, Microsoft is abandoning the client base that got them to the point where they CAN compete with Oracle and DB2, and that isn't good, for the client base or for Microsoft. Customers have come to rely on Microsoft to put out a great product at a reasonable price. This focus on Enterprise Only for all mission-critical features puts SQL Server out of reach for startup businesses. (Yes, I know there's now Windows Azure SQL Database, but if you're in a place where your internet connectivity isn't always reliable you have no reasonably priced solution.)

    My request to Microsoft is to please continue to support Database Mirroring, and remove the "deprecated" label from that technology. It works, it's reasonably easy to implement, and it provides some level of comfort that ensures that businesses can continue to operate if a server fails for any reason.


  • Tech Ed Demos for "Manage SQL Server System and Performance Data with PowerShell"

    This morning I presented my session Manage SQL Server System and Performance Data with PowerShell at Tech Ed NA in New Orleans. We had a good sized audience who were very responsive and had many great questions. I had a great time presenting and the feedback I've gotten so far tells me the audience enjoyed it as well.

    I really appreciate everyone's coming out for the session and look forward to doing it again at Tech Ed Europe in Madrid later this month.  I've attached the slide deck and demos for everyone to try out.


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


More Posts Next page »

This Blog


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