PearlKnows WeB log, Stardate 2015.9.8: This week’s topic is strategies for

managing an enterprise, brought to us by one half of the Midnight DBA train,

SQL MVP Jen McCown.  This is a great topic, and

you don’t have to be a Captain Kirk, although he did pretty good job of

managing his Enterprise (except maybe when he ordered it's destruction.) Ok, unless your server room or hosting space is being invaded by Klingons, you don't want to do anything like that.  And of course, always have a backup plan. Here is the original

invite to the T-SQL Tuesday #70 blog block party, originally started by Adam Machanic (b / t), is hosted this month by Jen.

If you are a

DBA managing multiple SQL Servers, you will need a strategy on how to manage

them, and that is why I wrote the book Healthy SQL. Some have called it a guide to the DBA

galaxy, but in content and form, it is a comprehensive guide to healthy SQL

Server performance.  The book actually

shows the person how to perform a SQL Server health check, tools, tips and

links to help better organize the DBA’s day. The entire book is dedicated to a

strategy to ensure that your entire SQL Server infrastructure is certifiably

healthy, and is definitely an enterprising endeavor. Such a strategy to

managing the enterprise is laid out in the the table of contents, you can

surmise that in fact, each chapter is dedicated to an area and various aspects

of what a DBA must do to successfully manage a SQL Server environment.  Automation, of mundane, manual DBA routines is the key to successfully managing a SQL Server enterprise.

So, one

strategy to employ in managing your SQL Server enterprise, is to know where all

the SQL Server are, and all their properties, by taking an inventory. Here is one of my previous blogs on Getting SQL Server Info, with T-SQL script to get you that top-level info about your SQL Server instance(s).  Perhaps you can set up a Central Management Server and then execute the Get SQL Server Info script against multiple instance targets, and insert the data into an inventory table!  You designate one instance to manage all the others and serve as the CMS. You can create a CMS in SSMS by selecting View ? Registered Servers ? Central Management Servers. Right-click and select Register Central Management Server to register an instance of the CMS. Now you got yourself a master inventory list. 

You can use your inventory list for Microsoft licensing true-ups, which are similar to audits, to reconcile the number of purchased SQL Server licenses vs. the number of running and deployed SQL Server engines. After you collect the data, you can store it, parse it, query it, and run reports on it. You also can add some of the
other properties and modify the script as needed.  Once you have all the SQL Servers identified in the enterprise, you can set up as much automation to your heart desires.  You can also collect critical performance data for all your servers, to create a performance baseline to use in measurement and comparison to all future data points. To keep track of all collected info in one place, you can create a centralized data health repository.  The quickest way to experiment and do this out of the box, is to set up a Management Data Warehouse (MDW), which uses the data collector, that I discussed on my blog Achieving Healthy SQL with MDW. 

Of course, once you have some good data samples stored in your MDW, you can easily report on the state of your SQL Servers.  And you can easily access these by navigating SSMS to your MDW repository database, Right-Click ? Reports ?Management Data Warehouse ?Management Data Warehouse Overview.  You can also create your own custom reports.  Much of this with step-by-step walk throughs and demos are in my book.

Another aspect of enterprise strategies for automation, is automated monitoring. There are many software vendors out there offering SQL Monitoring Solutions, and you should definitely be running at least one of them. Proactive, continuous monitoring and alerting are essential to managing multiple SQL Server instances. By
setting up automated monitoring, you ensure that you are being proactive and not reactive to a potential technical issue or outage before escalation. With targeted monitoring, you can get in front of and resolve a situation before it becomes known to the general user population and management. There are a number of
out-of-the-box solutions, as well as established third-party vendors. You should also look into SQL Server Agent Alerts, Operators, and Notifications in conjunction with Database Mail, for automated alerting.  You will want to specifically set up the SQL Server Agent alerts for severity 17 through 25, which will capture the most critical errors, including insufficient resources and fatal errors. In addition, you will want to set up alerts for specific errors 823, 824, and 825. These errors relate to database corruption and potential I/O subsystem errors that threaten database integrity. Early detection is key to avoiding corrupt databases and consistency errors. SQL MVP Tim Radney, gives a primer on setting up SQL Server Agent alerts here. The topic of monitoring and setting up SQL agent alerts and operators is covered extensively in Chapter 8 Monitoring & Reporting in Healthy SQL.

So, the above are some simple strategies you should consider and use if you are managing a SQL Server environment, and by using automation to help implement these strategies, will put you ahead of the game, as Captain of your MSSQL Enterprise!

Here are the T-SQL Server Rules of Engagement:

1. Write

   a blog post about the topic. Don’t have a blog? Start one!
   

2. Include

   the T-SQL Tuesday Logo and link it back to the original invitation post.

3. Publish

   your blog post Tuesday, September 8th, 2015, between 00:00 GMT & 23:59 GMT.

4. Leave

   a reply on the original invite with a link to your blog post (for the

   round-up).

5. Share

   you post with the community! Tweet it out using the #tsql2sday hashtag

 And of

course, if anyone is interested in learning more about my book Healthy SQL – A

Comprehensive Guide to Healthy SQL Server Performance, published by Apress, you can go to the url:

                     http://bit.ly/orderHealthySQLnow

  You can also

get the book on Amazon: http://bit.ly/HealthySQLonAmazon

  For all

things SQL, news, events, jobs, info, and other fun tweets, follow me on

twitter @Pearlknows and join the #HealthySQL campaign to

keep your SQL Servers healthy!