Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
creieru
Starting Member
12 Posts |
 Posted - 2009-12-04 : 06:22:56
|
| Hello,i have 2 servers in 2 different locatio, 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 subscriberDo 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 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-04 : 08:14:40
|
| No. In fact replication will break and you'll need to recreate the publication(s) as soon as you restore the subscriber over the publisher.Basically, you have two choices. (1) for those rare updates/inserts, simply mandate that they occur on the publisher only, this way they'll be replicated to the subscriber(s). (2) Don't replicate those rarely updated tables and manually update in both locations when required.I would choose option 1 above in most cases.IMPORTANT: A subscriber should be treated as READ ONLY. You can't make it read only (because that would break replication) but it should be treated as such.If you need to allow write operations on both, then you should choose Peer to Peer Replication. |
 |
|
|
|
|
|
|
|
nedd a sugestion for transactional replication