December 4, 2009 at 4:26 am
Hello,
i have 2 servers in 2 different location, server A and server B, and i have set a transactional replication between these two (server A act as distribuitor and publisher, and server B acts as a subscriber). The replication works fine, but i have a situation that a have to resolve.
The replicated database contains some tables which are lists of countries, lists of currencies that are rarely updated. Let's say that in server A, in the table than contain a list of countries (CountryTable), a new country is added. Than this will be replicated to table B. But if a new country is added to CountryTable from server B, that record will not appear in server A, and if these new country will be needed in server A, than it will be inserted in CountryTable from server A, and replicated in CountryTable from B location (the country will be than duplicated).
To resolve this, first i've tried to set up a transactional replication with updatable subscribers...but my database contains many tables, and both servers were running very slow after i configured this type of replication.
So, i've choose simple transactional replication.
I'm thinking that at an specified time interval, to backup the database from server B (witch will contain all datas) and than to restored in server A.
so, i've made a SSIS package to:
- backup database from server B
- run script to drop publication from A and subscriber from B
- restore the database in server A
- run script to recreate the publication and it's subscriber
Do you this that this is OK? or it might be another way...like consider just to stop the replication, than restore and than just start replication again? It will be a problem to drop-recreate publication for many times?
Thanks
December 4, 2009 at 5:03 am
What you have suggested (SISS package) does seem to be a logical way round your issue (if a bit long-winded). Is this 'CountryTable' the only excception? Cause if there are certain known exceptions you could always set them as not for replication.
Problem is, as far as I know replication isnt a two way street, its bidirectional. Have to be honest that i have never had much luck with repl, i tend to use mirroring but that wouldnt give you your second server.
Let m know what you come up with.
Adam Zacks-------------------------------------------Be Nice, Or Leave
December 4, 2009 at 5:25 am
the CountryTable table is used in foreign-keys relations...so if i marked it NOT FOR REPLICATION, then i'll have a problem...
December 4, 2009 at 5:44 am
Hmmm, when its changed, is that to add a conutry (which will add a foreigh key) or just to modify some data?
You dont have t o replicate all columns in a table. If you set NOT FOR REPLICATION it can be column specific (havent actually done it before but know its possible).
If you send Gail Shaw (Gilamonster) a message she will know, shes helped me out on this before.
Adam Zacks-------------------------------------------Be Nice, Or Leave
December 4, 2009 at 6:16 am
What about merge replication?
December 4, 2009 at 6:20 am
That might work!
http://msdn.microsoft.com/en-us/library/ms151329.aspx
Adam Zacks-------------------------------------------Be Nice, Or Leave
December 4, 2009 at 7:15 am
Have you thought about dropping that article (table) from the publication (which is Transactional Replication) and create a new publication (transactional Replication with updatable subscriptions) with this article (table).
In short, isolating only that table and have Transactional Replication with updatable subscription.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 7, 2009 at 11:21 am
Schadenfreude-Mei (12/4/2009)
If you send Gail Shaw (Gilamonster) a message she will know, shes helped me out on this before.
However I have a policy whereby I do not answer questions sent via PM, partially cause I get quite a few.
Are you sure it was me? Replication's not one of my strong areas and I really couldn't have answered this question.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 1:03 am
GilaMonster (12/7/2009)
Schadenfreude-Mei (12/4/2009)
If you send Gail Shaw (Gilamonster) a message she will know, shes helped me out on this before.However I have a policy whereby I do not answer questions sent via PM, partially cause I get quite a few.
Are you sure it was me? Replication's not one of my strong areas and I really couldn't have answered this question.
Pretty sure it was you. Might be wrong though. I wasnt suggesting he spam you, you know. Just that you've always helped me. 🙁
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply