Difference between revisions of "Master Database Replication"

Latest revision as of 14:50, 20 January 2009

With a warm standby setup of 2 Sybase ASE's and a Replication Server it is often needed to keep the login information between those 2 database servers in sync. Various methods can be used to achieve this, but with Replication Server 15 in combination with ASE 15 you can now use "master database replication". Master database replication is specifically designed to keep roles, logins and password characteristics in sync between ASE's.

This page describes the steps to be taken to create a warm standby environment for the the master database.

Assumptions of the environment

The setup makes the following assumptions:

• The Replication Server is already configured and running.
• Replication is done between ASE servers
• The master database between the two ASE servers are in sync, in other words: logins, passwords and roles are the same across the two master databases.

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, password and suid of the maintenance user on both servers are identical. For security reasons, do not alias the account to dbo.

use master
go
sp_addlogin "<maintenance_user>","<maintenance_user_password>"
go
grant role replication_role to "<maintenance_user>"
go
sp_adduser <maintenance_user>
go

-- check the value of the suid
select suser_id("<maintenance_user>")
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 both servers.

isql -Usa -P<password> -S<active_server> -Dmaster -i changed_rs_install_primary.sql 

Grant the maintenance user permissions to the new objects, run the following statements on both servers.

grant execute on rs_marker to <maintenance_user>
grant execute on rs_ticket to <maintenance_user>
grant execute on rs_ticket_report to <maintenance_user>
grant execute on rs_get_lastcommit to <maintenance_user>
grant execute on rs_update_threads to <maintenance_user>
grant execute on rs_check_repl_stat to <maintenance_user>
grant execute on rs_update_lastcommit to public
grant execute on rs_initialize_threads to <maintenance_user>
grant all on rs_threads to <maintenance_user>
grant all on rs_lastcommit to <maintenance_user>

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>.master

Create a connection from the replication server to the active database

create connection to <active_server>.master
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>.master

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

Configuration of the ASE servers

Configure the Rep Agent

The configuration of the Replication Agent can now be done. Apply the commands on both ASE's.

use master
go
sp_config_rep_agent "master", "enable", "<repserver>", "<rep-agent_user>", "<rep-agent_password>"
go
sp_config_rep_agent "master", "send warm standby xacts", true
go
sp_setreplicate rs_marker,"true"
go
sp_setreplicate rs_update_lastcommit,"true"
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. Again, apply the commands on both ASE's.

use master
go
sp_reptostandby "master","all"
go

Start the RepAgent

Now you can start the RepAgent on the active ASE.

sp_start_rep_agent "master"
go

Configure the replication server for the standby database

Create a connection from the replication server to the standby server

create connection to <standby-server>.master
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>.master

Start the connection from the replication server to the standby database

resume connection to <standby-server>.master

Restart the connection from the replication server to the active database

When the connection to the standby server is created, the connection to the active database is stopped. Just restart it.

resume connection to <active-server>.master

Congratulations, you now have a working Warm Standby configuration for the master database!