DM223 Coordinating Database Backups Using Sybase Replication - PowerPoint PPT Presentation

1 / 31
About This Presentation
Title:

DM223 Coordinating Database Backups Using Sybase Replication

Description:

Inherent in any replication system is latency - time from primary ... The replicate backup can be restarted by simply resuming the connection. Our application ... – PowerPoint PPT presentation

Number of Views:103
Avg rating:3.0/5.0
Slides: 32
Provided by: cnot2
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com