HA/Disaster and Recovery Solution Mirroring Review & Log Shipping

(1)

HA/Disaster and Recovery Solution Mirroring Review & Log Shipping

1/16 Written by

Zakir Hossain, Chief, Data/Report/Apps OPS - Worldwide (Oracle/SQL Server/MySQL)

USAID, US Dept. of State

CEO, Data Group

CS Graduate (OSU), OCP, OCA, MCDBA, MCITP-DBA, Security+, Oracle RAC-Admin, Oracle Backup/Recovery-Admin, Oracle Performance/Monitoring-Admin, Oracle App Server-Admin, System Admin (Windows/RedHat), ITIL V3, SCJP (Sun Certified Java Programmer)

Special Note

You are not allowed sharing notes, questions/answers with discontinued students and outsiders. If anybody needs to get notes, questions & answers, they need to contact directly with

Zakir Hossain

..::: NOTE :::..

Altering, printing, and sharing with any training institute/individual or commercial/business use without written permission is 100% prohibited.

It is a Federal Copy Right Violation of Intelligence Product. Violators will be prosecuted with the fullest Extent of Federal Law.

(2)

2/16 Review: Mirroring

Log Shipping

Compare Mirroring and Log Shipping

Decision Making: Mirroring VS Log Shipping Review: Mirroring:

1. Requirements:

a. Servers: Min 2 and for synchronized – 3 b. Same version: Except Express Edition

c. 32 bit vs 64 bit: Issue?? No issue since storage format is same (Ending is same)

d. Service Account: Same service account e. DB Name: Must be same

f. Port numbers must be allowed from firewall g. Full Recovery Model for Principal DB 2. Numbers of Servers and Role:

a. Roles

i. Principal Server – Contains source database ii. Mirror Server – Contains target database

iii. Witness Server – forms quorum, monitor the servers 3. Advantages/Disadvantages

4. Operational Mode a. Synchronized:

i. High Safety with Automatic Failover ii. High Safety with Manual Failover b. Asynchronized:

5. Failover Process:

a. Maintenance: Windows upgrade/SP, HD i.

(3)

3/16 6. Steps to configure:

a. Check and ensure Full Recovery Model for Principal DB b. Backup – Full and Tran

c. Copy backup to Mirror Server

d. Restore on Server with same name using NORECOVER Mode e. Configure Mirroring

7. Software Update (From 2005 to 2008) 8. Apply SP 9. Endpoint: 10. Port numbers: 5021/22/23 11. Security Log Shipping: - Available since 2000

Log Shipping: Automated process to move Log records from one server to another sever and restore them automatically

- DB name secondary could be same or could have any name - Could have multiple copies of principal db on multiple servers - 32 bit and 64 does not matter

- 3 Jobs are required besides Alert job:

o Backup job: LSBackup_DB_Name: Primary Server Example: LSBackup_CTS

o Copy job: LSCopy_Primary_Instance\Secondary_Instance_DB_Name: Secondary Server

Example: LSCopy_DGCLUSTERLAB01\DG_INST212_CTS o Restore Job: LSRestore_

(4)

4/16

Example: LSRestore_DGCLUSTERLAB01\DG_INST212_CTS o Alert Job: LSAlert_Instance_Name: Primary Server

Example: LSAlert_DG_INST210

o Alert Job: LSAlert_Instance_Name: Secondary Server Example: LSAlert_DG_INST212

- Can combine 2005 and 2008 or above. However, older version must be principal - Monitoring Jobs (Alert):

o Consider No monitoring Server:

 2 Alerts jobs – 1 on Principal and 1 Secondary as mentioned above o Consider monitoring Server exist: 1 alert job will be created on Monitor

server

o Can alert based on configuration – Email, text, paging, net send Requirements

- Servers: 2 Required, 3 (Monitoring Server) Optional - Version: all except Express

- DB Recovery - Full

- DB Name does not have to be same - Agent must be running

- Accounts:

o Must need to have write on backup directory

o Copy job account – Read permission on backup directory and write permission on Copy Directory

o Restore job account – Need Read permission on Copy Directory Lab/Project: Log Shipping Doc and Configuration

(5)

5/16

2. Primary DB Instance Name: DGCLUSTERLAB01\DG_INST210 3. Primary DB Name: CTS

4. Secondary Server Name: DGCLUSTERLAB01

5. Secondary Instance Name: DGCLUSTERLAB01\DG_INST212 6. Secondary DB Name: CTS

7. Monitoring Server: No Monitoring Server (Best Practice) 8. Account Name: dba

9. Directories:

a. Primary Server: LogShipping\Backup\CTS SeverA

G:\LogShipping\Backup\CTS Network Path: \\SeverA \ CTS b. Secondary Server:

H:\LogShipping\Copy\DB_Name c.

10. Configure Log Shipping 11. Step

(6)

6/16 a. Verify Recovery Model set to full

b. Tack backup – Full and TL

c. Copy back file to Secondary Server (Copy) d. Restore the DB using Backup – using mode:

NORECOVERY (LIKE MIRRORING) OR- Cannot access to db STANDBY – Can access to ready only (Cannot modify or insert new

(7)

7/16 records in the DB)

(8)

8/16

(9)

9/16 g. Configure Primary Server:

(10)

10/16 h. Secondary Server Configuration

Tab1:

(11)

(12)

12/16 Tab 3:

(13)

13/16 i. Click OK

(14)

14/16 12. Validation/Verification of LS:

a. Verify Jobs created or not

b. Verify directories for backup and copy job c. Verify transactions

d. Examples of transactions: i. Create table

ii. Insert records iii. Update

iv. Delete 13. Write some programs Job Name:

LSBackup_CTS – Primary

LSAlert_DGCLUSTERLAB01\DG_INST210 – Primary

LSCopy_DGCLUSTERLAB01\DG_INST212_CTS – Secondary LSRestore_DGCLUSTERLAB01\DG_INST212_CTS – Secondary

(15)

15/16

LSAlert_DGCLUSTERLAB01\DG_INST212 - Secondary Failover Process: Disaster

1. Copy backup files not copied to secondary 2. Find last file restored

3. Restore file not restored in sequence

4. Recover the DB using following command: Restore database CTS

WITH RECOVERY 5. Disable jobs

Failover Process: Maintenance

1. Copy backup files not copied to secondary 2. Find last file restored

3. Restore file not restored in sequence 4. If server and DB available

a. Take another TL using Job & Copy/Restore b. Tale Log backup & Copy/Restore

c. Recovery the DB using following command: Restore database CTS

WITH RECOVERY 5. Disable jobs

(16)

16/16 How to Remove Log Shipping: