SQLServer

The Froogle DBA – Management Data Warehouse Part 1

moderndba SQLServer ,

Originally Posted 8/26/2014

So far we have discussed two of the three core components of the Froogle DBA’s toolbox (Central Management Servers and Extended Properties).  With these two features we can manage systems at scale by improving our ability to apply scripts to all or a portion of our environment, make our environment more self-documenting, and improve communication between the members of our DBA team.  In my last post I showed you how you can populate folders in CMS using the Extended Properties you have defined on your databases using PowerShell.  There is an issue with this approach however.  It makes it more difficult (not impossible) to answer the question “Which databases don’t have Extended Properties set?”.  This is a limitation that the Management Data Warehouse can help us overcome.

The Management Data Warehouse (MDW) is a database that collects data sent by data collectors running on the same, or different, instance(s) of SQL Server.  That is to say that it is really just like any other database, except this database is populated by Data Collectors configured in Instances of SQL Server to gather information of interest.  Out of the box, SQL Server provides Data Collectors that can be configured to collect information of interest in troubleshooting performance problems in SQL Server.  It also ships with some reports designed to run inside of SQL Server Management Studio to analyze this data.

Setting up Management Data Warehouse involves two steps.  First, we need to create the Management Data Warehouse database on the server we want to consolidate data in our environment on.  For the Froogle DBA we will use the same instance of SQL Server we use to host Central Management Server to host the Management Data Warehouse database.  We will do this to facilitate some of the other integrations that make the Froogle DBA framework easier to support and maintain.  Secondly, on each of the instances you want to gather data on, you will need to configure Data Collection to report to the Management Data Warehouse database.

In order to configure the Management Data Warehouse database you will need to follow the images below.

Now that we have a place to put data we collect, we need to tell SQL Server to collect data we can later use.  In order to do this we need to configure Data Collection.  The Data Collection is going to be run as jobs on the instances of SQL Server that are collecting.  As such you need to be aware of the account that the SQL Server Agent is running on each of the machines and ensure the accounts have the appropriate permissions in the MDW database, or you can configure an account for Data Collection to run as and use proxies to run Data Collection as that account.  Some data collection relies on caching data in a folder in the file system.  Go ahead and create a folder for this purpose (i.e., C:\MDWCache) and then make sure that the account that is going to perform Data Collection has full control on the folder.  In order to configure collection follow the steps in the following images.

At this point you have Management Data Warehouse up and running and the default collectors collecting data.  Don’t forget to configure Data Collection following the second set of images on all the instances you wish to manage.  We will show how to use the data collected and some custom collectors important to the Froogle DBA later.

Gathering Extended Properties into your Management Data Warehouse database

moderndba SQLServer , ,

Originally Posted 8/27/2014

In the first post on the Management Data Warehouse we looked at how to set it up and start collecting default data using Data Collection.  In this post, we will see how to create a custom collector to gather all Extended Properties at the database level on our Instances configured for Data Collection.   This shows how easy it is to create a custom collection set and collect information about our SQL Server instances.

Before we dive into the process, lets discuss a little more about what we are trying to accomplish.  If you have been following along with the Froogle DBA posts, then you know that I recommend you document your database inside the databases themselves using Extended Properties.  By doing so, you can use Central Management Servers to report on your environment.  But sometimes it is just easier and faster to query this information from a single source.  Since we are already leveraging Management Data Warehouse why don’t we use it to consolidate this information from across our environment.  We are going to gather all Extended Properties set on databases, once a day and store them in the Management Data Warehouse database.  We are going to decide we are not going to keep a lot of history on this information, so we will just keep one weeks worth of data.

With this information let’s start working on building our collector.  To start with we need to understand that the way data collection works is that a collector is going to run on each of our instances and report back to the Management Data Warehouse.  This is a distributed collection model and means we will eventually need to “install” this collector on each of our instances.  The implication of this is we will only need to worry about writing a collector to gather information for one instance and then deploy that collector to all of our instances.  We can also further simplify the process of creating the collector by using the data collection’s ability to run a script against all user database on an instance.  With this we only need to write a query to gather the database level Extended Properties for a given database.  The query we will use is shown below.


WITH NULLRECORD AS (
 SELECT 0 AS class
)
 SELECT
sys.extended_properties.name
, sys.extended_properties.value
 FROM NULLRECORD
 LEFT JOIN sys.extended_properties ON NULLRECORD.class = sys.extended_properties.class;
The reason we perform the left join is to ensure that we get at least one row per database.  The default collectors that come with SQL Server are gathering data at the instance level and not the database level.  We could use the query below, but we would not be able to answer the question in MDW of which databases are missing Extended Properties.

SELECT
 sys.extended_properties.name
 , sys.extended_properties.value
FROM sys.extended_properties
WHERE sys.extended_properties.class = 0;

So now that we have our query let’s dive into the process of creating a Data Collection Set.  A Data Collection Set is made up of a Collection Schedule, and a series of Collection Items.  We will have just the one collection item which will gather the Extended Properties.  If we had other things we would like to collect once a day it is best practice to add them as additional items to a single Collection Set vs. having individual Collection Sets for each.  This greatly improves the scalability of Management Data Warehouse.  So let’s address each of these one at a time.  We’ll tackle them in reverse order starting with the Collection Set.

How we build the Collection Set is determined by the Data Collector Type we wish to use.  There are four Data Collector Types provided by SQL Server; Generic T-SQL Query, Generic SQL Trace, Performance Counters, and Query
Activity.  Since the information we wish to gather is based on a T-SQL query we will be using the Generic T-SQL Query Collector Type.  Each collector type has a different set of information it needs which is provided in an XML segment to the Data Collection engine.

In this case we need to tell it the Query to run, the Table to load the data into, and what database(s) to run the query
against.  You can look at the Generic T-SQL Query Collector Type link for more specifics, but the XML segment we want to use is as follows.

<ns:TSQLQueryCollector xmlns:ns=”DataCollectorType”>
<Query>
<Value>
WITH NULLRECORD AS (
SELECT 0 AS class
)
SELECT
sys.extended_properties.name
, sys.extended_properties.value
FROM NULLRECORD
LEFT JOIN sys.extended_properties ON NULLRECORD.class = sys.extended_properties.class
</Value>
<OutputTable>ExtendedProperties</OutputTable>
</Query>
<Databases
UseUserDatabases=”true”/>
</ns:TSQLQueryCollector>

This segment says to run the query against all user databases (<Databases UseUserDatabases=”true” />) and store the results in a table in the custom_snapshots schema in the Management Data Warehouse called “ExtendedProperties” (<OutputTable>ExtendedProperties</OutputTable>).  We will need to wrap this XML in a stored procedure call later, but before we put this together we need to address the issue of scheduling.

SQL Server ships with 6 schedules for Data Collection Sets.  They are all designed to run multiple times a day.  There are collection schedules available for collecting at 5, 10, 15 and 30 minutes.  There are also collection schedules available to collect at 1 and 6 hours.  We could just use the 6 hour collection and collect multiple times a day.  But since we don’t have a business need to do so we will take advantage of the fact that we can create our own schedules. All of the schedules created for data collection all start with “CollectorSchedule_”.  To avoid confusion (and the chance that SQL Server adds a daily collection schedule in the future) we will avoid using that convention.  We’ll use “Collector_Schedule_” instead.

In order to create a new schedule we need to use the sp_add_schedule stored procedure in the msdb databases.  If you are interested in all the options available you can refer to the information in BOL.  But for our purposes to create a schedule to run daily at 01:00 we would use the following script.  We will intentionally not include the time of day in the name of the schedule, because as our environment grows (or if you have a large environment already) we may need to stagger the times that instances report into Management Data Warehouse to improve performance.

USE msdb;

IF NOT EXISTS(
 SELECT schedule_uid
 FROM [msdb].[dbo].[sysschedules]
 WHERE name = N’Collector_Schedule_Daily’
)
 EXEC sp_add_schedule @schedule_name =  ‘Collector_Schedule_Daily’,
 @enabled = 1
 , @freq_type = 4
 , @freq_interval = 1
 , @freq_subday_type = 1
 , @freq_recurrence_factor = 1
 , @active_start_time = 010000
 , @owner_login_name = ‘sa’;

So now that we have the xml segment for the collector and the schedule, lets put it all together into a single script which will create the schedule if needed, drop the collection set if it exists, create the collection set, and then finally start up the collection set.  This is the script that we can use with Central Management Server to get all of our Instances collecting Extended Properties and reporting them to the Management Data Warehouse.

USE msdb;

DECLARE @collectorname VARCHAR(128);
SET @collectorname = ‘Database Extended Properties’;

IF NOT EXISTS(
 SELECT schedule_uid
 FROM [msdb].[dbo].[sysschedules]
 WHERE name = N’Collector_Schedule_Daily’
)
 EXEC sp_add_schedule @schedule_name =  N’Collector_Schedule_Daily’,
 @enabled = 1
 , @freq_type = 4
 , @freq_interval = 1
 , @freq_subday_type = 1
 , @freq_recurrence_factor = 1
 , @active_start_time = 010000
 , @owner_login_name = ‘sa’;

IF EXISTS(
 SELECT name
 FROM [syscollector_collection_items_internal]
 WHERE name = @collectorname

)
EXEC sp_syscollector_delete_collection_set @name = @collectorname;

DECLARE @collector_type UNIQUEIDENTIFIER;
SELECT @collector_type = collector_type_uid
FROM [msdb].[dbo].[syscollector_collector_types]
WHERE name = N’Generic T-SQL Query Collector Type’;

DECLARE @schedule UNIQUEIDENTIFIER;
SELECT @schedule = schedule_uid
FROM [msdb].[dbo].[sysschedules]
WHERE name = N’Collector_Schedule_Daily’;

DECLARE @collectionsetid INT;
DECLARE @collectionsetuid UNIQUEIDENTIFIER;
EXEC [msdb].[dbo].[sp_syscollector_create_collection_set]
@name=@collectorname,
@collection_mode=1, — Non-cached mode
@description=N’Records Database Extended Properties’,
@days_until_expiration=7,  — 1 week
@schedule_uid=@schedule,
@collection_set_id=@collectionsetid OUTPUT,
@collection_set_uid=@collectionsetuid OUTPUT;

DECLARE @parameters XML;
SET @parameters=‘<ns:TSQLQueryCollector xmlns:ns=”DataCollectorType”>
<Query>
<Value>
WITH NULLRECORD AS (
SELECT 0 AS class
)
SELECT
sys.extended_properties.name
, sys.extended_properties.value
FROM NULLRECORD
LEFT JOIN sys.extended_properties ON NULLRECORD.class = sys.extended_properties.class
</Value>
<OutputTable>ExtendedProperties</OutputTable>
</Query>
<Databases
UseUserDatabases=”true”/>
</ns:TSQLQueryCollector> ‘;

DECLARE @collectionitem INT;
EXEC [msdb].[dbo].[sp_syscollector_create_collection_item]
@name=@collectorname,
@parameters=@parameters,
@collection_item_id=@collectionitem OUTPUT,
@collection_set_id=@collectionsetid,
@collector_type_uid=@collector_type;

EXEC sp_syscollector_start_collection_set @name = @collectorname;

So now we have a collector we can use to collect Database Extended Properties for all our user databases.  Once we deploy this to all of our SQL Server instances we will have a central repository of this information we can use.  There are many other collectors we could write to gather information on our environment.  We will save those for another time.

I hope you have been enjoying the Froogle DBA series.  I would love to hear any feedback you might have.

Managing the 3rd Party sysadmin curse in Microsoft SQL Server

moderndba SQLServer

Originally Posted 8/28/2014

An issue that has always come up with 3rd party applications that run on top of SQL Server is their insatiable desire to have sysadmin role access to the instance they run on.  It is an issue I have dealt with for as long as I can remember.  Historically, the documentation would ask for the access, but when it came down to it we always found a way that they could get by with lesser permissions (i.e., db_owner and maybe throw in some permissions in msdb to manage jobs).  But lately, we have been seeing more and more applications that are building sysadmin role checks into their installers and not moving forward without it.  Microsoft even requires it for System Center Operations Manager and other products.

My team is a cautious bunch, and we try our best to use shared instances as much as we can to keep down costs (we run a density of about 25 databases per instance).  Which means we are spending more time these days tracing installs and negotiating with vendors on permissions on these 3rd party applications.

For those of you who came to this page looking for the silver bullet, I am sorry that is not the purpose of this post.  Instead, I am interested to here what you all have tried to manage this issue.  I but together a quick light hearted survey to try and see how most people are handling this issue.  I am also interested to hear if anyone has found a great way to deal with this issue.  The survey is hosted by SurveyMonkey.

The Froogle DBA – Building CMS based on Extended Properties

moderndba SQLServer , ,

Originally Posted 8/24/2014

Before I dive into the next component in the Froogle DBA’s toolbox, I wanted to give you all an idea of what’s to come.  In the original Froogle DBA plan on which my SQL Saturday presentation was built there are three component involved in automatically documenting the managed environment in Central Management Servers (CMS).  I already went over Extended Properties and showed you how to add them to a database.  I spoke a little about the advantage of using Extended Properties to document your environment.  I even talked about using CMS to query the Extended Properties to answer some common questions.

While I was procrastinating from working on my presentation it dawned on me that not everyone will want to run the other piece of the Froogle DBA trinity, Management Data Warehouse (MDW).  A lot of people will already have monitoring tools in place, and MDW is probably the hardest piece to scale and manage.  So I thought, how could people take advantage of tighter integration between CMS and Extended Properties without having to run MDW.  So I spent some time today an through together a PowerShell script that can be used to populate groups in CMS based on Extended Properties you have defined on your databases.

The idea is that you will have a group inside of CMS that lists all the Instances of SQL Server you manage.  This script will query all the Extended Properties defined at the database level on these instances.  It will then look for those that start with a specific Prefix (the default is “CMS “).  It will then build a group for each of these properties under the root of the CMS, removing the prefix,  So, for example the Extended Property “CMS Application” would create a group in CMS called “Application”.  It will then build a sub-group for each unique value defined for that Extended Property.  Then lastly it will register all the instances of SQL Server that had that value under the sub-group.  For example, if you have a database on Instance1 and another on Instance2 with the Extended Property “CMS Application” set with the value of “Application 1” you would see the results in the following picture.

2161216_orig

Now I need to fully disclose that this is a script that I have not run in production.  So, please verify this in a test environment before turning it loose on production.  And, I would be very interested on any feedback you might have on the script.  Did you find it useful?  Did you need to modify it to fit in your environment?  Have ideas for enhancements?

Well, I guess it’s time to reveal the script.

#=========================================================================
# Filename:     PopulateCMSFolders.ps1
# Created on:   20140824
# Created by:   John Greeson
#
# Description:  This script will query all the databases belonging to
#               instances in the "Instance List" group and look for 
#               Extended Properties starting with "CMS ".  It will then 
#               build supporting folders in CMS based on those variables if
#               they are missing.  It will empty all the folders it found
#               and then repopulate them with the list of subfolders and 
#               instances if found in the query of Extended Properties.
#
# Requirements: Inorder for this script to run the CMS Server needs to be
#               registered in SSMS by the user running the script on the 
#               machine it will run on.  This means if you want to automate 
#               the task you will need to determine what windows account
#               the automated task will run as, log onto the machine it 
#               will run as as that user, and register the CMS Server in 
#               SQL Server Management Studio.
#
#  The MIT License (MIT)
#
#  Copyright (c) 2014 John Greeson
#
#  Permission is hereby granted, free of charge, to any person obtaining a copy
#  of this software and associated documentation files (the "Software"), to deal
#  in the Software without restriction, including without limitation the rights
#  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
#  copies of the Software, and to permit persons to whom the Software is
#  furnished to do so, subject to the following conditions:
#
#  The above copyright notice and this permission notice shall be included in
#  all copies or substantial portions of the Software.
#
#  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
#  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
#  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
#  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
#  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
#  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
#  THE SOFTWARE. 
#
#=========================================================================


#=========================================================================
#                          VARIABLES TO CHANGE
#=========================================================================

# Central Management Server
$CMSInstance = "SQLMON";

# Master List of Instances
$MasterInstanceList = "Instance List";

# Extended Property Prefix
$Prefix = "CMS ";

#=========================================================================
#                                THE MAGIC
#=========================================================================

Import-Module SQLPS -DisableNameChecking;

# Create data table to store Extended Properties
$table = New-Object system.Data.DataTable “ExtendedProperties”
;
# Add Columns to data table
$col1 = New-Object system.Data.DataColumn Instance,([string]);
$col2 = New-Object system.Data.DataColumn Connection,([string]);
$col3 = New-Object system.Data.DataColumn Database,([string]);
$col4 = New-Object system.Data.DataColumn Name,([string]);
$col5 = New-Object system.Data.DataColumn Value,([string]);

$table.Columns.Add($col1);
$table.Columns.Add($col2);
$table.Columns.Add($col3);
$table.Columns.Add($col4);
$table.Columns.Add($col5);

# Set path to the CMS list containing the master list of Instances
$pathToListOfInstances = "SQLSERVER:\SQLRegistration\Central Management Server Group\" + $CMSInstance + "\" + $MasterInstanceList;

# Get list of instances
$instanceList = get-childitem $pathToListOfInstances -recurse;

# Gather All Extended Properties
foreach($instance in $instanceList)
{
    $instanceName = $instance.Name;
    $instanceConnection = $instance.ServerName;

    $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instanceConnection;

    foreach($database in $server.Databases)
    {
        foreach($property in $database.ExtendedProperties)
        {
            $row = $table.NewRow();
            $row.Instance = $instanceName;
            $row.Connection = $instanceConnection;
            $row.Database = $database.Name;
            $row.Name = $property.Name;
            $row.Value = $property.Value;
            $table.Rows.Add($row);
        }
    }
}

# Set path to the CMS root
$pathToCMSRoot = "SQLSERVER:\SQLRegistration\Central Management Server Group\" + $CMSInstance;

# Conditionally populate groups
$rootGroups = $table | where Name -like $($Prefix + "*") | Group-Object -Property Name;
foreach($rootGroup in $rootGroups)
{
    $groupName = $rootGroup.Name;

    # Strip off the tag from the Extended Property
    $firstLevelGroup = $groupName.Substring($Prefix.Length);

    # Define path to the first level group
    $groupPath = $pathToCMSRoot + "\" + $firstLevelGroup;
 
    if(!(Test-Path $groupPath))
    {
        mkdir $groupPath;
    }

    # Empty out groups we manage
    Remove-Item $($groupPath + "\*") -Recurse;

    # Create Subgroups
    $subGroups = $table | where Name -eq $groupName | Group-Object -Property Value;
    foreach($subGroup in $subGroups)
    {
        # Define path to the first level group

        $groupPath = $pathToCMSRoot + "\" + $firstLevelGroup + "\" + $subGroup.Name;

        mkdir $groupPath;

        # Add instances to subgroup
        $instances = $table | where Name -eq $groupName | where Value -eq $subGroup.Name | sort-object -Property Instance -Unique;

        foreach($inst in $instances)
        {
           $con = $inst.Connection;
           New-Item -Name $inst.Instance -path $groupPath -ItemType Registration -Value ("Server=$con; integrated security=true");
        }
    }
}

RDBMs Platforms and Airline Seat Categories

moderndba MySQL, Oracle, SQLServer , ,

So I just finished a weeklong trip to Northern Italy with the family. This was our first time bringing my children to Italy (My wife and I have been twice before, and I used to live in Italy for 3 years while in High School). I am also late in a process at work of bringing on support for an Highly Available MySQL solution. This effort at work is being performed by a group I lead which is made up of Microsoft SQL Server and Oracle DBAs that was recently formed out of two separate teams. As I was on the plane ride home (and trying hard to stay awake) I began to think of the differences between MySQL and SQL Server and Oracle.

Admittedly, we are not through the implementation phase in our MySQL rollout, but we have spent the past year researching and have engaged partners to help us architect and build out a highly available infrastructure for MySQL. As I sat in Economy Class with my wife and 3 children (the last two international trips my wife and I have taken were business class) I started to think that there is a lot of parallels between the 3 platforms and the 3 classification of travel on most international flights (Economy, Business, and First classes).

First, let me start by upsetting some of my SQL Server DBA brethren and say that Oracle is still the first class option in the RDBMS space. The price also reflects this reality. While the starting point for Oracle and Enterprise Edition of SQL Server are not that far apart, Oracle will get to a whole nother level quickly as you add HA and performance features that are already baked into the Enterprise Edition of SQL Server. But for that extra money you get options that just aren’t available in SQL Server (i.e. RAC and Exadata).

SQL Server fills the business class space in this analogy. Some would like to argue with me on this and talk about the value. But really, with all the recent changes to the Microsoft Licensing model for SQL Server pushing you into buying Software Assurance and putting all the HA options in Enterprise and deprecating Mirroring in Standard Edition I think I can win the argument that while there is still a lot of value, it is much more of a business class value point than an economy class one.

Just as in business class, where you pay a bit more for your ticket but all your drinks are included and the product is a bit more polished, buying SQL Server Enterprise Edition also covers 99% or potential needs and is very polished. I still have concerns about Microsoft’s trajectory for SQL Server and what it will do to the product long term, that is for a different post. So that leaves us with MySQL.

So far, I would say that MySQL is very much the economy class in this situation. First, I don’t see MySQL as being free (as in free beer) as some would argue. The support costs still exist. To be successful you will need to wither invest in training an/or support and this has a cost. Second, MySQL has matured to the point where it will get you to the same place as Oracle and SQL Server. In fact, I can make a good argument that MySQL (with Galera) provides a level of protection and performance that is not available in the other two if architected and used correctly. But, just as you have to work to sneak the extra drinks and snacks in economy class, there is far more work in architecting, building, and supporting these highly available environments in MySQL.

MySQL however provides you much more control over your costs. You can choose how much to invest in external vs. internal support. Companies like Facebook and Google pay to have experience on staff able to not only support MySQL but to also modify the code base. Most companies do not need this level of in house experience. Especially with so many companies now providing support for MySQL. For most all that is needed is a trained DBA and access to support from one of these vendors.

Well, that was my great revelation on the flight back from Europe. Now time to go try to sneak some champagne from Business class.

SQL Server Slow Query Log Using Extended Events

moderndba SQLServer , ,

Originally Published 11/12/2014

So we had a customer that had a slow application but wasn’t sure what queries were causing them problems.  We provide access to our customers to see a dashboard that allows them to drill down into waits to find slow responding queries, but the interface was too much for them to digest.  Other database platforms that I support allow you to log  queries running longer than a given amount of time to a log so that they can be analyzed.  I thought it would be helpful to provide the same functionality to these customers to help them identify their problem areas.  I’ve been looking at an excuse to play with Extended Events and thought this would be a good opportunity to help out my customers and learn something new.

The plan was was to use Extended Events to log queries taking more than 500 milliseconds to a file.  I could have used other targets, but I am thinking of possibly throwing this data into Hadoop later.  (If I do, I’ll be sure to blog about that as well.)  Setting up the Extended Event Session was not that difficult.  I based it off of Paul Randal’s blog post on logging queries that were expensive based on CPU.  After modifying it to filter based on duration and not CPU as well as removing some noise from the results this is the script I used to set up and start the Extended Event Session.

USE MASTER;
GO

/* Conditionally drop the session if it already exists */
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'EE_SlowQueryLog')
    DROP EVENT SESSION EE_SlowQueryLog ON SERVER;
 GO  

 /* Create the session */
CREATE EVENT SESSION EE_SlowQueryLog ON SERVER
    ADD EVENT sqlserver.sql_statement_completed
    (   ACTION (
               sqlserver.client_app_name
              ,sqlserver.client_hostname
              ,sqlserver.database_id
              ,sqlserver.database_name
              ,sqlserver.plan_handle
              ,sqlserver.sql_text
              ,sqlserver.username)
         WHERE duration > 500000 /* 500 milliseconds in microseconds */ 
              AND sql_text NOT LIKE 'WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue)%' /* Exclude WMI waits */
              AND sql_text NOT LIKE '%sp_GetSlowQueries%' /* Exclude traffic from stored procedure to read data */)
    ADD TARGET package0.asynchronous_file_target
    (   SET FILENAME = N'C:\ModernDBA\EE_SlowQueryLog.xel', METADATAFILE = N'C:\ModernDBA\EE_SlowQueryLog.xem')
    WITH (max_dispatch_latency = 1 seconds);
 GO 

 /* Start Session */
 ALTER EVENT SESSION EE_SlowQueryLog ON SERVER STATE = START;
 GO

This script sets up a collection and stores the queries in a file on the operating system.  But instead of having to grant everyone access to the file to see the contents I created a stored procedure that read the contents of the file and formatted them into something more useful.  I started with the select in Paul Randal’s blog post but in order to run the sys.fn_xe_file_target_read_file function you need to have VIEW SERVER STATE permissions on the server.  I wasn’t excited about granting everyone access to this, so I decided to sign the procedure with a cert to allow permissions to everyone.  This process was modeled after the SQL Server Separation of Duties Framework.

The process involves making sure that master keys exist in both the master and ModernDBA databases.  Then setting up a certificate that matches in both databases.  Then you can associate the certificate with a login that has the VIEW SERVER STATE permission and use the certificate to sign the procedure in the ModernDBA database.  This will allow the procedure to run as the login and get to the slow query log files.  Before you run the script below you will want to change the password to something a bit more secure.

/*  THIS QUERY NEEDS TO BE RUN IN SQLCMD MODE                          */
/*  To enable SQLCMD mode in SSMS use the Query Menu and select SQLCMD */

:SETVAR  Password                 "Password1!" 
USE MASTER;
GO

/* Conditionally create the ModernDBA database */
IF NOT EXISTS(
    SELECT * FROM sys.databases WHERE name = 'ModernDBA'
    )
CREATE DATABASE ModernDBA;
GO

/* Create a Database Master Key (DMK) in the master database */
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name LIKE '%databasemasterkey%')
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$(Password)';
END
GO

/* Create a certificate on the server. */
IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'cert_SlowQueryLog')
    CREATE CERTIFICATE cert_SlowQueryLog WITH SUBJECT = 'Sign Slow Query Log Procedure';
GO

/* Backup Certificate to file */
BACKUP CERTIFICATE cert_SlowQueryLog TO FILE = 'C:\ModernDBA\cert_SlowQueryLog.cer'
    WITH PRIVATE KEY ( FILE = 'C:\ModernDBA\cert_SlowQueryLog.pvk' , 
    ENCRYPTION BY PASSWORD = '$(Password)' );
GO

/* Create a login and map it to the certificate. */
IF NOT EXISTS(SELECT * FROM sys.server_principals WHERE name = 'login_SlowQueryLog')
    CREATE LOGIN login_SlowQueryLog FROM CERTIFICATE cert_SlowQueryLog;
GO

/* Revoke the ability for this user to connect */
REVOKE CONNECT SQL FROM login_SlowQueryLog;
GO 

/* Grant VIEW Server State to User */
GRANT VIEW SERVER STATE TO login_SlowQueryLog
GO

USE ModernDBA;
GO

--Create the DMK in the user database
--Import the certificate
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name LIKE '%databasemasterkey%')
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$(Password)';
END
GO
IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = 'cert_SlowQueryLog')
BEGIN
    CREATE CERTIFICATE cert_SlowQueryLog FROM FILE = 'C:\ModernDBA\cert_SlowQueryLog.cer'
         WITH PRIVATE KEY
              (FILE = 'C:\ModernDBA\cert_SlowQueryLog.pvk', 
              DECRYPTION BY PASSWORD= '$(Password)')
END
GO

/* Create Stored Procedure */
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_GetSlowQueries')
    DROP PROCEDURE dbo.sp_GetSlowQueries
GO

CREATE PROCEDURE dbo.sp_GetSlowQueries 
WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON

    SELECT
    data.value ('(/event[@name=''sql_statement_completed'']/@timestamp)[1]', 'DATETIME') AS [Time]
    , CONVERT (FLOAT, data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT')) / 1000000 AS [Duration (s)]
    , CONVERT (FLOAT, data.value ('(/event/data[@name=''cpu_time'']/value)[1]', 'BIGINT')) / 1000000 AS [CPU Time (s)]
    , data.value ('(/event/data[@name=''physical_reads'']/value)[1]', 'INT') AS [Physical Reads (# page Reads)]
    , data.value ('(/event/data[@name=''logical_reads'']/value)[1]', 'INT') AS [Logical Reads (# page Reads)]
    , data.value ('(/event/data[@name=''writes'']/value)[1]', 'INT') AS [Writes (# Page writes)]
    , data.value ('(/event/action[@name=''username'']/value)[1]', 'VARCHAR(MAX)') AS [User]
    , data.value ('(/event/action[@name=''database_name'']/value)[1]', 'VARCHAR(MAX)') AS [Database]
    , data.value ('(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [SQL Statement]
    , SUBSTRING (data.value ('(/event/action[@name=''plan_handle'']/value)[1]', 'VARCHAR(100)'), 15, 50) AS [Plan Handle]
    FROM 
    (SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file
         ('C:\ModernDBA\EE_SlowQueryLog*.xel', 'C:\ModernDBA\EE_SlowQueryLog*.xem', NULL, NULL)
    ) entries
    ORDER BY [Time] DESC;
END
GO

/* Add Certificate to procedure */
ADD SIGNATURE TO dbo.sp_GetSlowQueries
BY CERTIFICATE cert_SlowQueryLog 
GO 

/* Grant Execute to everyone */
GRANT EXECUTE ON dbo.sp_GetSlowQueries TO [public];

Now that the query is created all we have to do is grant any users we want to be able to see the slow query log access to the public role on the ModernDBA database.  To do that we can use this script.

/*  THIS QUERY NEEDS TO BE RUN IN SQLCMD MODE                          */
/*  To enable SQLCMD mode in SSMS use the Query Menu and select SQLCMD */

:SETVAR  Login                    "testme" 
USE [ModernDBA]
GO
CREATE USER [$(Login)] FOR LOGIN [$(Login)]
GO

Now that we have granted the user access to the stored procedure they can perform filter selects against the data using common TSQL techniques.  One such method is to load the data into a temp table as shown  below.  While this query returns all the data you could easily modify the SELECT statement to only return the rows of interest.

CREATE TABLE #SlowQueries (
     [time]              DATETIME
   , [duration]         FLOAT
   , [cpu]                  FLOAT
   , [physical_reads]       BIGINT
   , [logical_reads]        BIGINT
   , [writes]               BIGINT
   , [user]             NVARCHAR(128)
   , [database]         NVARCHAR(128)
   , [text]             VARCHAR(MAX)
   , [plan_handle]          VARCHAR(100))

INSERT #SlowQueries ([time], [duration], [cpu], [physical_reads], [logical_reads], [writes], [user], [database], [text], [plan_handle])
EXEC sp_GetSlowQueries;

SELECT *
FROM #SlowQueries;

DROP TABLE #SlowQueries;

This process will store 5 1GB files worth of slow queries that can be looked at by the customer.  Performance of reading the files in is a bit on the slow side once you have some data in them.  That is why I have excluded the stored procedure from the Extended Event Session.  Longer term, I may load this data into a table and grant access to the table.  But for now this helped my customers identify queries that needed tuning and was relatively easy to set up.
How have you set up access for your customers to see slow performing queries in SQL Server?

The Froogle DBA – Extended Properties

moderndba SQLServer ,

Originally posted 8/23/2014

In the previous blog post we introduced Central Management Servers (CMS) and went over how to set one up and create groups and add servers.  Central Management Servers provide the Froogle DBA the ability to centrally manage their environment and share a common list of instances with other DBAs on their team.  The power of CMS really begins to grow however when you have it set up so you can apply scripts and evaluate policy to groupings of like instances.  For example, if we are asked to tell us what applications are still running on SQL Server 2005 instances it would be great to have a group in CMS defined in CMS with just SQL Server 2005 instances in them.  We could then query the group to return the list of database names on all SQL Server 2005 servers rather quickly.

But how do we figure out what application these database support.  One way would be to keep track of the mapping of databases to applications in either a spreadsheet or a table in a databases.  But there is another more scalable option, Extended Properties.  Extended Properties are key value pairs that can be stored inside of a databases and associated with the database itself or any of the objects inside the database.  You can see the extended properties in Management Studio by right clicking on an object and selecting properties.  In the property window there will be a tab for Extended Properties.  The following images show how you can see the Extended Properties for a database.

Right click on the database and select "Properties".
Right click on the database and select “Properties”.
On the left side click on the "Extended Properties" tab.
On the left side click on the “Extended Properties” tab.
On the Extended Properties tab you can see the key value pairs that have been defined for the object (or not defined as in this case).
On the Extended Properties tab you can see the key value pairs that have been defined for the object (or not defined as in this case).

To add an extended property to the database it is as easy as typing the key in the first empty row under “Name”.  Then if you want to assign a value to the property just enter that next to it under “Value”.  The following images show how to add an Extended Property.

Enter the name of the Extended Property under "Name".
Enter the name of the Extended Property under “Name”.
If you wish to assign the property a value you can do so now by entering it next to the name of the Extended Property under "Value".
If you wish to assign the property a value you can do so now by entering it next to the name of the Extended Property under “Value”.

But why would we want to use these Extended Properties?  In order for us to scale our support for SQL Server systems we have to do our best to make the environment as self documenting as possible.  So, as in the examples above, you can create an Extended Property in each of your databases that keeps track of the application(s) supported by the databases.  This means that if the database moves from instance to instance you always know what the database is supposed to be supporting.  This also allows you to answer the question proposed above definitely with a single query without having to translate databases to applications using a spreadsheet or a lookup table.

Below is an image of some Extended Properties I have found beneficial over the years.  We have discussed how you can use CMS to query Extended Properties to answer questions, but we can also improve Central Management Servers with Extended Properties.  You will notice in the image that there are two Extended Properties on this database prefixed with “CMS”.  We will show in a later blog post how we can use these Extended Properties to build dynamics groups inside of CMS.  But that is for another day.

9525218_orig

The Froogle DBA – Central Management Servers

moderndba SQLServer ,

Originally published 8/22/2014

Before I start talking about Central Management Servers let me take a moment to better explain what I mean about managing SQL Server at scale.  In the first blog post in this series I failed to mention what I meant by scale.  Scale means a lot of different things.  In this case, scale is meant to the overall size of an environment.  Most often this means having a very large number of databases and/or instances managed per DBA.  

The core of the Froogle DBA’s ability to manage Microsoft SQL Server at scale is Central Management Servers (CMS).  A Central Management Server is an instance of SQL Server that maintains server groups that contain the connection information for one or more instances of SQL Server.  CMS was introduced in SQL Server 2008, so the instance of SQL Server serving as the Central Management Server has to be running SQL Server 2008 or newer.  But you can use CMS to manage instance at least as far back as SQL Server 2000.  Instructions on how to register a Central Management Server can be found here, or you can consult the images below.

Right click on “Central Management Servers” on the Registered Servers tab and select “Register Central Management Server…”
Enter the connection information for the SQL Server 2008 or newer instance you want to use as your Central Management Server and click "Save".
Enter the connection information for the SQL Server 2008 or newer instance you want to use as your Central Management Server and click “Save”.
Now your Central Management Server will show up in the list under Central Management Servers. Note: All DBAs on your team will need to do this to see the servers and groups you register.
Now your Central Management Server will show up in the list under Central Management Servers. Note: All DBAs on your team will need to do this to see the servers and groups you register.

The step above will need to be done on all client machines you wish to share Central Management with.  In the case of the Froogle DBA, this means they will want all the members of their team to register the same CMS.

Once you have your Central Management Server registered, the fun really begins.  Now you can create a central version of the truth as to what SQL Instances your team manages.  You would be surprised the number of places I have been where the simple question of “Which instances are we responsible for?” can’t be answered.  I recommend just having a group under your CMS named “Instance List” which you simply place all your managed instances in.  First, this allows you to quickly see all the instances you are responsible for, and secondly we will use this  list later on when we start integrating other SQL Server components with CMS.  In order to create a group in CMS you can follow the instructionshere, or consult the images below.

Right click on the CMS Server name (or a group to create a subgroup) and select "New Server Group...".
Right click on the CMS Server name (or a group to create a subgroup) and select “New Server Group…”.
Fill in the group name and description and select "OK".
Fill in the group name and description and select “OK”.

Now you can register the SQL instances you manage under the “Instance List” group you just created.  If you are like me the first instance you are going to want to create is the Instance serving as your Central Management Server.  This will fail, as the next series of images shows, with the following error .

“You cannot add a shared registered server with the same name as the Configuration Server.
(Microsoft SQL Server, Error: 35012)”

Right click on the group you want to add an instance to and select "New Server Registration...".
Right click on the group you want to add an instance to and select “New Server Registration…”.
Enter your CMS server information and select "Save".
Enter your CMS server information and select “Save”.
The error message you get.
The error message you get.

In order to register the CMS server in CMS you are going to need to register it by IP address.  You will find a lot of advise on the internet to use the loopback interface (127.0.0.1) to register the CMS server.  This is great if you want everyone to have to log into the CMS server to use Management Studio.  If you take this advice and connect to a CMS server and run a query on the CMS server everything will look like you are querying the CMS server, but you will really be querying the default instance of SQL Server on the host Management Studio is running on.  Instead, you are better off ensuring that your CMS server has a static ip address (or pin the ip via DHCP).  If you just can’t get a static ip address for your CMS server then you can register it this same way, but in place of the ip address you are going to need to know the port number SQL Server is running on and then enter ServerName,PortNumber (i.e., SQLMON,1433) for the Server Name in the New Server Registration window.  An example setting it up using the ip address in the New Server Registration window is shown in the next images.

Enter ipconfig from the command line in a command window on your CMS server. If you can't log into your CMS server you can ping the server by running "ping CMSServerName" replacing CMSServerName with the name of your CMS server. Record the ip address assigned to your CMS server.
Enter ipconfig from the command line in a command window on your CMS server. If you can’t log into your CMS server you can ping the server by running “ping CMSServerName” replacing CMSServerName with the name of your CMS server. Record the ip address assigned to your CMS server.
Enter the ip address in the "Server Name" box towards the top of the New Server Registration window. Then to display a more friendly name change the "Registered Server Name" box towards the bottom of the window to be the name of your CMS server. Click save.
Enter the ip address in the “Server Name” box towards the top of the New Server Registration window. Then to display a more friendly name change the “Registered Server Name” box towards the bottom of the window to be the name of your CMS server. Click save.
You now have your CMS server registered inside of CMS and it will work regardless of what machine you connect to CMS from.
You now have your CMS server registered inside of CMS and it will work regardless of what machine you connect to CMS from.

Registering the rest of the instances in your environment should be pretty straight forward.  I have run into situations where I needed to register instances where DNS would not resolve the SQL Server.  If you run into this situation you can use the same technique as above (assuming that you have a static ip address for the instance to add).  If the instance is running on a non default port, you may need to add the port number (i.e.,  192.168.1.5,1234 with the ip addresss then a comma then the port number).

Once you have servers registered in CMS, you can now run scripts in Management Studio on either individual servers or the group as a whole.  You can also evaluate policy at the group level and perform a lot more tricks.  For now this is enough information for the Froogle DBA.  If you don’t plan to follow the Froogle DBA plan then you may want to add other groups and register servers manually.  Some example of the groups that I have found useful are grouping servers on the following:

  • Version of SQL Server
  • Edition of SQL Server
  • Applications
  • Environment (Development, Test, QA, Integration, Production, etc..)
  • Features (SQL Server features such as Mirroring, Availability Groups, Clustering, etc..)

But ultimately the groups you create and use are the ones that are meaningful and useful to you and your team.  There is a lot more about Central Management Servers to go over with for Froogle DBAs, but this is enough to get you started.  We will revisit CMS as we go over other tools in the Froogle DBA’s toolbox.

The Froogle DBA

moderndba SQLServer

Originally published 8/20/2014

First, I really meant Froogle and not Frugal.  Froogle is a play on words combining Frugal and Google.  The Froogle DBA is the name of my talk that I will be giving at SQL Saturday #320 in Raleigh, NC on 6 September 2014.  It is basically a presentation on how I have seen components of SQL Server put together along with some open source monitoring.  This configuration has allowed a team of 2 Database Administrators to manage about 2,000 databases on over 70 instances of SQL Server.  They are able to provide 24×7 on-call support to everything from departmental to mission critical systems.  Technically speaking I pitch in on the after hours rotation, so you could argue it is 2.5 Database Administrators, but either way this is managing a lot.

This framework is frugal because it allows our employer to get the most from it’s SQL Server and human capital investments.  The Google comes in because we have found that this framework makes it very easy for us to take information, and/or scripts, we find through with our google-fu and integrate them into our environment.  The major components of this framework are Central Management Server, Extended Properties, Management Data Warehouse, Policy Based Management, and an open source monitor.  Right now, that monitor is Zenoss.  I have seen other open source monitors used with great success as well.  We like Zenoss because it provides agentless monitoring of our servers.  In our minds that is one less thing to maintain.

I plan to post on each of these topics to provide some background for my presentation.  This will be information that I won’t have time to cover, but compliments the presentation.  The hope it that through the information on this will provide a richer understanding of how to successfully implement the framework.  I hope this information is as beneficial to you as it has been to us.

SQLSaturday #320 Raleigh 2014

moderndba SQLServer

Originally published 8/20/2014

4058256_orig

I was humbled to have been selected to present at the upcoming SQL Saturday event in Raleigh, NC  on 6 September 2014.  I will be giving a talk on “The Froogle DBA”.  No that is not a typo, it is me trying to be clever.  If you want to know what it means then you’ll have to come down and listen to the talk.  In the process of preparing my presentation I quickly realized two things.  First, the topic I am discussing is way bigger than can be fully covered in 75 minutes.  Second, I needed a place to put all the scripts and other goodies for the talk.
Well, this is the space that I have created to hopefully handle both of these issues.  I plan to use this blog as a place to cover the content of the presentation in more depth.  I also plan to provide copies of my slides and scripts here as well.  So, stay tuned for more great information on how to use your existing SQL Server investment and some open source and freely available tools to manage and monitor SQL Server at scale.