Voice of the DBA

Tag: Database Weekly

  • Why We Need Backups First

    One of the things I show when I talk about TDE is that the backup of the certificate is very important. When you create a certificate, SQL Server warns you right away that a backup of the certificate is needed. Following Murphy’s Law, if you continue on with enabling TDE, right after the encryption is completed, your master database will become corrupt and you won’t be able to get to your data. It just takes a few seconds, so back up your certificates.
    I was reminded of this while reading a short piece on some police body camera footage being lost. I don’t want to focus on the police actions or cameras or anything but this statement: ” A backup system had been purchased but hadn’t been set up. “
    That’s a fairly egregious breach of duty for some IT personnel in this case. The system was installed in 2009, and this event apparently occurred during a software upgrade in 2012. While I understand a backup system might not get configured immediately, I think 3 years stretches the limit of an acceptable delay. However, even if the backup system were not in place at the time of the software upgrade, wouldn’t a backup before upgrade be required? Isn’t that a standard practice?
    There have been other reports of similar issues, which is disconcerting. It appears the use of technology for archival of data isn’t well developed, or properly understood by the IT staff in charge of the process. Of course, this happens in plenty of enterprises and other organizations where IT staff seem to think that backups are important in and of themselves. In case you’re confused, they are not. Restores are the important things to have, which means you must verify your backup process allows the data to be retrieved.
    Many of us have scripted methods that practice this regularly, refreshing other environments, restoring databases for DBCC, or some other task. Few of us actually practice this ourselves across all databases on a regular basis, ensuring our skills for tail log backups, NORECOVERY and STANDBY restores are fresh and ready for emergencies. However, we should not take this task lightly. It is important that we are able to recover data in almost all situations.
    If we can’t do that, then we are truly disregarding our duty as the custodians of data.
    Steve Jones

  • Paying for Data

    Would you recommend your organization pay a ransom to get the key to decrypt data that’s become encrypted on your database systems? What if the data were encrypted in backups going back two weeks?

    It’s an interesting question, and one I might expect we will have to deal with at some point. This week I saw an announcement that another organization, this time a university, paid a ransom to recover data. Before you react with “of course, not,” think about this quote from the University Vice-President:

    “We are a research institution,” she was quoted as saying. “We are conducting world class research daily and we don’t know what we don’t know in terms of who’s been impacted and the last thing we want to do is lose someone’s life’s work.”

    That’s a powerful statement, and certainly one that I could see salespeople, accountants, and more making as an appeal to upper management. What’s interesting in the piece is that the university acknowledges that using the decryption keys is a complex endeavor, requiring IT to get systems operational again. I hope that at the least they also made new backups of the encrypted data before using the keys. Who knows what sort of results come about from criminals that have encrypted, and the offered to provide reversal instructions and keys.

    I expect the ransomware efforts to become more prevalent across the next few years, especially as people have success. Even if fewer people pay ransoms, I bet plenty of people will look to disrupt organizations with these actions. Perhaps even a competitor will look to introduce ransomware into your company? Maybe by offering a job to an individual that leaves such a surprise behind as they get ready to move to a new employer.

    There are all sorts of issues here, and potential attack vectors. The fact that ransomware (or other malicious software) could be introduced to remain idle, or even transparently encrypt and decrypt systems until a date passes and the keys delete themselves, is horrifying. At the very least, I think this brings to mind the idea that we need multiple versions of backups, so mirroring a file system to another location, or some cloud service like Azure, Dropbox, etc., isn’t enough. Perhaps we should also have checks in place that look for changes. After all, can you imagine a rouge process implementing TDE and letting it run for a month before deleting the certificates from your system and truncating all the tables a week later?

    Steve Jones

  • Install Cumulative Updates

    For years I haven’t recommended installing Cumulative Updates (CU) as they are released. The main reason is that Microsoft has had language associated with their CUs that say customers should not install a particular CU unless they are experiencing the specific issues the CU corrects. Each CU has (in the past) also noted that you should stick with the latest Service Pack otherwise. That disclaimer has been enough for me to be concerned about CUs in a general sense, despite the insistence that the CUs were tested as well as SPs. My thought was that if the testing was the same, that disclaimer wouldn’t exist.
    Well, things have changed. The latest CUs have this language in the section where a KB says that Microsoft recommends CUs as they are release:
    • SQL Server CUs are certified to the same levels as Service Packs, and should be installed at the same level of confidence.
    • Historical data shows that a significant number of support cases involve an issue that has already been addressed in a released CU.
    • CUs may contain added value over and above hotfixes. This includes supportability, manageability, and reliability updates.
    That’s good news, though as Kendra Little notes, you still need to test. Bugs will still exist in patches, and really all software, so it’s important that you test in your own environment. That means you need a test plan that can easily run, preferably an automated test plan. If nothing else, this is a good reason to use tSQLt and have tests written for your system. At least you can verify important queries and systems are working. Kendra has a good list, so read her post.
    While I think the quality of CUs is up and they are probably as safe as most of the Windows patches we get every month (and are often installed no matter our individual feelings), I’d still be wary. If you can’t test, if you’re busy, if this is a month you can’t afford for things to fail, then don’t install the CU. This is like throwing your own developers’ code into production without any testing. Make sure you know what is being changed, and you look for obvious problems. No one will be too upset of an obscure issue, but if your scheduled jobs start failing, you’ll dramatically reduce the confidence people have in you.
    I am still wary of all patches. They’re disruptions, to both your routine, and potentially to availability as well. Make sure you test, but if you have the time, I’d say keeping up with patches is worth doing. Microsoft is constantly fixing issues, and you want to take advantage of their hard work, if you can verify the patches don’t degrade your system.
    Steve Jones

  • Power BI Week

    This past week was T-SQL Tuesday #75, with the topic of Power BI. There were some interesting posts, and we’ve included a few in this week’s newsletter. This was also the week where the winners of the Power BI report contest were announced, and it was a good entry. This was the one I actually thought was the best one. If you haven’t seen it, take a few minutes and go look at the entry and play with the data. It’s interactive, so be sure to see how well the Denver Nuggets have competed against Mr. Curry.

    I first looked at Power BI last summer, watching a presentation and being intrigued at the ability of the tool to easily get data from web pages. I then talked with a friend that works with Power BI regularly, and they showed me how to loop and gather information from many pages, easily assembling the data into a dashboard that was interactive. I was very impressed, and I’m looking forward to doing a bit more work on the platform myself.

    Quite a few people have been concerned that the development of tools like Power BI mean that there is less of a need for IT staff. I find myself thinking quite the opposite. There will always be data quality issues with something like Power BI, and there is a certain level of programming to build more complex reports. Not to mention the refresh and update process is slow, which can make the tool feel cumbersome.

    However users will find some reports are more useful, or more important, and need some help from technology professionals. Whether that’s developing more DAX formulas and measures to enrich displays or just providing stable, clean data sources that consolidate data from various places, there will be plenty of work for IT staff that support companies using Power BI.

    To me, Power BI is the type of tool that empowers users and lets them experiment a bit on their own. Once they determine what they really want, then they can engage me to build a more robust, reliable, and useful tool that helps the business.

    Steve Jones