Today:
Tuesday, January 13, 2015 is the 62nd entry and first T-SQL Tuesday
of the year 2015, and I am honored to be this month’s host to kick of the New
Year! Last week, I made the
announcement, and invited everyone to the blog party started many quantum ago,
by SQL MVP Adam Machanic (b|t).
Here’s the official invite to Healthy SQL with details and T-SQL Tuesday’s
blogger’s bill of rights.
Please join
us and write about your resolution for a Healthy SQL Server environment, and
what you would do to ensure your SQL Servers are at their peak performance and
are achieving SQL Fitness.
One
way to do this is to perform regular health checks of your SQL Servers, just
like people, to make sure they are up to date with all its optimizations. There are so many ways to maintain a healthy
SQL Server environment that one could write a book on it!
I
am looking forward to all of your blog posts today, which must be published
between 00:00 GMT Tuesday, January 13th, 2015 and 00:00 GMT
Wednesday, January 14th, 2015.
Don’t forget to make sure your post contains the T-SQL Tuesday logo
image on the original invite, and use trackbacks
or post a comment to this post with a URL link to your post. Finally, be a
T-SQL social butterfly and tweet about your post using the #TSQL2sday hashtag.
Where
does one start with their health checks.
In quick summary, start with an inventory of your SQL Servers, create a
checklist of what data you will need to collect, and then begin your collection
process by establishing a baseline.
Performing
regular health checks will enable you to get ahead of potential performance
issues before they arise. When creating and maintaining your baselines, you
will immediately be aware of any deviations from normal operating server and
database behavior.
If you need
a quick start way to setting up continuous performance metric collection, and
looking to create a central repository of performance metadata, with some neat
canned reports all in one, AND your SQL Servers are version 2008 and higher,
then I suggest you explore a gem of a feature called Management Data Warehouse
(MDW), which is available out-of-the-box.
So what exactly is this MDW that
is spoken of? MSDN describes it as, “the management data
warehouse is a relational database that contains the data that is collected
from a server that is a data collection target. This data is used to generate
the reports for the System Data collection sets, and can also be used to create
custom reports. The data collector infrastructure defines the jobs and
maintenance plans that are needed to implement the retention policies defined
by the database administrator.”
In SQL
Server 2008, the key data collection engine for the MDW was introduced, called
the data collector. The data
collector is a component of SQL Server 2008 that collects different sets
of data, and can be set to run continuously or at a user-defined scheduled
interval. The data collector can be used
itself to set up custom collections, and even add them to be integrated with
the MDW. I recommend your familiarize
yourself with the data collector – the data collector is your friend – by first
reading Introducing
the Data Collector, up on TechNet.
MDW allows
you to very easily and rapidly deploy this system to collect, store,
analyze and report on performance stats. MDW is
a great way to baseline the performance of our SQL Servers, trend it
over time, and use it for monitoring and reporting on the state of the health
of the SQL Servers. If you’re a DBA starting out, or an accidental DBA, or even
a SQL Pro, and want to set something up fairly fast, then you would use the MDW
Wizard to step you through the process.
To learn more on how to Configure the
Management Data Warehouse, click on the highlighted link to go to the MSDN article
as entitled.
So,
where is this MDW thingy? Well, if you
have SQL Server Management Studio, then you’re all set to go. Connect to the SQL Server you would like to
use as a central repository of performance metadata, navigate SSMS to the
<Management> folder, drill-down to the <Data Collection> item, and
then right-click. You will see a pop-up
menu, and now select <Configure Management Data Warehouse>, as shown in the screenshot below:
When
you launch the wizard, you will be presented with two task choices, 1. Create
or upgrade a management data warehouse; and 2.Set up data collection. When launching the MDW wizard for the first
time, you will select the first option to create your warehouse, and continue
to follow the prompts until your database is created. The "Select Configuration Task" screen is shown here:
In addition, you'll
need to map logins and users to the proper MDW roles. There are three distinct roles for MDW you
should be aware of:
·
mdw_admin
- highest level of rights on the MDW database. Members of this role has both
read and writer permissions for uploading data, and reports.
·
mdw_writer
- this role allows users to upload the data to the MDW database.
·
mdw_reader
- lowest level of rights, and used for reports.
Once
the central MDW database repository is created, you will need to go and launch
the wizard a second time, choosing the option to “Set up data collection”. For each SQL Server that will participate and
upload performance data to the MDW, you will need to repeat these steps for
setting up data collection. The wizard will
take you through pointing the collector to the MDW database that was created.
There
are so many more things to talk about beyond the scope of today’s T-SQL
Tuesday, regarding MDW, and Health Checks.
Hopefully, this blog post, and the many others that will be posted
today, will give you numerous tips and ideas on how to make sure you have a
healthy SQL Server environment. You are
all on your way to achieving Healthy SQL!
Thanks
for reading, thanks in advance for posting, and thank you for the opportunity
to host this month’s T-SQL Tuesday #62!
I will be watching and reading your great posts, and resolve to have a
T-SQL Tuesday round-up real soon! Stay healthy! You can follow me on twitter: @Pearlknows
Y
