SQLSaturday #150 – Baton Rouge: Presentations Uploaded

My presentation slide decks and demo scripts from SQLSaturday #150 have been uploaded.

• Managing SQL Server in the Enterprise with TLAs
• SQL Server 2012 Database Engine – Why Upgrade?

Thanks to the planning team for selecting my sessions and thanks to everyone who attended my sessions – I enjoyed the opportunity to share my passion.   

SQLSaturday #150 – Baton Rouge – Signup Now!

There’s an awesome FREE technical training event coming to Baton Rouge on August 4, 2012. That’s right; SQLSaturday and Tech Day 2012 will be held at LSU’s new College of Business facility.  This is the fourth year that the Baton Rouge technical community has held this event and they expect around 400 people – if you live anywhere close by, then you should be there!  William Assaf (blog | twitter) even got some local TV exposure for the event this year.  

This event is bigger than your normal SQLSaturday. In addition to tracks for the SQL Server professional, there are also tracks for .NET developers, Windows Phone developers, SharePoint, and general professional development. Check out the full schedule here, and then sign up here.

Why am I plugging this event? Well, for one thing the Baton Rouge SQL Server community has always come west across the state line to support our SQLSaturdays in Houston. Secondly, I’ll be speaking at their event this year on “Managing SQL Server in the Enteprise with TLAs”.  TLA is “Three-Letter Acronym” for those unsure. We have lots of those in techno-speak. I’ll be covering CMS, PBM, EPM, MDW, and more…. If you work with SQL Server and don’t know what those are or how they can help you, then register today for SQLSaturday #150 and come to my session at 8:20am in Room 1700! 

Addendum: I’ll now also be presenting a second session “SQL Server 2012 Database Engine – Why Upgrade?” in the 2:45pm slot in Room 1700.

 

If you can’t attend this event, then check here for all the currently scheduled SQLSaturdays in the US and around the world! 

 

TSQL2sday #026 – Second Chances

What is TSQL2sday? Back in late 2009, Adam Machanic (blog | twitter) had this brilliant idea for a monthly SQL Server blogger event (the origin of TSQL2sday).  This month’s event is hosted by David Howard (blog | twitter) and this month Dave is letting us chose our topic from any of the prior 25 topics! As my first foray into this event wasn’t until the 14^th occurrence, I really like this idea and selected “TSQL2sday #007 Summertime in the SQL” as my second chance topic. Okay, so it is January, but it was 70+ degrees in Houston today, so quite balmy. However, that wasn’t why I chose this topic; I really chose it because this topic was about what is your favorite “hot” feature in SQL Server 2008 or R2. I thought about “updating” the topic to SQL Server 2012, but I’m really not sure yet which new “hot” feature of SQL Server 2012 will turn out to be my favorite – and after 3 years, I definitely know which SQL Server 2008 set of features is my personal favorite – CMS and PBM.

The Central Management Server (CMS) and Policy-Based Management (PBM) features have made the overall management of large numbers of SQL Server instances, well, manageable.

The CMS enables us to organize instances into multiple different classifications based on version, location, etc. We rebuild the CMS on a regular schedule based on the data in our asset management system. This ensures that all DBAs have access to a CMS with all known instances. If you are not familiar with the CMS – it does not grant any access to the instances themselves and connectivity using it only works with Windows Authentication, so there are no security loopholes here.

We then use these CMS groups as input into our various meta-data and compliance collection processes. Approximately 90% of our technical baseline compliance evaluation is accomplished via policies in PBM. We’ve incorporated all of this using the EPM (Enterprise Policy Management) Framework available on Codeplex with a few tweaks of our own to work better in our environment.

If you haven’t yet checked out the CMS and PBM features, I encourage you to do so today. I have two previous blog entries relating to this topic – “Managing the Enterprise with CMS” and “Taking Advantage of the CMS to Collect Configuration Data”.  I’d also highly recommend that you watch the SQL Server MCM Readiness Videos on the Multi-Server Management and PBM topics.

And, it is good to know that by the time this entry is posted – we should be back to our normal 50 degree January weather in Houston!  

SQL Server 2012 RC0 – SSMS Review

Microsoft released SQL Server 2012 RC0 (Release Candidate) this week – just in time for those of us traveling over the holidays to have something to do as we head to Grand-Ma’s (assuming we aren’t the one driving).  With any new release, I suspect most of us do the same thing – run as fast as we can through the install process and open SSMS and start playing!  If you haven’t been previously playing with “Denali” CTP3, then I encourage you to get the RC0 install (on a test server, of course) – if only for the new SSMS.

The first time you connect to an instance in Object Explorer, you’ll immediately notice differences from SSMS 2008 R2.

Oh look! There’s a folder called “AlwaysOn High Availability” – let’s go click it!

Yikes! That wasn’t nice. It seems like SSMS should already know this and have a “not available” icon showing like it does for my SQL Server Agent which is currently disabled.

OK – let’s try the other new folder “Integration Services Catalog”.  That’s interesting because I do have SSIS 2008 installed, but haven’t upgraded it. The only option here when I right-click is to “Create Catalog”. However, then I see that I need CLR enabled and once I do that then I need to make some other decisions.

Ok – I guess this will be something I need to do some more research on to know when we’ll want to use this and exactly how to use it. But, that is the whole purpose of this exercise, seeing what is new and where to focus future in-depth investigation.

So, back to drilling down through Object Explorer to see what else looks new…

Looks like I’m going to have to study up on Extended Events now that they’ve made it into SSMS; and Microsoft appears to be setting up some default entries here.

Now, I’ll start drilling down to the next layer of some of these objects. We use PBM (Policy-Based Management), so let’s see if anything is new there. Yep! Looks like Microsoft is installing policies by default in preparation for AlwaysOn usage.  So, when I get around to playing with AlwaysOn, it will be interesting to revisit these and see if they are automatically enabled when setting up AlwaysOn.

I didn’t show it in the image, but there are new Conditions created upon install for the AlwaysOn policies to use. And, obviously with new features in SQL Server 2012, new facets related to those features have also been added (76 facets in SQL Server 2008 R2 versus 84 in SQL Server 2012). Interestingly, if I connect to a SQL Server 2008 or SQL Server 2008 R2 instance from SSMS 2012, I see all the facets for SQL Server 2012. I think this means I’ll be able to keep using my central PBM repository from SQL Server 2008 R2, but run policies specific for SQL Server 2012 (using our version of EPM). More to investigate!

Lastly for this initial drive-by look, I checked out the Management\Legacy folder. Here’s the comparison of SQL Server 2008 R2 SSMS (first) and SQL Server 2012 (second):

SSMS 2008 R2 Legacy Objects

SSMS 2012 Legacy Objects

Looks like DTS and SQL Mail are finally gone as promised! 

Until next time, Happy Exploring!

What’s in Your msdb?

So, what’s in your wallet msdb?

If you’ve been working with Microsoft SQL Server anytime at all you can name the system databases fairly quickly – master, model, msdb, and tempdb. Almost rolls off your tongue as smoothly as Tenaha, Timpson, Bobo, and Blair (if you don’t know that reference, click here for some interesting Texas folklore). I’ve been working with SQL Server since 4.21 and the system db names have not changed from release to release. But, what did change drastically over all those releases are the contents – especially for msdb. Have you taken a look lately? I did and here is what I found.

Lots of new tables, lots of new views, and lots of new roles – well, just lots of new objects period! I always investigate what’s changed in master with every release, but it seems I’ve been delinquent in thoroughly examining msdb changes. It appears every new feature being added to the SQL Server database engine is being “managed” from the msdb database.

The objects supporting SQLAgent have always been in msdb – jobs, operators, alerts; along with the objects supporting database backup and recovery sets. Then DTS came along which later morphed into SSIS – guess where those objects are stored?  Maintenance plans, log shipping, and database mirroring also join the party in msdb. And, oh yeah – SQLMail which became DatabaseMail…

Now with SQL 2008 and R2, you can add to the list – CMS (Central Management Server), Data Collector, PBM (Policy Based Management), DAC (Data Tier Applications), UCP (Utility Control Point)… but “Wait!” you say. “Don’t the Data Collector and UCP have their own databases?”  Well, yes, they do – typically MDW and UMDW respectively. But, those databases only hold the collection results – the management and configuration of those features is all contained in msdb.

Here’s what the SQL Server 2008 R2 BOL says about msdb:

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.

After seeing this, I had to laugh, I totally missed Service Broker in my list, but then I’ve never implemented it. On the other hand, look at all the features using msdb that BOL just lumped into “other features”!  Bottom line – the BOL description really sells msdb short on its importance to so many features.  AND, the default recovery mode for msdb is Simple. Before SQL 2008 I would have said that a daily full backup was typically sufficient. You didn’t usually make too many changes to jobs such that you couldn’t easily recreate a day’s work if you lost it restoring to the prior day. And, best practice said to take a backup after making modifications which impacted it anyway… but now, with so many features tied to msdb – do you really know when you are making changes to msdb? Considering all that is now dependent upon msdb – is your backup and restore strategy for msdb adequate?

However, before I go examine my msdb recovery strategy, I’m thinking of opening a Connect suggestion – rename msdb to KitchenSinkDB. Do you think I could get some votes for that?

Managing the Enterprise with CMS

In Microsoft SQL Server 2008, a feature called Central Management Server (CMS) was added to enable the execution of T-SQL commands and Policy Based Management policy evaluation on multiple servers at one time.  As someone working in a large enterprise environment, I’m constantly looking for features which enable better, more efficient ways to manage hundreds of servers within a team support environment.  While the minimum version requirement to perform as a CMS is SQL 2008, you can register lower level version instances.  Now we are talking – because how frustrating is it to have a new feature available, but virtually useless until you have a majority of systems on the new version.  So, now you need only one SQL Server 2008 instance and you can start taking advantage of this new feature to manage the current SQL 2000 and SQL 2005 instances already in place.

However, if you read BOL (Books Online), then you’ll see that the instructions for adding groups and instances are very manual and done via the SQL 2008 SSMS (SQL Server Management Studio).  Did I mention that we have hundreds of instances to manage?  And, did you catch the reference to “groups”?  It doesn’t say explicitly in BOL (at least that I’ve found), but an instance can belong to more than one group.  This opens up a whole plethora of possibilities for using the CMS.  But first we must determine how to automatically create the groups and populate them and maintain them.

A little further investigation reveals that there are now a couple of tables in the msdb database named dbo.sysmanagement_shared_server_groups and dbo.sysmanagement_shared_registered_servers which are designed to contain the groups and registered servers for the CMS.  So, now I can bypass the manual registration in SSMS and write a PowerShell script (what else?) to create the desired groups and populate them from our central asset database based on whatever criteria I want the group based on.  Then I can schedule that script as a job to run in SQLAgent to ensure the CMS groups are refreshed and relatively in sync with the asset database (considered “the truth”).

The msdb database in SQL 2008 also contains two new roles: ServerGroupAdministratorRole and ServerGroupReaderRole.  If you want someone other than the Windows Authenticated logins already in the sysadmin role on the CMS instance to manage your CMS, then you can assign their Windows login to the ServerGroupAdministratorRole.  Similarly, if you want others to use the registered groups and servers in this CMS, then their Windows login must be assigned to the ServerGroupReaderRole on the CMS instance.

BOL (2008) states that the CMS Server cannot be a member of a group that it maintains, I haven’t figured out why yet. The work around is to register the CMS server name as servername\instancename, but register it as servername\instancename,port in its CMS Group, this seems to work just fine, but be aware of this caveat.

SQL Server 2008 R2 did not provide any changes to the CMS feature, so you should be able to use either SQL 2008 or SQL 2008 R2 as your CMS.  I’ve had no problems using SSMS 2008 with a CMS based in SQL 2008 R2 or using SSMS 2008 R2 with a CMS based in SQL 2008.

The first key to using CMS is to determine what groups make the most sense for your organization. Depending upon what data is available in your asset database, the choices are almost limitless.  For instance, you might chose groups based on region alone or based on region plus version for another combination.  Thus, you might have groups which look similar to AM, AP, EU, AM_2000, AM_2005, AM_2008, SQL2000, SQL2005, SQL2008, etc.  You could also have groups based on classifications such as DEV, QA, and PROD; or even based on application landscapes such as Biztalk and Sharepoint.

Some things to consider when setting up your CMS include connectivity and security.  The CMS is not magic – although it might seem like it the first time you perform a multiple-instance query!  The CMS does not store authentication credentials, only the server connection info.  Thus, when using SSMS to execute T-SQL or policies against a group, Windows Authentication is used.  The authenticated account will have no different privileges than if you had connected to each instance in an individual query window using Windows Authentication.  You’ll actually see in the status bar of the Query Window in SSMS how many instances in the group were successfully connected (e.g. 9 of 10 or 10 of 10).  Therefore, even if you connect successfully to every instance in the group, if your login has differing privileges on the instances, then your results may differ as well depending upon the privileges required to execute the query submitted.  So far, this works exactly like Registered Servers in your individual SSMS, however, the benefit is the ability to access the CMS groups and registered servers from anywhere in your organization, by anyone (with the proper access of course!).  No longer do you have to keep registering your servers – and your whole team now has access to the same groups and registered servers.

On the connectivity side, if a CMS group contains hundreds of instances, especially if regionally dispersed, it may take a while to establish the connection to all of the instances, so be patient – or consider further dividing your groups into smaller subsets.  If an instance is offline or there is a connection timeout or you do not have permissions, then an error\warning will be reported for that instance when you try to execute a query in SSMS against the group; results will still be returned for all other instances.

Also, be very, very, very careful when executing anything other than a SELECT statement – always check the status bar in the Query Editor window to validate the number or instances to which you are connected, which group you are executing against, who you are executing as and to which database you are connected by default.

So for a very simple example of what it can help you do, consider that you have version stored in your asset database and use that to build version groups, but you aren’t 100% sure that these are correct.  In SSMS, you can right-click the version group in the Registered Servers in SSMS and select “Run Query”.  Then, you can execute the query “Select @@version” or “Select SERVERPROPERTY(‘ProductVersion’)”.  You can quickly scan the results for any instances which do not return the expected version.

Hopefully, this provides a good introduction to CMS and has started you thinking about how you can use it outside of SSMS…which will be the subject of my next post!