Sfrgttk

What are the possible solutions of the given equation?

Have researchers managed to "reverse time"? If so, what does that mean for physics?

Making a sword in the stone, in a medieval world without magic

Russian cases: A few examples, I'm really confused

Distribution of Maximum Likelihood Estimator

Employee lack of ownership

Is having access to past exams cheating and, if yes, could it be proven just by a good grade?

PTIJ: Who should pay for Uber rides: the child or the parent?

Humanity loses the vast majority of its technology, information, and population in the year 2122. How long does it take to rebuild itself?

Identifying the interval from A♭ to D♯

Define, (actually define) the "stability" and "energy" of a compound

Why would a flight no longer considered airworthy be redirected like this?

The use of "touch" and "touch on" in context

Bash: What does "masking return values" mean?

Life insurance that covers only simultaneous/dual deaths

Does this AnyDice function accurately calculate the number of ogres you make unconcious with three 4th-level castings of Sleep?

Calculus II Professor will not accept my correct integral evaluation that uses a different method, should I bring this up further?

Does splitting a potentially monolithic application into several smaller ones help prevent bugs?

Is it possible to upcast ritual spells?

An Accountant Seeks the Help of a Mathematician

Be in awe of my brilliance!

What is the greatest age difference between a married couple in Tanach?

Should we release the security issues we found in our product as CVE or we can just update those on weekly release notes?

Ban on all campaign finance?

SQL Server AlwaysOn Table and Archiving

Restoring SQL Server databases - tips and tricks?How to do automated archiving for a SQL Server database based on interpretation of database metadata?Msg 1833, File 'ABC.ndf' cannot be reused until after the next BACKUP LOG operationExtracting Data from multiple archive tables(SQL Server)Why should I “Avoid partitioning dimension tables” on SQL Server?Partitioning a large table has not improved performance, why?MS SQL Server 2016 - Single Partitioned Table vs Multiply Separated TablesAlwaysOn DDL and Schema ChangesSQL Server: ensure primary key uniqueness over two tablesPartitioning a 4TB table while staying online (SQL Server Standard)

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!

sql-server availability-groups

share|improve this question

edited Jun 5 '17 at 16:10

Philᵀᴹ

25.6k65591

asked Jun 5 '17 at 12:57

StephenStephen

9018

bumped to the homepage by Community♦ 5 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

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!

sql-server availability-groups

share|improve this question

edited Jun 5 '17 at 16:10

Philᵀᴹ

25.6k65591

asked Jun 5 '17 at 12:57

StephenStephen

9018

bumped to the homepage by Community♦ 5 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

add a comment | 

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!

sql-server availability-groups

share|improve this question

edited Jun 5 '17 at 16:10

Philᵀᴹ

25.6k65591

asked Jun 5 '17 at 12:57

StephenStephen

9018

share|improve this question

edited Jun 5 '17 at 16:10

Philᵀᴹ

25.6k65591

asked Jun 5 '17 at 12:57

StephenStephen

9018

share|improve this question

edited Jun 5 '17 at 16:10

Philᵀᴹ

25.6k65591

asked Jun 5 '17 at 12:57

StephenStephen

9018

edited Jun 5 '17 at 16:10

Philᵀᴹ

25.6k65591

edited Jun 5 '17 at 16:10

Philᵀᴹ

25.6k65591

asked Jun 5 '17 at 12:57

StephenStephen

9018

asked Jun 5 '17 at 12:57

StephenStephen

9018

1 Answer
1

active

oldest

votes

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.

share|improve this answer

answered Jun 6 '17 at 0:35

Joe ObbishJoe Obbish

21.4k33187

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f175433%2fsql-server-alwayson-table-and-archiving%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

answered Jun 6 '17 at 0:35

Joe ObbishJoe Obbish

21.4k33187

add a comment | 

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.

share|improve this answer

answered Jun 6 '17 at 0:35

Joe ObbishJoe Obbish

21.4k33187

add a comment | 

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.

share|improve this answer

answered Jun 6 '17 at 0:35

Joe ObbishJoe Obbish

21.4k33187

share|improve this answer

answered Jun 6 '17 at 0:35

Joe ObbishJoe Obbish

21.4k33187

answered Jun 6 '17 at 0:35

Joe ObbishJoe Obbish

21.4k33187

answered Jun 6 '17 at 0:35

Joe ObbishJoe Obbish

21.4k33187