October 3, 2012 at 9:58 am
Hi guys,
I have a situation here, I haven't really got my head around yet, how to solve it.
The status quo is this:
On the PROD server I have a partitioned table TAB1 with daily partitions. It holds data for 30 days, around 450 million rows, around 190 GB.
I have a partitioned history table TAB1_HIST with monthly partitions. Everyday the oldest partition of TAB1 gets switched to TAB1_HIST and merged into the monthly partition.
So far so good.
My job is now to set up a UAT system on a different server that has (almost) live data in TAB1 for 30 days.
(TAB1_HIST won't be on the UAT system) Data on UAT simply gets deleted after 30 days.
I need no other data from the PROD server (well, only once on setup, but then the UAT system will be independent). I only need a (near) live data flow from PROD.TAB1 to UAT.TAB1.
Replication is not possible, since TAB1 is partitioned.
Log shipping is also not an option, since I need the target database operational and accessible.
I thought about discarding the partitioning an managing the 30 days sliding window myself with stored procedures, but the index fragmentation and log file growth spoke against it. (alas, we're taking 7 GB of data per day, 190 GB a month, and you can guess how big TBA1_HIST is)
Any idea on how I could manage this problem without replication?
(Please ask if I haven't described the problem clearly enough. English is not my mother tongue, sorry).
October 3, 2012 at 10:19 am
What is "almost" live data? An hour, 5 minutes, a couple seconds behind?
If it's something like an hour would a snapshot of a mirror work? I'm guessing it's something a lot closer to < 1 minute though.
October 3, 2012 at 10:29 am
I have no experience with replicating partitioned tables but this article makes me believe it's possible.
http://msdn.microsoft.com/en-us/library/cc280940(v=sql.105).aspx
October 3, 2012 at 10:42 am
(almost) live means I could get through with being up to 10 minutes behind live, within 5 minutes would be good. < 1 min would be fantastic.
October 3, 2012 at 10:49 am
brendan woulfe (10/3/2012)
I have no experience with replicating partitioned tables but this article makes me believe it's possible.http://msdn.microsoft.com/en-us/library/cc280940(v=sql.105).aspx
Wow, thanks for that. If that is true, that totally solves it. cool.
I always thought replication was not supported for partitioned tables.
I'll build a lab tomorrow and try it. I'll let you know if it works.
October 3, 2012 at 10:51 am
I wasn't sure either, so I had to look. Yes, please let me know how it goes. I am curious.
October 12, 2012 at 4:21 am
Sorry for coming back so late on this.
Yesterday I finally had time to set up a lab and run some tests.
Here the findings:
- Yes, partitioned tables can be replicated. Works a treat!
- Partition switches can also be replicated, even switching from a replicated table into a non-replicated table (as long as the partition ranges are valid)
- Partition splits and partition merges are NOT replicated.
- One needs to be extremely cautious of the partitioning schemes of the replicated tables (and non-replicated, if switches into such happen). An errror in the switching-replication will stop the whole replication, and then you have the switch executed on the Source server, but not the target server and it's a pain to get them in sync again.
That said, I will not use the switch-replication, but have my on switching, splitting and merging logic on the target server.
Important thing is the replication of the data, and that works just fine and is enough for my use case.
Thanks again for the tip.
October 12, 2012 at 5:03 am
Hi, read this question with interest and was wondering if a copy only backup from your PROD server restored to your UAT server would do the job seeing as you don't need up to date data? Any thoughts?
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply