August 1, 2013 at 5:39 am
Hi
I couldn't seem to find a forum for this, so picked this one as seemed to be the closest.
I've been looking round for a while on this but I'm either looking in the wrong places or there is not much out there.
I am looking at a partitioning a large table with millions or rows per month and would like to implement a sliding window technique or switching a months worth of data into an archive table every month.
In terms of the technique I'm OK with that (although if anyone has any advice then please feel free to chirp up). What I am not sure about is this, the DB will be participating in a Availability Group 1 Synchronous Replica and 2 Asynchronous Replicas , what kind of effect will this have on DB's / Availability Group?
Does anyone have any experience of this?
Cheers
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
August 2, 2013 at 1:57 am
Hmm no takers? Looks like I will be the first person ever to try Partition Sliding with Always On..... 😉 :rolleyes:
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
August 3, 2013 at 12:36 pm
I'd say you are the second 🙂
I'm setting up an Always On Group these weeks.
I also have some large tables that are partitioned by month. The database ist part of the Always On Group.
My first test of switching out an partition into another table worked fine at the first try. The partition was empty but the whole process worked.
The next time we have a partition full with data. I do not expect any problems as the mechanism of the sliding window seems to work. The interesting thing is: We use synchonous mode. How long will the transaction of switching out the table really need and what does this mean for the primary replica? How long will it be "non writeable". As our network connection between the primary and the only secondary replica is quite strong I do not expect any problems here neither.
In the end this means for me: As the sliding window itself works great it seems to be like on any "non-always-on-database", only to keep in mind the any transaction has to be committed from the secondary replica when using synchronous data transfer.
August 24, 2018 at 10:14 am
WolfgangE - Saturday, August 3, 2013 12:36 PMI'd say you are the second :-)I'm setting up an Always On Group these weeks. I also have some large tables that are partitioned by month. The database ist part of the Always On Group.My first test of switching out an partition into another table worked fine at the first try. The partition was empty but the whole process worked.The next time we have a partition full with data. I do not expect any problems as the mechanism of the sliding window seems to work. The interesting thing is: We use synchonous mode. How long will the transaction of switching out the table really need and what does this mean for the primary replica? How long will it be "non writeable". As our network connection between the primary and the only secondary replica is quite strong I do not expect any problems here neither.In the end this means for me: As the sliding window itself works great it seems to be like on any "non-always-on-database", only to keep in mind the any transaction has to be committed from the secondary replica when using synchronous data transfer.
Hi WolfgangE,
it is been a while since you wrote your post...
I'd like to ask you how is it going for the partitioned table on your AG?
I have a table which contains 1.7 billion rows and we are inserting 15m rows daily. there is a purge process running frequently.
thanks in advance:)
August 25, 2018 at 4:29 am
Hello 1974lg,
yes, quite a while since that post....
In the environment I was working on when writing this post I did not had any troubles with partitioning. Our main tables were about 10 to 100 millions of rows, and the partitioning actions worked just fine.
Today, I'm dealing with a database, where the largest table contains about 3 billion records, the top 10 tables have about 10 million inserts and several updates per month. We have 3 secondary replicas, 2 of them synchronious. The partitioning functions work fine, no matter of the AlwaysOn-Technology.
I don't know what you acutally want to achive, but here are 2 points that might be interesting for you:
1) Since SQL 2016, you can use TRUNCATE TABLE with a partition number. This means, if you just want to drop an old partition, there is no need any more to create a side table and switch the data into it. Just use TRUNCATE with the partition number.
2) It is a good practise to have one "empty" partition for the future. If you use the SPLIT-command to add a partition, it has to read the whole partition to split, whether there is data to move to the new partition. So, we leave one more partition than neccessary, so the system can split an empty partition. E. g.: Partitioning by month, we actually have 08/2018, so our current partition is 1808. We already have a 1809 partition. We can split the 1809 to a 1810 at the end of the month.
But his catually has nothing to do with the AlwaysOn-Technology, it is more about the behaviour of the partitioning technology.
I hope I could get you some information, otherwise you have to ask a question with more details what it's all about...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply