Category: Uncategorized

Building an audit in SQL Server 2008 is very easy, much easier than in previous versions using SQL Trace. That was painful, and I am glad that it’s not required anymoer.
Building a server audit consists of first setting up the audit. You do that in SSMS through the following steps. First find the “Audits” folder until the Security folder.

Right click this and select “New Audit”

That gives you the main dialog, where you can specify a number of options. First is the name, which isn’t important. It just allows you to identify each audit.

The Queue Delay is used to determine how the audit is recorded in the target file. If this is set to 0, then you have synchronous auditing. Audit records are immediately written to the target location. The default is asynchronous auditing, for performance reasons. This value is in ms, and defaults to 1 sec. This determines up to how long audit records can be held in a buffer in memory. There is a potential for audit record loss if the server crashes here. The minimum delay is 1000ms (other than 0) and the max is 2,147,483,647 (24 days, 20 hours, 31 minutes, 23.647sec)
The next option is the “Shut down server on audit log failure.” This is important if you need to ensure no actions can occur if auditing does not work. If this option (off by default) is checked, then if there is a write failure to the audit target, the server is shut down. Note that if this is checked, the login creating the audit must have the server shutdown permission, then the function fails.
The drop down allows you to choose the target location. You have three choices:

• a file
• The Windows Security Log
• The Windows Application Log

If you choose one of the Event logs, no other options are needed. If you choose the Security log, there are some additional configuration options (coming in a later blog). If you choose file, then you specify other options.
The file path can be specified and the SQL Server service account must be able to write to that location. You don’t get to choose the file name. That is automatically generated in the following format.
<audit_name>_<audit_guid>_nn_<timestamp_as_bigint>.sqlaudit
Once you pick the file name, you have to choose how large each file gets (maximum file) and the maximum number of files (Maximum). The sizes can be specified in MB, GB, or TB. The minimum size if 2MB and the max is 2,147,483,647TB. You can specify an unlimited size, which grows until the disk is full. You can specify the number of files as unlimited as well.
The last option is the RESERVE SPACE option. If checked, it reserves the space on disk of the file size specified and preallocates that to the audit file. This is only used if the maximum size is not set to unlimited.
By default, the server audit is disabled, meaning that no audit records are recorded. Since we haven’t specified any audit specifications at the server or database level, nothing will get recorded anyway. To turn the audit on in SSMS, you need to save the audit and then right click it and select “Enable Audit”

As I mentioned, without any audit specifications, nothing will get recorded. In another blog I’ll look over creating specifications and viewing audit records.

SQL Saturday #49 in Orlando just opened their call for speakers. I’m debating if I want to go out there. The event is on Oct 16, 2010, and this is always one of the larger SQL Saturday crowds. I have a lot of friends there, but I was hoping to get to SQL Saturday #45 in Louisville on Sept 25, just a few weeks before. With two weeks of travel in Nov with PASS and Connections, my travel schedule is getting booked.

I’m debating about going down there since this would be my second time there. If I have to choose, I’ll go to Louisville to a new city instead of an old one, but I’d like to get to both.

I heard this recently at my company. Our group, which is relatively removed from the main business of writing software, doesn’t always have the best relationship with everyone else. I think some of it might be that they don’t understand what we do, and some might be that they don’t see us doing anything often.

My boss was talking about how we might want to do more internal marketing. In other words, presenting some information about what we do, and how we help the business, to other groups.

That sounds strange, and if you have a small company, you might think that everyone knows what each person does. And they understand it.

However that’s not necessarily true. Often people get caught up in their work, and when they’re stressed or overworked, they might feel that others aren’t working as hard. Or that they are doing more than their share, even if everyone does a different job.

It’s natural that anyone feels that way. Just go do some manual labor, like moving boxes. If you work fast and hard, after a short time you’ll be a little annoyed if your partner doesn’t keep up.

Internal marketing is something that you ought to do periodically. If people get along well enough to do this informally, it is more helpful. Venting about your work, issues, or even successes over a drink after work goes a long way. If the workers don’t do this enough, think about buying lunch for everyone and having some informal presentations from different groups. Or even just letting them chat, but teach them to market their job.

It doesn’t have to be impressive or spectacular, but just show how the efforts are relevant to the business. I need to work on some sort of presentation to show what I mean.

Recently I was at a UK user group meeting and Simon Sabin of SQL Skills presented a short “SQL Nugget” on UDFs and some of the issues with performance. He then showed a better way using a TVF and mentioned that in many cases the CLR can perform on par with system functions for simple code.

A person in the audience mentioned that it wasn’t that simple. Many companies might have concerns over deploying CLR code in assemblies to their SQL Servers. He cited that there was concern about actually having someone write “code” that runs inside SQL Server.

I’m concerned about the CLR, but not necessarily from a stability standpoint. As much as I think there could be performance issues, I don’t think that the code written by a developer or DBA and installed in SQL Server means that the core engine is necessarily less stable. Sure there may be issues with their code, but are they more serious than the issues of crappy client code? Or poorly written queries?

After all, we deploy code all the time on our servers, and it might be just as poorly written. I’ve seen sp_prepareXML cause issues, and I’ve seen XPs, provided by Microsoft, lower the stability of servers.

So is it just CLR being a black box that worries people? Do they have CLR concerns about .NET assemblies being deployed on IIS?

And if they are, then shouldn’t that mean that better training, code reviews,and extensive testing are needed?

The CLR can be a great choice in some cases. It’s a specialized tool, one worth looking at, but not necessarily the one for all situations. Just be aware that you may have a bit of a battle if you choose to use it on your SQL Servers.

If you have deployed the CLR, let me know how it went and what challenges you faced.