Category: Blog

Visual Studio Live!–Washington DC–Save with me

I’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

August 5, 2016

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.

August 4, 2016

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.

August 3, 2016
Did My Dev Changes Get to Production?

One of the things that people don’t think about often is whether the changes made in development actually get to production. Usually this is because most changes go, and unless it’s a big change that causes a problem for a large number of customers (or a large customer), we often just deal with mistakes as we can.

That’s not ideal, and certainly not what I’d like to see as a developer. I’d like to get changes to customers quickly enough that I get feedback. In this post I want to look at how DLM Dashboard can help here.

Tracking Changes

There’s a disconnect in traditional software development. Developers write code to meet some spec, and often drop that into some storage spot, hopefully a Version Control System (VCS) and eventually that code gets deployed to production. Whether a web site, client local app, mobile, database, etc., that code tends to move in batches.

Developers typically haven’t been concerned about the overall packaging of all the code, mostly because code gets pulled from multiple people, so it’s hard to track this. We depend on project manager or some deployment person to ensure all changes get deployed.

For the database, because people often manually build, or at least edit, scripts, I find there are more changes for a particular line of code to get left out. That can be bad, and maybe really bad if you deploy the database in advance, such as a day or week before other code.

How do we track changes?

How can we track changes? In a VCS things are easy to see. However, we really need to see a bundle of changes. Having a version of the database, not just the latest version, becomes important. Let’s look at an example.

I’ve made some changes in development, and my CI process pushed them to my integration environment. This shows my current database pipeline like the image below:

In this case, I’ll get a close-up of the details, but I’ve moved my Integration environment from a database v of 4.2.70 to 4.2.71. This is from a new stored procedure, as you can see below.

I can see the details of the procedure, as I’ve shown here.

I typically might not look at each CI build, though perhaps as a DBA I’d go through a series of changes across some period of time. That’s a post for another day. In this case, I usually would approve all the changes occurring in Integration environments. Eventually I’ll end up with quite a few changes. I’ll make a few more, with a few more commits and up the version a few numbers after marking this version as reviewed.

I’ve made four separate commits, and now see this in my dashboard.

As you can see, the CI process has updated the integration database to 4.2.74 from 4.2.70. However, as I dig in, I can see details. The “Review” button only gets me the changes from the most recent commit.

However, I can click “See Schema History” at the top and then click the particular version to see all the changes. Here are the first two.

I don’t care about these changes by themselves. However, I do want to be sure that the appropriate changes have made it through to other environments, especially production. Let’s deploy some changes to the Test environment. Easy for me in Octopus Deploy. Let’s deploy 4.3.74.

Once Octopus Deploy does it’s thing, I should see the same changes in the Testing environment. However, when I look at the SimpleTalk_Test database, how can I tell the changes are correct? It’s not easy.

There are a lot of objects in here, and comparing them piece by piece is a pain. Certainly I could use something like SQL Compare, but do you really want to have to double check your deployments by running another tool? Will you remember to do this? What if development has moved on, as shown below?

To run a comparison, I’d now need to track back versions in my VCS.

However, if you look at the image above, you’ll see that I know what the state of every object in my test environment is. It’s at version 4.2.74. That’s the versions I had in Integration above, and the version I deployed to Test.

Verification

If a developer isn’t sure why something doesn’t work in production, or test, or anywhere else, they can look at the changes that were actually deployed, and reference back to the state they worked with in development. In fact, they could even create a new database, and trace back the exact version of all changes in the VCS or CI server, and actually try to reproduce the issues.

Above all, a developer can tell quickly that if version 73 of the database is deployed, but the code expects version 74, there’s one thing that might need to be checked first: the changes made to the database in v74.

Hopefully you’ll see there is value in using DLM Dashboard as a developer to ensure the work you complete gets deployed correctly to later environments. If you don’t have an automated pipeline like I do, and DBAs or developers create manual scripts, it’s even more likely DLM Dashboard can help ensure all the correct code is deployed.

DLM Dashboard is free, and each instance works for up to 50 databases, so download it to day and give it a try.

Download DLM Dashboard

August 2, 2016