Technology Tips And Tricks

Sometimes it is necessary to resync the warm standby replication. This could be due to an error, or if replication definitions need to be changed, like changing replication behavior for text and image columns.

In this case the steps to resync a warm standby are as follows.

These are the commands used to re-sync a standby database.

isql -Usa -P<sa_password> -S<rep_server>

>suspend connection to <ws_server>.<ws_db>
>go
>drop connection to <ws_server>.<ws_db>
>go

isql -Usa -P<sa_password> -S<priamary_server>

>use <source_db>
>go
>sp_stop_rep_agent "<source_db>"
>go

isql -Usa -P<sa_password> -S<rep_server>

>create connection to <ws_server>.<ws_db>
>set error class to rs_sqlserver_error_class
>set function string class to rs_sqlserver_function_class
>set username to <source_db>_rep
set password to <password>
>with log transfer on
>as standby for <primary_server>.<source_db>
>use dump marker
>go

isql -Usa -P<sa_password> -S<primary_server>

>use <source_db>
>go
>sp_start_rep_agent "<source_db"
>go

isql -Usa -P<sa_password> -S<rep_server>

>admin logical_status
>go

–it should say Suspended/Awaiting for Enable Marker on <ws_server>.<ws_db>. DO NOT PROCEED if this is not there.

isql -Usa -P<sa_password> -S<primary_server>

>dump database <source_db> to "<file-name>"
>go

isql -Usa -P<sa_password> -S<ws_server>

>load database <ws_db> from "<file-name>"
>go
>online database <ws_db>
>go
>sp_dropalias <source_db>_rep
>go
>sp_dropuser <source_db>_rep
>go
>sp_addalias <source_db>_rep.dbo
>go

The previous steps are only necessary to synchronize the master database syslogin entry with the database users. If your primary database and the warm standby database are in synch with the user definitions, this step can be omitted.

isql -Usa -P<sa_password> -S<rep_server>

>resume connection to <ws_server>.<ws_db>
>go

Check the status of your replication with the System Check commands.