Nov 2012 – The Modern Dataist

Goodbye to the DBA

I\’ve been in this game for some time now, but I feel that a career change is in order.

Being a DBA has taught me lots of excellent skills, and I\’m a bit sad to be leaving that behind, but gone are the days when it was just about backups and logins and performance tuning gnarly T/SQL statements and procedures.

So, I\’ve decided to become a Data Professional instead. I\’m not actually leaving my current place of work, I\’m not moving between departments, and I\’ll still sit at the same old battered desk, but Data Professional is a title that more reflects what DBAs have to do, and it\’s where the job is going, to be honest.

Back in the day (a phrase I particularly dislike) SQL Server was an RDBMS, plain and simple. To some it was a dark art, and many people I knew at the time were terrified of the thing.

But it was just that – an RDBMS, with rules that had not changed since God was a lad. No frills, just a relational data store.

Adding DTS and Reporting Services to the product stack changed things a little, and SSIS and Analysis Services altered the scope again, but the basic rules of engagement were still the same. Sharepoint was seen as an irrelevance by some die-hards, and was little trusted beyond being an online document repository

However, with the release of SQL Server 2012 (and to some extent SQL Server 2008 and Sharepoint 2013), the game is up.

In today\’s data-driven world, it\’s not good enough to sit behind your RDBMS any more. The world is waking up to the power of data insight, the opportunity to divine hidden realtionships in your \’Big Data\’ (another annoying neologism).

The modern DBA must be far more than the DBA at the turn of the last century. There is a need for them to be an expert in all things data, and that is a bigger and scarier world than ever before.

So it is with a great deal of excitement that I make this career change from DBA to Data Professional. Actually, it\’ll be nice to have a job titile that you can use in social situations with no need for long and protracted explanation of what I do 🙂

There are some challenges ahead, I\’m sure, but I\’m really looking forward to it.

I hope you are too.

Back soon….

Monitoring SQL Server Notification Services – some comment and some advice.

Wow. How much fun has the last few weeks been ?!??

Short answer: Not fun at all.

Long answer: Great fun, if you\’re into supporting deprecated platforms with scant few internet resources at your disposal.

SQL Server Notification Services first sprang into life with the release of SQL Server 2000. It was aimed at multi-channel delivery of event-based subscriptions, and as such has been largely superceeded by things like Reporting Services Data-driven Subscriptions, SQL Server Integration Services, and Sharepoint.

Sadly, with the release of SQL Server 2005, Notification Services was deprecated, and disappeared entirely by the time SQL Server 2008 was released. There isn\’t even an node in the Object Explorer in SQL Server 2008 Management Studio. That being said, if you have Notification Services anywhere, you probably know about it already, and you\’re also probably looking at removing the product from your production estate.

It\’s not that the product itself was bad, you understand – it was excellent, but I\’m guessing the guys at Microsoft decided that there was a better way of doing it in existing products, so there was no gap for the product to fill. Either that, or they decided that an offering as opaque and labyrinthine as SQL-NS had no future in a pared-down internet-aware world.

SO, as you may be able to tell, there\’s not much love for SQL-NS these days. At least, none that I could find anywhere. Sure, there are useful descriptions of the object model and some info on performance monitoring views on MSDN and Technet, but other than that, niente. It seems to be the forgotten tech that no-one talks about – the ginger step-child of SQL Server, if you will.

Having spent some of the last few weeks bogged down in getting SQL-NS to tell me EXACTLY what it\’s been doing, there are a few things that I now know:

1. Retries are VERY important.

If SQL-NS decides to stop distributing for any reason, you\’re going to want it to have another go as soon as possible.
The retry interval is specified in your application\’s .ADF file, looking something like this –

                        P0DT00H05M00S
                        P0DT00H05M00S

Important to note here is that the retry delay is NOT circular. The above results in only 2 retries, each starting 10 minutes after the last failure. If you want more retries, you add more elements.

2. A status of \’Distribution Failed\’ DOES NOT mean that everything was rolled back, or that nothing was sent.

\’Distribution Failed\’ means that there were some notifications that could not be delivered, not that they could not be sent. As long as the number of notifications generated by a batch matches the number sent, you win. In this case, it\’s likely that you have some malformed email addresses that could not be delivered. Review the Distribution Log for DeliveryStatusCode 4.

                SELECT * FROM dbo.NSDistributionLog nl
                        WHERE nl.DeliveryStatusCode = 4
                ORDER BY nl.SentTime DESC

3. You can change the amount of information sent to the Windows Event Log by altering the SQL-NS environment start up file to change the logging level for each operation.

This file is normally in <>\\Program Files\\Microsoft SQL Server\\90\\NotificationServices\\9.0.<>\\Bin\\ and is called NsService.exe.config. Logging is set at the ,, node, with values ranging from 1 (being not much) to 4 (being firehose mode).

In this example, we have some detail on the Distributor, but not much on the other elements. You can change these to log the required amount of detail you require, then simply restart your SQL-NS instances to start using the new values.

These settings are implemented at the SERVER level, not per application. If you have multiple SQL-NS applications on one server, ALL of them will apply the logging level changes once they are restarted. This can lead to a huge amount of noise in the Event Log if you\’re only troubleshooting one application out of several.

4. The \’performance reports\’ baked into the product are a bit rubbish.

Actually, when they\’re all you\’ve got to go on, they\’re OK, but they can take a while to execute, and may not give you information in a format that\’s easily understood.

SO, what can we do… ?

To get more timely information, and to enable me to have a history table with useful information in, I wrote a small query that looks at all the core tables for reporting back the current status of a given SQL_NS application. This script is run on an application that has only 1 \’channel\’ (delivery protocol), but you can extend the select to include the channel name should you require it.

SELECT            nwi.BatchID                                         AS [BatchNumber],
                           nnb.NotificationCount              AS [Generated],
                           COUNT(nn.NotificationId)                     AS [Sent],
                           nwisc.[Description]                AS [Status],
                           nwi.NumberOfDeliveryAttempts       AS [Attempts],
                           CASE
                                        WHEN nwi.NextRetryTime > GETDATE()
                                                AND nwi.DistributorWorkItemStatusCode NOT IN (1,2)
                                                THEN \’Retry Pending\’
                                        ELSE \’No more retries\’
                           END                                                         AS [RetryStatus],
                           nwi.FirstStartTime                 AS [Started],
                           nwi.LastEndTime                    AS [Ended]
                FROM   NS<>EventEvents ne
                           JOIN NSRuleFirings1 nf
                                        ON nf.EventBatchId = ne.EventBatchId
                           JOIN NS<>NotificationNotificationBatches1
                                        nnb
                                        ON nnb.RuleFiringId = nf.RuleFiringId
                           JOIN NSDistributorWorkItems nwi
                                        ON nnb.NotificationBatchId = nwi.BatchId
                           JOIN NSDistributorWorkItemStatusCodes nwisc
                                        ON nwisc.DistributorWorkItemStatusCode =
                                                nwi.DistributorWorkItemStatusCode
                           JOIN NSCCSSNotificationNotifications nn
                                        ON nn.NotificationBatchId = nnb.NotificationBatchID
                GROUP BY
                                nwi.BatchId, nnb.NotificationCount,
                                nwisc.[Description], nwi.NumberOfDeliveryAttempts, nwi.NextRetryTime,
                                nwi.DistributorWorkItemStatusCode, nwi.FirstStartTime, nwi.LastEndTime

This returns data that tell me the most recent batch numbers, how many notifications were generated in that batch, the number of notifications actually sent (or attempted to be sent), the current distributor status for each batch, whether there are any retries pending, and the start and end times of the whole batch process.

Which is a LOT more useful that a slap in the face with a wet cod, or even anything else… 🙂

Back soon…