table replication setup, Exercises of Computer Science

Download table replication setup and more Exercises Computer Science in PDF only on Docsity!

Kuwait Finance House – Malaysia

i MAL

Setup Details

Replication Server 15.7.1 – Warm Standby and Table

Replication Setup

V1.

ICT Professional Services

ICT Professional Services

• 1 Introduction............................................................................................................. Table of Contents
  • 1.1 Purpose........................................................................................................................
  • 1.2 Scope...........................................................................................................................
  • 1.3 Special Abbreviations and Terminology........................................................................
• 2 Background.............................................................................................................
  • 2.1 Objectives of Warm Standby System...........................................................................
  • 2.2 System Constraints......................................................................................................
• 3 The Warm Standby Solution..................................................................................
  • 3.1 Technical Briefing.........................................................................................................
  • 3.2 Implementation............................................................................................................
    • 3.2.1 Prerequisites...........................................................................................................................................
    • 3.2.2 Databases..............................................................................................................................................
• 4 Warm Standby System Setup................................................................................
  • 4.1 Installation Sequence...................................................................................................
    • 4.1.1 Pre-installation Task...............................................................................................................................
    • 4.1.2 Build Replication Server.........................................................................................................................
    • 4.1.3 Error Class Creation...............................................................................................................................
    • 4.1.4 Change the REP sa password and create ra login.................................................................................
    • 4.1.5 Create Logical Connection.....................................................................................................................
    • 4.1.6 Add Active and Standby Databases.....................................................................................................
    • 4.1.7 Materialize the data..............................................................................................................................
    • 4.1.8 Post-installation Task............................................................................................................................
  • 4.2 ASE Configuration Changes.......................................................................................
    • 4.2.1 New Logins...........................................................................................................................................
    • 4.2.2 Enable RepAgent Thread.....................................................................................................................
  • 4.3 Sybase Replication Server Configurations.................................................................
    • 4.3.1 Stable Device Configuration.................................................................................................................
    • 4.3.2 Configure RepAgent for the Active Database from imalp1 (tuning)......................................................
    • 4.3.3 Configure RepAgent for the Active Database from imaldr (tuning).......................................................
    • 4.3.4 Configure Replication Server RS from REP (tuning)............................................................................
    • 4.3.5 Tune DSI to Active Database (imalp1.imal) from REP.........................................................................
    • 4.3.6 Tune DSI to Standby Database (imaldr.imal) from REP.......................................................................
• 5 Table Replication..................................................................................................
• 6 Appendices............................................................................................................
  • 6.1 Replication Resource File “REP_install.rs” for creating RS Server.............................
  • 6.2 Bcp out.......................................................................................................................
  • 6.3 Bcp in.........................................................................................................................
  • 6.4 Replication definitions................................................................................................
• 7 Switchover Procedure..........................................................................................
  • 7.1 Assumptions...............................................................................................................
  • 7.2 Switchover from Active to Standby.............................................................................
    • 7.2.1 Pre-switch tasks...................................................................................................................................
    • 7.2.2 Switch tasks..........................................................................................................................................
    • 7.2.3 Post-Switch tasks.................................................................................................................................
  • 7.3 Switchback from Standby to Active............................................................................
    • 7.3.1 Pre-switch tasks...................................................................................................................................
    • 7.3.2 Switch tasks..........................................................................................................................................
    • 7.3.3 Post-Switch tasks.................................................................................................................................
• 8 Synchronize replication system using dump & load........................................
  • 8.1 Synchronize Standby using Active dump................................................................... - Manual Procedure STANDBY_RESTORE....................................................................................................
  • 8.2 Resync all Tables.......................................................................................................
• 9 Some useful commands......................................................................................
• 10 Troubleshooting..................................................................................................
• OP17F08 – Setup Details Issue Date: 09-04-
• Version 1.0 Last Revision Date: 09-04-

ICT Professional Services

1 Introduction

1.1Purpose

This document describes the Technical Architecture and implementation of the Warm Standby and Table replication for the continuous availability of the i MAL database.

1.2Scope

This document deals with the Technical Architecture, i.e.: the hardware, software and protocols that underlie and support the functional applications. It does not specify or discuss the functionality of those applications except where necessary to clarify the use of the application technology. It includes the configuration, project implementation, and related procedures.

1.3Special Abbreviations and Terminology

Term Meaning ASE Sybase Adaptive Server Enterprise RS Sybase Replication Server RSSD Replication Server System Database RA / RepAgent Sybase Replication Agent, Sybase thread that captures and forwards the transactions in the database DSI Sybase Data Server Interface that applies the captured transactions to the Disaster Recovery site WS Warm Standby DDL Data Definition Language (object schema changes) DML Data Manipulation Language (insert, update, delete statements)

2 Background

2.1Objectives of Warm Standby System

The main objective of the project is to setup the production database with continuous availability. Apart from the benefit of disaster recovery, the solution can be used for testing and planned maintenance.

2.2System Constraints

i MAL is a 24x7 environment which cannot accept planned or unplanned downtimes. “Sybase Replication Server Warm Standby Configuration” Solution guarantees an online, state- consistent copy of the production databases. Replication Server replicates DDL commands as well as DML commands in a transactional and selective manner thus assuring the consistency between the active and standby sites. Sybase replication server works asynchronously thus reducing the impact on the active site. Replication can be enabled or disabled dynamically in Server, database, object, and session or command level without affecting the production system. Standby databases can be rebuilt or synchronized on the fly, without interrupting the production operation. Sybase Control Center monitors the Sybase ASE and RS servers in the domain and can be OP17F08 – Setup Details Issue Date: 09-04- Version 1.0 Last Revision Date: 09-04-

ICT Professional Services used to automatically trigger defined actions.

3 The Warm Standby Solution

The WS Solution for i MAL database consists of three main logical Sybase server environments, namely Production ASE, Replication Server, and Standby ASE environments.

3.1Technical Briefing

A warm standby system is a pair of state-consistent Adaptive Server databases linked by a single Replication Server  Active database — the Adaptive Server database to which clients are currently connected  Standby database — an online copy of the active database at a separate Adaptive Server on a separate machine  Replication Server — forwards all commands performed in the active to the standby State consistency means that the standby database is consistent with the active database at an earlier point in time  Unlike hot standby solutions, Replication Server does not use two phases commit  Instead, commands are forwarded to the standby as they occur in the active During the latency period, commands may be:  Waiting in the active database log to be read by the RepAgent  Traversing the network  Waiting in a Replication Server stable queue for processing  Awaiting execution at the standby dataserver There are two types of Warm Standby implementation: Automatic  Replication is enabled at the database level  Replicates both data definition language (DDL) and data manipulation language (DML)  Requires Adaptive Server and Replication Server 11.5 or later Semiautomatic  Replication is enabled at the table level  Replicates only DML  Requires Adaptive Server 10.0 or later and Replication Server 11.5 or later Automatic Warm Standby:  Replicates an identical image of the active database, including changes to object schemas  All attributes of the standby should match the active: Users and permissions Object and device names  All tables in the database are marked for replication at once  DDL transactions are issued in the standby using the same login and password that executed them in the active database  DML transactions are issued by the maintenance user  Only the results of triggers are replicated Replication Server turns off triggers for connections to the standby using set triggers off OP17F08 – Setup Details Issue Date: 09-04- Version 1.0 Last Revision Date: 09-04-

ICT Professional Services

3.2 Implementation

3.2.1 Prerequisites

Before starting the replication configuration, the following should be ready:

• ASE should be installed on Production, DR and reporting servers
• Make sure imal databases on all servers have the same size and their log & data distributed properly on their devices
• ASE should be installed on the replication server to hold the replication database repository REP_RSSD
• Check the related guides and release bulletins from SAP Sybase
• The replication binaries should be already installed on the replication server
• Create "ra" login on all ASE servers with SA, SSO, MON, REPLICATION roles

3.2.2 Databases

The user databases involved for the warm standby setup are "imaldb" and "imaldb_con" OP17F08 – Setup Details Issue Date: 09-04- Version 1.0 Last Revision Date: 09-04-

ICT Professional Services

4 Warm Standby System Setup

4.1Installation Sequence

4.1.1 Pre-installation Task

a) Make sure DR ASE Server is ready and configured with all the databases created with the matching sizes b) Stop any scheduled dump database jobs on these databases till we complete the WS configuration c) Double check the connectivity between Production & DR site for the ASE with respect to the firewalls and rules d) Make sure tables syslogings and sysloginroles are identical on PROD and DR ASE (you can use bcp in/out – refer to bcp section) e) Add the server entry called REP on the replication server o In Windows add the below to the sql.ini file: [REP] master=TCP,10.20.209.11, query=TCP, 10.20.209.11, o In UNIX add the below to the interfaces file: REP master tcp ether 10.20.209.11 4488 query tcp ether 10.20.209.11 4488 f) Create two maintenance user logins at both ASEs (production & DR) use master go sp_addlogin imaldb_maint, "imaldb_maint_ps", imaldb go sp_role 'grant', 'replication_role', 'imaldb_maint' go g) Enable rep agent threads at both ASEs sp_configure "enable rep agent threads", 1 h) Run sp_reptostandby ALL for each one of the 2 databases at both ASEs use imaldb go sp_reptostandby imaldb, "ALL" go i) Create two database devices called RSSD_data and RSSD_log at imalrssd with respective sizes 8GB data and 2GB log disk init name = RSSD_data, physname = "c:\Sybase\data\RSSD_data.dat", size = '8192M' go OP17F08 – Setup Details Issue Date: 09-04- Version 1.0 Last Revision Date: 09-04-

ICT Professional Services

4.1.6 Add Active and Standby Databases

Using rs_init add the connections to the databases a) First add active database imaldb to the replication system b) Then add standby database imaldb to the replication system

4.1.7 Materialize the data

a) Dump the active databases b) Load the standby databases & bring them online ON DR use master go use imaldb go sp_dropuser imaldb_maint, imaldb go sp_addalias imaldb_maint, dbo go sp_role 'grant', 'replication_role', 'imaldb_maint' go c) Connect to REP and issue resume connection to imaldr.imaldb go d) Check the connection status using admin who_is_down go admin logical_status go e) Run the below on REP to exclude the listed tables from replication to DR: suspend connection to imaldr.imaldb go alter connection to imaldr.imaldb for replicate table named SYNC_LASTPINGDATE set dsi_command_convert to 'u2none,d2none,i2none' go alter connection to imaldr.imaldb for replicate table named TMP_CASH_FLOW_ACC_BAL set dsi_command_convert to 'u2none,d2none,i2none' go alter connection to imaldr.imaldb for replicate table named TMP_REPORT_DEALS_SETL_DET set dsi_command_convert to 'u2none,d2none,i2none' go alter connection to imaldr.imaldb OP17F08 – Setup Details Issue Date: 09-04- Version 1.0 Last Revision Date: 09-04-

ICT Professional Services for replicate table named DOF_TMP set dsi_command_convert to 'u2none,d2none,i2none' go alter connection to imaldr.imaldb for replicate table named TMP_MT701_OUT set dsi_command_convert to 'u2none,d2none,i2none' go alter connection to imaldr.imaldb for replicate table named TRS_DOF_TMP set dsi_command_convert to 'u2none,d2none,i2none' go alter connection to imaldr.imaldb for replicate table named TMP_PASTDUE set dsi_command_convert to 'u2none,d2none,i2none' go alter connection to imaldr.imaldb for replicate table named TRS_POLICY_DATA_ALL set dsi_command_convert to 'u2none,d2none,i2none' go alter connection to imaldr.imaldb for replicate table named TRSSETLPOLICYREPORT set dsi_command_convert to 'u2none,d2none,i2none' go alter connection to imaldr.imaldb for replicate table named CARDLIST set dsi_command_convert to 'u2none,d2none,i2none' go resume connection to imaldr.imaldb go f) Create all replication definitions(refer to appendix 6.4)

4.1.8 Post-installation Task

a) Make sure that the “ trunc log on checkpoint ” is enabled for all the databases at imaldr & imalrssd On imaldr: use master go exec sp_dboption imaldb,"trunc. log on chkpt",true go exec sp_dboption imaldb_con,"trunc. log on chkpt",true go On imalrssd: use master go exec sp_dboption REP_RSSD,"trunc. log on chkpt",true go OP17F08 – Setup Details Issue Date: 09-04- Version 1.0 Last Revision Date: 09-04-

ICT Professional Services

4.3.2 Configure RepAgent for the Active Database from imalp

(tuning)

set replication off go use imaldb go exec sp_stop_rep_agent imaldb go exec sp_config_rep_agent imaldb, "connect dataserver","imalp1" exec sp_config_rep_agent imaldb, "scan batch size", "20000" exec sp_config_rep_agent imaldb, "fade timeout", "60" exec sp_config_rep_agent imaldb, "retry timeout", "30" exec sp_config_rep_agent imaldb, "scan timeout", "15" exec sp_config_rep_agent imaldb, "skip ltl errors",true exec sp_config_rep_agent imaldb, "skip unsupported features",true exec sp_config_rep_agent imaldb, "send buffer size", "16K" exec sp_config_rep_agent imaldb, "priority", "4" exec sp_config_rep_agent imaldb, "short ltl keywords" ,"true" exec sp_config_rep_agent imaldb, "send structured oqids","true" go exec sp_start_rep_agent imaldb go set replication on go

4.3.3 Configure RepAgent for the Active Database from imaldr

(tuning)

use imaldb go exec sp_stop_rep_agent imaldb go exec sp_config_rep_agent imaldb, "connect dataserver","imaldr" exec sp_config_rep_agent imaldb, "scan batch size", "20000" exec sp_config_rep_agent imaldb, "fade timeout", "60" exec sp_config_rep_agent imaldb, "retry timeout", "30" exec sp_config_rep_agent imaldb, "scan timeout", "15" exec sp_config_rep_agent imaldb, "skip ltl errors",true exec sp_config_rep_agent imaldb, "skip unsupported features",true exec sp_config_rep_agent imaldb, "send buffer size", "16K" exec sp_config_rep_agent imaldb, "priority", "4" exec sp_config_rep_agent imaldb, "short ltl keywords" ,"true" exec sp_config_rep_agent imaldb, "send structured oqids","true" exec sp_start_rep_agent imaldb go

4.3.4 Configure Replication Server RS from REP (tuning)

configure replication server set "cm_max_connections" to "128" configure replication server set "exec_cmds_per_timeslice" to "10000" --For Windows 2003, the maximum value for memory_limit is 2047 --For Other platform, set the memory_limit to 70% of the server memory configure replication server set "memory_limit" to "8192" configure replication server set smp_enable to 'on' configure replication server set "num_client_connections" to "100" configure replication server set "num_concurrent_subs" to "100" configure replication server set "num_msgqueues" to "800" configure replication server set dsi_row_count_validation to 'off' configure replication server set "num_msgs" to "204800" configure replication server set "num_mutexes" to "6000" configure replication server set "num_threads" to "5000" configure replication server set "dsi_bulk_copy" to "off" configure replication server set "sqt_max_cache_size" to "402653184" configure replication server set "stats_sampling" to "on" configure replication server set "sts_cachesize" to "10000" configure replication server set "sub_daemon_sleep_time" to "10000" configure replication server set "sub_sqm_write_request_limit" to "2097152" configure replication server set "exec_sqm_write_request_limit" to "2097152" configure replication server set "sts_full_cache_rs_classes" to "on" OP17F08 – Setup Details Issue Date: 09-04- Version 1.0 Last Revision Date: 09-04-

ICT Professional Services configure replication server set "sts_full_cache_rs_columns" to "on" configure replication server set "sts_full_cache_rs_config" to "on" configure replication server set "sts_full_cache_rs_databases" to "on" configure replication server set "sts_full_cache_rs_datatype" to "on" configure replication server set "sts_full_cache_rs_diskaffinity" to "on" configure replication server set "sts_full_cache_rs_columns" to "on" configure replication server set "sts_full_cache_rs_objects" to "on" configure replication server set "sts_full_cache_rs_functions" to "on" configure replication server set "sts_full_cache_rs_queues" to "on" configure replication server set "sts_full_cache_rs_repdbs" to "on" configure replication server set "sts_full_cache_rs_repobjs" to "on" configure replication server set "sts_full_cache_rs_dbreps" to "on" --Reboot REP

4.3.5 Tune DSI to Active Database (imalp1.imal) from REP

suspend connection to "imalp1"."imaldb" go configure connection to "imalp1"."imaldb" set dsi_cmd_batch_size to '32768' go configure connection to "imalp1"."imaldb" set dsi_keep_triggers to 'off' go configure connection to "imalp1"."imaldb" set dsi_sqt_max_cache_size to '104857600' go configure connection to "imalp1"."imaldb" set dsi_xact_group_size to '261424' go configure connection to "imalp1"."imaldb" set dsi_max_xacts_in_group to '60' go configure connection to "imalp1"."imaldb" set db_packet_size to '4096' go configure connection to "imalp1"."imaldb" set dsi_commit_control to 'on' go configure connection to "imalp1"."imaldb" set exec_cmds_per_timeslice to '10000' go configure connection to "imalp1"."imaldb" set exec_sqm_write_request_limit to "1048576" go configure connection to "imalp1"."imaldb" set md_sqm_write_request_limit to "1048576" go resume connection to "imalp1"."imaldb" go

4.3.6 Tune DSI to Standby Database (imaldr.imal) from REP

suspend connection to "imaldr"."imaldb" go configure connection to "imaldr"."imaldb" set dsi_cmd_batch_size to '32768' go configure connection to "imaldr"."imaldb" set dsi_keep_triggers to 'off' go configure connection to "imaldr"."imaldb" set dsi_sqt_max_cache_size to '1677721600' go configure connection to "imaldr"."imaldb" set dsi_xact_group_size to '261424' go OP17F08 – Setup Details Issue Date: 09-04- Version 1.0 Last Revision Date: 09-04-

ICT Professional Services Repeat the same steps with the following information: SQL Server name: imalp Sa user : sa Sa Password: ****** Database: phx_rep Will the database be replicated : now Maintenance user: phoenix_maint Maintenance password: phoenix_maint_ps 3- Follow the steps mentioned in the text files located under “d:\phoenix\Table Replication Steps” for each table to setup the replication

6 Appendices

6.1 Replication Resource File “REP_install.rs” for creating RS

Server

sybinit.release_directory: /sybrs sybinit.product: rs rs.rs_operation: rs_install rs.rs_idserver_name: REP rs.rs_idserver_pass: sybase rs.rs_id_server_is_rs_server: yes rs.rs_name: REP rs.rs_requires_ltm: no rs.rs_charset: cp rs.rs_language: USE_DEFAULT rs.rs_sortorder: binary rs.rs_rs_errorlog: C:\Sybase\REP-15_5\install\REP.log rs.rs_rs_cfg_file: C:\Sybase\REP-15_5\install\REP.cfg rs.rs_rssd_sqlsrvr: imalp rs.rs_rssd_db: REP_RSSD rs.rs_rs_sa_pass: sybase OP17F08 – Setup Details Issue Date: 09-04- Version 1.0 Last Revision Date: 09-04-

ICT Professional Services rs.rs_create_rssd: yes rs.rs_rssd_sa_login: sa rs.rs_rssd_sa_pass: sybase rs.rs_rs_pass: sybase rs.rs_ltm_rs_pass:sybase rs.rs_rssd_prim_pass: Sybase rs.rs_rssd_maint_user: imaldb_maint rs.rs_rssd_maint_pass: imaldb_maint_ps rs.rs_rsdddb_size: 8192M rs.rs_rssd_log_size: 2048M rs.rs_rssd_db_device_name: RSSD_data rs.rs_create_rssd_database_dev: no rs.rs_rssd_log_device_name: RSSD_log rs.rs_create_rssd_log_dev: no rs.rs_diskp_name: D:\sybase\data\stableq\part01.dat rs.rs_diskp_lname: part rs.rs_diskp_size: 204 rs.rs_diskp_vstart: 0 rs.ltm_name: ltmprs rs.rs_ltm_errorlog: USE_DEFAULT rs.rs_ltm_cfg_file: USE_DEFAULT rs.do_add_replication_server: no rs.do_add_ltm: no

6.2 Bcp out

The below can be used to bcp out the logins and roles: bcp master..syslogins out syslogins.out -c -Usa –P****** -Simalp bcp master..sysroles out sysroles.out -c -Usa –P****** -Simalp bcp master..sysloginroles out sysloginroles.out -c -Usa –P****** -Simalp bcp master..syssrvroles out syssrvroles.out -c -Usa –P****** -Simalp

6.3 Bcp in

The below can be used to bcp in the logins and roles: bcp master..syslogins in syslogins.out -c -Usa –P****** -Simaldr -b bcp master..sysroles in sysroles.out -c -Usa –P****** -Simaldr -b bcp master..sysloginroles in sysloginroles.out -c -Usa –P****** -Simaldr -b bcp master..syssrvroles in syssrvroles.out -c -Usa –P****** -Simaldr -b PS: allow updates to system tables must be enabled on the destination server for the bcp in to work, it can be disabled after that: To enable: use master go sp_configure "allow updates to system tables", go To disable: use master go sp_configure "allow updates to system tables", OP17F08 – Setup Details Issue Date: 09-04- Version 1.0 Last Revision Date: 09-04-

ICT Professional Services

7 Switchover Procedure

7.1 Assumptions

1. PROD is the Head Office site (main data center)
2. DR is the Disaster Recovery site (DR data center)
3. Initial Active database is in PROD
4. Initial Standby database is in DR

7.2 Switchover from Active to Standby

7.2.1 Pre-switch tasks

5. Make sure all i MAL users can connect to DR site
6. Test connection from PROD to REP, from REP to DR and vice versa
7. Make sure imaldb_maint & imaldb_con_maint are aliased as dbo users to imaldb & imaldb_con databases respectively
8. Disconnect all the clients from the Active Database Server (imalp1) 8.1. You can use SADS to log off all i MAL users 8.2. imal_sms should be stopped (if running as service it should be stopped to prevent it from trying to reconnect) 8.3. You might need to kill all sessions in the Active Database Server except for the following  your session  imaldb_maint  imaldb_con_maint  jstask  Rep Agents  Any other logins used by the cluster and/or replication and/or monitoring tools, etc. (you can use sp__killall login procedure if there a lot of sessions)
9. Make sure no i MAL users can connect to PROD site (imalp1)

7.2.2 Switch tasks

1. Stop all the RepAgents in the Active Database Server (imalp1) use master go exec sp_stop_rep_agent imaldb go
2. Make sure all the replication is quiesced; In the Replication Server (REP) execute the following: OP17F08 – Setup Details Issue Date: 09-04- Version 1.0 Last Revision Date: 09-04-

ICT Professional Services admin quiesce_check The output should be: Server Message: Number 14043, Severity 0 Server 'REP', Line 0: Replication Server REP is Quiesced

3. Switch the replication by using the switch active command  At the Replication Server (REP), enter this command: switch active for LDS.imaldb to imaldr.imaldb with suspension go wait for switch for LDS.imaldb go  During switchover, you can monitor switching status with this command at the Replication Server (REP): admin logical_status The output will show “suspended” instead of “active”
4. Start the RepAgent for the NEW Active (imaldr) use imaldb go sp_config_rep_agent imaldb,"connect dataserver","imaldr" go exec sp_start_rep_agent imaldb go
5. Make the necessary changes for the users to point to the new active (DR)
6. If iMAL*SAFE is implemented, before connecting the applications to the DR site, apply the below script on imal database @ DR: BEGIN DECLARE @LL_MAX_ID NUMERIC(16) DECLARE @LL_MAX_ERR NUMERIC(16) SELECT @LL_MAX_ID = ISNULL(MAX(ID),0) FROM SYNC_STORE_FORWARD SELECT @LL_MAX_ERR = ISNULL(MAX(ID),0) FROM SYNC_ERROR IF @LL_MAX_ERR > @LL_MAX_ID SELECT @LL_MAX_ID = @LL_MAX_ERR set identity_insert SYNC_IDENTITY ON INSERT INTO SYNC_IDENTITY(CODE)VALUES(@LL_MAX_ID) DELETE FROM SYNC_IDENTITY OP17F08 – Setup Details Issue Date: 09-04- Version 1.0 Last Revision Date: 09-04-