Voice of the DBA

Tag: syndicated

Silly Photos at Work

Someone I stumbled across this picture of @brento on my drive.

This might be the best one:

And this was a good time:

However, I have a collection of silly moments. Enjoy.

July 20, 2018
My #SQLCareer Day–July 19

I’ve asked for #SQLCareer posts, and I haven’t done any. My job isn’t a data platform DBA or developer, but it is SQL related, so a glimpse of the nonsense I perform. I’m asking for more posts, so a note at the bottom.

This should have been a cool day, after all, I’m scheduled for my first (partial) presentation at Microsoft’s Inspire conference. It turned out to be not so great.

The day started with my waking up in the Tropicana in Las Vegas. I was a last minute addition to the Inspire staff, so I grabbed the nearest Hilton. I travel enough that it’s worth me to try and stick with one airline and hotel company. As I write this, I got two free nights for my wife and daughter in South Dakota this week for a volleyball camp.

However, I get up around 7 and pack up. As I’m doing so, my daughter calls. I took her car key while she was gone to get copies made. A minor crisis distracted me on Tuesday afternoon and her key is in my car. At the Denver airport.

I apologize for the hectic, chaotic nature of my life and for not putting it back. I make mistakes, and know my kids do as well. I expect them to apologize, and I should do the same.

I check emails and answer a few, then shower and pack up.

7:45am – Walk across the bridge to catch the tram from the Excaliber to the Manadalay Bay resort. It’s got to be 90F this morning already, and I find the first station is blocked off for construction. Great.

Walk to street level and start heading to the next station. I get there and don’t see an easy way up. It’s a coin flip to walking inside and looking for a way or crossing the street to the Luxor. Everything is far in Vegas, and I elect to walk. I get into the Luxor and it seems as easy to use the internal walkway to the Mandalay as it is to wait for the the tram.

7:52 – Starbucks. Priorities, yo.

8:05 – Inspire registration. I had problems on Wed, so I needed to complete this today. I start, click to pick my MS account login, and when I click the “Next” button, the internet disappears. Fortunately the event staff was helpful and brought me a laptop to complete registration.

8:30 – The Inspire schedule is a bit of a mess. I’m not sure where to go, but pick my co-speaker and he texts me directions to the room. I head over and sit down, watching a few other Microsoft partners talk about their companies and strategies.

I have 3000 steps today, but I see a couple interesting things that partners are doing. One of which might be something we try at Redgate. In any case, it’s an interesting half hour. Then I head over to an empty room to practice my talk with co-speakers, go over timing, and review my notes.

9:45 – We’re on. There are three of us talking about SQL Operations Studio, which seems to be Ops Studio to MS. I have 15 minutes at the end, so I stand around for a bit. Me on stage, just waiting around. The professional look today.

While waiting on stage, I check Slack and respond to a few messages. I do my talk, get one question, and then walk out. That’s after I’ve finished my coffee.

11:00 – The schedule seems to be just some general partner stuff to close the day. I ping a friend for lunch, but he’s down the strip at another hotel. With a 1:30 flight, I decide to give up and get a cab to the airport.

Not wanting to waste time, I forgo an Uber and get a cab. The guy talks a slightly long way around the West side of the airport, which is annoying. It’ s not my money, but at Redgate, we try to spend wisely. This feels like I should have stood around waiting for an Uber. I could have answered a few emails while waiting. I did that in the cab, but likely I didn’t save anything other than a few minutes standing and wasted $5.

In any case, I get to the airport, walk to security, get through and make my way to the terminal. I do this enough that if I don’t try to be efficient, I waste lots of time. Fortunately from taxi drop off to sitting down for lunch is about 15 minutes for me. I sit there and go through a few product items on Slack, and respond to a question on the Redgate Hub. Nothing that important, but it’s the type of busy work that will catch up to me if I don’t get things done.

Post lunch I still have about 45 minutes before boarding, so I pull out the laptop, charge my phone, and start this post. I also answer a few emails and load some Database Weekly items for the newsletter. It’s my week to get links and write an editorial, so I try to do a little of this everyday. I find it keeps me in touch with what’s happening in the database world.

One article is on Managed Instances in Azure and how they are different. It’s interesting and I learn about the Job Object, which I knew nothing about. I always seem to learn a few things every week that I didn’t know from someone. I also caught up on a couple SQL Career posts from Jen Stirrup and Brent Ozar.

The flight home is fine, but it’s cramped. I don’t do any work and just read a bit and watch part of a movie. I seem to see parts of movies here and there, but never bother to finish if they’re not that interesting.

6:30pm – I hit the gym on the way home for 30 minutes and then get my daughter’s keys made and then some chores at home.

7:45pm – One last check of things and finish this post. I realize I’ve scheduled tweets for the next three newsletters all tomorrow. Grrr, I delete them all and need to rebuild the schedules. Takes about 30 minutes.

Not a productive workday, but since I travel again Friday to SQL Saturday Louisville, I’ll let it go.

July 19, 2018
Data Masker for SQL Server and Temporal Tables
A customer had a question recently about using Data Masker for SQL Server and temporal (or system versioned) tables. I decided to make a quick demo that will show how this works.

This is a simple demo, using just a few changes, but the concepts are useful for larger systems.

Setting up a temporal table

In the demo database for Data Masker, there is a dbo.dm_customer table. I’m going to make this a temporal table with a few schema changes. First, I’ll add two columns that will be used to store the valid datetime ranges.
```
ALTER TABLE dbo.DM_CUSTOMER

ADD StartTime DATETIME2(3) GENERATED ALWAYS AS ROW START
         DEFAULT GETUTCDATE(),
     EndTime DATETIME2(3) GENERATED ALWAYS AS ROW END
         DEFAULT CONVERT(DATETIME2(3), '9999-12-31 23:59:59.999'),
     PERIOD FOR SYSTEM_TIME(StartTime, EndTime);

GO
```
Next, I’ll enable system versioning, and specify the history table.
```
ALTER TABLE dbo.DM_CUSTOMER SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DM_Customer_History));

GO
```
At this point, let’s test. I’ll query the history table and show it’s empty.

Now, let’s make a couple changes.
```
UPDATE dbo.DM_CUSTOMER SET customer_firstname = 'Jillian' WHERE customer_id = 1000002

UPDATE dbo.DM_CUSTOMER SET customer_firstname = 'Sian' WHERE customer_id = 1000004

INSERT dbo.DM_CUSTOMER ( customer_id,customer_firstname,customer_lastname, customer_gender, customer_company_name,
     customer_street_address, customer_region, customer_country, customer_email, customer_telephone,
     customer_zipcode, credit_card_type_id, customer_credit_card_number)

VALUES

(   '1000', 'Steve', 'Jhones', 'M', 'Redgate', '123 My St', 'CO', 'US',
     'sjones@sqlservercentral.com', '30333333333', '80014', '1', '12`345566')

GO
     

UPDATE dbo.DM_CUSTOMER
      SET customer_lastname = 'Jones' WHERE customer_id = 1000

UPDATE dbo.DM_CUSTOMER
      SET customer_lastname = 'Jilly' WHERE customer_id = 1000002

UPDATE dbo.DM_CUSTOMER
      SET customer_lastname = 'Jill' WHERE customer_id = 1000002

GO
```
We requery, and you can see there is data in the history table (at the bottom). All of my changes are captured in a sequence of copies of the row.

There are a few more rows in the history table, but they got cut off in the image. That isn’t that important. Just note that the names match up, for the most part, between the tables.

Using Data Masker

Now the interesting part. I have a masking set that’s similar to ones I’ve used in other articles. In this case, I’m changing names in two substitution rules. However, these rules are set for the dbo.dm_customer table. What about the dbo.dm_customer_history table?

To change this, I need to do a few things. First, I need a Command rule to disable system versioning. This is the way I disconnect the tables so that I can update data.

This has to take place before I change dbo.dm_customer, otherwise I’ll insert a bunch of new data in the history table. My ordering looks like this:

Once this is complete, I now need a Table-Table sync rule to ensure data in one table matches the other. When I create the rule, I need to have a way to join the two tables together. In this case, I use the customer_id column.

Note that I’m ensuring the names and credit card numbers match here. This will wreck my history table slightly, in that all of the rows for a customer_id will have the same value, but this does ensure I don’t have sensitive information leaking.

Once I’m done, I make this a child of the other rules. This ensures that the substitutions run on the main table, then are synchronized to the history table.

You’ll notice I also have another command rule at the bottom. The text for this rule is below, and this rule enabled the temporal link again. I’m assuming the application is somehow using this. If not, you can ignore this rule and leave the tables disconnected if that’s Ok in your application.

All I need to do now is run the rules. Let’s do that.

Once the rules are complete, let’s query the tables again. I’m only looking for those rows that were changed previously.

As you can see, the dbo.dm_customer (top) table and the dbo.dm_customer_history table (bottom) are synchronized. The multiple values I had for different versions of customer 1000002 are now all the same. I’ve lost history, but if this were sensitive information, that works out. I could have used random values instead for the history table, but here I can see linkages if I’m checking for similar data.

This is a Hard Problem

Trying to determine how to rewrite history for audit tables is hard. Data Masker can do pretty much anything you would like, but here we need to be sure that we have an idea of what we want to do.

I’ve presented a simple version of setting all history to the current value, which will work in some cases. If not, let me know what you need to see, and I’ll build you a scenario.

Data Masker for SQL Server is an amazing tool in the SQL Provision bundle. Combined with SQL Clone, you can deploy databases with masked data for dev and test environments in seconds. Give it a try today and see what you think.
July 18, 2018
Is this a SQL Provision Cloned Database?
As I work with SQL Provision, I keep finding new questions and concerns from clients and customers. Recently I had someone wonder if we could determine whether or not a database on which they were working was a SQL Clone cloned copy.

You can, and it’s easy to check. When SQL Clone creates a database, it will use the base image, and ensure there is an extended property set on the database itself.

The function sys.fn_listextendedproperty() is used to return the database extended properties. We can use the DEFAULT keyword for the various parameters, like this:
```
SELECT objtype ,
        objname ,
        name ,
        value

FROM fn_listextendedproperty(DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

GO
```
This gives me an empty result set on a non-SQL Clone database if I have no extended properties set. If I had others set, I might get some result. For a database I’ve setup with SQL Provision, I’ll get this:

For the most part, I don’t care that I’m using a clone rather than a native SQL Server database, but there could be places I do care, and certainly I want to filter out this extended property from my version control system.

SQL Provision is a great tool for rapidly giving new environments to developers without the hassles of restoring copies and using lots of storage space. If you want to give it a try, download an evaluation today.
July 18, 2018