Database Mirroring & Snapshots SQL Server 2008

(1)

1

DB Architechs, USA

DB Architechs, France

Database Mirroring & Snapshots

SQL Server 2008

(2)

Agenda

–

SQL Server User Group

Database Mirroring

–

SQL Server 2008

Database Mirroring 2005 vs 2008

benchmark

Database Snapshots

–

SQL Server 2008

SQL Performance &

Tuning Tool (SQL Shot !)

(3)

3

Paul Bertucci

Founder Database ArchitechsFounder Database Architechs

Former Chief Data Architect Former Chief Data Architect –– Veritas & SymantecVeritas & Symantec

Currently Chief Architect Currently Chief Architect -- AutodeskAutodesk

Author of SQL Server 2000, 2005 & 2008 Unleashed! Author of SQL Server 2000, 2005 & 2008 Unleashed!

CoCo--Author of ADO.NET in 24 hours Author of ADO.NET in 24 hours

Author MS SQL Server High AvailabilityAuthor MS SQL Server High Availability

Author Sybase Performance & TuningAuthor Sybase Performance & Tuning

Author Sybase Physical DB DesignAuthor Sybase Physical DB Design

Veritas SQL Server Performance Series Veritas SQL Server Performance Series [email protected]

[email protected]

Thierry Gerardin

•

• Managing Principal Managing Principal -- Database ArchitechsDatabase Architechs

-- Management, Research and Development, DB Design and P&T for Management, Research and Development, DB Design and P&T for MS SQL Server, Sybase and Oracle

MS SQL Server, Sybase and Oracle

•

• European focus until recently/company growth leader European focus until recently/company growth leader –– multiple yearsmultiple years •

• Expanding DB ARCH consulting practices to USA and AsiaExpanding DB ARCH consulting practices to USA and Asia •

• Sybase P&T and DBA Certified ProfessionalSybase P&T and DBA Certified Professional •

• Contributor to SQL Server 2005 and 2008 Unleashed!Contributor to SQL Server 2005 and 2008 Unleashed!

[email protected]

Database Architechs

(4)

4 4

Database Architechs

Primary Services

Database Design Data Analysis & Modeling Data Architecture Performance & Tuning High Availability Master Data Management Distributed Data &

Data Replication

Business Intelligence & Data Delivery

Platforms

Database Education & Training

(5)

Past and Current

Intel

Applied Materials

Cisco

Apple Computers

Charles Schwab

PG&E

Aplia

Thomson Learning

AGIS

Sybase

Veritas

Symantec Corporation

Honda Motors

Nissan Motors

Toshiba Computers

Breg International

Juniper Networks

CSAA

Safeway Stores

Federal Express

Wells Fargo

Bank of America

Robert Half

Merrill Lynch

Metalinc

many others….

(6)

Database Mirroring?

A “complete” copy of a database that is created and maintained with as up to the second completeness that is possible a mirror image.

Adventure Works DB

=

Adventure Works DB 6 6

What’s new or improved?

Compression in log transactions across topology

Various other performance enhancements

Page fixing on the mirror side (AUTO)

(7)

7 7 SQL Server 2008 “Source” CallOne DB CallOne DB SQL Server 2008 Secondary Server “Destination” translog \Backup\CallOne_tlog_200405141120.TRN TxnLog backups Primary Server TxnLog Copies \LogShare\CallOne_tlog_200405141120.TRN _TxnLog Restores MSDB DB SQL Server 2008 Monitor Server “Monitor” L a s t lo g s h ip p e d D e la y A n s w e r Delay between logs loaded Delay Answer

Log Shipping

(8)

DB

Mirroring

MSDB DB SQL Server 2008 SQL Server xyz Witness Server Adventure Works DB SQL Server 2008 Mirror Server translog Principal Server translog Adventure Works DB

Network

Client Client Client Client

B C A

D D

(9)

Copy

-

on

-

write Technology

The new “copy-on-write” technology that Microsoft has created, is at the core of the database mirroring capability. A transaction from a client connection to the principal server (arrow label A) is written to the adventure works database

(label D). Once the transaction is written to the principal servers transaction log, it is immediately copied (arrow label B) and written to the Mirror Server (also labeled D). When this physical log record is written to the mirror server, it sends back an acknowledgement (arrow label C) to the principal of its write success. This is the “copy-on-write” technology. The end result is that the mirror server is in the exact same state as the principal server (when the physical log record has been successfully written on the mirror side).

9

(10)

DB Mirroring Terms

The Principal database server - is the source of the mirroring. You can mirror one or more databases on a single SQL Server instance to another SQL Server instance. You cannot mirror a database on one SQL Server instance to itself (the same SQL Server instance).

The Mirror database server - will be the recipient of the mirroring from the principal database server. This mirrored database will be kept in a hot standby mode and

cannot be used directly in any way. In fact, once you have configured database mirroring, this database will show its status as in continuous “restore mode”.

The Witness database server - is used when you want to be continuously checking to see if any failures have occurred to the primary database server and to help make the decision to failover to the mirror database server. It is optional, but a sound way to configure database mirroring. If you do not identify a witness server, the principal and mirror are left on their own to decide to fail-over or not.

10

(11)

DB Mirroring Modes

High-availability mode, High-protection mode, and High-performance mode.

Synchronous operation - a committed transaction will be committed (written) on both partners of the database mirroring pair. This obviously adds some latency cost to a complete transaction (it is across two servers). High-availability mode and High-protection mode use synchronous operations (termed High safety)

Asynchronous operation - transactions commit without waiting for the mirror server to write the log to disk. This can speed up performance significantly.

High-performance mode uses asynchronous operations.

Whether the operations are asynchronous or synchronous depends on the “transaction safety” setting. This is controlled by the SAFETY option when configuring with Transact-SQL commands (Set to FULL for synchronous operations, set to OFF for asynchronous operations).

[you must sacrifice levels of protection for performance]

11

(12)

Fail

-

over methods

Automatic failover - is enabled with a three-server configuration; a principal, a mirror, and a witness server. Synchronous operations are required and the mirror database must already be synchronized (in sync with the transactions as they are being written to the principal). Role switching is done automatically. This is for high-availability mode.

Manual failover - is needed when there is no witness server and is also doing synchronous operations. The principal and the mirror are connected to each other and the mirror database must already be synchronized. Role switching is done manually. This is for high-protection mode.

Forced Service - in the case where there is a mirror server available, but it might not be synchronized. It can be forced to take over when the principal server has failed. This will possibly have data loss, since the transactions were not

synchronized. This is for either high-protection or high-performance mode.

12

(13)

Rule

-

of

-

Thumb

• If you need to increase the availability of the database layer

• If you need to have automatic data protection (redundant storage of data) • If you need to decrease the downtime that would normally be required to

do upgrades

You should use database mirroring:

And very often:

• If you need to off load reporting (periodic data snapshots) without impacting the transactional system use database mirroring with database snapshots. • Combo Needs: If you need data distribution, high availability and high data resiliency - use data replication with database mirroring.

13

(14)

Cannot be used with:

14

Database Mirroring cannot be used for any of SQL Server’s internal

databases

TempDB, Master DB, MSDB, or Model DB.

Database Mirroring is NOT supported in SQL Server Workgroup

Edition or Express Edition. However, these server editions could be

used as Witness servers.

You cannot have database mirroring for databases enabled with

FILESTREAM storage.

(15)

15 15

HA

Extreme Availability High Availability Standard Availability Acceptable Availability Marginal Availability

Near zero downtime!

Minimal downtime

With some downtime tolerance

Non-critical Applications

Non-production Applications

8,760 hours/year | 168 hours/week | 24 hours/day

525,600 minutes/year | 7,200 minutes/week | 1,440 minutes/day

(99.5% - 100%)

(95% - 99.4%)

(83% - 94%)

(70%-82%)

(up to 69%)

Characteristic Availability Range

Availability Range describes the percentage of time relative to the “planned” hours of operations

(16)

Windows 2003 EE Windows 2003 EE

SQL Server 2008

(Virtual SQL Server) SCSI

Local Binaries

CLUSTER1

SQL Clustering basic configuration

S: C: C:

Master DB

TempDB

Appl 1 DB

CLUSTER2

SQL Server 2008 (physical)

SQL

Connections

Quorum Disk Q: VSQLSERVER2008 Cluster Group Resources MS DTC SQL Agent MSCS MSCS SQL Full Text Search 16 16

(17)

17 17

Example

Configuratio

n

MSDB DB SQL Server 2008 SQL Server 2008 Witness Server Adventure Works DB SQL Server 2008 Mirror Server translog Principal Server translog Adventure Works DB

Instance: SQL08DE01 _Instance:_SQL08DE02

Instance: SQL08DE03

Endpoint name: “EndPoint4DBMirroring1430” _{Endpoint name: “EndPoint4DBMirroring1440”}

Endpoint name: “EndPoint4DBMirroring1450”

TCP: Listener_Port: 1430 TCP: Listener_Port: 1440

TCP: Listener_Port: 1450

Role: PARTNER _{Role: PARTNER}

(18)

1

18

(19)

2

19

(20)

3

20

(21)

Creating Endpoints

create endpoint for PRINCIPAL server

--CREATE ENDPOINT [EndPoint4DBMirroring1430]

STATE=STARTED

AS TCP (LISTENER_PORT = 1430, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER,

AUTHENTICATION = WINDOWS NEGOTIATE , ENCRYPTION = REQUIRED ALGORITHM RC4)

01/05/2009 13:17:23,spid55,Unknown,The Database Mirroring protocol transport is now listening for connections.

01/05/2009 13:17:23,spid55,Unknown,Server is listening on [ 'any' <ipv4> 1430].

SQL Server Log entry

21

(22)

Creating Endpoints

create endpoint for MIRROR server

--CREATE ENDPOINT [EndPoint4DBMirroring1440]

STATE=STARTED

AS TCP (LISTENER_PORT = 1440, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER,

create endpoint for WITNESS server

--CREATE ENDPOINT [EndPoint4DBMirroring1450] STATE=STARTED

AS TCP (LISTENER_PORT = 1450, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = WITNESS,

22

(23)

Backup Principal

BACKUP DATABASE [AdventureWorks] BACKUP DATABASE [AdventureWorks]

TO DISK = N'C:TO DISK = N'C:\\Program FilesProgram Files\\Microsoft SQL Microsoft SQL Server

Server\\MSSQL10.SQL08DE01MSSQL10.SQL08DE01\\MSSQLMSSQL\\BackupBackup\\AdventureWorks4Mirror.bak'AdventureWorks4Mirror.bak'

WITH FORMATWITH FORMAT

GOGO

----Processed 17672 pages for database 'AdventureWorks', file 'AdveProcessed 17672 pages for database 'AdventureWorks', file 'AdventureWorks_Data' on file 1.ntureWorks_Data' on file 1.

----Processed 3 pages for database 'AdventureWorks', file 'AdventurProcessed 3 pages for database 'AdventureWorks', file 'AdventureWorks_Log' on file 1.eWorks_Log' on file 1.

----BACKUP DATABASE successfully processed 17675 pages in 7.718 secBACKUP DATABASE successfully processed 17675 pages in 7.718 seconds (17.890 MB/sec).onds (17.890 MB/sec).

BACKUP LOG [AdventureWorks] TO BACKUP LOG [AdventureWorks] TO

DISK = N'C:DISK = N'C:\\Program FilesProgram Files\\Microsoft SQL Microsoft SQL Server

Server\\MSSQL10.SQL08DE01MSSQL10.SQL08DE01\\MSSQLMSSQL\\BackupBackup\\AdventureWorks4MirrorLog.bak' AdventureWorks4MirrorLog.bak'

GOGO

----Processed 1849 pages for database 'AdventureWorks', file 'AdvenProcessed 1849 pages for database 'AdventureWorks', file 'AdventureWorks_Log' on file 1.tureWorks_Log' on file 1.

(24)

Restore to Mirror

RESTORE FILELISTONLY RESTORE FILELISTONLY

FROM DISK = N'C:FROM DISK = N'C:\\Program FilesProgram Files\\Microsoft SQL Microsoft SQL Server

RESTORE DATABASE AdventureWorks RESTORE DATABASE AdventureWorks

WITH NORECOVERY, WITH NORECOVERY,

MOVE 'AdventureWorks_Data' TO 'C:MOVE 'AdventureWorks_Data' TO 'C:\\Program FilesProgram Files\\Microsoft SQL Microsoft SQL Server

Server\\MSSQL10.SQL08DE02MSSQL10.SQL08DE02\\MSSQLMSSQL\\DATADATA\\AdventureWorks_Data.mdf',AdventureWorks_Data.mdf',

MOVE 'AdventureWorks_Log' TO 'C:MOVE 'AdventureWorks_Log' TO 'C:\\Program FilesProgram Files\\Microsoft SQL Microsoft SQL

Server

Server\\MSSQL10.SQL08DE02MSSQL10.SQL08DE02\\MSSQLMSSQL\\DATADATA\\AdventureWorks_Log.ldf'AdventureWorks_Log.ldf'

GOGO

----Processed 17672 pages for database 'AdventureWorks', file 'AdveProcessed 17672 pages for database 'AdventureWorks', file 'AdventureWorks_Data' on file 1.ntureWorks_Data' on file 1.

----Processed 3 pages for database 'AdventureWorks', file 'AdventurProcessed 3 pages for database 'AdventureWorks', file 'AdventureWorks_Log' on file 1.eWorks_Log' on file 1.

----RESTORE DATABASE successfully processed 17675 pages in 6.716 seRESTORE DATABASE successfully processed 17675 pages in 6.716 seconds (20.560 MB/sec).conds (20.560 MB/sec).

RESTORE LOG AdventureWorksRESTORE LOG AdventureWorks

Server\\MSSQL10.SQL08DE01MSSQL10.SQL08DE01\\MSSQLMSSQL\\BackupBackup\\AdventureWorks4MirrorLog.bak'AdventureWorks4MirrorLog.bak'

WITH FILE = 1, NORECOVERY;WITH FILE = 1, NORECOVERY;

GOGO

----Processed 0 pages for database 'AdventureWorks', file 'AdventurProcessed 0 pages for database 'AdventureWorks', file 'AdventureWorks_Data' on file 1.eWorks_Data' on file 1.

----Processed 1849 pages for database 'AdventureWorks', file 'AdvenProcessed 1849 pages for database 'AdventureWorks', file 'AdventureWorks_Log' on file 1.tureWorks_Log' on file 1.

(25)

7

25

(26)

9

26

(27)

11

27

(28)

12

28

(29)

13

29

(30)

14

30

(31)

15

31

(32)

16

32

(33)

PH Topology PH Topology SQL Server 2008 Adventure Works DB SQL Server 2008 Mirror Server translog Principal Server translog Adventure Works DB Instance: SQL2008xyz Instance: SQL2008zzz

Endpoint Name: “endpoint4mirroring”

Role: PARTNER Role: PARTNER

PH

Topology

SQL Server 2008 Principal Server Active Passive C lu s te re d O L T P A p p li c a ti o n R e p li c a ti o n

(34)

DB Mirroring 2005 Benchmarks

Send Rate

(35)

DB Mirroring 2008 Benchmarks

Send Rate

(36)

DB Mirroring 2005 Benchmarks

(37)

DB Mirroring 2008 Benchmarks

Restore Rate

(38)

DB Mirroring 2005 Benchmarks

(39)

DB Mirroring 2008 Benchmarks

(40)

40

DB Mirroring Benchmark Summary

Overall

send rate

41 % faster (2005 vs 2008)

Overall

restore rate

52% faster (2005 vs 2008)

Overall availability topology inherits the restore rate ~ 50% more available Example:

743 ms Transaction (in 2005 mirroring configuration) 379 ms Transaction (in 2008 mirroring configuration)

In failover, what would have been 20 seconds before mirror becomes principal turns into roughly 8-10 seconds (or 3 seconds turns into 1.5 seconds, so on).

(41)

41

Snapshots

(42)

01

SQL Server 2008 AdventureWork s DB translog Source Server Database Snapshot Poi nt-in -tim e Rep ortin g U sers (Rea d-O nly) Tra ns ac tio na_l U se_rs Reverting if needed ---RESTORE DB AdventureWorks

FROM DATABASE_SNAPSHOT = ‘xyz’

CREATE DB ‘xyz’ AS SNAPSHOT OF

(43)

43 43 02 02 SQL Server 2008 SQL Server AdventureWork s DB Snapshot AdventureWork s DB

Source Data

Pages

Empty Sparse file

of a Snapshot just created

(no updates to original

data pages have occurred yet)

Sparse

File

Pages

(44)

Source

Data

Pages

System Catalog

of changed pages

Copy of original

pages for snapshot

only when a page is changed

(Copy-on-write)

Sparse

File

Pages

(45)

45 45 04 04 SQL Server 2008 SQL Server AdventureWork s DB Snapshot AdventureWork s DB

Source Data

Pages

System Catalog

of changed pages

Sparse

_File

Pages

SELECT …..data……. FROM AdventureWorks SNAPSHOT Snapshot Users

(46)

05 05 SQL Server 2008 SQL Server AdventureWork s DB Snapshot AdventureWork s DB

Data

Pages

System Catalog

of changed pages

Copy of original

pages for snapshot use

only when a page is changed

(Copy-on-write)

Sparse

File

Pages

Transactional Users translog U p d a te R o w

A

B

C

C o m m it te d

(47)

06

SQL Server 2008 AdventureWorks DB translog Source Server Database Snapshot All U se_rs Database Snapshot Database Snapshot 6:00AM Snapshot 12:00PM Snapshot 6:00PM Snapshot Restore from Any Point-in-time

Snapshot if needed _SnapshotDatabase 12:00AM Snapshot

UPDATE AWSource.tableX set xyz = …

(48)

07

SQL Server 2008 AdventureWorks DB Source Server Database Snapshot U se_rs SAFEGUARD Snapshot (Before the mass changes) Restore from snapshot

(if changes are not correct)

Generating Mass Changes

UPDATE AWSource.tableX set xyz = …

(49)

08

SQL Server 2008 AdventureWorks DB Source Server Database Snapshot Te stin g U se_rs

BEFORE TEST Snapshot Restore from the BEFORE snapshot

after testing cycle is complete and can continue with next test.

(50)

09

SQL Server 2008 AdventureWorks DB translog Source Server Database Snapshot Tra ns ac tio na_l U se_rs Database Snapshot Database Snapshot

6:00AM Reporting Snapshot 12:00PM Reporting Snapshot

6:00PM Reporting Snapshot

Database

Snapshot 12:00AM Reporting Snapshot

Po int- in-tim e R ep ort_ing U se rs (R ea d-O nly₎

(51)

51 51

10

MSDB DB SQL Server 2008 SQL Server 2008 Witness Server AdventureWorks DB SQL Server 2008 Mirror Server translog Principal Server

N

et

w

or

k

N

et

w

or

k

Database Snapshot Rep ortin g U sers AdventureWorks DB translog Database Snapshot If t h is s e rv e r n o d e b e c o me s t h e mi rr o r.

(52)

52

PH Topology With Snapshots

PH Topology With Snapshots SQL Server 2008 Adventure Works DB SQL Server 2008 Mirror Server translog Principal Server translog Adventure Works DB Instance: SQL2008xyz Instance: SQL2008zzz

Endpoint Name: “endpoint4mirroring”

Role: PARTNER Role: PARTNER

PH

Topology

SQL Server 2008 Principal Server Active Passive C lu s te re d O L T P A p p li c a ti o n R e p li c a ti o n Network Network Reporting Users Database Snapshot

(53)

53 53

DB Mirror and

Replication

SQL Server 2008 Principal Server SQL Server 2008 Mirror Server SQL Server 2008 Witness Server SQL Server 2008 Principal Server SQL Server 2008 Mirror Server SQL Server 2008 Subscriber SQL Server 2008 Subscriber Publisher Distributor

(54)

54

Distributing Data

Data Access Latency Autonomy Sites

(locations) Frequency Network Machines Owner Other

Read Only

Reporting short high many high fast/_stable _server/site1 1 OLTP

site

Each site only needs regional data Central Publisher Transactional repl filter by region REPLICATION Read Only

Reporting long high many low fast/_stable _server/site1 1 OLTP

site

Each site only needs regional data Central Publisher Snapshot repl filter by region Read Mostly

A few updates short high < 10 medium fast/_stable _server/site1 1 OLTP

site Regional updates on one table Central Publisher Transactional repl Updating Subs Read Mostly

A few updates medium high < 10 medium slow/_unreliab _server/site1 _updateAll

Regional update all tables

Central Publisher Merge repl

Inserts

(new orders) short high many high fast/_stable _server/site1

1 report

site

Each site only needs regional data Central Subscriber Transactional repl Hot/Warm Spare Very

short high < 2 high

fast/ stable

1

server/site 1 OLTP_site Fail-over

Central Publisher Remote Distributor Transactional repl

Read equal

Equal updates short high < 10 medium fast/_stable _server/site1 _updateAll

Regional update all tables Peer-to-Peer Transactional repl Database Mirroring Database Mirroring Database Mirroring