Voice of the DBA

Author: way0utwest

Startup Jobs
Are there things you need to make sure are running when your SQL Agent starts? Did you know that you can run a job when the Agent starts?

I haven’t often used this, but there are cases where it was handy. In one system I administered, we wanted very fast inventory lookups for one of our groups. I know that we could have easily let the first few people just wait for their queries, but it constantly generated complaints from workers, and the VP in that area didn’t technically understand why. Explaining buffer pools, and caches weren’t an option.

Our solution was a quick fix, which “hid” the problem. When the instance started, we had a startup procedure that ran, running a few queries that would load lots of data into memory. By the time people could connect to the server, it was usually warm, and complaints went away.

A similar feature exists for SQL Agent. When you build a job, you can select a schedule that runs when the Agent starts. Imagine that I have a procedure that logs when my agent starts. I want this to log the datetime when the agent starts. I know it’s in the errorlogs, but if I have a lot of restarts in a short time, I could lose that information.

I can create a table and procedure to log this:
```
CREATE TABLE Startups
(
  StartDate DATETIME
)
GO
CREATE PROCEDURE spStartAgent
AS
INSERT Startups SELECT GETDATE()
RETURN
GO
```
If I create a new job, I can enter a step that runs my job.

I do that, and then click the scheduling tab (in SQL Server 2008 R2). Once I do that, I can drop down the Schedule type box, which defaults to “Recurring”.

In the drop down, the first selection is “Start automatically when SQL Server Agent starts”. If I select this, all the other fields on the form get disabled.

I named this Agent Startup, so I can re-use this schedule if I need to. Now this schedule will run my job every time the Agent starts.

If I restart my Agent (not my instance), and check my log, sure enough I see a note in my table that logs startups. However the execution of the job isn’t logged in the Agent error log.

Perhaps another good job would be to copy over the most recent error log into an archive folder, one where a hacker might not think to look. I could even set the rights for the agent to only allow files to be created, not altered or deleted. That way I could retain the file for auditing purposes.
August 2, 2012
Forty Five Minutes

Excellent: Dilbert on data analysts

The best part is the last quote: I like how you punctuate your ignorance with certainty.

August 1, 2012
Password Resets

I read this piece from Troy Hunt, which is a long look at the password reset process for a web application. It’s one of the first that I’ve seen which talks about the different implementations, along with the pitfalls and advantages of each.

It’s a great look at passwords, and there are definitely a few things in there I think should be built into authentication frameworks. I know we need to change a few things at SQLServerCentral and I’ve added them to the list.

Pass this one along to your developers. They should be aware of this stuff.

July 31, 2012
Creating a DDL Trigger
One of the most amazing features to an old SQL Server 4.2 guy was the addition of DDL triggers to the server. As with any trigger, these can be problematic in that they can overload a server, and they ALWAYS fire, so you can cause yourself problems, but in terms of auditing, I think they’re great.

As a quick example, perhaps you’re worried about new logins, as I talk about in my AlwaysOn and Contained Databases in SQL Server 2012 presentation. You want to capture when a new login is created. You can do this with a DDL trigger like this one:
```
USE master
GO
CREATE trigger CatchLogins on ALL Server
for CREATE_LOGIN
as
declare @data xml
set @data = eventdata()

SELECT @data
```
That doesn’t do much, but if I run this code:
```
CREATE LOGIN Delaney WITH PASSWORD = 'test'
```
I get this result:

Not overly helpful, but if you click on it, you see the event data as an XML document
```
<EVENT_INSTANCE>
  <EventType>CREATE_LOGIN</EventType>
  <PostTime>2012-07-23T11:47:41.427</PostTime>
  <SPID>62</SPID>
  <ServerName>SEVENFALLS</ServerName>
  <LoginName>SevenFalls\Steve</LoginName>
  <ObjectName>Delaney</ObjectName>
  <ObjectType>LOGIN</ObjectType>
  <DefaultLanguage>us_english</DefaultLanguage>
  <DefaultDatabase>master</DefaultDatabase>
  <LoginType>SQL Login</LoginType>
  <SID>Djwpf8IHNUicam9m2DkoBQ==</SID>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE LOGIN Delaney WITH PASSWORD = '******'
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>
```
I can parse this out and store it. What do I want? Probably I want the server and object, the date for tracking, maybe the creator, but definitely the SID. The text doesn’t help since it doesn’t have the password. All I can do then is go find the user or admin and ask them to recreate this login on the secondary servers.

Let’s start parsing. You have two choices here with the XML: the .data or .query methods. There may be more, but that’s what I know. I’ll parse in two ways here:
```
ALTER trigger CatchLogins on ALL Server
for CREATE_LOGIN
as
declare @data xml
set @data = eventdata()

select 
  @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
, @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(1000)')
, @data.query('(/EVENT_INSTANCE/ServerName)')
```
This returns some data.

You can see the .query returns XML, which (to me) is a hassle. So I’ll stick with the .value clause.

I would probably create a table here that stores this data. If I used a generic table for multiple types of audit data, I’d need to include the type of event as well. You can just use the first XML document for different audit types to see what’s returned, and then deal with it as appropriate.
July 31, 2012