How to Stop Sybase ASE Replication in These 2 Steps

You must be knowing conventional method of stopping and starting the replication between primary and secondary server. This involves multiple steps at replication server like suspending replication, dropping the connection and performing few more steps at primary server. These steps again depend on whether we want transaction logs to be propagated to replication server and stop movement of logs from replication to secondary server. There are many key points to be considered while suspending or stopping replication.

Here, we are going to discuss two easy steps to suspend replication between primary and secondary server. Before getting into these two methods, lets understand types of replication from where it could be controlled:

1. Session Level Control
2. Object Level Control at Database
3. Database Level Control

We are not going into 3rd option as of now since this is the most detailed one and requires due deliberations from multiple perspective – Size of Transaction Logs at Primary database, Network Bandwidth, Queue Size at Replication server etc.

We shall start with 1st option which is Session Level Control, replication can be stopped and started for any database session within a set of query or inside a stored procedure by running below command:

set replication off
go
/*
Your code of changes(insert/update/delete) etc..
*/
set replication on
go

Any code inside above block will not be considered for replication and will not be replicated from Primary to Secondary server. Please keep in mind that the user who has to execute above code has to have replication role assigned(at least). If sa role is assigned then that will be sufficient.

Now, lets see the second option which is at object level in the database. Required permission is same as 1st option. Please note that this would clear secondary log chain and hence we need to re-initialize connection to secondary at replication server. Steps would be as below:

/*At Replication Server */
suspend connection to data_server.database_name
go
drop connection to data_server.database_name
go
/*At Primary Server */
sp_reptostandby database_name, ‘none’
go

Once job is completed which you don’t want to replication, you may turn on the replication using below command:

/*At Primary Server */
sp_reptostandby database_name, ‘all’
go

Please note that above step would take sometime to execute based on load at the server and also could cause blocking, so its advisable to execute them after working hours or at a time when there is less activity on the server. Afterwards, you would need to configure the data_server connection at replication server and dump marker needs to be created, database dump needs to be taken at primary server, loading should be done on secondary server and then we can resume connection at replication server for the data_server of this database.

Please leave your comment if you liked this post or have any feedback.