Voice of the DBA

Category: Editorial

  • Wow. Just Wow

    Wow. Just Wow.

    Yes, I meant to use a capital letter there. The Red Cross’ Blood Service in Australia had database backups on a website that anyone could access. That means that anyone could download the backup, which contained PII (Personally Identifiable Information) about donors. This was a mysql dump file, which is shockingly easy to restore, so anyone could have read the file inside of an hour.

    To be fair here, this wasn’t the Red Cross’ fault. A partner had put the file on a website, perhaps for developers or an analyst to download, but the web server had directory browsing available and was serving data on a public IP. That’s three major faults, and perhaps a fourth since the backup file wasn’t protected at all.

    How many of us have innocently taken a backup, put it in a location for someone else to download inside our company, and not thought anything of the action? If you have done this, would you know if the file were accessed and downloaded by the wrong person? In most cases we wouldn’t because the correct person might download the file as well and we wouldn’t think anything had ever gone wrong.

    That’s a problem with data. We don’t get necessarily notified if someone makes a copy. We certainly have no way of knowing if the backup files we create are ever restored by an unauthorized user, much less just copied. We often just assume that data at rest is just at rest and isn’t ever mishandled without our knowing about it.

    This is a good reason why we should be practicing security by default in most every case. All our backups should be protected at the very least with passwords. Even if everyone in the organization knows the password, some random person that might steal a laptop or browse an unsecured web server won’t know it. The password also shouldn’t be easily guessed, and if possible, the data should be encrypted. Always.

    What’s more, I would hope that we would use secure ways of moving information around with our fellow employees and partners. I know Dropbox is a very convenient and tempting mechanism to use, but is it really appropriate? It might be good enough if you’ve protected the data in other ways, but I’d be very, very wary of using any public service that doesn’t allow for some sort of security that can be tied to individual, authenticated users.

    No matter how you choose to protect and transfer database backups, I’d also be sure that you don’t leave the information there indefinitely. Have expiration times where files are removed. All encryption and protection can be cracked, given enough time and resources, so don’t leave your files there forever.

    Steve Jones

    The Voice of the DBA Podcast

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

  • A Joke Come True

    Yesterday was the opening of Microsoft Connect 2016. If you didn’t have the chance to watch the opening keynote, I’d recommend you do so. Mostly because one of my jokes, or perhaps a dream, has come true. SQL Server runs on Linux, and you now run it yourself. I know Microsoft has talked about this for most of 2016, and they have used it in demos, but there haven’t been public bits available.

    I’ve been lucky. In fact, I’ve been running SQL Server on Ubuntu since April of this year as part of a private program. I’ve been testing various Redgate Software tools as well as my demo code from presentations and so far everything has run. This includes the tSQLt framework and my tests that make use of SQLCLR. This includes my AlwaysEncrypted demos. I was very impressed that these features just worked, as though this instance were any other SQL Server that I had installed.

    What’s more, the installation and updating of SQL Server on Ubuntu, using apt-get, is far, far smoother than the installation on Windows. To be fair, this is a default installation, and I haven’t tried to set up all the various options and settings that are available on Windows. The various additional subsystems (SSIS, SSAS, SSRS, etc) aren’t available as well, but still, it’s a very smooth process. As I’ve updated various release candidates across the last few months, I run two commands: “apt-get update”, and “apt-get install mssql-server”.

    I don’t know if this is a good business decision for Microsoft. Time will tell, but I can’t help but think that the addition of another platform on which SQL Server can run is good for the product. More people will consider SQL Server as their database platform, with all of the powerful features and capabilities that brings to a database driven application. I suspect this will mean that many developers working in non-Microsoft environments with Linux, Java, PHP, and more will begin to consider SQL Server as an alternative to PostgreSQL and MySQL, in addition to Oracle and DB2. Certainly there is still a cost to using SQL Server, but it’s an incredibly powerful platform, one that now has a more consistent programming surface since almost all features are now available in Standard as well as Enterprise with SQL Server 2016 SP1. Getting RLS, Columnstore Indexes, In-Memory OLTP tables and more in all additions is a major win, and another of the pet peeves I’ve wanted changed for years.

    I am very interested to see how people view these changes, and if they will impact you? Do you want to run SQL Server on Linux? Since In-Memory OLTP and other features are now on Standard, are you interested in upgrading to SQL Server 2016 now? Perhaps you’re a little more excited about Microsoft and SQL Server with all of the new development changes announced yesterday? Let me know today.

    Steve Jones

     

  • Held Hostage by the Database

    Your database platform will constrain and limit the flexibility you have in evolving your software. It doesn’t matter which platform you choose, which type of database, or even the format for your data. At some point, you will be dealing with legacy data in some legacy schema or structure, and your development, and certainly deployment, will be slowed or impacted. Face it, the need to maintain state for your data is an impediment in a relational system. In a NoSQL or other store, the need to maintain code in your application that can interpret your data might be the issue over time.

    That’s not to imply this need to maintain state has to slow your development. On the contrary, there are many companies that find themselves moving quickly, able to make database changes on a weekly, or even daily, basis. There are multiple tricks and techniques for managing change, but ultimately the real secret is having a process that computers can follow and your developers adhere to.

    In other words, having some automation.

    Certainly you need to program the automation, or use tools like those from my employer, Redgate Software, but it doesn’t matter which what process you use. The process does need to be flexible because it will change over time. I can almost guarantee that the way in which you need to deploy code to the database in your environment will change over time. It has to as the needs and requirements of your business change.

    This means the way in which your developers need to build, test, and package changes will need to grow and evolve as well. While every developer and sysadmin needs to work within the process, the process does also need to be flexible as needs change. That isn’t to imply that your process should change every week, or for every deployment, but it will need to do so periodically, and hopefully, rarely.

    There are techniques to make deploying database changes easier on the developer and system administrator, but there are no magic techniques. All the tools I’ve encountered, including those from Redgate, do the same types of things you’d do manually. They just save you time and stress by helping you get set up and easily maintain your deployment tasks over time.

    Whether you use tools or not, please don’t allow the database to hold back your software development. Learn new ways to alter your database. Learn the ways to make changes to large tables. Learn how to avoid downtime. Just learn to design database changes in a better way and then automate the deployment of those changes.

    Steve Jones

    The Voice of the DBA Podcast

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

  • A New Recovery Tool for Your Toolbelt

    Those of us that administer databases and are responsible for ensuring the integrity and availability of data know that being able to restore systems is a core skill. In the SQL Server world, this should mean more than just being able to run a RESTORE DATABASE Sales FROM DISK = ‘Sales.bak’. Many of us would easily recognize that we should be able to use the WITH MOVE option. We should also understand when to use (and not use) NORECOVERY and STANDBY. Perhaps more importantly, we need to know the default option is RECOVERY.

    If we delve further, perhaps we could debate and discuss when a tail log backup is needed. Many might mention how to programmatically use FILELISTONLY and HEADERONLY to generate scripts.  A few of you would note that restoring databases means restoring logs as well and an understanding of the STOPAT option is important. Perhaps learning the ins and outs of restoring a filegroup is important in your environment. There are many nuances and options with restoring a database that a system administrator could learn.

    Apart from performing a restore, a careful DBA might think that we should try to avoid restoring databases if possible. Perhaps a DR system using Log Shipping can be used to recover data quicker than a database restore. Maybe periodic snapshots are valuable in recovering from those “whoops” mistakes. As our data sizes grow larger, we may need to develop more creative ways of recovering from user mistakes.

    Recently I saw the SQLCAT team had written a post on using Temporal Tables to recover from an “oops” mistake. If you haven’t looked at these structures, they are new in SQL Server 2016. Temporal tables, or system versioned tables, allow a user to view a row as of a certain timestamp, which can be especially useful for DML mistakes. Have you ever updated every row of a table to the same value because of a poorly written query? I have. A temporal table would have been handy in this case. Perhaps even more useful are the cases where a table is receiving regular inserts and updates, which can be very difficult to recover from with backup files.

    Certainly the majority of us don’t yet have temporal tables, and we might not even set up the option on many tables even if we were running SQL Server 2016. Over time, however, some of us will use these system-versioned objects, and perhaps we will run into a situation where recovery using temporal queries is more efficient than restoring an entire database. If that were to happen, wouldn’t you want to have some skills in this area?

    As SQL Server continues to evolve, many of the techniques and skills we’ve built across time will continue to work. There will be new ways of accomplishing work, based on features and enhancements that we ought to consider, especially when they might be more efficient than our past techniques. As you work with SQL Server, I hope you keep learning about the “new” ways that you might accomplish the tasks for which you’ve developed solutions in the past. Sometimes the old way might prove to be a better choice, but you won’t know that unless you learn, practice, and measure the effects of a new method.

    Steve Jones

    The Voice of the DBA Podcast

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