Warm Standby setup instructions - Revision history

Psap: /* Preparation of the Replication Server */

2015-10-12T11:50:12Z

‎Preparation of the Replication Server

Psap at 12:29, 27 February 2013

2013-02-27T12:29:05Z

Psap at 11:23, 30 October 2012

2012-10-30T11:23:29Z

Psap at 00:07, 22 March 2007

2007-03-22T00:07:00Z

Psap at 00:01, 22 March 2007

2007-03-22T00:01:49Z

Psap at 15:17, 21 March 2007

2007-03-21T15:17:39Z

Psap at 15:13, 21 March 2007

2007-03-21T15:13:04Z

Psap at 15:05, 21 March 2007

2007-03-21T15:05:29Z

New page

Sybase Replication Server allows you to create and maintain a warm standby environment. The creation can be done with, for instance, the rs_init utility or Sybase Central. In some cases extra flexibility is needed and this can best be achieved when command line statements are used.
For various other reasons people prefer using this method.

This page describes the steps to be taken to create a warm standby environment through a combination of commands given to ASE and Replication Server. It should be considered as a kind of worksheet. Feel free to make changes to suit your individual need. This might be needed when you are dealing with a database with big table(s) holding text or image datatypes or you want to optimize performance using replication definitions and subscriptions.
At the end, the commands are given to create a third copy of the database to build, for instance, a separate reporting environment. To use this feature you need RepServer 12.6 or later.

==Assumptions of the environment==
* The setup makes the following assumptions:
* The Replication Server is already configured and running.
* Replication is done between ASE servers
* Materialization will be done with a dump-load scenario
* Replication is performed without replication definitions and subscriptions
* User activity will continue throughout the creation process
The setup is divided into a number of steps, where each step is a separate unit of work.

==Preparation of the Sybase ASE-servers==
'''Create a maintenance user on both ASE's''' The maintenance user is used by the replication server to apply transactions. Make sure that the name and password of the maintenance user on both servers are identical. For practical reasons make the maintenance user aliased to "dbo" in the database. Since a dump-load scenario is used to materialize the standby database, make sure that the suid of the maintenance user on both servers are identical.

sp_addlogin "<maintenance_user>","<maintenance_user_password>"
go
grant role replication_role to "<maintenance_user>"
go
-- check the value of the suid
select suser_id("<maintenance_user>")
go
use <database>
go
sp_addalias "<maintenance_user>","dbo"
go

'''Install the replication server stored procedures and tables''' To do this, make a copy of the script rs_install_primary.sql located in the $SYBASE/$SYBASE_REP/scripts directory. Remove the last two commands of the script (the dbcc settrunc and the sp_setreplicate commands). The commands that are skipped will be executed at a later stage, when the replication agent is configured. Apply the script on the active server in the right database.
isql -Usa -P<password> -S<active_server> -D<database> -i changed_rs_install_primary.sql
'''Configure both servers for replication'''

sp_configure "enable rep agent threads",1

==Preparation of the Replication Server==
'''Create a logical connection on the replication server'''. The name of it does not have to match with the name of the primary database server and database, but this convention is widely used.

create logical connection to <active_server>.<database>

'''Create a connection from the replication server to the active database'''

create connection to <active_server>.<database>
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to <maintenance_user>
set password to <maintenance_user_password>
with log transfer on
as active for <active_server>.<database>

'''Create a login in the replication server''' This login is used by the rep-agent running in the ASE to connect to the replication server.

create user <rep-agent_user> set password <rep-agent_password>
go
grant connect source to <rep-agent_user>
go

==Configure the RepAgent==
The configuration of the Replication Agent within the active ASE should now be done. All steps within this paragraph should be executed within a controlled time frame since the transaction log cannot be cleared between the execution of the first sp_config_rep_agent and the sp_start_rep_agent.
The configuration is only needed on the primary server. Since we use a dump-load scenario the configuration is copied to the standby server during the materialization phase.

@@ Line 85: / Line 85: @@
   go
-==Mark the database for replication==
+==Mark the database for replication and grant permissions==
 Execute the sp_reptostandby stored procedure in the active ASE to activate replication from the active database to the replication server.
   use <database>
   go
   sp_reptostandby "<database>","all"

← Older revision		Revision as of 00:01, 22 March 2007
Line 3:		Line 3:

	This page describes the steps to be taken to create a warm standby environment through a combination of commands given to ASE and Replication Server. It should be considered as a kind of worksheet. Feel free to make changes to suit your individual need. This might be needed when you are dealing with a database with big table(s) holding text or image datatypes or you want to optimize performance using replication definitions and subscriptions.		This page describes the steps to be taken to create a warm standby environment through a combination of commands given to ASE and Replication Server. It should be considered as a kind of worksheet. Feel free to make changes to suit your individual need. This might be needed when you are dealing with a database with big table(s) holding text or image datatypes or you want to optimize performance using replication definitions and subscriptions.
−	~~At the end, the commands are given to create a third copy of the database to build, for instance, a separate reporting environment. To use this feature you need RepServer 12.6 or later.~~

	==Assumptions of the environment==		==Assumptions of the environment==

← Older revision		Revision as of 15:17, 21 March 2007
Line 111:		Line 111:

	Congratulations, you now have a working Warm Standby configuration!		Congratulations, you now have a working Warm Standby configuration!
		+
		+
		+	[[Category:RepServer]]

← Older revision		Revision as of 15:13, 21 March 2007
Line 60:		Line 60:
	The configuration of the Replication Agent within the active ASE should now be done. All steps within this paragraph should be executed within a controlled time frame since the transaction log cannot be cleared between the execution of the first sp_config_rep_agent and the sp_start_rep_agent.		The configuration of the Replication Agent within the active ASE should now be done. All steps within this paragraph should be executed within a controlled time frame since the transaction log cannot be cleared between the execution of the first sp_config_rep_agent and the sp_start_rep_agent.
	The configuration is only needed on the primary server. Since we use a dump-load scenario the configuration is copied to the standby server during the materialization phase.		The configuration is only needed on the primary server. Since we use a dump-load scenario the configuration is copied to the standby server during the materialization phase.
		+
		+	use <database>
		+	go
		+	sp_config_rep_agent "<database>", "enable", "<repserver>", "<rep-agent_user>", "<rep-agent_password>"
		+	go
		+	sp_config_rep_agent "<database>", "send warm standby xacts", true
		+	go
		+	sp_setreplicate rs_marker,"true"
		+	go
		+	sp_setreplicate rs_update_lastcommit,"true"
		+	go
		+	sp_start_rep_agent "<database>"
		+	go
		+
		+	==Mark the database for replication==
		+	Execute the sp_reptostandby stored procedure in the active ASE to activate replication from the active database to the replication server.
		+
		+	use <database>
		+	go
		+	sp_reptostandby "<database>","all"
		+	go
		+
		+	==Configure the replication server for the standby database==
		+	All steps within this paragraph should be executed within a controlled time frame since the stable queue in the replication server cannot be cleared between the execution of the create connection and resume connection.
		+	'''Create a connection from the replication server to the standby server'''
		+
		+	create connection to <standby-server>.<database>
		+	set error class to rs_sqlserver_error_class
		+	set function string class to rs_sqlserver_function_class
		+	set username to <maintenance_user>
		+	set password to <maintenance_user_password>
		+	with log transfer on
		+	as standby for <active_server>.<database>
		+	use dump marker
		+
		+	'''Dump the database on the active server so it can be loaded into the standby server'''
		+
		+	dump database <database> to "<file-name>"
		+
		+	'''Load the database dump into the standby server'''
		+
		+	load database <database> from "<file-name>"
		+	go
		+	online database <database>
		+	go
		+
		+	'''Start the connection from the replication server to the standby database'''
		+
		+	resume connection to <standby-server>.<database>
		+
		+	Congratulations, you now have a working Warm Standby configuration!

@@ Line 63: / Line 63: @@
 This login is used by the rep-agent running in the ASE to connect to the replication server.
-  create user <rep-agent_user> set password <rep-agent_password>
+  create user <rep-agent_user>
   go
   grant connect source to <rep-agent_user>

@@ Line 32: / Line 32: @@
 '''Install the replication server stored procedures and tables'''
-To do this, make a copy of the script rs_install_primary.sql located in the $SYBASE/$SYBASE_REP/scripts directory. Remove the last two commands of the script (the dbcc settrunc and the sp_setreplicate commands). The commands that are skipped will be executed at a later stage, when the replication agent is configured. Apply the script on the active server in the right database.
+To do this, make a copy of the script rs_install_primary.sql located in the $SYBASE/$SYBASE_REP/scripts directory. Comment out 2 commands of the script: the dbcc settrunc and the sp_setreplicate commands. These can be found at the end of the file, or just above the definition of the rs_send_repserver_cmd stored procedure.
   isql -Usa -P<password> -S<active_server> -D<database> -i changed_rs_install_primary.sql

@@ Line 14: / Line 14: @@
 ==Preparation of the Sybase ASE-servers==
-'''Create a maintenance user on both ASE's''' The maintenance user is used by the replication server to apply transactions. Make sure that the name and password of the maintenance user on both servers are identical. For practical reasons make the maintenance user aliased to "dbo" in the database. Since a dump-load scenario is used to materialize the standby database, make sure that the suid of the maintenance user on both servers are identical.
+'''Create a maintenance user on both ASE's'''
   sp_addlogin "<maintenance_user>","<maintenance_user_password>"
@@ Line 28: / Line 30: @@
   go
-'''Install the replication server stored procedures and tables''' To do this, make a copy of the script rs_install_primary.sql located in the $SYBASE/$SYBASE_REP/scripts directory. Remove the last two commands of the script (the dbcc settrunc and the sp_setreplicate commands). The commands that are skipped will be executed at a later stage, when the replication agent is configured. Apply the script on the active server in the right database.
+'''Install the replication server stored procedures and tables'''
   isql -Usa -P<password> -S<active_server> -D<database> -i changed_rs_install_primary.sql
 '''Configure both servers for replication'''
@@ Line 35: / Line 41: @@
 ==Preparation of the Replication Server==
-'''Create a logical connection on the replication server'''. The name of it does not have to match with the name of the primary database server and database, but this convention is widely used.
+'''Create a logical connection on the replication server'''
   create logical connection to <active_server>.<database>
@@ Line 49: / Line 57: @@
   as active for <active_server>.<database>
-'''Create a login in the replication server''' This login is used by the rep-agent running in the ASE to connect to the replication server.
+'''Create a login in the replication server'''
   create user <rep-agent_user> set password <rep-agent_password>
@@ Line 83: / Line 93: @@
 ==Configure the replication server for the standby database==
 All steps within this paragraph should be executed within a controlled time frame since the stable queue in the replication server cannot be cleared between the execution of the create connection and resume connection.
 '''Create a connection from the replication server to the standby server'''