5

We have a large DB (500GB and expanding). 95%+ of the data is stored in 3 tables (one table has 2bn+ rows). The data is largely immutable - i.e. once it's added it's only then read afterwards. We can't archive old data.

We're using SQL Server 2017 which allows for compressed backups but even then it takes a long time to backup and copy across the wire to backup server.

We'd like to speed up this process (and for DR purposes have the backups in the cloud - probably Azure) - Differential backups are small enough, but I think that we'd still need to regularly take full backups (e.g. full each weekend and differential each night is still a problem for us)

My feeling is to use partitioning to split the large tables on some sensible column (clustered on identity ID) then we can back up the older partitions just once and mark them as read-only and not need to back it all again.

This is not an ideal situation as we're going to need to keep adding more partitions in the future. Also the DB cannot be taken down for more than a few minutes so I think I'm going to have to make a shadow version of the data with the partitions then do some switch-over so reduce downtime which is a bit risky and complicated.

If anyone has backup strategies for this sort of DB configuration that they think would work better (or can confirm that this seems like a good idea given the constraints that I have) I'd be glad to hear :)

Additional information:

Current backup schedule:

  1. Full backup (nightly) - compressed backups are about 100 GB (500 GB uncompressed) and take about 40 minutes (with compression)

  2. Log backups (Every 10 minutes) - Almost instant and only about 20 MB each.

Now I know that, for some, 40 minutes is not a very long and time and 100 GB is not a very large file, but I also know that given that 95%+ of the data is immutable and could be safely backed up only once, the backups could take less than a couple of minutes and be a couple of gigabytes (and that's being conservative).

I believe that partitioning is one of the tools used to help manage backups and specifically for this type of scenario, and I am hoping to get someone with real-world experience with this (or SQL Server based alternatives for my scenario) to shed some light on what works for them.

  • 1
    Not an answer, but since you are on SQL Server 2017 and the majority of your data is not changing, I'm hoping you are using data compression and not just relying on backup compression. Backing up to multiple files can save a ton of time, so look into doing that if you aren't already doing it. Separating tables into different filegroups would allow you to do filegroup backups - check out the internet for this topic (as you probably have already done). – Scott Hodgin Sep 26 at 12:01
  • 3
    Not to be that guy, but I'll be that guy... 500GB compressed shouldn't be taking that long... seems like you need to address the network bottleneck and not try to add another 3 levels of complexity to your backups to work around it... – Jacob H Sep 26 at 12:04
  • 1
    Additionally, when considering various options around database backups, you should also be mindful of your Recovery Time Objective and Recovery Point Objective. There are plenty of good articles and blog posts on the Internet about these topics. – Scott Hodgin Sep 26 at 12:19
  • The backup take enough time, and is a LOT of bandwidth to use copying up to Azure each day/week.... would be massively faster if we only backed up the freshly added data. Would really like not to have to back up all of the data each time... hence the idea of partitioning tables... hence the question – Mark Sep 26 at 15:32
  • 1
    @Mark It'd be very helpful if you could add more details, i.e. actual backup time, backup size, time to transfer to backup server/Azure, etc. Why do you need to speed up the process? Is there a target RTO/RPO/SLA for the backup server? The backup script you're using would be good to include as well. As others have said this size isn't that large and shouldn't be an issue for the setup you described, unless we know more information. – LowlyDBA Sep 26 at 19:51
3

I think you should look into log shipping once you get it setup your 10 minutes logs can be shipped to the server, so only changes are updated.

Assuming the log shipping destination is also where you want your backups kept, you can do backups there. Other then the initial backup, you can run for years and only ship the logs.

You could take full backups at both your primary and log shipping destination. That way you would have backups in both locations, and your only data moving daily is the logs being shipped.

Alternately you can use AlwaysOn instead of log shipping for the same effect.

Licensing: I am not an expert on licensing, but my understanding is that if the Log shipping (or AlwaysOn) destination instance is for DR only, there is no additional cost.

  • Thanks! I have been using log shipping with a lot of success recently and am a big fan of its simplicity and robustness. Whilst this, and the inferior availability groups - only inferior as we're on Standard edition - do provide good "high availability", they do not provide the ability for cloud-based backups (unless, of course the secondary SQL server is in the cloud)... – Mark Sep 27 at 13:54
  • In fact the really big reason why this is going to not work at all for us is that is that if someone does accidentally delete lots of data, that transaction will be shipped to the standby server and we'll still need a backup to get the data back.... – Mark Oct 1 at 15:47
  • 1
    @Mark in my answer I suggested taking backups on both instances, for just this reason. Assuming you are going to figure out someone did something stupid quickly, overwrite nightly. Your goal is to have current backups off site for DR with the minimum of overhead. There is no reason you can't have fulls and differentials, and maybe even log backups at both locations. – James Jenkins Oct 1 at 15:56
  • Sorry missed that in you message - good plan! – Mark Oct 1 at 16:20
1

I will write an answer, however really it depends on infrastructure you have, or you can afford:

  1. Why keep read only and transaction data in one database? Maybe read only data should be on separate set of files/database/disk/servers. I don't believe you are reading continuously 500 GB data file all the time and all over. Separation gives you ability to backup only this part which is changed. It may be backup as replication to separate database server
  2. You may research data deduplication in connection with data boost protocols. It means something ( software agent ) during backup compares changed data and moves only changed part ( like in differential backup). The difference is that on deduplicated storage such systems may construct offline synthetic full backup. Even if it requires rdbms performing classical full backup, transfer of the data is just difference. Depending on various factors you may gain much faster full backup as a result.
  3. You may research snapshot technology from various vendors. Some of such solutions are able to perform consistent snapshots for various database rdbms. It may be fully certified solution.
  • Thanks... I was really looking for something concrete and specifically SQL Server related. – Mark Sep 27 at 7:00
  • All I had wrote is perfectly related to SQl Serer, and it is even easier because many vendors targets their products to Microsoft SQL Server included. – kakaz Sep 27 at 7:44
1

In the end, went for partitioning option:

Partitioned the main "data" tables (with 95% data), drawing the "archive/readonly" line at 6 months ago.

Byproduct was that the tertiary tables (5% data) where in their own partition and could be restored independently (useful for those meta-data messups!)

Will also be using Log-shipping for DR, but since it requires additional licenses to be able to take backups off secondary, we're still left taking local backups.

Also experimenting with Microsoft Azure Backup Server to add some additional off-site goodness.

0

The best strategy depends on your RPO-Recovery Point Objective, as well as the RTO - Recovery Time Objective aka how fast must you restore it as well as how much data are you allowed to loose.

You might actually split a database in file groups that have different SLA's where 1 table get's backed up more frequently than others. Also when working with large databases (we have those that are several TB) you are not able to backup a whole database as the backup it self will take more time than that your infrastructure can cope with... a daily backup will fail if backing up takes more than a day…

We have generates a control server that has all databases of the Organisation as well as their RTO and SLA and run a backup based on how much data has changed as well as the servers last known IO data.

What we do is we have scripted the restore scripts to a SQL Job on the given server and have the job pool the transaction log and measure changes. when performing a backup we generate a restore script (locally and remotely) and whenever we have a change in RTO/RPO/SLA we update the server. If wen need to recover (in-place or hot swap/recovery server) we execute the recovery script that get's the appropriate backups Full, differential and transactional for a point in time or simple recovery.

We have to many databases for any other approach, also we have to high a financial exposure if "manually" doing things. DBA 1 and DBA 2 might do things differently also how can you consistently implement something on "all" servers when you get a security update or worse.. new idea in managemnt…

I have... a long time ago written a concept that addresses this, it also contains some scripts that will likely still work when changing paths, however it assumes password protecting backups, you might need to remove that as later versions of SQL server dropped that support.

The scripts imbedded as .SQL files at the end of the document.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.