Voice of the DBA

Category: Editorial

  • Disaster After Disaster

    Running out of diesel in a disaster is not something you want to happen.
    Running out of diesel in a disaster is not something you want to happen.

    There was a large hurricane in the US a short while back. It was a devastating storm for many people, and my heart goes out to those that suffered or are still suffering. There are lots of lessons to be learned in many areas, but a few surprising ones for those people that run technology infrastructures. A number of data centers were shut down because of physical flooding, but others were shut down after electrical substations failed and they were unable to run their generators.

    When I evaluated data centers a decade ago, I was always shown the number of UPSes on site, and the high capacity diesel generators with large fuel tanks that were available just in case of extended outages. Salespeople would bring out their contracts that showed suppliers would commit to refilling their fuel tanks, providing for every contingency.

    Except a lack of diesel. In Denver we wouldn’t have the need for a staircase bucket brigade, but we might have the need for a roadside chain gang carrying containers in a blizzard. You cannot plan for every contingency because there are many factors out of your control. When a disaster gets large enough, it doesn’t matter what you have contracted for. There will be outside influences, like the lack of elevators or the inability of trucks to physically reach your location.

    One of the Red Gate customers was in New York and almost lost their data center after the storm. They asked our technical team to help them prepare scripts to restore data backups for their clients in the event they had to send the full, diff, and log backups (along with application code) to the customers. It was an last-ditch effort to allow their customers to continue to run their service. Fortunately they never had to use any of the scripts, but it did help the company realize they need to build more options for business continuity in the event of future disasters.

    I hope none of you ever experiences anything like Hurricane Sandy. You can’t completely prepare, but you can practice your recovery skills on a regular basis and be prepared to respond when disaster strikes.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Test Your Restores

    The ultimate testers.

    I was talking with someone the other night about their database systems and they mentioned they had implemented TDE (Transparent Data Encryption) to comply with HIPAA regulations. This person had verified that they were backing up the Database Encryption Key, which you definitely need if you want to restore a backup from a TDE encrypted database. However they weren’t sure if the certificate that protected the DEK, and the master keys on that instance were being backed up. Probably most scary to me, they hadn’t tested any restores of the database.

    Encryption is serious business, and if you are going to implement it in your databases, you had better be sure you understand how the various keys and certificates work. You better be sure you have protected your passwords, and that you can find them in the event of some issue.

    Most importantly, though, is that you need to practice recovering your database to another instance. Preferably you’d learn how to recover on an instance that hasn’t ever enabled encryption as well as one that has a different SMK or DMK.

    Practicing restores isn’t just about encryption and the potential for data loss because you don’t have a key. Practicing restores is important for all of your systems to be sure you have the skills to successfully complete a restore. It helps ensure you know where the files, tapes, disks, or any other resources are located. Most importantly it ensures that your backup process is actually running smoothly.

    Please don’t assume your backup process works. Whether you’re an accidental DBA stuck with their first SQL Server, or a ten year senior DBA that has performed hundreds of restores at previous jobs. You need to test your process and ensure that you can perform restores on the systems you are managing.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Grace Under Pressure

    Grace Under Pressure
    How will you react when things go poorly? Will you maintain your composure?

    I once worked at a large, 10,000+ person company. We had a large data center with hundreds of machines, where we one day we lost power. Not power from the electric utility and had our UPSes and generator kick in. We lost power when some maintenance caused all of our UPSes to trip off line and cut power to all the servers.

    I was in the data center, surprised by the sudden quiet. Unfortunately one of our senior executives was also in the data center and proceeded into the raised floor area. As various technicians and sysadmins attempted to restore power and reboot systems, this senior executive watched, commenting, questioning, and often berating the employees. Not a good situation for anyone, least of all the people trying to reconnect high voltage wires together.

    Most of you will never experience a large disaster and need to recover your systems. Even fewer of you will recover from disasters with anyone other than your peers or a direct manager watching you. However you shouldn’t count on being that lucky. Whether the disaster is small or large, your fault or a natural occurrence, I hope that you are able to successfully restore your systems with some professionalism and grace under pressure.

    The key to a strong performance in a stressful situation is the same in technology as it is in sports, music, and almost any other endeavor with an audience. They key is practice.

    Simulate disasters, pretend that refresh of a development system is really a restore after a fire. Think about the various possible scenarios that might require you to recover a system and incorporate practice time into your daily routine.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • The Default DBA

    When we setup SQL Server, there are a few choices we need to make, some of which require a few items be prepared in advance. Service accounts are one of those preparation tasks that DBAs usually perform in advance of installing an instance. There are a few things, however, that aren’t configured when SQL Server is installed, like alerts and operators. I find that often these items can be missed by inexperienced DBAs, who forget to configure standard alerts. They might also not have a mail account ready, which is required for the instance to send mail.

    Perhaps there’s a better way for SQL Server to request this information when it is installed. If one part of the setup program asked for default mail information, it would be easy to pre-configure the instance to setup alerts and enable them for communication with a DBA. Personally I’d prefer that setup get a mail profile and a default DBA group email address that receives information about the operation of the instance that the DBA should be aware of.

    There can be a hesitation from experienced DBAs that don’t want setup to install mail for them, but it would be easy to make this an optional component, with a checkbox like the one for a blank sa password. You actively choose to install the server differently if necessary, but otherwise setup would push DBAs to ensure that notifications are configured. We could even see a pre-selected list of alerts that will be enabled, with the option to disable all or just selected items.

    I don’t know that Microsoft will build these types of defaults into SQL Server, but I wish they would. We’ve done that with SQL Monitor at Red Gate, and we have seen the advantages that come when you provide good practices and knowledge to less experienced DBAs: they gain some knowledge transfer and have software running at a high level from the very beginning.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.