database mirroring and log shipping
Post on 06-Jul-2018
215 Views
Preview:
TRANSCRIPT
-
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
-
8/17/2019 DATABASE Mirroring and Log Shipping
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........................................................................................................&
-
8/17/2019 DATABASE Mirroring and Log Shipping
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
-
8/17/2019 DATABASE Mirroring and Log Shipping
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
&;"
-
8/17/2019 DATABASE Mirroring and Log Shipping
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
Microso&t Cor-oration @*++
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
-
8/17/2019 DATABASE Mirroring and Log Shipping
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
Microso&t Cor-oration @*++
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
-
8/17/2019 DATABASE Mirroring and Log Shipping
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$
Database Mirroring and Log Shipping Working Together 7
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/17/2019 DATABASE Mirroring and Log Shipping
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
Microso&t Cor-oration @*++
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspxhttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
-
8/17/2019 DATABASE Mirroring and Log Shipping
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
Microso&t Cor-oration @*++
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
-
8/17/2019 DATABASE Mirroring and Log Shipping
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
Microso&t Cor-oration @*++
-
8/17/2019 DATABASE Mirroring and Log Shipping
11/11
Database Mirroring and Log Shipping Working Together 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
Microso&t Cor-oration @*++
top related