In this post, I will describe SRS Business Continuity solution based on Multi Site Availability feature. Multi Site Availability (MSA) can be considered as an extension of Warm Standby that we saw in previous post . It allows to replicate a source database against one or several target databases. (1:n replication, 1 source database and n target databases). MSA is a proven replication solution. It was first shipped with version SRS 12.6 in 2003.
Replication to multiple targets
Database replication scope
DML replicationDDL replication
No downtime when initializing target
SAP ASE to SAP ASE replication
ORACLE to ORACLE replication
Target databases are online; they can be used to offload reporting activities
Use Cases MSA may be used in various use cases. I will give examples for server and site availability.
This picture depicts a MSA system with several targets. It is used for both server availability and workload distribution purposes. Assume a hotel booking system. Readonly clients consult the availability of rooms on the target databases. Actual reservations are booked on the source database and replicated in real time on target databases.
Source, middleware and target systems are hosted in the same location (New York data center).
In normal operations:
Source system is used by read/write clients.
Target systems are used by readonly clients.
In case of a planned downtime (for maintenance purposes) or a unplanned downtime of host #1:
one of the target databases (of host #3 - #6) will take the role of source database, and replicate against the other targets
read/write clients will be switched on the new source database
This picture depicts a MSA setup used for site availability purposes.
Source and two target systems are hosted in different locations (Paris data center and Amsterdam data center).
In normal operations:
Source system (from host #1) is used by production applications. It is hosted in Paris data center.
Target system (from host #2) is a local database for HA purposes; it is hosted in Paris data center as well.
Target system (from host #3) is a remote database for DR purposes; it is hosted in Amsterdam data center.
In case of a planned downtime or unplanned downtime:
Paris target system is preferedly used in case of failure of source database server.
Amsterdam target system is used in case of failure of the Paris data center site.
There are two SRS here, one for the source site and another on the remote DR site. Having two SRS is a best practice for remote replication. It increases the performance and the resilience level of the replication system.
Setting up a MSA configuration.
Database replication definitions and database subscription.
A MSA configuration relies on two main concepts:
Database replication definition Data from the source database is “published” via one “database replication definition”. The database replication definition details the location of source data (server name, database name). By default all the tables of the source database are involved. There is one “database replication definition” per MSA system.
Database subscription(s) In order for the source database to be replicated to a target database , a “database subscription” must be defined for the target database. The database subscription refers to a particular “database replication definition” and the location of the target database. Several “database subscriptions” can refer to the same “database replication definition”.
Installing a MSA is a simple process. Like Warm Standby, setting up the replication system can be done without any downtime. There is no need to stop the primary site. Replication sites are initialized (or materialized) with a backup of the primary site using “dump marker”. To setup a MSA system, you can run the following procedure (prior installation of SRS is assumed):
Create a connection from the replication server to the active database on the replication server
create connection to <source_server>.<source_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
Create a login on the replication server
create user <rep-agent_user> set password <rep-agent_password> grant connect source to <rep-agent_user>
Mark the source database for replication (here DML + DDL operations replicated) exec sp_reptostandby "<source_database>","all"
Create a connection from the replication server to the standby server create connection to <target_server>.<target_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>
Create the database replication definition for the source database create database replication definition my_database_repdef with primary at <source_server>.<source database>
Create the database subscription for the target database define subscription my_database_subscription for database replication definition my_database_repdef with primary at <source_server>.<source database> with replicate at <target_server>.<target_database> subscribe to truncate table use dump marker
Backup the database on the source server
Transfer and restore the database backup into the target server
Start the connection from the replication server to the target database resume connection to <target_server>.<target_database>
Complex configurations can be set up with MSA. Multiple targets can be defined easily.
Bi-directional replication can be achieved by defining two MSA systems simultaneously.
Heterogeneous replication setups are possible with MSA, for instance ORACLE source -> ORACLE targets and ORACLE source -> SAP ASE targets.
Usually for Business Continuity all the objects are included for replication. The replication scope, however can be customized by defining a subset of tables to replicate.
Replication Server does not switch client applications to the target database. However, client applications can be redirected by updating directory information (DNS, or $SYBASE/interfaces).
It is recommended that tables in the source and target databases have a primary key defined.
For performance purposes, in addition to database replication, regular table replication definitions should be created as well.
The next post will outline a Business Continuity solution that implements MSA replication in order to provide an HA/DR capability for SAP Business Suite running on SAP ASE.