Voice of the DBA

Author: way0utwest

  • Creating a Server Audit – SSMS

    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.
    Audit_001
    Right click this and select “New Audit”
    Audit_002
    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.
    Audit_003
    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”
    Audit_006
    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 – Call for Speakers

    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.

  • Internal Marketing

    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.

  • Getting a Page of Results

    The other day I was looking over a couple of articles on paging, looking to see if I could learn something new in T-SQL. I’ve implemented some SQL2000 era paging systems, none of which performed wonderfully, so I checked out Jacob Sebastian’s basic Server Side Paging and Paul White’s Optimizing Paging Part 1.

    I’ve done systems similar to Jacob’s, but he had an interesting use of the OVER clause in his code. He had this code:

    ;WITH emp AS (
  SELECT 
    CASE 
      WHEN @SortOrder = 'Title' THEN ROW_NUMBER()OVER (ORDER BY Title) 
      WHEN @SortOrder = 'HireDate' THEN ROW_NUMBER()OVER (ORDER BY HireDate) 
      WHEN @SortOrder = 'City' THEN ROW_NUMBER()OVER (ORDER BY City) 
          -- In all other cases, assume that @SortOrder = 'LastName' 
      ELSE ROW_NUMBER()OVER (ORDER BY LastName) 
     END AS RecID,   , LastName
   , FirstName
   , Title
   , HireDate
   , City
   , Country
   , PostalCode
 FROM employees

    This is a great solution in SQL Server 2005. It’s much different than what I had done in SQL 2000, where I’d typically approach the problem by using the sort key to get the next page.

    So say I had this data in a table (Customers):

    CustomerID    Customer

    ———–   ————

    1             Jones

    2             Smith

    3             Johnson

    4             Allen

    5             Gates     

    Then suppose I wanted page 1, 2 results per page, ordered by Customer. I would want to see “Allen, Gates” on page 1. I’d use this code.

    select top 2 Customer

    from Customers

    Order By Customer

    If I wanted page 2, I’d go here:

    select top 2 Customer

    from Customers

    where Customer > ‘Gates’

    Order By Customer

    And this would get me “Johnson” and “Jones” since the WHERE clause would reset results. If you have control of the application code, and you can pass in the previous values, you can easily build pages like this.

    If you switch orders, say to the CustomerID, then you can easily pass that in as well, and use that for ordering.

    There is a downside, however. Any ideas? I’ll post that in my next look at paging.