January 20, 2011 at 12:34 pm
Hi all...I am throwing this out there hoping that you experts can send me some advice and direction on replication issues...
We have 2 remote servers, one publisher, one subscriber. They live on opposite coasts. LENGTHY ETL's feed the west coast server which is replicated over to the east coast server and then the BI servers read from there and deliver data to the Data Warehouse. Well, in all this, it hiccups. Locking I am sure, but the replication queue gets backed up and we are forever playing catchup!! Does anyone have ideas on how to make this a seamless process?? Is SQL SERVER 2008 better at replication than 2005? We are rookies at this replication stuff basically and need some strong advice. Thank you in advance for any advice, recommendations or alternatives!! :hehe:
January 20, 2011 at 12:41 pm
Replication and large batch loads don't play well together. While you can do some things to help replication out if you are constantly playing catch-up now it probably won't be enough to clear things completely.
I'm assuming you need the data in both locations and if so, why not just load it to both using your current ETL process?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 20, 2011 at 2:47 pm
January 20, 2011 at 3:11 pm
If Replication is the requirement then yes there are some tweaks that can be done with Server 2008 that will help replication, mainly in the area of network configuration. I don't have the documents before me but if you Google it you should find something regarding this pretty easily. Shout if you don't. I'm not sure if there is anything really associated with SQL Server though in those changes. I personally have not used them.
Have you implemented subscriptionstreams in your distribution agent job? If not you should consider that as well as that will most likely help in your situation. Again though if you are talking latency that never catches up it might not cover it all.
One other note too is that if you have more than one table being replicated that you are loading data into you should consider breaking the tables into multiple publications and then using independent distribution agents so that you can have that data moving simultaneously rather than serially.
Not sure if all that makes sense. If not please let me know and I will explain more later.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 20, 2011 at 3:40 pm
I will check out the subscriptionstreams and the network configuration...thanks for the heads up! Also, we are toying with your same idea of pulling those ETL tables into a publication of their own...and running them separate. I would think that would help tremendously!! I will keep you posted! Thank you again, I appreiciate your advice!!:-P
January 20, 2011 at 3:52 pm
Great, I'll look forward to hear how things work out for you. One note that I didn't make clear in my last post, subscriptionstreams are available in 2005 so you can take advantage of that today.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 20, 2011 at 4:18 pm
January 27, 2011 at 12:15 pm
Hi David, I am back!!! Ok, have implemented more ETLs, breaking down the big ones into smaller. That is going well. Now really looking into the Subscriptionstreams. I see in the MSDistribution_Agent table there is a column for subscriptionstreams. All the tables are null at this point. I have read several articles about this subject and all point to good things. I can't find any article where it actually SETS THIS UP!! Got any references for me???????
thank you in advance!! :hehe:
January 27, 2011 at 12:25 pm
You can add -subscriptionstreams N in the distribution agent job under the run agent step. Put that in just before the -continuous if you have that in place.
You can review this page as well as there is another option listed there to apply them. I have not tried that method though, only editing the job step.
A note from BOL on this that you should read and consider as follows;
Important
"When you specify a value of 2 or greater for -SubscriptionStreams, the order in which transactions are received at the Subscriber may differ from the order in which they were made at the Publisher. If this behavior causes constraint violations during synchronization, you should use the NOT FOR REPLICATION option to disable the enforcement of constraints during synchronization."
Let me know if I can help further.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 27, 2011 at 3:03 pm
OK Dave...still digging and scratching here!!
In taking a look at the Distribution database, I see (for the most part) 3 jobs for each publication. REPL_LogReader, REPL_Snapshot and REPL_Distribution. Is it the REPL_Distribution job that I add the -Subscriptionstreams N to?? I do have -Continious on those jobs.
:w00t:
January 27, 2011 at 3:04 pm
January 27, 2011 at 3:13 pm
Angelindiego (1/27/2011)
OK Dave...still digging and scratching here!!In taking a look at the Distribution database, I see (for the most part) 3 jobs for each publication. REPL_LogReader, REPL_Snapshot and REPL_Distribution. Is it the REPL_Distribution job that I add the -Subscriptionstreams N to?? I do have -Continious on those jobs.
:w00t:
Aye - it would be the REPL_Distribution job that you want to change. 😉
By the way when I specify N in the "-subscriptionstreams N" command, N is some number that you define. I would start with 2 and see how that works. I have gone up as high as 8 but found that it didn't really seem to benefit any higher than 4 in my situation. That was all trial and error though. :hehe:
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 27, 2011 at 3:15 pm
Angelindiego (1/27/2011)
....but there's more............Say I have 20 jobs for replication....do I have to go thru each one manually and add this parameter or is there a quick and dirty script for that (built or not)??
There is a script option that I posted in the link two posts ago. Again though I haven't used it but it should work ok.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 27, 2011 at 3:25 pm
January 27, 2011 at 3:27 pm
My pleasure!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply