Replication/Distributed Transaction Question

Posted on 2005-05-13
Last Modified: 2010-03-19
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.
Question by:richn
    1 Comment
    LVL 11

    Accepted Solution

    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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how the fundamental information of how to create a table.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now