Tag Archive for backup

How Much is Your Data Worth?

Data is the new currency in today’s modern businesses. From the largest international conglomerate down to the smallest neighborhood Mom-and-Pop shop, data is EVERYTHING! Without data, you don’t know who to bill for services, or for how much. You don’t know how much inventory you need on hand, or who to buy it from if you run out. Seriously, if you lost all of your data, or even a small but vitally important piece of it, could your company recover? I’m guessing not.

“But,” you say, “We have a disaster recovery site we can switch to!”

That’s fine if your racks melt down into a pool of heavy metals on the server room floor, then yes, by all means switch over to your disaster recovery site because molten discs certainly qualify as a “disaster!” Databases hosted on private or public cloud virtual machines are less susceptible, but not immune, to hardware failures.  But what about a failure of a lesser nature? What if one of your production databases gets corrupted because of a SQL Injection hack, cleaned out by a disgruntled employee, or is accidentally purged because a developer thought he was working against the DEV environment? Inadvertent changes to data are no respecter of where such data is stored, or how it is stored! And, sorry to say, clustering or other HADR solutions (High Availability/Disaster Recovery, such as SQL Server Always On technology) may not be able to save you in some cases. Suppose some data gets deleted or is modified in error. These ‘changes’, be they accidental or on purpose, may get replicated to the inactive node of the cluster before the issue is discovered. After all, the database system doesn’t know if it should stop such changes from happening when the command to modify data is issued. How can it tell an ‘accidental purge’ from regular record maintenance? So the system replicates those changes to the failover node. You end up with TWO copies of an incorrect database instead of one good one and one bad! And worse yet, depending on your data replication latency from your primary site to the disaster recovery site, and how quickly you stop the DR site from replicating, THAT may get hosed too if you don’t catch it in time!

Enter the DATABASE BACKUP AND RESTORE, the subject of this article. Database backups have been around as long as Relational Database Management Systems (RDBMS). In my humble opinion, a product cannot be considered a full-featured RDBMS unless it has the capability of performing routine backups and allows for granular restore to a point in time. (Sorry, but Microsoft Excel and Access simply do not qualify.) Being a Microsoft guy, I’m going to zero in on their flagship product: SQL Server, but Oracle, SAP, IBM and many others will have similar functionality. (See the Gartner Magic Quadrant for database systems for a quick look at various vendors, including Microsoft a clear leader in this Magic Quadrant.)

So what is a BACKUP? “Is it not simply a copy of the database?” you say, “I can make file copies of my Excel spreadsheet. Isn’t that the same as a backup?” Let me explain how database backups work and then you can decide the answer to that question.

First of all, you’ll need the system to create a FULL database backup. This is a file generated by the database server system, stored on the file system, the format of which is proprietary to the system. Typically, full backups are taken once per night for a moderately sized database, for example under 100 GB, and should be handled via an automated scheduling service such as SQL Agent.

iStock_000006412772XSmallNext, you’ll need TRANSACTION LOG backups. Log backups, as they are known, record every single change in the database that has occurred since the last full or log backup. A good starting point is scheduling log backups at least every hour, with possible tightening down to every few minutes if the database is extremely active.

Now, to restore a database in the event of a failure, you need to do one very important step: backup the transaction log one last time if you want to have any hope of restoring to a recent point. To perform the actual restore, you’ll need what is known as the ‘chain of backups’ which includes the most recent full backup and every subsequent log backup. During the restore, you will be able to specify a point in time anywhere from the time of the full backup to the time of the latest log backup, right down to the second or millisecond.

So we’re all set right? Almost. The mantra of Database Administrators the world over regarding backups is this: “The backups are only as good and sure as the last time we tested the RESTORE capability.” In other words, if you haven’t tested your ability to restore your database to a particular point in time, you can’t be sure you’re doing it right. Case in point: I saw a backup strategy once where the FULL backups were written directly to a tape drive every night, then first thing in the morning, the IT guys would dutifully eject the tapes and immediately ship them out to an off-site storage location. How can you restore a database if your backups are not available? Case two: The IT guys, not understanding SQL backup functionality and benefits, used a third party tool to take database backups, but didn’t bother with the logs. After four years of this, they had a log that was 15 times the size of the database! So big, in fact, that there was no space available to hold its backup. About a year after I got the situation straightened out with regular full AND transaction log backups going, the physical server (virtualization was not common practice then) experienced a debilitating hardware failure and the whole system was down for three days. Once running again, the system (a financials software package with over 20,000 tables!) was restored to a point in time right before the failure. Having the daily FULL backups saved the financials system (and the company). But also having the log backups saved many people a day’s work if we had had to go back to the latest FULL backup.

So, what’s your data worth? If your data is critical to your business, it is critical that you properly back up the data. Talk to us to learn how we can help with this.

About Todd: Todd Chittenden started his programming and reporting career with industrial maintenance applications in the late 1990’s. When SQL Server 2005 was introduced, he quickly became certified in Microsoft’s latest RDBMS technology and has added certifications over the years. He currently holds an MCSE in Business Intelligence. He has applied his knowledge of relational databases, data warehouses, business intelligence and analytics to a variety of projects for BlumShapiro since 2011. 

Technology Talks Newsletter CTA

The Business Value of Microsoft Azure – Part 3 – Backup

This article is part 3 of a series of articles that will focus on the Business Value of Microsoft Azure. Microsoft Azure provides a variety of cloud based technologies that can enable organizations in a number of ways. Rather than focusing on the technical aspects of Microsoft Azure (there’s plenty of that content out there) this series will focus on business situations and how Microsoft Azure services can benefit.

In our last article we focused on data security from the perspective of the ability for users to purposefully or inadvertently cause data to leave the organization. Today we’re going to focus on data loss from the standpoint of system failure, corruption or other disaster that requires access to a backup.

Many organizations still rely on tape based backup systems as the primary means of backing up critical business data. Let’s take the typical municipal office. Chances are that our fictional town of Gamehendge has either a traditional backup to tape solution or perhaps a disk-based virtual tape system where copies are then made to physical tapes. These tapes are sent offsite to a facility that manages tape archiving for disaster protection purposes. While this seems reasonable, our town faces a problem.

If a production system fails or if data needs to be restored due to user deletion/corruption it might take up to 24 hours for the IT department to work with the off-site records management company to request, locate and deliver the appropriate tape with the ensuing process to then actually recover the data.

One solution to this problem might be to set up a co-location solution with a hosting provider and replicate certain servers. Again, this is a fairly common practice. However, replicating all the servers in their environment is costly and so only a handful of the most high-priority systems are replicated. This approach, while a step in the right direction, only allows a few key systems to be restored in 2-3 hours, leaving the remaining systems to a 24 hour recovery period.

Our fictional town wants to free up their IT folks to spend time on value added activities. Right now the amount of time spent managing backups, restoring data and managing the replication processes makes this a challenge. Our town budget is just as tight as everyone else and so finding a creative way to address this issue without needing to hire another resource is critical.

Enter the Microsoft Azure StorSimple family of hybrid cloud storage solutions.

StorSimple is an on-premise enterprise storage area network that interoperates with Microsoft Azure to provide hybrid-cloud storage, data archiving, and fast disaster recovery. The solution replaces traditional backup processes with the concept of “cloud snapshots” that automate the process of creating copies of data remotely in the Azure Storage Cloud.

With our data securely backed up in Microsoft Azure Cloud Storage our town has a couple of options. They can purchase a second StorSimple appliance and deploy it at their co-location facility or they can use a virtual StorSimple appliance in the Microsoft Azure Cloud to quickly bring data or a virtual machine back online resulting in a significantly faster recovery time compare to tape storage.

Our town has realized additional benefits by pursuing this solution. Beyond the backup capability, the StorSimple device provides “bottomless” storage. With three tiers of data storage capability (SSD, HDD, and Cloud) the device intelligently transfers data from higher cost/higher performing storage (SSD) to lower cost HDD and eventually Azure Cloud Storage. This happens automatically based on usage characteristics and other criteria. Further, with the deduplication and compression technologies in the device our town has been able to reduce the total amount of storage space needed to protect its data.

There are other approaches that can be implemented as well using Microsoft Azure Cloud Storage, but with the StorSimple device it provides a significant step forward for cities and towns that have struggled to keep up with the ever growing demands for storage. Every town that has considered implementing a cop-cam officer mounted video camera solution will immediately face significant data storage and backup needs and StorSimple can play a key role in managing these costs.

As a partner with BlumShapiro Consulting, Michael Pelletier leads our Technology Consulting Practice. He consults with a range of businesses and industries on issues related to technology strategy and direction, enterprise and solution architecture, service oriented architecture and solution delivery.