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

TSQL2sday #70 – Strategies for Managing an Enterprise

tsql2sday

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!

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!

SQLSaturday #150 – Baton Rouge: Presentations Uploaded

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

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 14th 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!