• Categories

  • Recent Posts

  • Archives

  • Copyright Notice

    Copyright © Nancy Hidy Wilson, 2010-2013. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Nancy Hidy Wilson and nancyhidywilson.wordpress.com with appropriate and specific direction to the original content.

SQLSaturday #447 Dallas – I am Speaking

SQLSat_logoThe first SQLSaturday I ever attended was #35 in Dallas and the North Texas SQL Server User Group (NTSSUG) set the bar high as host. That was several years ago and scheduling conflicts have prevented me from making the drive up I-45 from Houston to attend another SQLSaturday there. However, this year, I’m honored to be presenting at SQLSaturday #447 Dallas on October 3, 2015. My topic is “Managing SQL Server in the Enterprise with TLAs”.  What are TLAs, you ask? Why Three-Letter-Acronyms, of course! The TLAs that I will be discussing which you as a SQL Server DBA should be utilizing are CMS, PBM, and EPM. Come to my session in Room 100 at 8:30am (updated 9/30 for schedule change) and find out how using these features will improve your productivity and help you ensure standards are being followed in your environment.

If you are a data professional within driving distance of the DFW Metroplex, you should consider attending this free day of learning at the University of Texas at Arlington (UTA) hosted by the North Texas SQL Server User Group.

Check out the entire schedule, including low-priced Pre-con sessions on Friday, and register today to take advantage of this free training!

If you can’t attend this event, then check here for all the currently scheduled SQLSaturdays in the US and around the world! There is likely one occurring near you soon!

PASS 2015 Board of Directors Election

The PASS 2015 BoD election is now underway! There are 4 tremendous candidates this year, including two incumbents. But, there are only 3 positions available. By virtue of being the only EMEA candidate running this year, incumbent Jen Stirrup will by default win the EMEA seat, leaving the other 3 candidates competing for the US\Canada seat and the Open seat.

Ryan Adams, Argenis Fernandez, and Tim Ford (the other incumbent) have all contributed significantly to the SQL Server community over the past few years. They are all three very deserving candidates and if elected each will serve the PASS community well.

However, as the immediate past Chapter Leader of the Houston Area SQL Server User Group, I want to let you know why I’m specifically endorsing Ryan Adams.

Ryan Adams knows what it’s like to be a DBA with no access to help or training, so he has committed himself to helping others so they don’t have to struggle in their day to day jobs like he did. He has served in the SQL Server and PASS communities for over 10 years and in almost every capacity available including:

  • He has been in local user group leadership for 5 years with the North Texas SQL Server User Group.
  • He is in his third year as President of the PASS Performance Virtual Chapter and volunteered a year prior to that.
  • He has been a Regional Mentor for the PASS South Central Region for 4 years.
  • He has served on the PASS Summit Program Committee twice now.
  • He served on the 2013 PASS Nomination Committee (NomCom).
  • He was a SQLRally organizer and has helped organize 6 SQLSaturday events in Dallas.

It is in his role as a Regional Mentor to chapter leaders (me!) that I recognized Ryan’s passion for the community and the leadership capabilities that he brings. He set the standard for Regional Mentors by engaging on a regular basis with the South Central Region’s chapter leaders, learning about our struggles, ensuring we had the resources we need, and helping to us to find success in running our chapters.

I am confident that Ryan will continue his passionate commitment and leadership to the greater PASS community and make an excellent PASS Board member.

Voting closes on September 23rd at Noon Pacific Daylight Time, so research the candidates and login to your myPASS page. You’ll see an orange Vote Now button if you are eligible to vote.

Learn Something New (or Old) Every Day

I’m a big proponent of constant learning – looking to learn something new every day. It doesn’t have to be some big revelation. Sometimes it is just a tip on how to do something more efficiently (like in PowerShell) and sometimes it isn’t even new!

Recently, I was in a discussion about proposing a best practice recommendation in the CIS SQL Benchmarks to ensure deleted Active Directory Windows logins are also removed from all SQL Server instances where they were granted a login. One of the team members did a little research and found a reference to a system stored procedure which might help – sys.sp_validatelogins. My first question was – is it a documented procedure? Microsoft warns against using undocumented commands as they could get changed with most any update. Yes, it is officially documented in BOL (2008+). Second question – since which SQL Server version? Since at least SQL 2000 per this BOL! I have to confess, as many years and versions that I’ve been working with SQL Server and researching various security aspects, I was surprised that I didn’t recall this procedure – especially when the first non-BOL reference that I found in my own search turned up an article written by my friend Tim Ford for MSSQLTips!

In large, complex environments, both the processes (coordination between teams with varying responsibilities) and the technical aspects (how to identify these logins) can be, well, complex! But, depending upon your AD structure and the trusts in place, you as a DBA could periodically run this system stored procedure on your instances to find Windows logins or groups which are SQL Server logins but no longer exist in AD. You can then do a more thorough search of the specific instance’s databases and remove the login from all databases where it is a user, ensure that it isn’t a database owner, and ultimately remove the login from the instance. There is no currently known security risk to leave these orphaned logins on your SQL Server, but just like cleaning up orphaned users in your databases which do not have a specific instance login, it is considered a best practice to perform this task. And, for those of us who are neat freaks – it just makes your instance “cleaner” to get rid of the clutter of obsolete logins!

While it would be tempting to automate this check and just drop the database users, then drop the login, I did find that Thomas LaRock documented an anomaly he found several years ago which would make me always want to manually double-check the AD for any accounts reported as orphaned to ensure the account is really no longer valid. But at least you have narrowed down the search by using this procedure.

So, when looking to learn something new – don’t forget sometimes what you may learn is old – both Tim’s and Tom’s blogs were from 2009!

TSQL2sday #70 – Strategies for Managing an Enterprise


Jen McCown (Twitter) of Midnight DBA is the guest host for this month’s SQL blogger event known as T-SQL Tuesday (#TSQL2sday) which was started almost 6 years ago by Adam Machanic. This month, Jen has assigned us the topic: Strategies for Managing an Enterprise. Jen will be doing a wrap-up summary of all blog posts submitted on this topic per the rules and I’m looking forward to everyone’s input on this subject.

I’ve been presenting a session for the past several years at SQLSaturday events entitled “Managing SQL Server in the Enterprise with TLAs”. The TLAs (three-letter acronyms) are CMS (Central Management Server), PBM (Policy Based Management) and EPM (Enterprise Policy Management Framework). I’ll be presenting this session at SQLSaturday #447 Dallas on Oct. 3rd, 2015, so you can come learn the details of these features then. But, per the assigned topic for this post, let’s focus on the “strategies” driving the usage of these features.

For me, one of the main goals in managing the enterprise is finding ways to reduce the effort in managing that landscape –whether two instances of SQL Server or two thousand instances. A strategy for getting there is organization. The CMS enables you to define groups to which you register your SQL Server instances and then you can perform tasks against those groups. Why perform a task per instance when you can do it for multiple instances at one time? The CMS is actually defined in tables in the msdb database of the designated instance. I would recommend having a dedicated “central management server” instance which you will use for CMS, PBM, EPM, and other administrative tasks.

With CMS, you can create many groups and register instances in multiple groups based on the tasks that you may want to perform against those groups. For example, you can create groups to organize by SQL Server version, by Production\UA\QA\Dev\Test, by Application, by location, and be sure to have one group with all your SQL Server instances registered to it. SQL Server Management Studio (SSMS) enables you to run “multi-instance” queries using a CMS group. That is, you execute the contents of the Query window against every server instance in the specified group and the results are returned to the SSMS console.

A second strategy in managing the enterprise is standardization. Policy Based Management enables you to define expected settings (e.g. conditions) and verify whether an instance of SQL Server meets those conditions. Examples of policies could be checking that the sa login is disabled or ensuring the AUTO_SHRINK option is off on all databases. My recommendation is to configure the policies on the same instance as your CMS groups (e.g. your dedicated central management server) so that you only have to manage one set of policies. Policy definitions are also stored in the msdb database. You will also want to export the policies to a central file server. Policies are exported as XML formatted files. When evaluating the policies on a specific instance, you may use either the central management SQL Server instance or the file server where they are stored as the source. SSMS also allows you to manually evaluate policies against a CMS group – returning all the results to your SSMS console.

The third strategy is automation. If you have a CMDB (Configuration Management Database), then you can utilize it as the source for populating your CMS groups by scripting the entire process to keep your CMS groups current with the CMDB contents and setting this up as a SQLAgent job to schedule as needed. Policies can be assigned to categories. The EPM Framework provides a mechanism (a PowerShell script) to automate the PBM evaluations by category against a specific CMS group and store the results for reporting. EPM requires a database repository to store the results, so again I recommend creating this database on a dedicated central management server. Once you’ve been through the exercise of setting up your CMS, establishing policies, and configuring the EPM Framework for your environment, you’ll see additional opportunities to utilize the CMS for automating other tasks.

So, start leveraging the CMS, PBM, and EPM features today to reduce your efforts by organizing your instances, increasing standardization, and automating tasks in your enterprise!

Time For a Change

Shortly after the turn of the century (Y2K anyone?), I restarted the Houston Area SQL Server User Group (HASSUG) with the help of Tyler Chessman, who has been our local Microsoft liaison the entire time and without whom I couldn’t have run the group on my own for so long. I think the first meeting was in 2001, but I no longer have records going back that far to pinpoint the exact startup date. I know we met bi-monthly for the first year or so and then moved to monthly meetings in January 2003. We became a PASS affiliated Chapter when they began that program. In 2013, we combined the meetings for the Houston Area SQL Server User Group and the Houston BI User Group (which Tyler sporadically ran) into a single meeting with 2 topic presentations, one of which is always focused on the BI stack.

So, here we are some 14+ years later, having held 160+ meetings and having executed 4 SQLSaturdays, and it is time for me to hand over the reins of the group to some new leadership. Lynn McKee and Derek Wilson (no relation!) are now the official co-leaders of the group. Lynn and Derek have been shadowing me for the past few months as “chapter leaders in training” and I am more than satisfied that they are committed and ready to take the group to the next level.

I will still be around – attending meetings and presenting when needed (like in July and again in August!). I also promised Allen Kinsel (who took the lead on the past two SQLSaturday Houston events) that I’d help with the next SQLSaturday, too. And now he has it in writing, in public, for all to see! Leading the local chapter has given me the opportunity to meet so many wonderful people in our SQL Server community (aka #SQLFamily) – and not just locally. Participating in the Chapter Leader meetings at PASS Summit every year I look forward to engaging with other leaders from around the world. Over the past 15 years that I’ve attended Summit, so many of them are #SQLFamily to me now, too. It has been a great run and part of me will miss it, but I’m excited to be passing the torch to Lynn and Derek. They have some great ideas for the future and I hope you will give them your full support. After all, this is a volunteer job – we all do it for the love of the community.

If you’d like to volunteer to speak, sponsor, or otherwise help with the group, you can continue to use the HASSUG@sqlpass.org email account to contact Lynn and Derek. Follow the @HASSUG and @SQLSatHou Twitter accounts for info and keep an eye on the website for upcoming meeting info.

See you at the next meeting!

SQLSaturday #423 Baton Rouge – I am Speaking

I’m honored to be presenting again at SQLSaturday Baton Rouge on August 1, 2015. My topic is “Managing SQL Server in the Enterprise with TLAs”. What are TLAs, you ask? Why Three-Letter-Acronyms, of course! The TLAs which I will be discussing that you should be utilizing are CMS, PBM, and EPM. Come to my session in Room 1220 at 12:15pm and find out how using these features will improve your productivity!

If you are involved in almost any kind of IT work within driving distance of Baton Rouge, you should consider attending this free day of learning. Although using the SQLSaturday branding, there are more topics to be covered than just SQL Server. In addition to the traditional SQL Server AppDev, DBA, and BI tracks, there are tracks for .NET developers, SharePoint, Web/Mobile Development, Windows Server & Virtualization, Career Development, IT Managers, and more!

Check out the entire schedule, including low-priced Pre-con sessions on Friday, and register today to join more than 600 IT workers taking advantage of this free training!

If you can’t attend this event, then check here for all the currently scheduled SQLSaturdays in the US and around the world! There is likely one occurring near you soon!

TSQL2sday #68 – Just Say No to Defaults

T-SQL Tuesday (aka #TSQL2sday) is a monthly SQL Server blogger event started back in late 2009 by Adam Machanic (blog | twitter). For more info on its beginning and purpose see the origin of TSQL2sday. Each month a different SQL Server blogger is the host (announces the theme and compiles a recap). This month’s event is hosted by Andy Yun (blog | twitter) and the selected theme for this month is “Just Say No to Defaults”.

This is really embarrassing, but I’ve had a blog post started for this topic for years and somehow never got around to finishing it! Thanks, Andy, for giving me a reason to finally address a couple of my “must change” defaults.

Ease of installation is definitely a feature that has helped SQL Server to proliferate. You can have a functional system just by running setup and clicking Next, Next, Next….Finish! Without having to make any real decisions about what you are doing, you can be up and running in no time.

When installing the database engine component, the first change to be considered from the defaults presented during setup is the location of the various file types – however, I’m going to save that for others to address and may come back to it in a future post.

Today, I’m going to address a default that you can’t change during the setup dialog or via configuration file parameters. It must be adjusted post-install and is for SQLAgent.

Ever go to review the job history for a job and find nothing or only a couple of recent entries and you know the job has been running for weeks or even months? So, where is all that history? Sorry, you were the victim of the ridiculous defaults shown below which limit the total number of rows in the msdb.dbo.sysjobhistory table as well as set a max number of history rows per job.

To find this dialog in SSMS, right-click on SQLAgent, then select Properties, then select History.


These are defaults that you definitely want to change. In fact, instead of just increasing the number of maximum rows for the table and per job, I’d recommend that you decide on the time frame that you want to keep your SQLAgent job history and uncheck the “Limit size of job history log” option and check the “Remove agent history” option and specify the desired time frame instead as shown below. Many companies already have specifications for how long to retain activity logs, so using the time period that meets or exceeds those requirements should be helpful when it comes audit time.


Depending on the number of jobs and the frequency at which each is run, you may also need to keep a close watch on the size of msdb after changing this setting to find the optimum size for your msdb files to allow the sysjobhistory table to grow to its expected size without causing autogrow of your files. Remember to manage msdb just like any other application database with appropriate purges, backups, and other maintenance.

I can’t wait to see what others say “no” to in Andy’s round-up for this event. I’ll be looking for my other must change items and if I don’t see them, then I will be posting more soon!


Get every new post delivered to your Inbox.

Join 494 other followers