DM223 Coordinating Database Backups Using Sybase Replication

1
DM223Coordinating Database Backups Using Sybase
Replication
Curt Nothwehr Database Administrator U S WEST
Dex cnothwe_at_uswest.com
2
AGENDA

• Why coordinate database backups?
• Review some different approaches.
• Our preferred approach.
• Code samples.
• Side benefits.
• Conclusions.

3
Whats the problem?

• Components in a typical replication system

Primary ASE Server logscan thread
Primary Rep. Server
Rep. Agent (LTM)
Replicate Rep. Server
Replicate ASE Server
4
Whats the problem?

• Inherent in any replication system is latency -
  time from primary to replicate
• Affected by numerous factors, including
• Primary dataserver workload
• Transaction volume
• Network latency
• Rep. Server configuration
• Replicate dataserver workload
• Etc...

5
Whats the problem?

• Simultaneous backups of primary and replicate,
  not accounting for latency, may not be logically
  synchronized.
• Upon restoration, data will need to be resyncd
• rs_subcmp (Sybase-provided resynchronization
  utility)
• Site-specific subcmp-like process
• Rematerialize replicate
• These can be very time-consuming.

6
Coordinated backup approaches

• Several approaches are available for backing up
  replicated databases
• Some options well explore
• Dont coordinate backups
• Loosely coordinate backups
• Coordinate backups using Sybase-supplied
  rs_dumpdb/tran Rep. Server system functions
• Create a simple application to replicate
  coordinated backup requests

7
Coordinated backup approaches

• Dont coordinate -
• Why use this approach?
• Simple to implement
• Works if coordinated recovery is not a
  requirement
• Why not?
• Coordinated recovery to syncd backup is
  improbable
• Re-synchronization for large databases can be
  time-consuming

8
Coordinated backup approaches

• Loosely coordinate -
• Simultaneous backups on primary and replicate
• Why use this approach?
• Simple to implement
• Works if coordinated recovery is not a
  requirement
• Why not?
• Coordinated recovery to syncd backup not
  guaranteed, even if primary is quiesced
• Re-synchronization for large databases can be
  time-consuming

9
Coordinated backup approaches

• Coordinate with rs_dumpdb and rs_dumptran -
• Sybase provides these Rep. Server system
  functions which when enabled
• Cause dumps executed on the primary to replicate
• Coordinate replicate backups with primary,
  accounting for latency
• Suspend connection to replicate if dump fails on
  replicate

10
Coordinated backup approaches

• rs_dumpdb/rs_dumptran (contd) -
• To activate
• Configure replicate dsi connection
• alter connection RDS.dbname set dump_load to on
• Create a rs_dumpdb/rs_dumptran function string
• (see section 4 of Rep. Server reference for
  examples)
• Customized function string/stored procs can be
  employed

11
Coordinated backup approaches

• rs_dumpdb/rs_dumptran (contd) -
• Advantages
• Easy to implement
• Guarantees backup coordination
• Replicate backup logically matches primary
• Connection to replicate shuts down if replicate
  backup fails, providing opportunity to rerun
• Allows custom function strings for specialized
  replicate backups

12
Coordinated backup approaches

• rs_dumpdb/rs_dumptran (contd) -
• Disadvantages
• Limited flexibility
• Difficult to interface to 3rd party BR products
• Replicate backups must be coordinated with
  completion of primary (may or may not be a
  problem)
• All primary dumps will trigger a replicate dump
• Under certain conditions, it can be difficult to
  correlate primary and replicate backups

13
Coordinated backup approaches

• Customized backup application -
• A very simple backup application will
• Cause dumps executed on the primary to replicate
• Coordinate replicate backups with primary,
  accounting for latency
• Suspend connection to replicate if dump fails on
  replicate
• Interface with 3rd party BR apps
• Provide additional flexibility

14
Coordinated backup approaches

• Customized backup application (contd) -
• Components
• Primary and replicate stored procedures
• May or may not be the same
• Function replication definition
• Subscription for each replicate
• Openserver application (optional)

15
Coordinated backup approaches

• Customized backup application (contd) -
• How it works
• When the primary stored procedure is executed...
• The function repdef causes the backup request to
  be sent to all replicates, with parameters
• Subscribing replicates will then execute the
  replicate stored proc
• The stored proc optionally calls (via rpc) an
  Openserver to interface with 3rd party products
  or UNIX shell

16
Coordinated backup approaches

• Customized backup application (contd) -
• How it works (contd)
• If the replicate backup fails
• The replicate stored proc returns a non-zero
  return code
• The replication connection is suspended
  (optional)
• The replicate backup can be restarted by simply
  resuming the connection

17
Our application

• U S WESTs backup application -
• Similar to just described, except
• Our primary database is quiesced when backups run
• Primary and replicate stored procs are different
• The primary sps function is only to sent request
  to replicate
• Primary backup is performed using SQL-Backtrack
• Each replicate subscribes to unique instance of
  request

18
Our application

• U S WESTs backup application (contd) -
• Replicate backups are shadowed by Control-M job
• Control-M shadow jobs monitor backup progress
• We then trigger replicate events following
  backups
• We pass request date/time as an argument
• Allows us to correlate primary and replicate
  backups
• A customized openserver application was created
• Backup profiles are managed in a .cfg file

19
Our application

• U S WESTs backup application (contd) -

Backup OpenServer
Primary ASE Server
Rep. Server
Replicate ASE Server
SQL-Backtrack
Backup request
Return code
Backup Medium
Dump data
20
Our application

• U S WESTs backup application (contd) -
• Code sample - primary stored proc
• create procedure coord_backup
• _at_srvr_nm varchar(30), _at_bkup_profile varchar(20),
  _at_rqst_datetime datetime
• as
• declare _at_msg char(255)
• select _at_msgchar(10)convert(varchar(30),(_at_rqst_da
  tetime))" INFO Database backup or tran dump
  request sent to " _at_srvr_nm ". Backup alias
  specified " _at_bkup_profile ". Date/time
  requested " convert(varchar(30),(_at_rqst_datetime
  ))char(10)char(10)
• print _at_msg
• go
• sp_setrepproc coord_backup, function

21
Our application

• U S WESTs backup application (contd) -
• Code sample - function repdef
• create function replication definition
  coord_backup
• with primary at ltPDS1.dbnamegt
• deliver as 'coord_backup'
• (_at_srvr_nm varchar(30), _at_bkup_profile varchar(20),
  _at_rqst_datetime datetime)
• searchable parameters(_at_srvr_nm)

22
Our application

• U S WESTs backup application (contd) -
• Code sample - replicate stored proc
• create procedure coord_backup
• _at_srvr_nm varchar(30), _at_bkup_profile varchar(20),
  _at_rqst_datetime datetime
• as
• declare
• _at_msg char(255),_at_retvalue int,_at_dateval varchar(30)
• select _at_dateval convert(varchar(30),_at_rqst_dateti
  me)
• exec _at_retvalue BKUP_OPENSERVERbackup _at_srvr_nm,
  _at_bkup_profile, _at_dateval

23
Our application

• U S WESTs backup application (contd) -
• Code sample - replicate stored proc (contd)
• if (_at_retvalue !0)
• begin
• select _at_msg"FATAL Database Backup
  Failed for "_at_srvr_nm". Backup alias
  specified "_at_bkup_profile". Date/time
  requested "_at_dateval". See OpenServer
  Log."char(10)char(10)
• raiserror 99999 _at_msg
• return -6
• end

24
Our application

• U S WESTs backup application (contd) -
• Code sample - replicate stored proc (contd)
• else
• begin
• select _at_msg"INFO Database Backup
  Succeeded for "_at_srvr_nm". Backup alias
  specified "_at_bkup_profile". Date/time
  requested "_at_dateval"."char(10)char(10)
• end
• begin
• print _at_msg
• end

25
Our application

• U S WESTs backup application (contd) -
• Code sample - subscription
• define subscription sd_coord_backup for
  coord_backup with replicate at ltRDS1.dbnamegt
  where _at_srvr_nm ltRDS1gt
• activate subscription sd_coord_backup for
  coord_backup with replicate at ltRDS1.dbnamegt
  where _at_srvr_nm ltRDS1gt
• validate subscription sd_coord_backup for
  coord_backup with replicate at ltRDS1.dbnamegt
  where _at_srvr_nm ltRDS1gt

26
Our application
U S WESTs backup application (contd)
- Openserver application (code proprietary) Call
prototype ltOpenservernamegtbackup ltdestination
server namegt, ltbackup profilegt,
ltdatetimegt Destination server name is used in
where clause of subscription Backup profile
relates to an entry in the openserver .cfg file
27
Our application
U S WESTs backup application (contd)
- Openserver application (contd) .cfg file
examples customer /opt/datatools/backups/RDS1/c
ustomer -to ret_90 sunday /opt/datatools/backups
/RDS1/customer /opt/datatools/backups/RDS1/ord
ers -ret_365 -dbv_verify_and_dump all_dbs /o
pt/datatools/backups/RDS1
28
Our application

• U S WESTs backup application (contd) -
• Openserver application
• Executes a SQL-BT dtsbackup command
• Supplies dtsbackup arguments from .cfg profile
• Return code is passed back to stored proc
• Executes a shell script to create Control-M
  shadow job

29
Our application

• U S WESTs backup application (contd) -
• Issues
• Trasaction grouping must be turned off
• Rep server attempts to group transactions at
  replicate
• dsi_xact_group_size -1
• Possible performance issues

30
Side benefits

• Replicated backups using stored procs -
• Can be used to trigger events at replicate
• Can easily be configured to provide replication
  heartbeat
• No heartbeat table needed!
• Can be used to backup more than just the
  replicate database

31
Conclusions

• Coordinated backups -
• Are usually a good idea
• Saves time on restoration
• Primary doesnt need to be quiesced for
  rematerialization
• Easy to implement using either method
• Simpler is better
• Provides additional benefits