Customize Your Setup Experience With an MST (sorry, no funny robots…)

An unfortunate, let’s call it a “mishap”, recently had me digging through an MSI (Windows Installer) package to see if I could find the source of the “mishap”, and while this didn’t really have anything to do with SQL Sentry, it did remind me of helping one of our larger customers with automating deployment of their own flavor of setup for SQL Sentry.

I’m going to go through a method you can use to create your own personalized setup experience for a Windows Installer based setup package, but I do want to start with a short disclaimer. There are tools I’m going to talk about that can be used to really back yourself into a corner if you aren’t careful. Before trying any of this at home just make sure you have good backups of your original files, and a nice test environment to use while you work out the details.

What is an MST?

An MST is a transform that can be applied to a Windows Installer setup package (MSI). MSI packages are their own little database, and with the right tools, you can change or manipulate the data in the various tables of the MSI package. Creating a transform lets you apply differences at runtime vs. altering the package itself.

What you need

First, you will obviously need your MSI package. My examples are going to be using the SQL Sentry setup, and you can download that from www.sqlsentry.net/downloads if you have an existing license, or you can sign up for an evaluation at http://www.sqlsentry.net/download-trial/trial.asp. The setup is wrapped in an exe, but that isn’t a problem as we’ll see in a moment.

Next, you will need a tool that will allow you to peer deep into the soul of the MSI and do funny things to it. Microsoft is very good at that sort of thing, and provides us with a tool called Orca with the Windows SDK. You can find information and download links on the MSDN article that covers Orca.exe and its uses here.

Finally, you need a plan. Firing up Orca with your mind set on modifying the developer’s well tested setup package is just not recommended in most cases. There are a few good reasons to do it, and I’m going to be demonstrating one of those, but make sure that you’re going in with a specific goal in mind.

Our Plan

SQL Sentry comes with all of the software bits rolled into one setup package. You can choose to install just the server, just the client or both. Now and again an administrator will distribute the setup to one of their users intending for them to install the client, and they will accidentally install the server as well. It’s not really a problem for them to uninstall the server, but they could prevent this by providing a setup transform that excludes the server by default. That is what I’m going to provide instructions on.

But, this setup is an exe, not an MSI?

That is very true, so we need to extract the MSI. You can do this from the command line using the setup executable itself with the following syntax (I’m using the x64 install for SQL Sentry, the setup file name can vary based on your platform):

C:\> SQLSentrySetup-x64.exe /extract [path]

For me, the command looks like this: SQLSentrySetup-x64.exe /extract “G:\Files\InstallerTransforms”

There may be different ways for different packages to extract an MSI (among other things), but for SQL Sentry this is how it is done. If you are using something else, you may need to check your documentation or ask the vendor.

This will give you a file called “SQLSentrySetup.msi” in [path]. This is the MSI file we’re going to work with in Orca.

What do the insides look like?

Now you want to open the MSI file with Orca. I was able to simply right click the file and select “Edit with Orca”, but you can certainly use Start-> All Programs –> Orca (it has an icon that looks like a tiny Shamu!) and File-> Open if that is not available.

The first thing you might notice is a list of “Tables” on the left:

As I mentioned earlier, an MSI package is really just a small database. Most folks reading this are in the database industry, so this is really great. Already you’re dealing with a familiar adversary.

If you take a minute to click through the tables you’ll see they’re lain out in a familiar way with column headers at the top, and rows of values below in a grid.

Now, find the “Feature” table.

This is what we’re interested in. This tables controls the features to be included and their default behavior. You may recognize this as the following screen during a setup:

Don’t change anything at this point, as that will be changing the base MSI, and we want to apply our change using a transform.

So what about this MST (again, no robots) thing?

Right, so we know how to get into the MSI, but how do we create a transform? That part is pretty easy. With the totally unaltered MSI file open in Orca, look to the menu options at the top. You will see a menu called “Transform”. Select Transform-> New Transform.

The kicker here is that at this point there is really not any visible difference between editing the transform and editing the actual MSI, so just for a sanity check (which I tend to need often) I go to the file menu. The “Save” and “Save As” options should be disabled, and the “Save Transformed As” should be enabled (this can be used to create a transformed version of the MSI if you want).

Now, we can make changes with confidence that we’re working on a transform and not the actual MSI database.

In the feature table, look for the Feature named “Server_x64” with Title “Server Service”. This is the one we want to fiddle with. The Level column tells us whether the feature is included by default. This is documented here. Sometimes folks have the values for this customized, but SQL Sentry does not. The values we can use are as follows:

• 0: Not listed at all, and not available for install
• 1: Installed by default (this is what is set already)
• 2: Not installed by default

I’m going to set this to 2, so that they have it as an option, but they won’t simply install the server by accident because they didn’t know to unselect it:

Now I’ll just use the “Transform” menu to “Generate Transform…” to the same folder as my MSI file. I’ll call this one SQLSentrySetup_NoServer.mst.

Something nice in Orca that I should point out is that when you are working in a transform, the values that are different from the original will have a green border around them like this:

If you get into a situation where you are creating a very customized setup (as I was with the customer I mentioned earlier) it’s nice to be able to follow that crumb trail of green borders to know where you’ve been messing about.

So how do I use this?

Well, there you are then. You have a setup transform that will do exactly what I said it would. Please come again!

Oh, yes, there might be one thing left to do. I guess we do need a way to actually run the setup package with the transform applied.

That isn’t too difficult either. We just use a command like the following with msiexec:

C:\> msiexec /I [PathToInstaller] TRANSFORMS=[file1;file2;file(n);]

So for me this command looks like this: msiexec /I SQLSentrySetup.msi TRANSFORMS=”SQLSentrySetup_NoServer.mst”. I did not use a full path here simply because I was already running under the context of the location I wanted to work in. If you are just running your command prompt from the default location, you will want to ensure that you provide the full path to where you want to save the MST file. Also, notice that I only have one transform, but you can actually string them together separated by a “;” to apply several different transforms. Information on the command line parameters for msiexec can be found here.

Now, after running this, my setup wizard starts off like normal, but when I get to the component selection screen it defaults to looking like this:

I can pass this out to folks with the MSI and MST out on the network someplace. I can even schedule it for distribution with SMS or something similar.

Conclusion

With a little imagination, and some patience, you can come up with lots of ways to customize the setup package. Again, just remember to keep good backups, and don’t experiment on any system that matters.

If anyone is using SQL Sentry, and has a question about how to customize something specific in the MSI, just ask here or email me at jhall at sqlsentry.net. I’ll do my best to help out.

Until next time,
-JRH

Use Execute Action Parameters for Feeding Events to a Ticketing System

SQL Sentry Event Manager uses a system of conditions and actions for handling events. Basically, you define one or more actions to be taken as the result of a pre-defined condition being met. There are over a hundred pre-defined conditions that range from a SQL Server Agent job failing to a runtime threshold being exceeded for a T-SQL query, to the minimum value threshold for a performance counter being broken. Applicable actions can then be taken once the condition is met. Whether a condition supports a specific action really just depends on whether or not that action makes sense in that context. For example, “Run QuickTrace” is not an action for the “SQL Server: Deadlock” condition, because when a deadlock is detected the event has already occurred, and the QuickTrace would not contain anything relevant.

Two of the actions that are available for just about every action though, are “Execute Process” and “Execute SQL”. These two actions will allow you to execute either a Windows process, or a T-SQL command sequence on a specified server that is watched by SQL Sentry when the associated condition is met. This can be useful for several reasons, but what makes them even better is that ability to pass all of or specific parts of the event data as parameters to be used by that process or T-SQL batch.

A great use for the above feature is in automatic generation of technical incident records for ticketing systems. You can monitor for an event, and have it sent directly to your ticketing system for review by front line technicians providing all the information they need to make decisions without ever touching a keyboard, or even being present for that matter. For the rest of this post, I’ll go over a way to do just that, and hopefully it will spark some good ideas for anyone reading.

I’m going to assume that we’re already monitoring the server with SQL Sentry Event Manager, so from this point we want to choose a good candidate condition. One that is pretty common for a situation like this is “SQL Server: Agent Job Failure.” This condition is met when a SQL Server Agent job that is watched by SQL Sentry Event Manager logs a failure. We’ll enable the “Execute SQL” action for a backup job, as illustrated below.

Job, Condition, Action

At this point we’ve got everything enabled, and the SQL Sentry service will attempt to execute a T-SQL command batch whenever this job fails. We do still have something extremely important missing though, and that is what T-SQL to run, and where to run it.

We want to not only call a stored procedure that will create a support ticket, but also pass information from the event that can be used by a technician to locate and resolve the problem. In addition, we want to pass in a custom value of our own that will help the ticketing system classify and prioritize this incident.

The SQL Sentry feature that is going to allow us to do all of this is called “Execute Action Parameters.” You can read our documentation on this feature in our online help here: http://www.sqlsentry.net/help/ug/?System_Parameters.html, but we’re also going to spend some time setting this up and writing some code below.

For the purpose of this post, we’re going to keep it simple. I’ve created a small database called “Ticketing” and added the following table definition:

CREATE TABLE Tickets

(

    TicketID int NOT NULL identity(1,1)

        CONSTRAINT PK_Tickets_TicketID

            PRIMARY KEY CLUSTERED (TicketID),

    Title nvarchar(128) NOT NULL,

    [Description] nvarchar(2000) NOT NULL,

    ClassCD nchar(3) NOT NULL,

    PriorityHint tinyint NOT NULL

)

Obviously, a real ticketing system will require more information, but the basics are here, as well as two columns (ClassCD and PriorityHint) we can use to demonstrate custom user parameters.

I will also use the following stored procedure to populate the Ticketing table:

USE Ticketing;

GO

CREATE PROC InsertTicket

    @Title nvarchar(128),

    @Description nvarchar(2000),

    @ClassCD nchar(3),

    @PriorityHint tinyint = 0

AS

INSERT INTO [Ticketing].[dbo].[Tickets]

           ([Title]

           ,[Description]

           ,[ClassCD]

           ,[PriorityHint])

     VALUES

           (@Title

           ,@Description

           ,@ClassCD

           ,@PriorityHint)

GO

To this point it’s all very straight forward. We have a data table for storing ticketing data, and a simple stored procedure for populating it.

The interesting part comes when I define the T-SQL to run for my Execute SQL action. Below I will list the raw T-SQL as well as what it should look like in the actions configuration area of the SQL Sentry console. Then I will explain the special elements we see in the code and what they mean.

Entering the T-SQL command for the Execute SQL command is pretty easy. It’s done in the same area of the console application that was used to enable the “Execute SQL” action.

As seen below, you can select which server to execute the command on, and enter the command itself. Remember that only those servers that are monitored by SQL Sentry Event Manager will be listed in the “Server” drop-down.

I’m going to select my local server, since that’s really the only one I am monitoring right now, and enter the following for my “T-SQL Command Text”.

EXECUTE Ticketing.dbo.InsertTicket

    @Title = '<%ObjectType%> <%ObjectName%> Failure'

    ,@Description = '<%ObjectType%> <%ObjectName%> has encountered a failure on server [<%ServerName%>]'

    ,@ClassCD = '<%CLASS%>'

    ,@PriorityHint = 2

At first glance, this is just a normal execute statement, but there are some interesting elements within the string literals that go along with event processing in SQL Sentry, and deserve some additional explanation.

First, any time you see the following format <%…%> in this context, it is telling SQL Sentry that we want to pass some part of the event data as a parameter instead of just keeping the literal text. Take for instance “<%ObjectName%>.” Before this command is executed, any instance of “<%ObjectName%>” will be replaced with the name of the object that caused the event. In this case it will be our backup job “Backup User DBs – FULL.” These parameters are all self-explanatory of what information they will inject into the command, except for one. The “<%CLASS%>” parameter is special. The “<%CLASS%>” parameter is what we call a “User Parameter”, and I will go into more detail about that in a bit.

So, given this command, when my backup job fails, I should get a row in the Tickets table telling me that I need to investigate this tragic event. Now I’ll force the job to fail, and we can see it on the SQL Sentry event calendar, and I’ll also list the contents of the Tickets table.

Above you can see the job failure and the detail for the steps in the job. This job simply threw an error with the message “CLASS=PRD.” That really doesn’t mean much, but it becomes important when we look at the user parameter in a bit. Now let’s see what we end up with in the Tickets table below:

My execute action parameters were replaced by meaningful information about the event. I know what job failed, and what server it failed on, and I can go investigate what happened using my SQL Sentry calendar view.

If this were a database for a real ticketing system, I’m sure several members of the technical staff would have seen it, and it would be classified and prioritized. Notice the “Class CD” which I mean to stand for “Class Code” and “Priority Hint” which I intend to be used to hint at the priority level.

Let’s assume that “PRD” stands for “Production” in a list of codes somewhere, and that “2” is hinting at a priority of 2. You can see how I could send in a custom parameter from my event output, or just a literal value, such as the priority hint.

The error I raised in the job step is where I got the ClassCD from. I could just as easily have done this with a code comment, print statement, or other mechanism since all I need for this to work is for the NAME=VALUE (in this case CLASS=PRD) set to be somewhere in the output of the event. I put it in the error message here because I knew I was going to make the job fail, and it was just quicker. It’s really just a T-SQL RAISERROR statement. I have it listed below:

RAISERROR ('CLASS=PRD', 16, 1);

This is the user parameters feature that I was discussing earlier. I could embed any number of these into the output of an event, and use them to control processing of the specialized event data upon generating the support ticket. All that is required is that NAME=VALUE pair. Your parameter name will be in the following format: <%NAME%>. In my case it was <%CLASS%>.

 

And that pretty much covers it. You can imagine some of the things you can do with this. You can even get really creative, and pass the entire event message in to parse out pieces that we may not have thought about for pre-defined parameters.

 

Now, there is a second action that these parameters can be used with called “Execute Process”. This action can be used to execute a Windows process, but the parameters work the same way, except in that case they would be command line switches. Again you can find the details on usage in our online documentation.

 

I hope that this tutorial sparks some great ideas for SQL Sentry users, and as always the SQL Sentry support team is happy to help with your implementations of SQL Sentry features, so just ping us if you are interested in trying something like this out.

 

Until next time,

-JRH

Capture SQL Server Agent WMI Event Alerts with SQL Sentry Event Manager

SQL Sentry Event Manager is able to monitor SQL Server Agent Alerts and react to them out of the box. One limitation with that functionality creeps up when dealing with Agent alerts that are set to fire off of WMI events. While WMI event based alerts aren’t what I would call common place, they do make it easy to do some interesting things with an Agent alert that might normally take a bit more customization to do.

One thing that I know we can alert on using WMI events is when a database is dropped, so to keep the scope of this post small we’ll be using that for our Agent alert.

The first thing you want to do is create the alert using SQL Server Management Studio (SSMS from now on). In the SSMS Object Explorer look for the SQL Server Agent node, then expand it until you see the folder named “Alerts”. Right click the “Alerts” folder and select “New Alert…”. You should get a dialogue similar to the one below:

First give the Alert a name. You can call it whatever you want, but try and keep it simple, because we’re going to use this name someplace else later, and the simpler it is, the easier it will be to use. I simply named mine for the purpose it will server “Database Dropped”.

Under Type select “WMI event alert”. Namespace should default to “\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER”, but if it doesn’t you want to enter that for the Namespace field. Last, under Query, enter “SELECT * FROM DROP_DATABASE”.

In very short terms, we’re telling SQL Server to run an asynchronous query against the SQL Server WMI Provider that will return a row when someone executes a “DROP DATABASE” command. It’s not my goal here to get too far into the subject of WMI or even the SQL Server WMI Provider for Server Events, but you can find a wealth of information on it at http://msdn.microsoft.com/en-US/library/ms180560(v=SQL.100).aspx.

Now, just click ok, and leave SSMS running. The alert will actually be fired as is, but what we’re going to do is create a custom alert trap that will feed SQL Sentry enough information to display this alert on a calendar and perform actions when conditions are met involving the alert. We’ll come back in a bit, and do one more thing to it, but for now it is configured the way we want.

The way we’re going to have the Agent alert tell SQL Sentry all about the event is with an Agent job. An alert already watched by SQL Sentry uses the SQL Sentry Alert Trap Job that is maintained by SQL Sentry. We’re going to create a special alert trap job, just for our alert, that does some of the same things the default SQL Sentry alert trap does, but provides some different information. The most important piece of information is going to be the name of the Agent alert that this event data is from.

So, back in SSMS I create a new job called “Database Dropped Alert Trap”. I give the job one “Transact-SQL script” step and drop the following code into the "Command field:

set quoted_identifier off

begin tran

declare @msg varchar(256)

set @msg = 'Database [' + '$(ESCAPE_SQUOTE(WMI(DatabaseName)))' + '] was dropped by [' + '$(ESCAPE_SQUOTE(WMI(LoginName)))' + ']  from computer [' + '$(ESCAPE_SQUOTE(WMI(ComputerName)))' + ']'

exec spTrapAlert_20 '50000', '0','$(ESCAPE_SQUOTE(WMI(DatabaseName)))', @msg

update msdb.dbo.SQLSentryAlertLog_20 

set AlertName = 'Database Dropped' 

where ID = @@IDENTITY

commit tran

The above code may look a little complicated, but all we’re really doing is grabbing some basic information from the WMI event row. This is provided to us by SQL Server Agent, and is accessed using the bits formatted like “(WMI(XXX)))”. Those have to be escaped a certain way, thus the bits that look like “$(ESCAPE_SQUOTE”. Then we’re using the stored procedure SQL Sentry uses to add the alert event to the alert log table in MSDB. Finally, we update the most recently added row for Alerts giving it the name of our Alert (in my case ‘Database Dropped’), so that the SQL Sentry polling service can identify and associate the object that generated the event data. This all happens inside of a short transaction to make sure it all runs as a singular operation.

Don’t create any schedule for this job. Remember, it will be run as the target for our Agent alert, so no schedule is necessary.

For the next part, we need to perform the steps in order, so I will number them.

1.) Use the SQL Sentry console application to ensure that the new SQL Server Agent Alert (for me it’s named “Database Dropped”) is watched by SQL Sentry Event Manager.

2.) Back in SSMS, right click the agent alert and select properties.

3.) In the alert properties dialogue, select “Response” from the left side panel. If you have done everything to this point, it should look like the image below:

4.) In the drop-down under “Execute job” select the Agent job that we created special for this alert:

5.) Click ok.

That is all there is to it. Using the code from above for our job step, whenever someone drops a database from this server, the alert should fire and you will get an event in SQL Sentry telling you what database was dropped, what login executed the drop command, and what machine it was executed from. You can use the standard SQL Sentry Event Manager conditions and actions off of this alert, including Send Email and Send Page to be notified proactively.

You can experiment with other WMI events as well such as CREATE or DROP LOGIN. The WMI query is what needs to change, as well as some of the WMI properties gathered in the alert trap job step.

If anyone out there wants some assistance getting this set up, or if you just have questions, head over to forum.sqlsentry.net and post a new thread or just email us at support at sqlsentry.net.

Until next time,
-JRH

On the lighter side…

If you look to the right under my photo you will see a couple of pieces of information, one of those being my Xbox Live gamer tag. Given that, you might guess I’m a bit of a gamer. The truth is, it’s more than just a bit.

Rarely will I mix in any kind of gaming with my work-related presence, but I figure that if I can have my gamer tag on my blog, then what’s wrong with blogging about a game here and there? So here it comes.

I am floored by the complete awesomeness that is Starcraft II: Wings of Liberty. This is (IMHO) by far the best RTS to grace us with it’s presence *ever*. The level of detail and quality of production that are presented by this game are nothing short of amazing, and brilliant.

I haven’t been so immersed in a single player campaign for any game since Demon’s Souls on the PS3, and this is a strategy title! It draws you into the Starcraft universe in ways that I never imagined it would prior to release.

I’m not doing a full review here, I think it speaks for itself, but suffice to say that it will hold a spot on my PC for a long time to come. The *ever so long* wait was indeed worth it.

That said (here comes the work tie-in), I know lots of folks have been waiting on some of the great new features in SQL Sentry 6.0. Those that got a sneak peek back at Spring SQL Connections may have been waiting the longest, but I want to assure that the wait is indeed worth it.

You have to ask yourself, “Do I want something now, that may be ok, but might not be everything I envisioned, or do I want to wait for features and quality that will blow my socks off?”. I’m here to tell you that yes, you want to wait.

I’m not comparing SQL Server tools to video games, but I am pointing out that true quality is always and certainly, worth waiting for. If you don’t believe that, go out and pick up a copy of Starcraft II: Wings of Liberty. Let me know when you find your socks.

Thanks,
-JRH

SQL Saturday #46 (Raleigh, NC)

This past weekend (09/18/2010) marked the SQL Saturday Event for Raleigh, NC.

It was organized by long time friend Brett Tomson (LinkedIn) and members of the area SSUG, and they did a great job organizing the event.

First, the night before we were able to enjoy a nice dinner at a local place called '’The Pit”. It was indeed some of the best BBQ (Carolina style) that has ever passed my lips.

The day of, I was manning the SQL Sentry table with Steve Wright (Twitter), and we were both able to do a mid-day session for attendees. I did a one hour session called “Recipe for a Happy DBA: A Guide for SQL Server Developers”. It’s basically a talk for developers on how to do things in a way that gets you cool points with your DBA team. I had a small, but very dedicated, group of developers that were interested in the topic, and I hope they had as much fun attending as I had presenting.

Something neat that the hosts of the event did was to plan small giveaways for the end of every session. These were mostly books that were relevant to the track the session was a part of. This was a first for me to see at one of these events, and I thought it really added a nice flare. It also gave more incentive to attend some of the less popular topics.

All in all, I give two thumbs up to the folks that planned and executed for the Raleigh, NC SQL Saturday event. I look forward to more great things to come next time.

Thanks,
-JRH

Monitoring Replication Latency With SQL Sentry Event Manager

To start, let me state now that this post applies to a plain vanilla transactional publication on SQL Server 2005 and higher that does not allow anonymous subscribers. That’s not to say it won’t make sense for other topologies, but I have not explored others, and some of the concepts involved don’t make sense or exist for them anyway.

I recently had a really good request come along asking if SQL Sentry can send notification when replication latency breaks a specified threshold.

I like this type of request, because it’s something that isn’t addressed “out of the box”, but I knew that I could put something together with SQL Server functionality and some tools provided by SQL Sentry. I also admittedly have a little bit of a soft spot for replication going back to early 2002.

There are actually two solutions I’ve came up with for this. They both have pros and cons, so there is really nothing wrong with implementing both.

The first, and probably simplest to setup is to use the performance counter collection facility built into SQL Sentry Event Manager (details). Microsoft has seen fit to provide us with some performance counters specifically for tracking replication latency, both for distribution and for the log reader.

The setup procedure is outlined here. The only difference being that you will use the performance counters below, and not the counters discussed in the KB article.

One of the “not so great” aspects of using this method is that some of these counters are specific to a publication/subscription pair, and if your subscribers change, you will have to frequently add and remove counters from this job in the SQL Sentry console application. If your subscriptions are fairly static, then you have no worries here.

The counters you want to add are as follows:

• SQLServer:Replication Dist.
  • Dist:Delivery Latency
• SQLServer:Replication Logreader
  • Logreader:Delivery Latency

For the first, you will have a counter instance per publication/subscription combination. This is the one you’ll need to keep an eye on if your adding and dropping subscriptions regularly.

Summing these two counters gives you the time it takes for a transaction to go from the publisher to the subscriber. Unfortunately, the counter values can’t be summed in SQL Sentry and compared to a threshold, and there is no counter that sums them for you, so you will have to track the thresholds separately using the “Performance Counter: Threshold Max” condition for each one. This too is a minor shortcoming of using this method.

Even with a few hurdles though, setting things up using performance counters offers some advantages. It’s not overly complicated to set up, since you can literally do everything you need to do right from the SQL Sentry console application; there is no need to add a stored procedures to your publication database, and you can add the counters from any server to any watched job. That means that you don’t technically have to use a SQL Sentry license to monitor each server that has a publication to monitor. You would only need a license for the server running the job that has the counters attached.

The other method I worked out is a bit more technical, but it provides a way to alert on the latency for all subscriptions at once for a given publication, and bases the threshold on the total latency instead of the two separate values.

Here we’re going to add a stored procedure to the publication database, then create a SQL Server Agent job to run the procedure on schedule.

I will list the code first, then explain what it is doing:

-- This proc must be created in the publication database

-- use CTRL + SHIFT + M to replace variables

use <DatabaseName, varchar(35), Mydatabase>

go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<StoredProcedureName, varchar(50), MyProc>]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[<StoredProcedureName, varchar(50), MyProc>]

GO

/* Prototypes:

exec <StoredProcedureName, varchar(50), MyProc> 'MyPublication', 8, 1, 'myemail@mycompany.com'

exec <StoredProcedureName, varchar(50), MyProc> @pub = 'MyPublication', @emailResults = 1, @emailRecipients='me@mycompany.com;you@yourcompany.com'

exec <StoredProcedureName, varchar(50), MyProc> 'MyPublication'

NOTE: Email functionality works on SQL Server 2005 instances or greater watched by SQL Sentry.

*/    

    create proc <StoredProcedureName, varchar(50), MyProc>

        @pub sysname,

        @maxLatencySeconds int = 5,

        @emailResults bit = 0,

        @emailRecipients nvarchar(255) = '',

        @dropTable bit = 0

AS

set nocount on

    -- table def matches sp_helptracertokenhistory output 

    if object_id('tempdb.dbo.##repl_latency_stats') is NULL

    begin

        create table ##repl_latency_stats

            ( distributor_latency int NULL

            , subscriber varchar(1000) NULL

            , subscriber_db varchar(1000) NULL

            , subscriber_latency int NULL

            , overall_latency int NULL );

    end

    --clean out the temp table

    truncate table ##repl_latency_stats

    --send the token

    declare @tokenHandle bigint

    exec sp_postTracerToken @publication = @pub, @tracer_token_id = @tokenHandle output;

    --just sit until the max latency * 2 runs out so the token has time to get there

    declare @maxWait int = @maxLatencySeconds * 2;

    if @maxWait > 30

    begin

        set @maxWait = 30;

    end

    declare @waitfor varchar(10) = '00:00:' + CAST((@maxWait) as varchar(2));

    waitfor delay @waitfor;

    --get the results the results

    insert ##repl_latency_stats (distributor_latency, subscriber, subscriber_db, subscriber_latency, overall_latency)            

    exec sp_helpTracerTokenHistory @pub, @tokenHandle;

    --get rid of history

    declare @cutoff datetime = getdate();

    exec sp_deleteTracerTokenHistory @publication = @pub, @cutoff_date = @cutoff;

    --process results    

    if (@emailResults > 0 AND (select COUNT(*) from ##repl_latency_stats where overall_latency > @maxLatencySeconds) > 0)

      begin

        declare @subject varchar(100)

        declare @message varchar(100)

        declare @query varchar(500)

        select

            @subject = 'Replication Latency Threshold Exceeded',

            @message = 'Replication latency for the following subscriptions has exceeded ' + CAST(@maxLatencySeconds as nvarchar(2)) + ' seconds:' + char(10) + char(13),

            @query =

                'set nocount on

                select 

                    cast(subscriber as varchar(25)) as [Subscription Server],

                    cast(subscriber_db as varchar(40)) as [Subscription Database],

                    cast(distributor_latency as varchar(15)) as [Distributor Latency],

                    cast(subscriber_latency as varchar(15)) as [Subscriber Latency],

                    cast(overall_latency as varchar(15)) as [Latency Overall]

                from 

                    ##repl_latency_stats

                where overall_latency > ' + CAST(@maxLatencySeconds as nvarchar(2));

        exec msdb.dbo.sp_sentry_dbmail_20

            @recipients = @emailRecipients

        ,    @subject = @subject

        ,    @body = @message

        ,    @query = @query

      end

    else

      begin

            select 

                subscriber as [Subscription Server],

                subscriber_db as [Subscription Database],

                distributor_latency as [Distributor Latency: Seconds],

                subscriber_latency as [Subscriber Latency: Seconds],

                overall_latency as [Latency Overall: Seconds]

            from 

                ##repl_latency_stats;

      end

    IF @dropTable = 1

    begin

        drop table ##repl_latency_stats;

    end;

return

First off, I wrote the procedure using replacement variables, so you can name it whatever you want easily by using CTRL + SHIFT + M in SSMS. That is important to point out, since it won’t run until that is done.

The procedure takes the name of the publication to monitor, the latency threshold to use, 1 or 0 for whether to send an email alert if the threshold is broken, a list of standard format email addresses (separated by ‘;’), and 1 or 0 for whether or not to drop the temp table, which you generally won’t want to do, since it needs to be there when the email alerts are picked up.

We’re using functionality that was added to SQL server 2005 called a tracer token. Basically we’re doing the same thing a plumber does when he drops a microchip into your plumbing, and tracks it through to find a clog, only we’re just seeing how long it takes to get from the publication to the subscriber.

The procedure creates the token, then waits long enough for it to make it through and drops the results into a global temp table.

If you’ve specified to email alerts about breaking the threshold, those results are added to the SQL Sentry email Queue for this server (details), which is why the server needs to be monitored by SQL Sentry Event Manager. The alert will be delivered by the SQL Sentry server service, including the current contents of the temp table that break the runtime threshold, during the next message monitor poll.

Keep in mind that the threshold in this case is on the total latency, which is in seconds. Also, if the tracer token doesn’t make it to the subscriber within 30 seconds, you won’t get any results. This can be increased easily by changing some values just above the “WAITFOR DELAY” call, but 30 seconds should already be well beyond what is needed to get full results.

As always, anyone interested in setting this up that wants more information can post on forum.sqlsentry.net or email support at sqlsentry.net.

-JRH

What Happened There? Post #1

This is the first post in a series. I’m going to do some different things to a test instance of SQL Server, show you what it looks like in SQL Sentry Performance Advisor, then explain what I did and why Performance Advisor looks that way when it happens.

This should be a great series for anyone using SQL Sentry Performance Advisor day to day. If you’ve ever looked at those performance metrics and thought “what caused that?” it could be revealed in one of these posts.

The systems used here are basically “nothing special” VMs running on Hyper-V in a Windows 2008 R2 cluster. I’m sticking with SQL Server 2008 R2, but generally most things will be relevant to 2005 and some to 2000. I will try to keep things as close as possible to something that might really happen in a production environment, but do keep in mind that it is in a controlled test environment, and you may see some things that you know yourself would really (almost) never happen.

With the intro out of the way, let’s take a look at the Performance Advisor dashboard in history mode for today’s “What happened there?”.

Notice in the above screenshot I have circled the “SQL Server Memory” chart. That is really for the benefit of focusing in on that area for this post. Many may notice that this server has a lot of other scary things going on, but this time we’re focusing on SQL Server memory.

What we see in the chart is that memory being used by both the buffer cache and the object cache took a steep dive at about 9:13AM. Then again at around 9:15AM.

There are different things that can cause SQL Server to dump cache. You see it a little when memory pressure exists and things are constantly being flushed out to make room. There are other symptoms that go along with that like higher lazy writes and a low Page Life Expectancy (PLE), but in this case (even though there is normally memory pressure on this server) we see a more drastic immediate change.

Normally when there is a cache dump this large you can count on some pretty nasty behavior from the SQL Server. If it had gone all the way to zero I might even think the SQL Server services had crashed or been restarted, but in this case there is a perfectly logical, and safe (sort of) explanation.

So what happened there? This is a test server, and generally before testing the performance implications of any potential change we dump the cache on purpose in order to get more accurate results.

So, in this case the dramatic change in memory used by SQL Server is just the result of my running the following statements:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Oh, and yes, I did it twice just for good measure which explains the second dip at 9:15AM.

Now I have to go tell the QA team why I’m messing with their test boxes.

Until next time,

-JRH