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
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
