Ryan Adams Blog

SQL, Active Directory, Scripting


On September 7th, 2011 at 1pm Central Time the SQL Server Worldwide User Group will be airing my presentation on how to Manage your shop with CMS and PBM.  The webcast is free for SSWUG members and $29 for non-members.  As an added bonus, I will be in the live chat room ready to answer your questions.  Make sure to catch my session by Registering Here.  Here is the abstract:

Manage your shop with CMS and Policy Based Management

In this presentation we talk about Central Management Server and how it can help you manage a disperse environment. We will also cover what Policy Based Management is and how you can leverage its power to better manage your environment. With PBM we’ll see what it can and cannot do to help you enforce standards in your enterprise. We will cover and demonstrate PBM for the beginner from creating and evaluating policies to receiving alerts on policy violations.


When you import policies into your Policy Based Management Server you have two options.  The first option is “Replace duplicates with items imported”.  This option will replace any policies in your PBM server that have the same name as the one you are importing.  This can come in handy if you have to make a change to the policy and re-deploy it to several servers in your environment.  It is also great to use if you suspect the policy has been altered and you want to ensure the settings are replaced.  The real jewel to this setting is that it does not replace the evaluation history for that policy.

The other option is “Policy State” and describes what state we want the policy to be put in after the import.  Here are the choices we have.

  • Preserve State – Whatever state the policy was exported in, is the state we want after we import it.  If the policy was in a disabled state when it was exported then it will be in a disabled state after we import it.
  • Enabled – Imports the policy and enables it regardless of the export state.
  • Disabled – Imports the policy and disables it regardless of the export state.

If you are not familiar with the policy being imported, including the Microsoft Best Practice Policies, it is always suggested to import the policy in a disabled state.  You need to open and evaluate the policy for yourself and your environment before you import it.

I will be speaking for the PASS DBA Virtual Chapter on August 24th, 2011 at 11AM CT.  I will be delivering my presentation on Central Management Server and Policy Based Management.

If you have not checked out the virtual chapters that PASS has, then you need to Go Check Them Out.  These chapters are designed to fill the gap for areas of the country that do not have a local user group chapter.  Even if you have a local chapter in your area you need to check these out.  Why?  Most speakers have to pay for travel out of their own pocket and can’t visit every chapter in the country.  The virtual chapters provide a platform where you can watch the presentations via Live Meeting.  This means you get access to great speakers you might not otherwise get to hear, and best of all you get to do it from the comfort of your desk.

Don’t cheat yourself of amazing free training, and go check out the chapter(s) that interests you.  There is a $50 Amazon gift card up for grabs if you register by 5pm ET August 23rd.  You can register for my session HERE.  If you just want to attend this FREE session then you can access the Live Meeting directly by going HERE.  Below is the session abstract:

Manage your shop with CMS and Policy Based Management

In this presentation we talk about Central Management Server and how it can help you manage a disperse environment. We will also cover what Policy Based Management is and how you can leverage its power to better manage your environment. With PBM we’ll see what it can and cannot do to help you enforce standards in your enterprise. We will cover and demonstrate PBM for the beginner from creating and evaluating policies to receiving alerts on policy violations.

I get a lot of questions about the terminology surrounding PBM.  Here is a list of the terms you will want to become familiar with along with a description.

  • Facet – These are a grouping of properties based on a feature or particular aspect of SQL Server.  Be sure to look through these after you enable PBM to get a better idea of how Microsoft has organized the available properties.  Examples would be properties grouped around server level settings, databases, stored procedures, and triggers.
  • Condition – Conditions define and scope the object types you are looking to evaluate.  They are also used as filters for target servers and restrictions on server types like SQL Server versions.
  • Policy – Policies are containers that hold and describe the facets, conditions, targets, evaluation modes, and server restrictions you have chosen.
  • Target – Targets are the objects that contain the properties you are looking to evaluate.  For example, you may have chosen the database facet, but you can use a condition to define your target as a specific database.
  • Server Restriction – Again you can use a condition to restrict your policy to a particular type of server.  An example would be restricting to only servers running SQL Standard Edition.
  • Category – Categories are logical groupings of policies.  You might have 5 different policies that evaluate things regarding SOX audit compliancy.  You can add all 5 policies to a SOX audit category and then apply the category to the servers that require them.

I will be speaking at SQLSaturday #90 in Oklahoma City on August 27th, 2011.  I will be delivering my presentation on Central Management Server and Policy Based Management.

I’m looking forward to this event since I lived in the western Oklahoma City suburb of Yukon for about 5 years.  I’m hoping to visit some of the old stomping grounds if time permits.  I haven’t seen all that beautiful red clay they call dirt in a really long time!

I’m also looking forward to visiting with all my SQL friends from OKC and Tulsa.  This event should be a good one and a little different.  They only have 3 tracks and 15 sessions, so you can bet they will all be top notch.  The other really cool thing they are doing is to have one of the fathers of SQLSaturday, Steve Jones (Blog|Twitter), deliver a key note.

It is going to be a great event and there are only about 50 spots left so make sure to Get Registered, and stop by my session.  Here is the abstract:

Manage your shop with CMS and Policy Based Management

In this presentation we talk about Central Management Server and how it can help you manage a disperse environment. We will also cover what Policy Based Management is and how you can leverage its power to better manage your environment. With PBM we’ll see what it can and cannot do to help you enforce standards in your enterprise. We will cover and demonstrate PBM for the beginner from creating and evaluating policies to receiving alerts on policy violations.

Policy Based Management gives us centralized management of our SQL Servers.  It allows us to evaluate, configure, and enforce standards across the enterprise.  If you are familiar with Active Directory Group Policy Objects then you will see a direct resemblance between the two technologies.  PBM allows us to create rules for our SQL Servers so that we can ensure a consistent configuration across our enterprise.  You can use it to evaluate these rules and change any settings that do not conform.  In some instances you can even prevent users from making changes that do not conform to your rules.

PBM is only supported on SQL 2008 or above.  It is supported on any edition except Express, Web, and Compact.  It is a common misconception that PBM is supported in Express Edition.  In Express Edition you will see Policy Management under the Management node in SSMS, but if you attempt to enable it, you will receive an error that it is not supported on that edition.

Even though PBM is only supported in SQL 2008 and above, that is only for the management server itself that houses your policies.  However, you can use that PBM instance to evaluate both SQL 2000 and 2005 servers.  PBM stores all of its policies and configuration in the MSDB system database, so make sure you back up that database regularly.

Dallas TechFest was a 2 day event full of great content!  I’m a SQL guy and most of the conferences I go to are SQL centric.  This event, however, had something for everyone.  There was everything including SQL, .Net, SharePoint, Security, Kinect, and even Windows phone.   Overall, everything seemed to go very well.

I delivered two sessions for the event.  I got an email from an event organizer sent to all the speakers asking for feedback to help them improve things in the future.  The things I thought they did best was signage, maps of the venue building, maps of how to get there, and lunch.

What I am going to do here is list the things that other conferences had that Dallas TechFest was missing.  This is not a list of complaints, but a list of possible improvement ideas.  Also keep in mind that Dallas TechFest was a paid event and the other conferences I am comparing it to (like SQLSaturday events) are free.  So we are not comparing apples to apples and of course it is not a competition.  So here is my list of things commonly found at SQLSaturday and other conferences that are possible opportunities for improvement.

  • There was nothing provided for breakfast and no coffee.
  • If you wanted a drink with your lunch you had to walk half way around the building.
  • The rooms were spead out across the building and that did not lend itself well to networking.
  • Lunch was served out of the speaker room which caused confusion, as lunch was only supposed to be picked up from there.   It was to be eaten in one of the session rooms, but everyone thought they could eat in that room and organizers had to keep announcing the rules.
  • There were a lot of complaints about the photographer being intrusive, but I personally did not find 1 of the 2 to be an issue.
  • There were no room monitors to help speakers with setup.
  • There were no surveys for speaker feedback.
  • There were no surveys for event feedback.
  • Volunteers and organizers did not have event shirts so if you needed direction they were hard to find.

Again this was a great event with wonderful speakers and content.  Dallas is not the only community TechFest around so make sure to search the web for one in your area.  I know that there is also a Tulsa TechFest and Houston TechFest.  Unfortunately I won’t be at those this year since they conflict with the SQLPASS Summit.

The absolute best thing about the conference was a special event held on the first night for community and user group leaders.  It was awesome to share ideas with other leaders and talk about the difficult parts of running user groups.  I won’t get into all the goodness, as you can read what Jay Smith already wrote.


Many people already have connections setup in their local SSMS to all the servers in their environment.  We can import those connections into our CMS so we don’t have to manually recreate them all.  We can also export the connections in our CMS to have available for importing to another CMS, another local SSMS, or simply as an additional backup.  Please note that this should be an additional backup, because you should be backing up the MSDB on your CMS server.

Let’s look at an example.  We are going to export three local connections and import them to the East/Dev group in our CMS.  Here is what our local connection options look like, followed by what we see in SSMS.

Alias Server/Instance Authentication Type
Server1 Server1 Windows
Server1/INST01 Server1/INST01 SQL
File2.camelot.com File2 Windows

CMS Layout

We start by right clicking on Local Server Groups and going to Tasks>Export.  The following dialog allows us to choose which local group we want export, where to export it to, and if we want to include passwords.  By default, the option is selected to NOT include passwords, but we are going to uncheck that box so we can see what gets exported from a security perspective.

Export local SSMS Connections

Before we import this into our CMS we want to make sure that no passwords are exported in clear text.  We want to look at this for a connection using Windows Authentication as well as a connection using SQL authentication.  We know from our matrix above that Server1 is using Windows Authentication, so let’s check that out first and see what got exported.


We can see that the connection string is using the trusted_connection=true option as expected, so no password is included or necessary.  Now we know we don’t have to worry about plain text passwords being included with Windows Authenticated connections.  However, we still need to look at a SQL Authenticated connection.

type=”string”>server=Server1INST01;uid=sa; password=AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAWN50rqJbK0KumkZmeSq0rQQAAAACAAAAADZgAAwAAAABAAAACf0muawUxos7e658B76JBJAAAAAASAAACgAAAAEAAAANgj0Wy9O+YOw2tppBqYAAAAplr80XKV/SuFiRHFwGnEQTno6rDFFAAAAP3b2Ll4WMHyOKzOMuCQqekAivqX;pooling=false;packet

Here we can see that the username is displayed in plain text, but the password is encrypted.  If displaying the username is a security or auditing concern for your company then you need to be aware of this.  However, we can rest easier knowing that the password is not exposed in plain text.

NOTE: Remember that CMS does not allow SQL Authenticated connections, so pay attention to what happens when you import it.  You can read more about that in my post on Disadvantages of using CMS.

Switching back into SSMS, we can import these connections to our CMS by right clicking the folder we want to put them in and going to Tasks>Import.  The dialog we receive asks for the file to import and the folder we want them imported to.  The folder to import to will default to the folder you right clicked on in the previous step, but you can change it here if you wish.

CMS Import Wizard

We have now successfully exported our local connections into our CMS.  Here is what our CMS looks like now.

View of CMS after import

There is only one thing left to check.  The connection for Server1/INST01 was using SQL Authentication in our local SSMS and that is not allowed in our CMS.  Here is what happens when you import a connection that uses a SQL Authenticated login.

View of imported SQL Auth connection

You can still see the SQL Login ID that was specified for the connection, but Windows Authentication is now selected and greyed out.  SQL Authenticated logins automatically get switched to Windows Authenticated logins upon import.

T-SQL Wednesday

This month’s T-SQL Tuesday has been moved to Wednesday.  The topic this month is “crap code”.

This is a great topic to showcase what not to do and see some actual bad code.  After all we tend to learn best from our mistakes.  However, I’m not going to show bad code.  I want talk about code that may or may not be bad, but becomes bad by virtue of ignoring outside factors.  What I am talking about is being a well-rounded DBA and making sure you take off the blinders and look at things from a 1000 foot view.  Yeah it’s time to put on the big boy pants!

Let me explain what I mean with a single example that has two repercussions.  Let’s say you’ve written this beautiful piece of T-SQL and followed all the general rules.  You didn’t use functions that cause table scans, you didn’t use a ” SELECT * “, and you have a granular ” WHERE ” clause.  You’re proud of the code and everything seems fine until you run it and it returns results in an unacceptable amount of time.

Why is it slow?  I mean this is a beautiful and fantastic piece of code you wrote!  Well you didn’t think about how the table was indexed and although you had a granular ” WHERE ” clause the table has millions of rows and the column in your clause doesn’t even have an index on it.  Yeah that just happened.  You were so focused on the query you forgot to think about the outside factors, your indexes in this case, that have had a very profound effect on your query.  We have to remember to take a step back and think about the outside factors that can affect our code.  In this case the code was perfect and all you needed was a proper index.

The other repercussion to this example, is to remember that it is not a “set it and forget it” kind of thing.  Let’s say you added the index and the query is now both genius and lightning fast.  That’s great for now, but what about six months or a year down the road?  Maybe another administrator made some indexing changes, or maybe you changed the index to satisfy another query.  Now the query performs badly, so what we need to remember is to periodically check our systems to make sure things have not changed.  We also need to be sure when making changes that they don’t adversely affect other operations on the system.

If you need to setup a Central Management Server or just want to check it out, then here is how to do it.

Open SQL Server Management Studio and connect to the SQL instance you want to host your CMS.  By default SSMS does not show you the Registered Servers pane.  If you do not see a tab for the Registered Servers pane on the bottom left hand pane next to the Object Explorer pane, go up to view and select Registered Servers.  To enable CMS, simply right click on the Central Management Servers node in the hierarchy tree and select Register Central Management Server.  A create connection dialog will open where we can put in the connection details for our CMS.  For our example, we’ll use the server File2 for our CMS.


Your CMS server is now ready to configure with connections.  You will want to first decide how to layout the server group folders according to your business.  Once you have done that, you can right click on your server and select “New Server  Group” to begin setting up your folder structure.  Once you have your structure built out, you can right click the appropriate folder and select “New Server Registration”.  In our example we will right click the appropriate folder and select “New Server Registration”.  Here is what the registration details will look like: