Voice of the DBA

Author: way0utwest

  • Learn tSQLt and Unit Testing in Philadelphia

    SQL Saturday #390 is in Philadelphia on June 6, 2015. This is my second time attending the event, and not only am I speaking on Saturday, but I’ll be delivering information on Friday at a pre-con.

    My first SQL Saturday pre-con is taking place in Philly. I’m lucky to be asked by Sebastian Meine to co-present a day of training. We’re delivering a precon on Unit Testing in SQL Server with tSQLt. It’s available for the low price of only $99.99 now, with the price rising to $149.99 when the event begins.

    We’ll be covering quite a bit of the framework, showing you the ways in which you can write tests with examples and code that you can run that day. We’d like you to bring a laptop to write tests along with us, but we’ll have the code available later if you want to test back at the office. Here’s our agenda.

    • Introduction to Unit Testing
    • What is tSQLt?
    • Your First Test
    • Executing Tests correctly
    • Effective use of Assertions
    • Separation of Concerns
    • Testing Exceptions
    • Test Case Heuristics
    • Dealing with Test Data
    • Other Types of Testing
    • How Unit Testing fits into your Development Process

    Sebastian is the owner and developer of the tSQLt framework, which I’ve been using quite a bit over the last few years. It’s an amazing project, and really expands the ability of SQL Server developers to build better code and prevent regression issues in their applications. Quite a few SQL Server MVPs and highly successful consultants are using the framework, and I’d urge you to give it a try.

    If you’re in the Philadelphia area and want to jumpstart your testing knowledge, consider coming to our pre-con. It’s a great way to rethink how you write SQL code and put testing in place in a fairly low impact fashion.

    Register today

    Database Source Control

    If you’re in town on Thursday and want to learn about Version Controlling your databases, Redgate is also working with Ike Ellis to put on a full-day workshop covering Database Source Control. It’s $500 for the day, and you’ll get some in-depth knowledge on how to work with SQL Server code and version control as well as participate in hands-on labs that will give you the skills to implement the processes yourself. Find out more..

  • Idempotent

    I ran into the word idempotent in the Stairway to Integration Services. I had heard the word, but I hadn’t really considered how important it can be for a DBA or developer until that time. It’s a term used in computer science, as well as other sciences, but I think it’s one that many of us don’t consider when we’re writing code, especially code used to deploy software to other systems.

    Most of us have written scripts like this:

    if exists (select object_id from sys.objects where name = 'uspGetSales')
  drop procedure uspGetSales;
create procedure uspGetSales
as
...

    At the end of running this code, we have the system in a state. If we run this over and over, we’ll regularly return to the same state, which is often exactly what we want to occur. That works great when deploying code, but what about this:

    insert into states select 'CO', 'Colorado';

    If I run that multiple times, what will happen? Either I’ll get errors if one of these fields is a PK, or I’ll get multiple inserts. If I’m running this as part of a software deployment, do I want either of those conditions? Do I want my end user to experience either one?

    No, I don’t. Certainly if I’m manually making changes to systems I can probably avoid issues, but that’s not what I want to do. Maybe you do, but I don’t. I’d like to be able to restart my deployment if something fails, without causing other issues. I’d like idempotent code, like this:

    if not exists (select abbrev from states where abbrev = 'CO')
  insert into states select 'CO', 'Colorado';

    That way if I happen to run this code twice, or ten times, I arrive in the same place.

    I realize that building scripts and deployment processes that are idempotent is a pain. It’s work, but it’s also scriptable and repeatable work that is easy to automate over time with a few patterns. I also realize that a little extra work to prevent issues is often an investment that’s worth making for both my customers, and my reputation.

    Steve Jones

    The Voice of the DBA Podcast

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

  • 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.