Voice of the DBA

Category: Editorial

  • How Far Back?

    At the Redgate SQL Privacy Summit recently, there was panel I hosted that I thought was really interesting. At one point someone asked a question and there was a bit of a discussion about the right to forget in the EU. One of the fascinating things wasn’t about the rights, but about backups. One of our panelists noted that we often keep too many backups, most of which are really useless.

    This portion of the discussion started by asking how many backups people keep. Many of those in the audience kept many backups, sometimes dozens. Many of them had some rotation scheme that determined how many files were kept over time. I know I’ve worked in places where we kept over a week or two worth of backup files, often along with a monthly or quarterly backup for a year and then multiple year end backups in perpetuity. I guess since we didn’t really know how long those backups were valid as we just marked some tape as the end-of-year one and left it in a vault somewhere.

    What was also interesting is the panelist asked if many people had restored a backup more than a month old. Almost no one had, but a few had restored a year old backup. I think that’s common, and I find I’ve only restored one backup more than a month old. That one was actually for a legal case, and it was six or seven years old. I actually had to find someone at Microsoft that could provide us with an old version of an SP to restore the database.

    I’m wondering today about the rest of you. Not if you have done it, but would you do it today? How far back would you go for a backup of an important database? Assume this isn’t any requirement to find the data at a point in time, but for recovery purposes in a disaster.

    For me, I’d expect that most of the systems I’ve managed really would have struggled to go back a week. After that point in time, so many transactions are missing that there are serious repercussions. This is why I’ve often ensured I had automated restores happening all the time, since I need to be sure that we can restore from yesterday or two days ago. If I went back more than a week, it would be because we had corrupt backups. While the business would probably survive, I’m not sure my job would. If I hadn’t been able to produce a workable backup for the last week, I think most organizations would be justified in terminating me.

    Whether they would or not is another debate.

    Steve Jones

    The Voice of the DBA Podcast

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

  • No More Downtime

    At one point in my career, I worked for a wealth management firm. We managed funds for various customers, a part of which was making trades in the financial markets. Various brokers and companies used our platform to run their business, and performance was always an issue. At the time we were an NT 4.0/SQL Server 6.5 shop, though we moved to Windows 2000 and SQL Server 2000 while I was there.

    While we were looking at upgrades, a number of potential customers asked why we weren’t using AIX or Solaris or Linux. Management would come to a few of us technical leads to ask, and we usually had to provide some justification. Our success was hit and miss, though we did run into a few companies that were doing real time trading on the Windows platform, and a few of us had the chance to talk to them about how they managed their systems in an age when Windows hosts often needed patching, a requirement of which was rebooting. If you’re interested, the company actually had a fairly server oriented architecture built on top of Windows, essentially managing work by just connecting to whichever boxes were running.

    Things have changed. These days Microsoft has been working hard to build a better Windows OS to power Azure and they’ve done some amazing work. I saw a post on one of the things they’re doing, which is finding ways to patch the underlying OS without disturbing applications, including VMs that are running on the host OS.

    The work is amazing to me, and this is where we should have been going with all operating systems. We ought to be able to patch these without downtime, and certainly without disturbing guests or programs that might be running. As this rolls out to Windows hosts in our data centers, I could imagine an era when we have monthly patches for Windows that never cause downtime for SQL Server.

    Well, I guess there is some downtime. Applications such as the hypervisor are paused, which some of us might consider downtime. However, if the time frame were in the single digits of seconds, I’m not sure many clients would this as downtime. It’s along the lines of a network hiccup or a momentarily busy server. In some sense, this would be a great move forward for HA.

    On the other hand, this would raise expectation. Clients and customers would be less tolerant of downtime, which is something we can’t control with Windows and SQL Server patches. However, it is something we can control with our deployments. A system that is almost never down because of patches would put pressure on us to ensure that our enhancements to the database itself also didn’t cause downtime.

    While there’s no magic in how we make changes to objects in SQL Server, there are techniques that can make changes in stages, perform additive work, and use automation in a DevOps style workflow to limit downtime and lower the risk of making changes. To me, this might be a bigger change than cloud systems. With that in mind, I’d urge many of you to learn techniques to avoid interrupting applications when you deploy changes. There are ways to do this, but it takes some effort and practice to build better skills and habits.

    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.

  • Are you GDPR Ready?

    The GDPR is being enforced as of yesterday. It’s been a law for two years, but fines are now going to be assessed for violations. If you’re like me, you’ve been getting a lot of different types of privacy policy updates, new opt-in requests in email, some notices in email with the burden on you to opt-out, and more. A few people joked about how many they’ve been getting, and certainly I’ve seen no shortage of updates. I’ve even seem some updates to services that don’t allow access to content, such as YouTube, without clicking some accept button.

    It’s interesting to see the various approaches being taken. Last week in London, Redgate held a SQL Privacy Summit and I was honored to host a panel discussion from various industry experts. They had different takes on the GDPR, though most of these people were pro-GDPR, happy that some proper data handling was being enforced. That’s the attitude that many DBAs in know in the EU, as they now have some legal reasoning why we should implement better data handling and security practices.

    However, I’ve also seen that there are different interpretations of how to deal with data. Do you need to ask all customers to opt-in? Can you continue to use data in development and test environments? Can you process data as you already have if you disclose what you’re doing? Is the burden on the company or the data subject? I’m sure we’ll see various decisions and rulings from regulatory authorities across the next year as data subjects complain and companies try to do the minimum level of work.

    The idea of data being somewhat co-owned by a business and an individual is fascinating  I see both sides, and I certainly would like to have some rights over data about me. I definitely think my address, my date of birth, and more should be secured and companies that use my data should have some liability if it’s disclosed. I’m not sure about rights over how it’s used, but that’s certainly a discussion that’s coming.

    I’ve already seen one organization file suit over access to data, because they’re being forced to consent to handling that they disagree with. That is going to be something I watch carefully. Can a company change their terms arbitrarily, ask me to consent in a take-it-or-leave-it fashion, and withhold access to data? Do I own my messages and data stored in services? Is it co-owned?

    Like it or not, the GDPR is forcing us to have some discussions and debates about digital information, which is good.

    We’re mostly ready at Redgate, and certainly continuing to do work. Ultimately, our reading of the GDPR (with some backing from auditors), is that we don’t have to be perfect today, but we need to be making an effort and be able to prove that we are doing so. So on a day after the GDPR went into enforcement, what are you doing? Do you think you’re ready?

    Steve Jones

     

  • Republish: The Opportunistic Hacker

    Still in Ireland: The Opportunistic Hacker.