1

We have a number of tables that will process a large amount of inserts each day. For this reason, the tables are archived on a weekly basis using a labour intensive process of backing up the database and restoring to another server and then inserting the data from this backup into another archive database.

My plan is to introduce AlwaysOn for High Availability and use Table Partitioning to switch the archive partitioned data to another table. Has anyone any experience of using table partitioning in this way with AlwaysOn? Does it present any issues with AlwaysOn traffic to the Secondary when switching? Has anyone any other recommendations other than Table Partitioning?

Thanks!

  • Thanks for confirming that. That's ok. Am happy to stick with archiving in same database. I've deleted that wording from the question. – Stephen Jun 5 '17 at 13:09
0

Suppose you set up AlwaysOn but something goes wrong with your partition switching process and you accidentally delete all of the data. With your current proposal you'll automatically delete all of the data from your archive database as well. So even with AlwaysOn you'll still need to take backups, right?

Now consider the state of your current database immediately after you perform the partition switching. That's what you want on the archive database, right? So if you still need to perform backups anyway why not skip AlwaysOn and just back up and restore that database immediately after the switching?

You might have other reasons for using AlwaysOn so I can cover a few pointers but I'm not really an expert. At a high level, SQL Server keeps the databases in sync by replaying the transaction log so the secondary database is a byte-for byte-copy. A decent approximation of how an operation will affect redo traffic can be found by looking at how much data is logged to the transaction log. Partition switching doesn't require data to be moved so very little information should be logged. This means that you should not expect it to cause issues with AlwaysOn traffic.

One problem that you might run into is that you can't pick and choose which tables or filegroups get synced. It must be the entire database. So you'll be sending the transaction log activity for the "large amount of inserts per day" and anything else that you do through redo to the secondary. Your active database will also continue to grow in size over time switch you can't partition switch across databases as Sean Gallardy pointed out. So right now if your active database is 100 GB and your archive database is 1 TB you'll need to move all of that data from the archive database to the active database if you want to put them in an availability group.

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.