Voice of the DBA

Author: way0utwest

  • Visual Studio Live!–Washington DC–Save with me

    DCSPK14I’m going to be speaking this fall at Visual Studio Live, Washington D.C. edition on Oct 3-6. This is my first time attending the event, and I’m looking forward to speaking in DC.

    This is a three day conference, with a day of pre-cons, and if you’re on the East Coast, you might think about asking your boss to come. They’ve got a nice Sell Your Boss link with some reasons for you to attend.

    This is an event with a mix of technologies being covered, though mostly from a development standpoint. If you build software, consider coming to Visual Studio Live in DC in October. I’ve got a few SQL Server sessions, on DevOps and Always Encrypted, and hope to see you there.

    Register today with my code: DCSPK14

  • Dropping Masking from a Column–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    This is a quick one. As I experimented with Dynamic Data Masking for the Stairway to Dynamic Data Masking, and writing my Using SQL Compare with Dynamic Data Masking, I needed to remove masking from a column. I didn’t want to rebuild tables, and hoped there was an easy way to ALTER a column.

    There is.

    Here’s my table. I added masking to this table, but this is the scripted DDL.

    CREATE TABLE [dbo].[OrderHeader](
    [OrderID] [INT] IDENTITY(1,1) NOT NULL,
    [Orderdate] [DATETIME2](3) NULL,
    [CustomerID] [INT] NULL,
    [OrderTotal] [NUMERIC](12, 4) MASKED WITH (FUNCTION = 'default()') NULL,
    [OrderComplete] [TINYINT] NULL,
    [SalesPersonID] [INT] NULL,
PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

    Note, I have masking enabled on the OrderTotal column. If I want to turn this off, I merely run this:

    ALTER TABLE dbo.OrderHeader
ALTER COLUMN OrderTotal ADD MASKED WITH (FUNCTION='default()');

    This removes masking, and if I script the table again, I get this:

    CREATE TABLE [dbo].[OrderHeader](
    [OrderID] [INT] IDENTITY(1,1) NOT NULL,
    [Orderdate] [DATETIME2](3) NULL,
    [CustomerID] [INT] NULL,
    [OrderTotal] [NUMERIC](12, 4) MASKED WITH (FUNCTION = 'default()') NULL,
    [OrderComplete] [TINYINT] NULL,
    [SalesPersonID] [INT] NULL,
PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

    Easy to turn off if you need to. No application changes, and no security changes.

    SQLNewBlogger

    A quick, handy piece. Give a why you need this, and show how to do it. Any of you could write this in 5 minutes.

  • Defensive Programming

    At SQLServerCentral, we have a book on Defensive Programming in T-SQL. However not a lot of people have read it, or at least they haven’t downloaded it. That’s disappointing, as I think this is an area many of could improve our programming skills. Many of the techniques are simple things, or items to be aware of when writing code in order to make it more robust and reliable.

    I ran across a piece on the Enterprise Craftsmanship blog als covering this topic. That post is geared more towards C# and other OOP languages, but some of the concepts are valid for any software system. Certainly the need to guard against invalid values, especially NULLs is important in much code, and especially important in SQL code. I hope all of you understand the impact of NULLs on aggregates as well as other T-SQL commands, operators, and expressions. If you have any doubt, be sure you read about the impact of NULLs in BOL and perhaps write tests that inject NULLs into your code.

    There are places in our T-SQL constructs where we might want to develop code defensively. Checking parameters, using defaults, and watching out for ambiguous updates, and more. However, the place where I really think we want to be defensive is when we deploy changes to our production systems.

    I’ve written about idempotent code before, and all of you that develop software should be familiar with the concept. Perhaps more importantly, you should practice writing code that is idempotent to ensure that you really can write code this way. While not all deployment code needs to be written this way, there are plenty of cases where we want to include some sort of guard clause to prevent unexpected changes during our deployment.

    I’d encourage you to take a bit of time and examine the idea of programming defensively, practicing some of the techniques and making an evaluation of where this might improve your own code base. Who knows, maybe you can prevent some future bugs by learning a bit more today.

    Steve Jones

  • Dreaming of SQL Saturday

    I’m truly stunned at the responses I received to my post asking how far people have traveled for a SQL Saturday. I posted here, and at SQLServerCentral. In addition, I got plenty of emails from people, detailing their travels.

    Early warning, this gets a little long.

    I am truly stunned. In fact, when I read some of the notes and comments, I got tears in my eyes.

    Tears of joy. I’m touched by the way people feel about SQL Saturday. A few random things that stood out:

    “This was my first time attending and I would gladly attend again.  The distance is about 3000 km.  I elected to attend the SQL Saturday in Orlando and to include it as part of my vacation plans. ” 

    I asked, and this person moved their vacation plans to get to the event. That’s amazing. Someone changed vacation plans (slightly) to attend a SQL Saturday.

    (paraphrased) “As an attendee and volunteer I’’ve been to 14 SQL Saturdays, from 7miles to 266miles from my home” – This person moved, and has been to multiple events, each in the state of residence.

    Another person had gone to 2 events, both over 100mi. away. their quote: “Why? bc I love hearing the discussions and learning. Also since they are on weekends I make mini vacations out of them and do other fun activities. That may be something you may find others do as well. I have asked my company for Friday off since I am spending all my own time and money which makes it a 3 day weekend.”

    This one is sad to me: “We (3 of us) traveled 160 km twice. Two of our colleagues, from our remote site, traveled 300 km once. It is just too much, so we stopped attending.” I understand, especially in less populous areas that it can be hard to get to events like this. I have a dream, see (way) below

    This is great: “Pursuant to your article on  SQL Saturday, the controlling issue is “information and not “distance” – if the information is extremely valuable,  I would travel one thousand miles!” 

    While it’s quite exciting, I’d hope that no one would have to travel 1000 miles.

    This next response stuns me. I’ll describe it because it’s a mix of things. A person has lived in the middle of the country. There is a SQL Saturday in their city. In fact, they’ve been to that event 4 times. In total, this person has been to 19 SQL Saturdays with distances of: [21, 21, 450, 516, 251, 21, 511, 516, 319, 21, 450, 319, 378, 203, 6, 595 516, 319, 378]. Of those, they were an attendee at 16 of those events.

    Now, you might say, “see, people will go over 400 miles for an event.” Absolutely, and I don’t argue that. However, what I’d do is flip this around.

    How many events would this person attend if there were more within 100mi of their house? How many more people would get training if there were more SQL Saturday events, more often, in more locations? 

    Dreaming of 500

    I’d like to see 500 events a year in the US. I’d like to see three or four events in many cities a year. I’d like to see a thousand more speakers, just average DBAs and developers that are willing to share some knowledge. I want more, many more, many more slim, easy events. Have big ones if you like, but let’s get more training out there.

    I want more chances to speak, and learn, with the challenge of picking and choosing. I want to think of what we can do, not what holds us back.

    There’s a great scene in Apollo 13, which I bet a few of you remember. The engineers need to get the power usage to 12A from 60A. They have to find a  way to get the spacecraft back to Earth from the projected point in between the Earth and Moon where the astronauts might die.

    They are challenged to do so, with a famous quote that you may remember.

    I challenge you. I want you to think about how we make that leap to 500 events a year. I want every organizer to think about how they can create more events, not just their one big event a year. I want the events to fit a budget, not the other way around. Find a way to 500 events a year.

    I want every speaker to think about how you can inspire, prod, cajole, convince, and support another person or two in your area to speak. I challenge you to staff 500 events a year.

    I want everyone at PASS, from the Board of Directors to the staff at HQ to the volunteers that help PASS to think about growing SQL Saturdays, and finding ways to provide support. Maybe more support for some events and people than others, but take the challenge. Don’t tell why you can’t, dream of why you can. I promise you, more events will create more SQL Saturdays, and more changes to make larger, paid for events work for PASS.

    I want every attendee to share SQL Saturdays with your friends, and help grow to more events in your area, or a new area closer to you. Fifty people can make an event, if you just give back a little.

    Failure is not an option.

    It shouldn’t be. Let’s dream big. After all, who would have thought we’d get this far? Andy and I certainly used to wonder if we could actually have 12  year.

    500 events in the US in a year is crazy. It’s silly. Maybe it’s not possible, but I’d like to think we can get to 500 if you challenge yourself and just dream a little.