Replication/Distributed Transaction Question

We are currently running one SQL 2000 Server.  This runs in our main office.  We have a second company at a remote location, connected via a VPN over the internet with T-1s at each end.  The remote office uses Citrix for running our ERP application.  I was wondering if they could stop using Citrix and run the ERP application locally by placing a second SQL server at their location.  We are starting to run into some capability issues with Citrix.

The ERP database tables are divided into three groups.  One group for Company A, one for Company B, and one for Consolidated Information.  Users at both companies make frequent updates to their own tables, infrequent updates to the Consolidated tables, and only once in a blue moon updates to the other company's tables. Users at each company make frequent read requests to the Consolidated tables.

So here is how I would see this working. Can someone tell me if SQL Server supports this?

Users at Company A would connect to Server A at the main office.
Users at Company B would connect to Server B at the remote office.

Server A would handle all updates to tables in the Consolidated Group and the Company A group. If would then replicate those updates to Server B.

If Server A received a request to update a table in the Company B group, it would know that it had pass control of those updates to Server B and wait for Server B to replicate those changes back. It would however, be able to handle read operations for tables in the Company B group.

Conversly, Server B would be able to handle updates for tables in the Company B group, but would have to pass control to Server A for updates to tables in the  Consolidated or Company A groups. It would be able to handle read operations for both Consolidated and Company A tables.

I think for this to work, a lock would have to be counted as an update, and control handled by the appropriate server.

Does this sound like it is even close to something that can be done with replication, or am I way off base?

Just to clarify, I don't have the option of making changes to the ERP code to allow it to connect to two different servers. The user pick their database when they start, and the application expects all three groups of table to be within that same database.

Thanks in advance.
Who is Participating?
andrewbleakleyConnect With a Mentor Commented:
Transactional Replication would be a good choice in this senario. Look it up in SQL BOL or MSDN. But the best way is (if possible) setup a dummy LAN and try it out. SQL Server handles most what-ifs for you, it is easy to setup and configure and I find it highly reliable
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.