We are investigating the possibility to use transactional replication for an archive / reporting database in order to offload the production database. If we use the same software/client for accessing the archive, the client will update some tables in the archive/consumer like access logs i.e. primary keys/identifiers will not match the production database (which is not an application problem). The log function only adds rows to a table. Is this possible or will the transactional replication fail? Other issues?
Asked
Active
Viewed 25 times
2 Answers
0
If you have Enterprise Edition of SQL Server, please look at Peer-to-Peer Transactional Replication, that may fit the bill, allowing you to have changes made at the subscriber sync'd with the publisher (each node is a republisher).
Greg
- 3,861
- 3
- 23
- 58
0
I did the trial-and-error method and configured a transactional replication which was not that obvious but it seems to work. I then tried to add rows to a table in the subscriber / archive database and also to the same table in the producer side. What will happen is that there will be a primary key violation since the IDENTITY ranges are the same on both sides. One way to handle this is described here, PK Violation after transactional replication
-
are you saying you do not require the publisher/subscriber to be in sync? – Greg Oct 20 '15 at 22:05