Skip to main content

SQL Server Disaster Recovery

I was reading a blog post by Steve Jones on Monday and I started to write a comment on his blog, as it turned out it that comment turned into this post. Now this week is SQL Server disaster recovery week over on so I thought I’d get involved with a post of my own. Steve’s post was titled ‘Grace Under Pressure’ and Steve talked about a power failure in a server room at a large company he used to work at. Steve was in the data centre at the time of the power outage, along with a Senior Executive, the Senior Executive didn’t seem to handle the situation all that well and definitely didn’t make it an environment whereby the system administrators and DBAs could go about their business, and let’s face it, a quite stressful business of recovering production servers that have unexpectedly lost power.

No doubt the root cause analysis of the issue revealed that the power outage was a direct result of the Senior Executive being in the data centre :-)

Joking aside I have experienced first-hand those types of disasters that Steve talked about in his post

Power Outages to server rooms
Server room flooding
SAN disk issues that caused a filer crash and took several servers with it

I have been very fortunate in my career, many years ago, my first full time DBA gig was a great job and I got to work with a lot of great people. The company  was good enough to fund training courses and more importantly gave me a great deal of experience working with SQL Server and also the opportunity to learn from a great team of people. I got a taste of a first real disaster during my time here, I experienced lost power to a server room and it caused chaos.

Some of the simple things can help in this situation and because of the training and guidance I had received from some great people that used to work there when disaster did strike I was prepared with the following:

  • We had a fully documented run-book for each production server. This detailed everything from SQL Server versions, databases installed on the server. When the backups were scheduled to run, the location of the backups (On server and off server) and more importantly what needed to be done to recover the databases on the server
  • We were expected to test recovery scenarios regularly and ensure the documentation was up to date as a result. Recovering a database is stressful enough, and recovering a production server is not the first time you should be experiencing a server recovery. If you have practiced database restores, recovering from server crashes when you come to do it for real you have experience of doing it and a nice document to help you on your way
  • Local copies of SQL Server books-online installed. With SQL Server 2012 the help files need a little more to install locally. A client of mine didn’t think installing BOL locally was important as you get that off the internet. This is great when things are well and you can get to the internet. If you experience one of the disaster I listed above the internet might not available to you. I would recommend you download and install local copy of the SQL Server documentation you never know when you might need it.

I was also fortunate to have a really great manager who ‘kept the wolves from the door’ dealing with the business dealing with high ranking people keeping them informed but more importantly keeping them off the back of the people trying to fix things. He knew they were stressed as the business was suffering, he also knew that if they interfered it would likely to take much longer to fix. It’s like a vicious circle, things take longer to fix, senior people get more irate and round and round the circle goes.

I visited New York last week, more about that in another post, and it was apparent that the hurricane/storm that hit a few weeks back was still having a severe impact on homes and business in NYC. There are still building closed, some are still flooded and being pumped out. The phones lines are still available for some; you have to pay cash in some bars and restaurants. No doubt the storm is still costing the NYC economy.

IT disasters can happen to anyone, to handle them with grace you need to be practiced, prepared, have a plan and work as a team with no infighting, finger pointing or assigning blame to fix the issue as quickly and efficiently as possible.

For more information on SQL Server disaster recovery planning and other services we offer visit SQL Server Consulting page


Popular posts from this blog

SQL Server 2012 and Virtual Service Accounts

This post is written by David Postlethwaite
If you are using SQL Server 2012 you will probably have noticed that the default account for the SQL services has changed from that used in previous versions. With SQL 2005 and 2008 the default account for SQL service and SQL Agent service was “NT Authority\System”. This is one the built in accounts on a Windows machine, managed by the machine and selectable from a dedicated dropdown list

The Network Service account was introduced in Windows 2003 as an alternative to using the LocalSystem account, which has full local system privileges on the local machine, a major security concern.
The Network Service has limited local privileges easing these security concerns but when many services on a machine use the Network Service account it becomes harder to track which service is actually accessing resources and performing actions, because all the services are using the one Network Service account.
Also, this account, by default, has sysadmin per…

Always Encrypted

By David Postlethwaite

Always Encrypted is new features in SQL Server 2016 and it is also available in Azure SQL Database. Here you can encrypt columns in a table with a master key and a certificate so that they will appear as encrypted strings to those who don’t have the required certificate installed on their pc.
Once the certificate is installed on the computer then the unencrypted data can then be seen as normal.

The data passes from database to your application as the encrypted value, only the application with the correct certificate can unencrypt the data so it is secure across the wire. This will go some way to resolving the concern of people worried about putting their sensitive data on a shared server in the cloud such as Microsoft Azure and accessing the data across the Internet.

At the time of writing Always Encrypted is only supported with ADO.NET 4.6, JDBC 6.0 and ODBC 13.1 but expect other driver to become available.

The calling application (including SSMS) must also hav…

New in SQL Server 2017: Graph Databases

David has recorded and published a video of his presentation on SQL Server Graph Database. In his video which you can watch below, David provides an excellent introduction into SQL Server 2017 Graph Databases. In his presentation he looks at Tennis results at tournaments for  his favourite player "The Fed"  Rodger Federer.

David  shows how to set up graph database and work with them in SQL Server 2017.

Graph Database is not new. Other vendors have had graph database capabilities for some time so Microsoft are quite late to the market. In David presentation it appears that Microsoft have done a reasonable job of implementing some of the graph database features but he does point some of the limitations of the Microsoft product too and suggests that it is not ready for production yet but Microsoft seem serious about this feature.

Please watch the video and feel free to leave a comment or feedback - David is delivering a version of this talk on Graph databases in SQL Saturday Ka…