Voice of the DBA

Author: way0utwest

Someone Lost My Data

It was bound to happen. I’m sure it’s happened before, but this event was interesting to me as it wasn’t a customer issue, but a cloud vendor problem. There was an outage in Azure on Jan 29, which happens, but in this case data was lost. There was problem internal code in the Azure cloud that dropped some customer databases using the Azure Key Vault with TDE encryption. That was slightly disconcerting for me as I was setting up and testing Azure Key Vault this week.

There are frequent snapshots and Microsoft was able to restore the databases from one that was about five minutes old. Microsoft acknowledges that five minutes of data loss might be an issue and is asking customers that lost business or were affected by the drops to raise a support ticket. I have found Azure support to be pretty good about crediting my account when issues occur, and I hope they do the same here, though I wonder if they’ll compensate anything beyond the charges normally assessed to customers. They are offering credit for the restored and original databases for a few months as well.

Plenty of people are upset, and with good reason. There should not be Azure management code that drops databases. Or should there be? Would this be any different on premises?

I’ve had cleanup code that removed resources after some time. I don’t work at the scale of Azure, so I usually have things removed after a month or quarter. With the scale of Azure and potential costs, they might remove resources quicker, but I certainly have seen similar home grown, if-this-then-that code that does x when y occurs. In this case, I remove encryption keys, which might be not actually be removed for month or more. At that point, there is code that trigger a drop of databases. I’ve certainly seen users in various organizations drop, or restore over, the wrong database. In some cases they don’t realize it in five minutes, and often don’t have a way to restore from a five minute ago backup. Actually, relatively few people I know have RPOs under five minutes.

This is bad, but it isn’t necessarily out of the ordinary for complex IT environments. If this happened in an organization, the IT staff would be worried and hoping for forgiveness. Some people would want others fired, but most of the time management would understand these things happen. Perhaps not if this isn’t the first time, but usually we accept some people make mistakes. Most of us don’t have complete control of all aspects of our environment. We depend on network staff, storage people, employees that manage hardware, and more. It’s possible that any one of these people could destroy data inadvertently.

That happened here, though I don’t want to make excuses for Microsoft. They’re supposed to hire the best people and build processes that are better than what I’d expect inside an organization. Events will cascade into different areas, and there should be circuit breakers that prevent anything that could cause data loss in those events. Protect other people’s data with more care than you think you need. It’s your responsibility to do so.

Steve Jones

The Voice of the DBA Podcast

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

February 14, 2019
Adding Objects to our SCA Project

In my last post, I started out with a new SCA project, getting a connection to my database up and running. Now, let’s start the new project by adding some objects.

Adding Objects

I’m going to start with a couple objects to make this simple, but we’ll move on from there in other posts to describe the options and details.

Our database is not terribly useful without tables, so let’s create a table. I’ll start by using a query window to build a table. Right click the database and choose New Query.

Once this opens, let’s write a query. I’m comfortable building tables like this, so I’ll add the first table to hold my SQL Server builds. I’ll write the code shown below and then execute this.

At this point, the development database has a change, but it’s not in my project. I need to use the SCA pane to let VS know that I’m ready to import this change. Open the pane and click refresh. The following should appear when the process completes.

In this dialog, we see a new table was found. The process compares the Shadow database, where no changes have occurred, with the development database, which has a new table. This gives me a “diff” in essence and I can add this to my project.

The change is checked on the left, so I can click the Import and Generate Script button. I’ll do that and I see the image below. There’s a lot here, so let’s discuss this after the image.

The first thing to look at is the script that’s active in the upper pane. If you notice, the tab name is on the right side of that upper left pane, highlighted in blue. This is my first migration script. You can see the migration ID at the top and then a PRINT statement, which is useful for logging during the build or deployment process, then the top of my code.

On the right side, in the Solution Explorer, we see that my script has been added to the Migrations folder. The default name has been applied, but I can rename this if I want.

In the SCA pane, we see a few things. First, the change is grayed out because the change was scripted. Above that, we have a yellow bar that notes a migration was generated, and it gives us the path in the project. Finally, there is a “Refresh” button to verify the script. We want to click that. This will use our script with the Shadow database to verity the code is correct. Once I click that, I get a note that the project was verified.

At this point, the Shadow and development databases are in sync.

This tells me that my changes should work when I deploy them to another system. Hopefully that works, and we’ll tackle that next time.

February 13, 2019
Google’s Expensive Confusing Rules

I worked through the Y2K crisis. I call it a crisis because so many of us were worried and millions, probably hundreds of millions of dollars were spent by companies trying to ensure their software systems would work when the date changed to 2000/01/01 00:00:00. I was on call that night, celebrating the new year at home, not drinking because I was slightly worried my paranoid boss would call me in. He didn’t and there were few issues around the world in systems, perhaps because of the build up of the crisis and lots of prep work. I went through a similar set of concerns when the Sarbanes-Oxley act was passed in the US. Not much came of it, and companies spent a lot of time and money preparing.

Over the last two years, it has been deja vu as the GDPR moved towards the enforcement date last May. Lots of companies, including my own employer (Redgate Software) were concerned and spent time and resources getting ready. The enforcement date came, with some early complaints being filed, but few fines. Perhaps the preparation paid off, but more likely it just takes time for audits to occur and complaints to be investigated.

It appears that the first big fine has been handed down to Google. Recently the CNIL (the French data protection regulator) fined Google 50 million euros for not compliance with the GDPR. Their reasoning was that Google didn’t provide enough information about their data consent policies and didn’t give users enough control. They complained that the data is spread out in many different places and too difficult to understand. Other countries are investigating, and Google is going to appeal.

Personally the presentation and dissemination of information from Google should be top notch. That’s their job: to search, assemble, and present data. As someone that has paid for Google Apps and email, has used Google Analytics, and generally tried to understand some of the other products, I think too many engineers and not enough technical writers work at Google. I think they are surprisingly bad at making it easy to understand how to accomplish some task, including finding out some information about my account or my data. I’m not surprised that they were fined, since I think they have had an opt-out philosophy and the many different groups inside the company have considered the data gathered to belong to Google, not the human about which it refers.

I don’t know how this will play out, but as a person, I do hope that companies will be asked to clearly disclose what data they have, how they use it, and to delete it when the business purpose is complete. I’d prefer that I had more control and understanding of my data, including the crazy cookies and other mechanisms that track my browsing across sites. While some companies use this to provide customization, there is plenty of potential for misuse here, and I’d like it to stop.

As a data professional, I’d like to have clear understanding of how to treat and protect data. I’d prefer that we better secured it, didn’t use live data in development environments, and we built better habits as technology professionals. Data is truly and asset, and one that can easily help businesses grow, but it also has the power to be abused. I’d like that to stop with sensible rules that I can work within.

Let me know how you feel today.

Steve Jones

The Voice of the DBA Podcast

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

February 13, 2019
What is my why?–T-SQL Tuesday #111

This is an interesting question from Andy Leonard for T-SQL Tuesday: What Is Your “Why”? He is this month’s host and asks us to talk about our motivation, our passion. The question is: Why do you do what you do?

Why I Do What I Do?

I have had a great career in technology. Moving from developer to system administrator to developer to manager, and back through various incarnations. I have had success in lots of positions and helped improve systems and code, teaching and mentoring others along the way.

Almost twenty years ago, I participated in a user group and helped someone solve an issue. That inspired me to start writing articles that detailed how to build solutions that had helped me and my employer. Over time that led to SQLServerCentral and regular speaking and writing.

My career in databases really started for the money. I worked with a DBA that was better paid than any sysadmin or developer, and began to study and work with SQL Server. As I earned more, I thought I’d made a good decision, but over time I realized that there was a point where money was less important to me.

Time became the most important thing to me.

There have been many times where I would have traded more time for less money, though that isn’t quite the case now as I have two children attending college next year. Still, in the middle of my career I wanted to, because of the value that I placed on time with family and friends and living my life.

I started to give back with writing, speaking, teaching because I had people do that for me, and because I wished I’d had more. I wished that more people had shared solutions and ideas with me earlier in my career.

I also believe that we fundamentally ought to help others in life when we’ve been helped or been successful. We ought to pay it forward where we can, but more so we need to pay it back. It’s a bit of a social contract with others, a bit of a desire to be the rising tide that lifts all boats, and a bit of a moral obligation I feel to help the world because I’ve been blessed with success.

Everyone walks their own path, makes their own decisions, and follows their own moral code. I try to follow mine, and I hope to inspire others to help others where they can.

It’s why I do what I do, and I’m lucky that I’m able to do so, both as my job, but also because I am able to help others.

February 12, 2019