This is part 1 of a multipart series on Microsoft SQL Server data. We’re talking about SQL backups and copy data, different methods of using SQL copy data and creating them. To start, let’s review the different backup methodologies for SQL Server databases. There are really four different approaches that people use:
- The Dump & Sweep approach, followed very closely by
- Direct to Backup Software
- Storage Based Snapshots, and finally
- Change Tracking
What we’re going to do is run through all four of them, and talk about the pros and the cons of each, and exactly what they look like from a mechanics standpoint:
1) Dump & Sweep
So dump and sweep is when you have a SQL Server, it has some databases on it; you have your production storage with a number of volumes that have your SQL data on them. Some additional disk is provisioned to the backup server. That disk might be just like production disk, it could be a lower class of disk. Could be presented to that SQL Server via fiber channel, via iSCSI, via CIFS for example. And the DBA is responsible for sending up a SQL Server maintenance plan to do backups of these databases on to this disk. When they step up those maintenance plans, they’re going to set it up to do a full backup at least once, possibly full’s every single day. They’re going to set up transaction log backups so that throughout the day, at different points in time, they’re going to have those transaction log dumps going to this disk.
They may even set up differentials, where on day number one you run a full. On day number two you have a dif, and a differential means just the changes since the previous full backup. The challenge with difs is, on day number three, it’s still the changes since the previous full, not since the previous dif, and therefore it grows every time you do it. Eventually it becomes not worthwhile and you just run another full You have some retention on this disk, usually it’s relatively short, and the DBA has ability to access this disk often times from multiple places throughout the environment so that they can do their own restores copying that data back.
What a restore looks like, well, it looks just like a backup. Take the full, if you’re using difs take one dif afterwards, whichever one is closest to your point in time, copy them all back here. Well, as you initiate a restore process they get copied back to the production disk, and then bring the database online and optionally roll forward transaction logs. That process, of course, takes time, just like the backups take time. And backups using this methodology tend to generate a lot of IO on that server as they’re doing those full’s especially. Now, this isn’t the end of the story for dump & sweep, that’s the dump part. The sweep part is when you say, this doesn’t get my data necessarily offsite, this doesn’t give me the long enough retention.
So, you introduce a more traditional backup software. And the backup software has some target disk that it uses, often times this is deduplicated disks, compressed disk. It may also have tape. And what the backup software is going to do is the backup software is going to sweep by looking at what’s on this storage target location and basically copy it into its own. So you get the copies of all the different backups, including the transaction logs, into your backup destination. Then it may optionally copy them on to tape, it may optionally copy them to another backup server in another location so that you have some separation for disaster recovery. As you can see, this starts to add up with how many copies you may have. And don’t forget, every time you’re creating a copy you’re moving all of that data, so it really throttles the infrastructure in between the two, it takes a lot of time and energy, and it’s not especially fast if you need to get to that data at some point in time later.
2) Direct to Backup Software
That’s when option number two came in. Option number two is direct to your backup software. And what this basically does is it says it’s nice that we have this nice landing pad here, if you will, for the DBAs to use directly. But if we eliminated this completely we really don’t give up anything. This is the thinking of a backup administrator. And so the backup software can actually take directly from the SQL Server and send that data directly down into its storage. This eliminates the need for this copy, which is a good thing. The downside to this though is your SQL DBA, who previously was very empowered to run their backups, run their restores and own the process, now they have to work hand-in-hand with a backup administrator, and with a different product that’s not native SQL Server Tools. So that sometimes pushes them a little bit out of their comfort zone.
It also introduces some administrative burden when you need to do a restore; one person has to go to another in order to do that process. Sometimes, they’ll link between the servers. This is typically going over the network. This can often be slower than this, which might’ve been a high-speed link. So sometimes your backups actually take a little bit longer even though they take less space because you’ve eliminated this entire copy. This is the direct to backup approach, it’s very similar to dump & sweep in most regards.
Neither of these are especially effective when it comes to dealing with large data because they’re doing these repeated full’s it takes a lot of time, it takes a lot of space. And that’s when the third option came into play, and that’s the snapshot option.
3) Storage Based Snapshots
Snapshot option is when you go to your storage array and you say, you know what; I really need to make an instantaneous point-in-time version of my data. So with this approach, you basically freeze all IO on your databases for hopefully just a moment. And then you take a snapshot, a snapshot of your production disks. And when you do that you’re basically left with a full copy of your data as it looked at that consistent point in time. Now this is a good thing, and it happens usually in a matter of a second or less, because all it’s doing is creating a pointer-based copy.
The downside of – and this by the way, addresses large data backup window challenges very, very well. It’s not doing a lot of data movement, it’s not impacting the IO on your production server as it’s doing the backup, except for an instant. The downside is this is just a snapshot. And snapshots, well, they’re not backups. You tend to have very short retentions here, often times shorter than you had with the dump & sweep approach. And if you want to get this data offsite, well now you have to replicate it somewhere probably to another array, it’s also very dependant on the storage array, both in brand and vendor, but also in the design of your data. Whereas maybe your data layout here before was very oriented towards what your databases are being used for.
Now, it’s going to be oriented towards your backups, because you’re going to want different retentions to be snapped at different times, and then once you have this data, you still have to get that snapshot somehow to your backup software, and that’s still a big data movement event. You might mount the snapshot to your backup server and then back it up. But you still have that data movement; it’s just no longer on the critical path of the backup. So a big improvement when it comes to large data handling, a decrease in efficiency when it comes to flexibility on how you design your policies and run those jobs, definitely a decrease in efficiency when it comes to recoveries from a policy standpoint. Now you need a storage administrator often to be involved with doing those recoveries.
But, from a time standpoint, if you’re rolling back to a point of time or you want to mount up a point of time that’s in a snap, that can be very fast, it’s not a data movement event. So definitely better at handling big data. For small data it’s more complex and more interaction between components, more failure opportunities as well. And that’s when we come to option number four.
4) Change Tracking
Change tracking is taking a little bit of the best of both worlds, where you say, I’ve got my databases. I need to track what’s changing on them from day, to day, to day, so that my backup software, after the very first time it runs a backup and puts a copy, it can just get the changes. And if it can do that it can scale very well with large datasets, because your large dataset now doesn’t take hours and hours. It’s like the differential approach, except that with each one it’s just getting the changes, so it doesn’t grow day after day. With the better implementations of change tracking solutions, you can actually say, one full and after that incremental forever without ever having to do another full again. And when you go to do recoveries with incrementals you usually think, backup software incrementals, I have to do a restore of a full, and then every incremental to get to a point in time. With the change tracking approach, you usually don’t.
Each one of these, while looking like an incremental or while looking like an incremental to create it, it’s actually a virtual full. And so recovery of any of these tends to be, go to that exact point in time, and it’s a full, just the way you would want it to look, and then you can apply your transaction logs. Some of the products can even present directly from the backup server back again to any SQL Server an image from the point in time and bring it online. So this really addresses very well large data, it also addresses recovery time very effectively and integrates with your longer-term retention targets and your offsite replication.
So you might be saying, “Well, what’s the con, the negative of this?” Well really, there’s not many. It depends on the implementation. One of them is you can have a solution like this that integrates and depends on your storage array, where it’s not doing change block tracking here, it’s doing the change tracking by taking snaps, and then taking the incrementals from the snaps and copying them someplace else. Cons of that is it’s dependent upon the storage array, the vendor, the brand, compatibility matrix, et cetera. But that can definitely be made to work.
Another con of this is, a lot of this is outside the control of the SQL DBA. So while you usually retain the ability not with the storage array version but with the host-based version, you retail the ability to do individual database-level policies, different schedules, different retentions, it’s also typically not managed directly by the DBA. Can be, but really it’s the backup application that’s doing all of the magic in that scenario.
So that’s a quick rundown of the different approaches, the four different approaches for SQL Server data protection, where they started from, where they’re headed towards, and some of the pros and cons of each. Tune in next time when we talk a little bit more in-depth about recoveries, and the different methods of getting that data back in greater depth, and how they vary from method to method.
Coming soon: Learn about the SQL recovery process in video 2 of 2!
Looking for new SQL backup recovery and cloning tools? Here’s a free checklist to help evaluate vendors.
Mike Salins is a Principal Product Manager at Actifio, specializing in SQL Server, test data management, and disaster recovery automation. Before joining the product management team, Mike was a pre-sales Solution Architect, helping users design and deploy Actifio solutions. His experience prior to Actifio varies from corporate IT architect, post-sale software implementation consultant, and software development management, and he was even writing code at the start of his career. When he isn’t in front of a customer or a computer, he tries to spend time behind the wheel on a race track with friends and other auto enthusiasts.