locked
Transaction replication - subscriber db movement to another drive RRS feed

  • Question

  • Pub,& distr in same server : ServerA    ,   sub 1 in server B,   sub2 in server C

    Database :  DB   (pub) 

    Database : DB1     (sub 1)  

    Database : DB2     (sub 2) 

    Type:  transnational replication

    Scenario:

    Server B, going to reconfigure. there is disaster may happen on that database drive.

    for safer side i want to move this (sub 1) db to persistent Drive.

    how to move this subscriber db into another drive during replication setup?


    correct me if am wrong on below workaround:

    confirm no pending undistributed cmd

    stop “Log Reader Agent”.

    Snapshot Agent should be completed and stop


    make sub db offline with single user

    modify file name in system catalog

    copy mdf,ldf file physical to new location

    set da online & multi user mode


    start all replication jobs which is stopped earlier.



    Friday, December 21, 2018 10:35 AM

Answers

  • On a subscriber, you should not need to do anything special.  Detach the database, move the physical files to the new location, attach the database.   

    Replication to the subscriber may error while you move the files, but will catch up automatically when the database is reattached.  This is no different than if the subscriber was down for a period of time.

    Friday, December 21, 2018 12:41 PM

All replies

  • On a subscriber, you should not need to do anything special.  Detach the database, move the physical files to the new location, attach the database.   

    Replication to the subscriber may error while you move the files, but will catch up automatically when the database is reattached.  This is no different than if the subscriber was down for a period of time.

    Friday, December 21, 2018 12:41 PM
  • I like the backup and restore option (with keep replication).  Tom's way means less of an outage.

    When you backup the databases you can verify it to ensure that the backup is valid.

    If you do the detach move re-attach there is always a danger that some damage will occur during the detach.

    Either way you should run a checkdb before doing it either way.

    Friday, December 21, 2018 2:13 PM
    Answerer