database mirroring and log shipping

8/17/2019 DATABASE Mirroring and Log Shipping

1/11

Database Mirroring and Log ShippingWorking Together

SQL Server Best Practices Article

Writer : Sanjay Mishra

Contributors: Prem Mehra, Lindsey Allen

Technical Reviewers: Matt ollin!sworth, "uer!en Thomas, Mar# Sou$a,%ur$in Patel, Tom Michaels, Ra&&aella Canobbio, Steve Lindell, 'evin(arlee, Mar# Scurrell, Michael Redman, Michael Thomassy, Mi#eRuthru&&, Thomas )avidson

Published: "anuary *++

A--lies To: S.L Server *++/ Service Pac# *

Summary: Lo! shi--in! and database mirrorin! can wor# to!ether to -rovide solutions &or hi!h availability and disaster recovery0 1ou canconvert an e2istin! lo! shi--in! con&i!uration to a database mirrorin!con&i!uration0 Althou!h Microso&t only su--orts a sin!le mirror database,you can set u- lo! shi--in! &rom a database mirrorin! -air to additionalservers, to -rovide multi-le &ailover sites0 Moreover, you can easilyswitch which -air o& instances is usin! database mirrorin! and whichinstances are con&i!ured with lo! shi--in!0


2/11

Filename: 317211326 2

Copyright

The in&ormation contained in this document re-resents the current view o& Microso&t Cor-oration on the

issues discussed as o& the date o& -ublication0 %ecause Microso&t must res-ond to chan!in! mar#et

conditions, it should not be inter-reted to be a commitment on the -art o& Microso&t, and Microso&t cannot

!uarantee the accuracy o& any in&ormation -resented a&ter the date o& -ublication0

This White Pa-er is &or in&ormational -ur-oses only0 M3CR4S4(T MA'5S 64 WARRA6T35S, 57PR5SS,

3MPL35) 4R STAT8T4R1, AS T4 T5 36(4RMAT346 36 T3S )4C8M56T 0

Com-lyin! with all a--licable co-yri!ht laws is the res-onsibility o& the user0 Without limitin! the ri!hts under

co-yri!ht, no -art o& this document may be re-roduced, stored in or introduced into a retrieval system, or

transmitted in any &orm or by any means 9electronic, mechanical, -hotoco-yin!, recordin!, or otherwise, or

&or any -ur-ose, without the e2-ress written -ermission o& Microso&t Cor-oration0

Microso&t may have -atents, -atent a--lications, trademar#s, co-yri!hts, or other intellectual -ro-erty ri!hts

coverin! subject matter in this document0 52ce-t as e2-ressly -rovided in any written license a!reement

&rom Microso&t, the &urnishin! o& this document does not !ive you any license to these -atents, trademar#s,

co-yri!hts, or other intellectual -ro-erty0

© *++ Microso&t Cor-oration0 All ri!hts reserved0

Microso&t and S.L Server are either re!istered trademar#s or trademar#s o& Microso&t Cor-oration in the

8nited States and;or other countries0

The names o& actual com-anies and -roducts mentioned herein may be the trademar#s o& their res-ective

owners0

Table of ContentsIntroduction......................................................................................................1

Converting a Log Shipping Configuration to Database Mirroring.......................1

Set 8-00000000000000000000000000000000000000000000000000000000000000000000000000000000000000*

Log Shipping to a Third Destination fro a Database Mirroring !air................."

Set 8-00000000000000000000000000000000000000000000000000000000000000000000000000000000000000/

Potential Problem00000000000000000000000000000000000000000000000000000000000000000000000<

S#itching $et#een the Database Mirroring !air and the Log Shipping !air......%

Conclusion........................................................................................................&


3/11

Database Mirroring and Log Shipping Working Together 3

Introd!tion3n this -a-er we discuss the &ollowin! scenarios:

• "on#erting a log shipping !on$igration to database mirroring

• Log shipping to a third destination $rom a database mirroring pair

•

S%it!hing bet%een the database mirroring pair and the log shipping pairThis -a-er assumes that the reader has a basic understandin! o& lo!shi--in! and database mirrorin!0

"on#erting a Log Shipping "on$igration toDatabase Mirroring

Lo! shi--in! has been available &or many years, and with the advent o&database mirrorin! in Microso&t = S.L Server > *++/ Service Pac# ?9SP?, it may be advanta!eous to convert lo! shi--in! installations todatabase mirrorin!0 )atabase mirrorin! -rovides the &ollowin!advanta!es over lo! shi--in!:

• &o data loss option 's(n!hronos database mirroring)

• *tomati! $ailo#er option 's(n!hronos database mirroring %ith %itness)

%e&ore convertin! &rom lo! shi--in! to database mirrorin!, note the&ollowin! considerations:

• Database mirroring re+ires the F,LL re!o#er( model $or the database- %hereas log

shipping !an %ork %ith either the .,L/0LD or F,LL re!o#er( models4 I$ (or

database is rnning in the blk5logged re!o#er( model- (o mst !hange it to $ll to

se database mirroring4 .e a%are that the F,LL re!o#er( model ma( generate a

more transa!tion log4

• Log shipping has little or no impa!t on appli!ation per$orman!e on the primar(

ser#er4 Depending on the %orkload and the amont o$ the generated log- databasemirroring !an impa!t appli!ation per$orman!e4 For details- see Database Mirroring

.est ra!ti!es and er$orman!e "onsiderations4 #alate (or per$orman!e ser#i!e

le#el agreements %ith respe!t to (or %orkload and the net%ork !apa!it( bet%een

the t%o ser#ers4

1ou can convert an e2istin! lo! shi--in! con&i!uration to a databasemirrorin! con&i!uration without havin! to ta#e a &ull bac#u- o& the -rinci-al database and restore it on the mirror0 A sim-li&ied architecture&or this de-loyment is shown in (i!ure ?0

Microso&t Cor-oration @*++

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspxhttp://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspxhttp://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspxhttp://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx


4/11

Secondary

Log Shipping

Primary

S

1

S

2

B

ef

or

e

Mirror

DatabaseMirroring

Principal

S

1

S

2

A

ft

er

Figure 1: Converting a log shipping configuration to databasemirroring

Set ,pTo convert a log shipping configuration to database mirroring

1 nsre that log shipping is %orking $ine bet%een S1 and S2 S1 as primar( and S2

as se!ondar(4 8o !an !he!k the stats o$ the log shipping 9obs 'ba!kp- !op( and

restore) $rom the SL *gent 9ob histor(4 Make sre these 9obs are rnning ass!hedled %ithot an( errors4 *lso- ensre that the se!ondar( database is in the

&;"


5/11

Database Mirroring and Log Shipping Working Together >

This error occurs when some transaction lo! bac#u-s have been ta#enon the -rimary server, but have not yet been co-ied and restored tothe secondary server0 Lo! shi--in! wor#s on a schedule, and it is -ossible that at any !iven -oint in time, there may be sometransaction lo! bac#u-s ta#en on the -rimary, but not yet restored tothe secondary0

A&ter you have disabled the lo! bac#u- job, instead o& waitin! &or theco-y and restore jobs to e2ecute on schedule, you can manuallye2ecute the co-y job and the restore job on the secondary server0 %yde&ault, the co-y job and the restore job are scheduled to run every?/ minutes they may have been scheduled to run even less&reBuently0 Manually e2ecutin! the co-y job and the restore jobreduces wait time0

? *$ter (o ensre that all the log ba!kps taken so $ar ha#e been !opied and applied

to the se!ondar(- disable the !op( 9ob and the log restore 9ob on the se!ondar(4

> nable database mirroring %ith S2 as the mirror and S1 as the prin!ipal4 .( de$alt-

database mirroring is !on$igred as s(n!hronos4 I$ (o desire as(n!hronos

database mirroring bet%een S1 and S2- (o need to !hange sa$et( le#el a!!ordingl(4

6 ;emo#e the log shipping !on$igration bet%een S1 and S24

ote: avin! both database mirrorin! and lo! shi--in! enabled tothe same destination database at the same time is not su--orted0 3&database mirrorin! and lo! shi--in! are enabled to the samedestination database, you will see errors similar to the &ollowin! in the job history o& the lo! restore job on the secondary:

Error: Could not apply log backup file

':!ackup!#y$%&&&(%%%%)*+.trn' to secondary database '#y'.

,#icrosoft.-l-er/er.#anagement.0og-hipping1

Error: 2E-T32E cannot operate on database '#y' because it isconfigured for database mirroring. 4se 50TE2 5T55-E to remo/e

mirroring if you intend to restore the database.

The above wor#&low describes how to easily convert an e2istin! lo!shi--in! con&i!uration to database mirrorin!0

This a--roach may be use&ul in another use case0 Assume you have a lo!shi--in! con&i!uration 9without database mirrorin! between two serversS? 9the -rimary server and S* 9the secondary server0 1our businessreBuirement is to minimi$e the downtime o& the a--lication &or -lannedmaintenance o& the -rimary server0 4ne common a--roach is to &ailoverthe database &rom the lo! shi--in! -rimary to the secondary 9see (ailin!

4ver to a Lo! Shi--in! Secondary , but the a--lication will not &ail overautomatically and the downtime can be in minutes0 An alternativea--roach to ensure a seamless user e2-erience durin! -lanneddowntime is as &ollows0

14 "on#ert the log shipping !on$igration to s(n!hronos database mirroring b( sing

the set p steps des!ribed earlier in this se!tion4 Depending on the %orkload and

net%ork !apa!it( bet%een the primar( and se!ondar( ser#ers- (o might %ant to


http://msdn2.microsoft.com/en-us/library/ms191233.aspxhttp://msdn2.microsoft.com/en-us/library/ms191233.aspxhttp://msdn2.microsoft.com/en-us/library/ms191233.aspxhttp://msdn2.microsoft.com/en-us/library/ms191233.aspx


6/11

pi!k a lo% a!ti#it( %indo% so that the mirroring partners !an remain in the

s(n!hroni@ed state dring the maintenan!e %indo%4

24 er$orm manal $ailo#er o$ the database $rom the prin!ipal to the mirror4 *s the old

mirror takes p the role o$ the ne% prin!ipal- the appli!ation !an atomati!all(

re!onne!t to the ne% prin!ipal and !ontine pro!essing- pro#ided (o ha#e $ollo%ed

the best pra!ti!es re!ommendations des!ribed in the %hite paper Implementing

*ppli!ation Failo#er %ith Database Mirroring4

34 n!e the planned maintenan!e is !omplete- (o !an $ail ba!k to the original

prin!ipal4

?4 ;emo#e the database mirroring !on$igration4

>4 ;e5establish log shipping4

Log Shipping to a Third Destination $rom aDatabase Mirroring air

While database mirrorin! has many bene&its, the one notable advanta!eo& lo! shi--in! is its su--ort o& multi-le secondaries0 Those usin! lo!shi--in! with multi-le secondaries need similar &unctionality withdatabase mirrorin! in order to consider convertin! &rom lo! shi--in! todatabase mirrorin!0

8sers want multi-le co-ies o& a database D usually one &or local &ault -rotection, and one or more at remote locations &or disaster recovery0)atabase mirrorin! and lo! shi--in! can wor# to!ether to -rovide asolution &or this reBuirement0 A mirrored -air 9a -rinci-al and a mirrorcan shi- transaction lo! bac#u-s to a third server 9or to many secondary servers0 The -rinci-al server acts as the -rimary &or the lo! shi--in!con&i!uration0 When a &ailover ta#es -lace between the mirrored -airs,the new -rinci-al 9old mirror seamlessly ta#es over the role o& the

-rimary server &or the lo! shi--in! con&i!uration0 A sim-li&ied architecture is shown in (i!ure *0 3n this &i!ure, Servers S?and S* are con&i!ured as database mirrorin! -artners0 4ne -ossiblecon&i!uration may involve S? and S* in the same cam-us and con&i!ured with synchronous database mirrorin! to -rovide local &ault -rotectionand server SE at a remote site actin! as the lo! shi--in! destination to -rovide disaster recovery solution0 Another -ossible con&i!uration mayinvolve the three servers S?, S* and SE at three di&&erent sites0


http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspxhttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspxhttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspxhttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx


7/11

DatabaseMirroringPair

S

1

S

2

LogShipped

Secondary

S

3

LogApp

lyLOG

BACKUP

The principal and the mirror both areconig!red as log shipping primary"b!t only the principal is prod!cingthe log bac#!ps$ Upon ailo%er& thene' principal 'ill start prod!cing thelog bac#!ps at the same sharelocation$


Figure !: Log shipping to a third destination from a databasemirroring pair

Set ,pTo set up log shipping to a third destination from a databasemirroring pair

14 Setp database mirroring bet%een S1 and S24 S1 is the prin!ipal and S2 is the

mirror424 .e$ore (o set p log shipping- take a ba!kp $rom S1 and restore it %ith

&;"


8/11

to S24 &o% S2 is the prin!ipal and S1 is the mirror4 lease note that $ailing o#er to

the mirror database ma( impa!t the eAisting !onne!tions4 The appli!ation !an

atomati!all( re!onne!t to the ne% prin!ipal and !ontine pro!essing- pro#ided (o

ha#e $ollo%ed the best pra!ti!es re!ommendations des!ribed in the %hite paper-

Implementing *ppli!ation Failo#er %ith Database Mirroring4

>4 Set p log shipping $rom S2 to S3- sing S2 as the primar( and S3 as the

se!ondar(4

a4 Do not se the log shipping ser inter$a!e in SL Ser#er Management Stdio4

The ser inter$a!e allo%s onl( one primar( $or a gi#en log shipping !on$igration4

In this !ase- %e are setting p t%o ser#ers 'althogh onl( one %ill be a!ti#e at

an( gi#en point in time) as the primar( ser#er4 Instead- se the s!ript generated

in step 3b- bt do not eAe!te the entire s!ript4

b4 The generated s!ript has t%o se!tions4 ne se!tion is marked as BCS!ript to be

rn at rimar( C- and the other as BCS!ript to be rn at Se!ondar( C4 Ae!te

onl( the se!tion marked as BCS!ript to be rn at rimar( C on S24 The se!tion

BCS!ript to be rn at Se!ondar( C has alread( been eAe!ted on the se!ondar(

'S3) in step 3!- so there is no need to eAe!te it again4

ote: )o not chan!e anythin! in the scri-t0 5s-ecially, ma#e sureyou use the same share location &or bac#in! u- the transaction lo!as s-eci&ied in the setu- &rom S? to SE0

!4 Test to #eri$( that log shipping $rom S2 to S3 is %orking4

64 er$orm a manal $ailo#er o$ the database $rom S2 to S14 *gain- S1 is the prin!ipal

and S2 is the mirror4

74 I$ (o !hanged to s(n!hronos database mirroring in step ?- !hange ba!k to

as(n!hronos database mirroring4

This -rocedure sets u- both S? and S* as the -rimary &or lo! shi--in!03n the event o& a database mirrorin! &ailover, the new -rinci-al will

seamlessly ta#e over the res-onsibility o& the lo! shi--in! -rimary0Lo! bac#u- jobs are created on both S? and S*0 The lo! bac#u- job onthe -rinci-al 9s? will -roduce the lo! bac#u- &iles on the share, whichwill be -ic#ed u- by the co-y job on the secondary0

The lo! bac#u- job on the mirror 9S* will continue to e2ecute, but willnot -roduce anythin!0 1ou will see the &ollowin! messa!e in the jobstatus on the mirror:

5 transactional log backup could not be generated because the database

is in either 632EC37E28 mode or -T568 mode.

The messa!e is sel&Fe2-lanatory and it is o#ay to !et this messa!e onthe mirror0

To the secondary server 9SE it ma#es no di&&erence which server 9S? orS* is actin! as the -rimary0 The secondary server will always &ind thereBuired lo! bac#u- &iles on the same share location, and continue toa--ly them0

The con&i!uration described here shows one secondary database0 1oucan have several lo! shi--in! secondary databases0


http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspxhttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx


9/11

Database Mirroring and Log Shipping Working Together E

otential roblem1ou should be aware o& a -otential -roblem that could arise when lo!shi--in! &rom a database mirrorin! -air to a third server0 The lo!shi--in! secondary may e2-erience an error condition i& databasemirrorin! &ailover ta#es -lace when Gthe mirrorin! hardened LS6H is less

than Gthe lo! shi--in! bac#u- LS6I 9&or details, see )atabase Mirrorin!Sessions 0 LS6 is the lo! seBuence number 9see 3ntroduction to Lo!SeBuence 6umbers 0 This can ha--en on rare occasions when somethin!similar to the &ollowin! ha--ens:

• S1 has hardened LS&s E- 1- 114

• S2 has hardened LS&s E- 14

• The appli!ation has not (et re!ei#ed a !on$irmation that LS& 11 has been !ommitted

be!ase S2 has not (et hardened4

• * $ailre on S1 !ases $ailo#er to S24

• The last log ba!kp taken on S1 had LS& 114

•

The ne% log ba!kp taken on S2 in!ldes onl( LS& 1 'does not in!lde LS& 11)4• The log ba!kp taken on S2 no% is ot o$ s(n! %ith the se!ondar( ser#er 'S3)-

be!ase S3 has re!ei#ed the pre#ios ba!kp that in!lded LS& 114

1ou could see errors such as the &ollowin! in the 5RR4RL4J o& thesecondary:

The backup data at the end of ":!ackup!#y$%&&**&%%*)%&&.trn" is

incorrectly formatted. ackup sets on the media might be damaged and

unusable.

The lo! shi--in! secondary is now out o& sync with the -rimary0 1oumust rebuild the lo! shi--in! secondary, startin! with a &ull bac#u- &romthe -rimary0 This will be resolved in a &uture release o& S.L Server0

S%it!hing .et%een the Database Mirroring airand the Log Shipping air

3& you are lo! shi--in! to a third server &rom a database mirrorin! -air,and you need to switch which -air o& servers are database mirrorin! -artners and which is the lo! shi--in! secondary, you can do this &airlyeasily0 4ne scenario where you mi!ht need to do this is when you do arollin! u-!rade o& all three servers0 (i!ure E illustrates the be&ore anda&ter con&i!uration0


http://msdn2.microsoft.com/en-us/library/ms189901.aspxhttp://msdn2.microsoft.com/en-us/library/ms189901.aspxhttp://msdn2.microsoft.com/en-us/library/ms190411.aspxhttp://msdn2.microsoft.com/en-us/library/ms190411.aspxhttp://msdn2.microsoft.com/en-us/library/ms189901.aspxhttp://msdn2.microsoft.com/en-us/library/ms189901.aspxhttp://msdn2.microsoft.com/en-us/library/ms190411.aspxhttp://msdn2.microsoft.com/en-us/library/ms190411.aspx


10/11

Princi

palS

1 LogShipped

Secondar y

S

3

S

2

Mir r

or

DBM

LS

LSB

e

f

o

r

e

PrincipalS1 Mir

ror S

3

S

2

LogShipped

Secondar y

LS

DBM

LS A

f

t

er

Figure ": S#itching bet#een the database mirroring pair and thelog shipping pair

As illustrated in (i!ure E, we start with a con&i!uration where S? and S*are the database mirrorin! -artners, and SE is the lo! shi--in!secondary0 3n the tar!et con&i!uration, S? and SE are the databasemirrorin! -artners, and S* is the lo! shi--in! secondary server0

To achieve the target configuration

14 ;emo#e S2 as log shipping primar(4 Do not se the log shipping ,I $rom in

SL Ser#er Management Stdio4 Instead- !onne!t to the master database on the

mirror ser#er 'S2)- and rn the $ollo%ing t%o stored pro!edres in the $ollo%ing

order:

a0 52ecute sp$delete$log$shipping$primar%$secondar% on S*0

b0 52ecute sp$delete$log$shipping$primar%$database on S*0

24 Stop the database mirroring session bet%een S1 and S24

34 Follo% the steps in "on#erting a Log Shipping "on$igration to Database Mirroring to

!on#ert the log shipping !on$igration bet%een S1 and S3 to a database mirroring

!on$igration4 S1 is no% the prin!ipal and S3 is the mirror4

?4 Follo% the steps in Log Shipping to a Third Destination $rom a Database Mirroring

air to set p log shipping to S2 $rom the database mirroring pair 'S1 and S3)4 I$

(o !omplete this !on#ersion in a short period o$ time- and (o ha#e retained all the



11/11


transa!tion log ba!kps taken dring this !on#ersion- it is not ne!essar( to per$orm

a $ll restore on S2 %hile setting it p as a log shipping se!ondar(4

"on!lsionLo! shi--in! and database mirrorin! can wor# to!ether to -rovide

solutions &or hi!h availability and disaster recovery0 1ou can convert ane2istin! lo! shi--in! con&i!uration to a database mirrorin! con&i!uration0 Althou!h Microso&t only su--orts a sin!le mirror database, you can setu- lo! shi--in! &rom a database mirrorin! -air to additional servers, to -rovide multi-le &ailover sites0 Moreover, you can easily switch which -air o& instances is usin! database mirrorin! and which instances arecon&i!ured with lo! shi--in!0


database mirroring and log shipping

Documents