Voice of the DBA

Category: Editorial

  • Can Auditing Fail?

    I saw someone’s list of requirements for a SQL Server auditing system recently that included many of the standard items most of us would want. DDL changes should be captured, along with configuration alterations. The performance impact should be low, and all versions of SQL Server must be supported. However, there was one requirement that surprised me. This person noted that the auditing must be asynchronous and the application should not be dependent on the auditing. If auditing failed, the application shouldn’t have any of its transactions limited or rolled back.

    I’m sure there are use cases where this is important, and where the auditing might not be critical, but the auditing data is available for informational purposes to troubleshoot any issues that relate to the database. However in many places where auditors review information, or the data is part of a legal record, the auditing cannot fail. If it does, then the application needs to stop working. If an audit is truly an audit of activity, then all activity must be audited.

    The C2 audit mode in SQL Server has been deprecated. Probably for multiple reasons, but It did seem that the idea that a failure in auditing could stop the database wasn’t a setting many people were willing to implement. To me, this means that auditing isn’t as important as having the system continue to process data. If that’s the case, then is auditing that important?

    I know auditing data can be overwhelming. I know that the management of audit data, including archival, is complex. I also know that most of the data isn’t very useful and will never be examined. However when we need audit data, we really, really, need accurate audit data. I really wish that Microsoft would integrate auditing better into SQL Server to ensure the data can be easily managed, compressed, and archived in an automated fashion.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Complete Definitions

    I saw a script from a user recently that looked like this:

        Create db x on file =
    go
    alter db set compatbility = 120
    go
    alter db set containment = none
    go
    ...
    alter db set service_broker off
    go

    That’s interesting because most of the code I see looks more like this

        create database x;
    go

    That’s it. At first I thought this was overkill, but then I wondered. If we are actually creating a new database for an application, shouldn’t we set the options the way that our application needs them rather than rely on defaults? I know that often many of us work with defaults on our instances and databases, but should we expect all configuration options to just be at their defaults? I know I certainly have been burned in the past with default changes.

    I’ve seen similar scripts for code, with SET ANSI_NULLS and other settings in the script. While I sometimes think that code gets in the way, I know that the script contains the settings that work for the code I’ve written and tested. If I remove those settings, it’s entirely possible that my code might not work. Do you know all the ways in which the various SET parameters for code affect your code? I’m sure that many of us don’t think about these changes when we’re writing code, assuming that the dev, test, and production servers will be the same as our database. In most cases they are, but not always.

    There’s a trend towards explicitly declaring the settings for an environment and then letting the system ensure that it maintains those settings when it’s built, or perhaps every so often. If that’s the case, and someone makes a mistake, say on ANSI_PADDING, do we really want to assume none of our code was compiled with those settings? I’m not sure I do, and I suspect that we should be explicitly putting all our settings at the top of scripts to ensure code behaves as we expect.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Finding Problem Code

    Performance of a SQL Server seems to be one of the main concerns for most developers and DBAs. We are under pressure to constantly squeeze more performance out of our applications and reduce the time it takes for users to interact with systems. Reports can be especially problematic as users don’t want to wait for results, but certainly data entry tasks can’t be impacted either.

    Whether or not we can actually make changes to the system, or even have time to bother might be irrelevant. I’m sure there are plenty of databases that some of us don’t even bother to try to tune because we’re busy elsewhere. Likely writing more code for additional features or reports that have been requested. As a result, we may pile up lots of code that isn’t necessarily run often. However when that code is executed, we’ll receive no shortage of criticism if the code doesn’t perform well.

    This week I wanted to know how you might go about finding the code that is problematic if there isn’t a complaint. If you do have time to pro-actively tune your system, what are the techniques you use to examine a system. I suspect the more advanced people will have answers, and I hope they share them as there’s no shortage of readers that may struggle to improve the impressions of their database server.

    What’s the best way to find the SQL statements you need to tune?

    It’s a simple question, but imagine that you know there are complaints, but don’t know what is the best way to focus your time. You, as a new or accidental DBA, want to improve the system, but where do you start? What code is the problem? Is the report that your boss complains about that’s the issue or are there other queries that slow down the report execution?

    Share the way you’d tackle a system when you don’t have a specific query in mind to tune.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Is It Time to Upgrade?

    There was a post from the SQL Server Data Platform Advisor the other day that noted it’s been almost 10 years since SQL Server 2005 was released and it’s time to upgrade. At least, if you want to remain in support. Microsoft Support notes that support ends on Apr 12, 2016, which is just under a year away. While that might seem like a long time, it can take months to get an upgrade approved, planned, and executed, so if you think you’ll want to upgrade before support ends, you definitely should start planning.

    However, do you need to upgrade?

    That’s an interesting question. I would assume that if you have SQL Server 2005 in your environment, it’s been running since sometime before the end of 2009. It’s possible you installed 2005 after SQL Server 2008 was released, using the downgrade rights you have. I expect that you might have done that for a year, but I’d hope that if you were installing new instances after 2010, that you were using 2008 or 2008 R2.

    Given that assumption, I’m guessing that you’re relatively pleased with how SQL Server 2005 has been performing. At least I’d think you are. If you’re not, then why haven’t you upgraded? If it’s budget, then does support ending really make a difference? If whoever approves purchases has been satisfied with performance, why upgrade now? If your database backs a third party application, will you have to upgrade that code as well? If so, another reason it might not be worth making a change.

    We haven’t really seen patches for SQL Server 2005 in a long time. There have been two hotfixes and one security update since the last Cumulative Update, and the last one was in 2012. While I do think it’s possible that more security bugs might be found, the likelihood is low. Chances are that if you’ve been secure this long, you’ll still be secure for a few more years.

    If you are continuing to develop your code, however, there are lots of improvements in later versions of SQL Server that you might take advantage of. T-SQL has continued to grow and progress, with new features. Replication has improved, as have some of the manageability of the servers. Certainly security and encryption have advanced, so those could be reasons to consider moving to a more current version. The wealth of information available for tuning your system, as well as more efficient code and a better query optimizer might lead you to consider both a hardware and software upgrade.

    While there are some compelling reasons to upgrade with greatly improved performance and many more features that can help improve development, if you have systems that are working then is it really worth the licensing cost? Especially now that SQL Server is licensed by the core and not socket. Working is a feature, and one that I do think is worth considering as part of your decision.

    Steve Jones

     

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.4MB) podcast or subscribe to the feed at iTunes and LibSyn.