Unanswered: Replication, identity columns and two-way communication
At the moment there is an internal database server (SQL1) which is accessed directly by employees. A new SQL Server (SQL2) has been installed outside of the firewall and will be used by the webserver, it is therefore necessary to get updates from SQL1 being pushed out to SQL2.
At the moment I have setup Transaction Replication to push out updates on a 30min interval with SQL1 as the distributor. I had considered the automatic immediate updates, but wasn't sure about how much traffic it was likely to generate -- the updates are likely to be few and far-between -- especially since the firewall is not the most efficient at present.
I had warnings about identity columns but I didn't consider it as a problem since 99% of updates will be from SQL1 to SQL2, without any updates/inserts on SQL2.
However, there are one or two tables which will have records inserted into it on SQL2, and so updates need to be sent from SQL2 to SQL1 also. The identity column is used to generate a reference number, but it doesn't necessarily have to be in sequence.
Is it ok to just create a publication on SQL2 and set it up to push updates to SQL1, or set up SQL1 to pull updates from SQL2. Also, what's the best way to get around the problem with ID columns in tables on SQL2 no longer have the Identity column set so as to enable replication etc. Can I get away with just setting a different range of values for each server?
I've never touched replication before today so sorry if I've misunderstood something.