SQL Server Performance

Replication between 3 servers

Discussion in 'SQL Server 2005 Replication' started by Raja.V, Aug 15, 2007.

  1. Raja.V New Member

    Hi Everyone.....
    There are 3 servers ServerA, ServerB and ServerC.
    I need to replicate the data from Server A to Server C.But because of some firewalls between them I cannot do that.So I take the intermediate server as ServerB and then first replicate the data from ServerA to ServerB where ServerA is the publisher and ServerB is the subscriber.. .Now from here again I setup replication between ServerB and ServerC as ServerB being the publisher and ServerC being the subscriber.So in this case ServerB acts as both publisher as well as a subscriber.So by this way I will have to replicate the data from ServerA to ServerC...
    I actually tested this on a dev server with in 3 databases... I mean I did this on a single instance of SQL server 2000 with 3 databases in it.......so when I replicated from DB1 to DB2 all data got replicated.After that replication I again started another replication on DB2 and DB3 but the problem here is when i select the articles to publish it says the tables does not have a primary key so again I went to individual tables in DB2 and then altered the table inorder to add the primary key constraint by using the command
    USE <DB2>
    GO
    ALTER TABLE <TABLE NAME>
    ADD CONSTRAINT ID_PK PRIMARY KEY (EMPLOYEE_ID) ;
    So I did this on every table on the DB2 database and then again replicated it to DB3 when the replication from DB1 to DB2 is still going on ......
    So basically DB2 was a publisher as well as a subscriber at this point .
    Everything got replicated perfectly, except for the point of adding the primary constraint to the DB2 database.So is there anyway I can avoid adding the primarykey constaint after doing the replication between the DB1 and DB2 and start the replication between DB2 and BD3 ???
    Now my question is
    1) Will I be able to do that same thing which i have done in a single instance with the differnet servers in differnet instances???i.e., replicating the data from ServerA to ServerC ?
    2) If not can I just do the replication between the ServerA and ServerB then copy the data in to a different database on ServerB and then again replicate it from ServerB to ServerC
    or if there is anyother way please do let me know abt that
    Thanks in advance,
    Regards,
    Raja.V
  2. ndinakar Member

    For Transactional Replication, the tables must have primary key to be replicated. In your scenario, fix all your tables that need to be replicated. Then backup the db and restore it on both ServerB and ServerC and then re-setup replication from A--> B and B--> C.
  3. dineshasanka Moderator

    does peer-to-peer replication fits to your problem. But you need to have sql server enterprise edition to enable p2p replication

Share This Page