Re-Sync after Primary DB load

When loading a dump into a primary database, the connections to the primary and the warm-standby databases need to be dropped and rebuild. In this case, also disable the REP-Agents on both DBs:

use <DB>
go
sp_config_rep_agent <DB>,"disable"
go

Do this step after dropping the replication connections. After loading the dump into the primary database, before setting up replication, don't forget to:

• disable the RepAgent again
• drop the maintenance alias user again and re-add it as a regular user.

When just resyncing a Warm-Standby database, follow these instructions:

Sync WS replication

• On primary replication server, drop physical connections:

drop connection to <rep.srv._ws>.<replicated.db>
go

• disalbe rep_agent on WS

use  <replicated.db>
go
sp_config_rep_agent <replicated.db>, "disable"
go

• drop <replicated.db>_maint user or alias in WS db

use  <replicated.db>
go
sp_dropalias <replicated.db>_maint
go

• drop <prim.db>_maint user or alias in prim. db

sp_dropalias <prim.db>_maint
go

• sp_adduser _maint to db

sp_adduser <prim.db>_maint
go

• check logical connection name on REP.srv.

admin logical_status
go

• rebuild WS connection to WS with rs_init
  • When asked if this DB is a replicated DB, answer YES
  • Setup a "physical connection for a logical connection"
  • Setup a standby database
  • "initialise using dump and load"? -> YES
  • "use dump marker"? -> YES

• drop <replicated.db>_maint user on prim

sp_dropuser <prim.db>_maint
go

• add alias (user <prim.db>_maint) to dbo

sp_addalias <prim.db>_maint, dbo
go

• dump and load db

dump database <prim.db> to "/dumpdir/<prim.db>
go

load database <replicated.db> from "/dumpdir/<prim.db>.bck"
go

• set db online

online database <replicated.db>
go

• resume connection to WS

resume connection to <rep.srv._ws>.<replicated.db>
go

If you have any problems resuming the connection and you find messages like these in the Rep-Errorlog:

"The DSI thread for database '<rep.srv._ws>.<replicated.db>' is being
shutdown. DSI received data server error #17232 which is mapped to
STOP_REPLICATION. See logged data server errors for more information.
"The error was caused by line #158 of stored procedure 'sp_dropuser'
on the data server mapped from input command #2 (or a command before
it) of the failed transaction."

or:

"The DSI thread for database '<rep.srv._ws>.<replicated.db>' is being
shutdown. DSI received data server error #17234 which is mapped to
STOP_REPLICATION. See logged data server errors for more information.
The error was caused by line #167 of stored procedure 'sp_addalias'
on the data server mapped from input command #2 (or a command before
it) of the failed transaction."

then you probably did something wrong while dropping and recreating the 'maintenance user'. Repeat the following command until you find a different error message. Doing this, you will skip the "create-" and "drop-user" commands, which you don't need on the replicated system.

resume connection to <rep.srv._ws>.<replicated.db> skip tran
go