Microsoft SQL Server Case Study

Microsoft SQL Server

Microsoft SQL Server® is a market leading enterprise level database solution used by any a large number and variety of applications on which to host their databases and store their data. Microsoft SQL Server is an incredibly powerful, scalable and robust solution, however it is it’s robustness that often leads customers into a false sense of security.

As with anything in life, things can go wrong, and this is true with SQL Server. Your valuable data can be lost for a number of reasons such as hardware failure, theft, fire, flood, user error etc. and so it is worth planning for such events to make recovery as painless as possible.

With SQL Server, there are many ways to improve the recovery from data loss, such as mirroriing, transaction log shipping and always on high availability, all of which offer differing levels of protection at a variety of price points. Here we will look at the simplest and most cost effective solution for an SME to protect their data – a decent backup.

A Real-Life Example

Before we look at how we should implement SQL Server backups, let us look at a real life example of how a good backup strategy works.

In this particular example, the customer was running Microsoft SQL Server standard to host their Microsoft Dynamics® NAV database. Microsoft SQL Server was running on its own dedicated server with the Dynamics NAV database configured to use a full recovery model with full backups running daily at night and log backups running hourly during the working day to a network share on a different server.

On this particular day, the customer’s IT manager decided to test the UPS protecting the SQL Server, by unplugging it from the wall, something he had diligently been doing on a regular basis, however this time the UPS failed and the server immediately lost power. The server was powered up, and at first, all seemed to be OK, until after a short while (about an hour) it became apparent that the G/L Entry table (a somewhat important table in a NAV database) was corrupt. The customer in question was a distribution company and had a number of shipments that they needed to get out of the door before the end of the day, and so the prospect of recovering the database at that point in time was not very appealing. After a short discussion with Dynamics Consultants, we made a small tweak to the setup to allow them to continue processing warehouse transactions without needing to write to the G/L Entry table, allowing them to continue to ship orders for the rest of the day.

This still left the customer with a corrupt database, and now with a number of shipments processed, as well as other database activity, since that corruption had happened. However, because their database was configured with a full recovery model, we were able to restore to a fresh database the last full backup prior to the failure and all transaction log backups since, including a final log backup before disabling the database, and in so doing leaving the customer an uncorrupted database with no data loss, very happy, and with an extremely relieved IT manager.

Backup Strategy

So, what can we learn from this. Firstly, don’t test your UPS during the working day, but more importantly, make sure you have an appropriate backup strategy. (NOTE: Many VM level backup strategies would not have worked in the above situation, as it would have also backup up the corruption).

So what is the correct backup strategy? Well, there is no right or wrong answer as it depends very much on the level of database updates, what the database is used for, the size of the database and individual’s assessment of the risks associated with a failure in terms of acceptable downtime and data loss, but as a starting point, an SME should consider the following for a production database.

• Full Recovery Model
• Backups to a separate network server.
• Backups to an offsite location.
• Daily full and hourly log backups
• Backup encryption

If you are unsure about your database backups, then you should seek advice from an experienced SQL Server administrator, or alternatively attend our SQL Server Basics course to obtain a good working overview.

Find out More

If you would like to find out more about SQL Server, why not join one of our extremely popular SQL training courses? Based at our comfortable offices on the outskirts of Southampton, Hampshire, our expert consultants have 4.5star rating reviews.

SQL Server Training >

Tom Jenkins

Tom is one of the founding directors of Dynamics Consultants. He has worked on ERP / CRM systems since 1995, initially as an end user and later as a developer/consultant. Before founding Dynamics Consultants, Tom worked in a management role for a machinery importer / reseller, where his work included inventory management and purchase control, systems development, and IT project management.