Voice of the DBA

Tag: disaster recovery

  • Two Days Off

    I almost couldn’t believe this when I saw the article. The Verizon Cloud is shutting down for 48 hours. Apparently they have maintenance scheduled for this weekend and notified their customers that their virtual machines will be shut down early Saturday morning. There are some legacy Verizon cloud-type services that will be available, but the platform they’ve been pushing to customers will be down.

    This isn’t good news for Verizon or their customers, but it also doesn’t help the cloud overall as a service. This outage reinforces the idea that reliability isn’t necessarily better for vendors than individuals. If costs for the cloud are anywhere near that of on-premise hosting, this event would certainly make me think twice about moving anything really important in my organization to a single cloud vendor.

    I suspect that most cloud vendors have outages like this, but they don’t shut down their entire clouds. When a large amount of maintenance is needed at a data center, most vendors would migrate customers to a separate data center or another part of their cloud while they perform their work. Either this mainenance is a major change to Verizon’s entire infrastructure that can’t be staged on just a part of their system, or they’ve poorly planned their architecture and maintenance.

    Either way, this weekend will certainly be a good DR test for enterprises that might have important applications hosted with Verizon. It will also be a chance to test how these clients notify their own customers of potential issues or how they respond to problems. I wouldn’t want to have an application hosted with Verizon as it would be a lot of work for me and likely a weekend away from family, but I know I’d learn a lot about how well I’ve prepared my own systems for fault tolerance.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Test Your Situation

    I gave a talk on Transparent Data Encryption (TDE) recently and a number of people in the audience were using the feature. However when I asked how many of them had restored a TDE database, not all hands remained up. When I asked how many people had restored their TDE encrypted backup  to a different server, one that didn’t have TDE enabled, very few hands remained in the air.

    That’s not good, and I certainly hope those people don’t experience a disaster from which they cannot recover. I’m sure they are not alone. I suspect that many of the people managing a TDE database have restored a database this year, and are confident they can do so. However what they don’t know is if they can restore those TDE databases on any other instance, including a newly installed one.

    They’re not alone. I see many, many people implement features they don’t really understand. Microsoft has made it easy to set up replication, clustering, and more in your environment, but without providing some of the robustness and reliability that many people need. The ease of setting up a feature is one thing. The ease of ongoing management and recovery when issues occur is something else entirely.

    I really wish that Microsoft would go further than making implementation easier and include direction for ongoing tasks. When databases are created, ask the user to set up backups and help them create the jobs. When encryption is implemented, do more than display a warning message. Help adminstrators prepare for recovery with templates or jobs that automatically build certificate or key backups. When replication is set up, include a script to rebuild the environment for when it breaks.

    I doubt we’ll get this, and many companies and employees will continue to implement features they don’t understand. You can only help your own situation, and you should be ensuring you understand and can rebuild all the extra features you’ve installed in the event of a disaster.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Drop and Recreate

    Those of you that manage replicated environments have learned to have one thing handy: a script that recreates your replicated publishers, distributors, and subscribers. I was reminded of my past needs for those scripts recently when I saw this post on dropping and recreating all the synonyms for a database.

    It’s easy to depend on backup and restore to recover from issues, but how often do you face problems with an environment that aren’t related to data? If you lose a stored procedure, or have a problem with the configuration of jobs, or principals, can you easily drop and recreate an object? That code is usually tiny, but if the only copy you have is in a backup file, you have to restore a lot of data just to get some code.

    Certainly everyone should keep all this data in version control, and I’d encourage you to be sure that not only development code (tables, views, stored procedures, etc) are kept in there, but also configuration settings, jobs, roles, and the various other things a DBA is responsible for in a production environment.

    However I’d also go one step further and ensure that you have scripts to recreate all aspects of your environment if you need to do it. Many of the comparison tools will let you store a snapshot of database schema items in a folder and then easily help you recreate a script if needed. That covers the database, but for the instance level items, you need to be sure you have an easy way to checkout a copy from version control (you are using version control now, right?) and execute the scripts on a SQL Server. You can use T-SQL, PoSH, or even VBScript, but be sure you have the code handy.

    Do you?

    Steve Jones

    The Voice of the DBA Podcast

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

  • A SQLServerCentral DR Event

    We had a disaster at SQLServerCentral this past weekend. It wasn’t a big disaster, but it was an event that required a restore of data.

    An Administrative Error

    On Saturday, I was attending SQL Saturday #331 in Denver. In between my sessions, I was prepping a few demos and finished getting ready earlier than expected. Since I had a few minutes, I checked my email and immediately knew we had an issue on the site.

    We’ve been fighting spam for months, slowly tweaking our posting system in the forums. With the start of the American football season, we’ve been getting hundreds of posts every Friday and Saturday. I’ve tried to ensure these posts are removed before our newsletters are generated so that they aren’t filled with advertisements, but it’s been a chore.

    One of the things I can do in the forums is select a series of posts and mass delete (or open, close, hide, etc) the group. For most of the SPAM posts we receive, the posts all occur in the same few minutes and are grouped together. I’ve gotten in the habit of deleting these batches of posts, watching for a legitimate SQL Server post at the end.

    However on this day, one of the our regular threads was buried in the middle of all the SPAM posts. This was THE Thread, the most active and long lived discussion on the site with 45k posts. I inadvertently deleted the post and went on to give my presentation.

    Afterwards, I got a private message from the site, telling me the discussion had been deleted around 2:00pm MST.

    Quick Reaction

    The first thing I did when realizing what had happened was connect to our production database cluster through VPN. When I opened Management Studio, I ran a few queries to verify the discussion had been deleted, and not just “marked for deletion”. Logical deletes exist in many applications, and if this is the case in your own disaster, the last thing you want to do is initiate a database restore.

    In this case, the data was gone, so I immediately tried to initiate a restore. Since over an hour had passed, I didn’t want to restore over top of the current database. Instead, I wanted to restore a copy as a new database, as of 1:45pm or so.

    I selected the proper options, marking the full backup from overnight and the log backups throughout the day. I didn’t have time to worry about using STOPAT and trying to get close to the actual time of data modification, so I choose the last backup I knew would be good. Verifying the database name was a new name, I clicked OK.

    And nothing happened.

    Actually, that’s not true as I got an error. The backup system in use by Rackspace, our provider, doesn’t keep the files available from SSMS. I don’t have rights to work within the restore system, or even request one from Rackspace, so I opened a ticket with Red Gates support for a restore.

    Had this been a situation where the site was down or users were unable to read articles or post, I would have escalated this for immediate action. However since this was a restore of a single thread, and one that exists for entertainment more than education, I chose not to bother our IT staff on a Saturday night or Sunday.

    The Fix

    When I woke up Monday morning, I had a message that the restore had been completed to my specified new database (SSCForumsOld) as of 1:30pm MST on Saturday. I hadn’t asked anyone to do more than this, so this was the extent of actions taken by Red Gate.

    Again, I could have specified actions in more detail, but rather than try to explain to someone in email which thread, and which posts needed to be restored, I decided to handle this myself. After taking my children to school, I sat down and got to work.

    I’ve known the PK of this particular thread since I’ve had to work with in in the past. Connecting from Management Studio to the production instance, I verified I could see the 45k messages in the SSCForumsOld database. I ran the same query on the SQLServerCentralForums database, and validated the data was still missing. I then built a query that would perform an INSERT..SELECT of the parent posts from the restored database to the production system. This took longer than expected, with the table having a number of locks for about 2 minutes. However the post details had been moved.

    That left me with the need to move the actual words of each post, which are stored in a separate table (for some strange reason). Rather than lock up the forums for minutes, I spent time rewriting my next insert to use batches of 1000, and only insert those messages which had not already been moved. Since I could join on PKs, this went quickly, in a few seconds. I next changed my batch size to 5000, and this completed in about 15s.

    That seemed like enough time to run quickly, but also a good batch of data and manually executed this 9 times to move all the data. A quick check on the site showed THE Thread was back and I posted a few notes to let users know.

    Aftermath

    There are still a few issues with points for users that have posted to this topic being incorrect, but that is a lower priority item and I am letting our developers look at it. There is at least one known bug with points, and it’s possible we have another here.

    My personal lesson learned was that I need to move a little more slowly when removing SPAM. I also don’t want to trust myself to do it regularly, so I also spent part of Sunday morning writing a little code and scheduling a job to delete posts with certain patterns of titles that the spammers use. I tried to limit to those obvious subjects so that no legitimate posts are removed.

    We are also escalating some of the issues with SPAM, and with the return of my manager from sabbatical, I’m hoping we can build a few more filters to limit the disruptions in the forums.

    And of course, everyone that posts to THE Thread has my apologies for the mistake.