<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Jeremy Egbert
»
Convert Mirroring to Log Shipping
16 posts, Page 1 of 2
1
2
»»
Convert Mirroring to Log Shipping
Rate Topic
Display Mode
Topic Options
Author
Message
JeremyE
JeremyE
Posted Wednesday, August 18, 2010 10:06 PM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 2:07 PM
Points: 2,386,
Visits: 2,656
Comments posted to this topic are about the item
Convert Mirroring to Log Shipping
Post #971597
Alistair Board
Alistair Board
Posted Thursday, August 19, 2010 3:56 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 06, 2013 2:56 AM
Points: 4,
Visits: 58
We have 2 servers that used to implement mirroring.
In both access even though the mirroring was removed conventionally there appears to be a number of processes related to the mirroring that seem to persist.
Notably the BROKER_TASK continues to be seen as a significant wait type when on similar systems which never had mirroring it is not seen at all.
I have tried to help myself.
On a test system I deleted broker related items and did remove the wait type and did not dame the server.
However I was not comfortable or confident and was perplexed thinking that removing the mirroring should have put the system back to the sate it was in before mirroring was implemented.
I am left feeling that the only way back to the pre-mirroring sate is to reinstall - tell me I am wrong, please.
Post #971722
JeremyE
JeremyE
Posted Thursday, August 19, 2010 10:39 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 2:07 PM
Points: 2,386,
Visits: 2,656
You may have to delete the mirroring endpoint as well if there are no databases mirrored. There are various other internal tasks to SQL Server that use Service Broker also. For details on Service Broker waits see the blog below.
http://blogs.msdn.com/b/sql_service_broker/archive/2008/12/01/service-broker-wait-types.aspx
Post #972025
Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
Posted Thursday, August 19, 2010 11:08 AM
Say Hey Kid
Group: General Forum Members
Last Login: Friday, April 26, 2013 11:57 AM
Points: 708,
Visits: 660
Items 9 and 14 are missing from the screen shots. I assume you are referring back to the main 'Transaction Log Shipping' page in the properties view. It might be helpful to show the full screen shot. Thanks for the article.
Can you please explain why you disabled the restore transaction log job, and when it would be necessary to leave it on?
Post #972046
JeremyE
JeremyE
Posted Thursday, August 19, 2010 11:22 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 2:07 PM
Points: 2,386,
Visits: 2,656
The reason for disabling the restore transaction log job on the secondary was given in the 3rd paragraph of the article.
"Once we convert the mirrored database to a log shipped database we can disable the restore job on the secondary and have the secondary available as a hot standby in case of an upgrade failure. This allows us to quickly backout of the upgrade by simply bringing the secondary online and pointing the application servers at it. Once the system is back online we can reestablish mirroring back to the original principal. If the upgrade is successful, we enable the restore job on the secondary, allow the secondary to catch up, and then reestablish mirroring."
If you are not planning to use the secondary as a backout plan for a failed implementation then you can just enable the restore transaction log job and convert mirroring to log shipping.
Post #972055
Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
Posted Thursday, August 19, 2010 12:06 PM
Say Hey Kid
Group: General Forum Members
Last Login: Friday, April 26, 2013 11:57 AM
Points: 708,
Visits: 660
So, this example is only intended for fail implementations? You plan on reverting back to mirroring when the implementation is complete?
Or, did you mean to include a statement to come back later and turn on that job after the code implementation was complete?
I guess what I'm not clear on is what the restore trans log job is for, in the big picture sense.
After I have converted my system to TLS, is this restore TL job the only step I need to disable/enable for all future code implementation fail over tasks?
Post #972095
JeremyE
JeremyE
Posted Thursday, August 19, 2010 12:44 PM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 2:07 PM
Points: 2,386,
Visits: 2,656
Dan Guzman - Not the MVP (8/19/2010)
So, this example is only intended for fail implementations? You plan on reverting back to mirroring when the implementation is complete?
Or, did you mean to include a statement to come back later and turn on that job after the code implementation was complete?
Once the code upgrade completes successfully the LS restore job can be turned on to catch up the secondary. Then we can reestablish mirroring for high availability.
The reason for switching to log shipping from mirroring is to have that "hot" standby copy ready on the mirror/secondary server which the application servers can point to. This brings the entire system back online faster in the event of a code upgrade failure. The traditional way of backing out of a code upgrade would be to restore the principal/primary database from backup. That may take several hours depending on the size of the VLDB creating a longer backout time.
Post #972126
SQLRNNR
SQLRNNR
Posted Thursday, August 19, 2010 5:16 PM
SSCoach
Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:46 PM
Points: 18,732,
Visits: 12,329
Thanks for the article. Good stuff.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #972236
simon.murin
simon.murin
Posted Thursday, August 19, 2010 8:31 PM
Valued Member
Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 62,
Visits: 470
Well, wouldn't it be easier to suspend DB mirrorring session in this case?
In case of upgrade failure: 1)turn off db mirrorring; 2) bring mirror db online.
3 t-sql statements in total:
1) ALTER DB SET PARTNER SUSPEND
2) ALTER DB SET PARTNER OFF
3) RESTORE DATABASE DB WITH RECOVERY
Post #972260
JeremyE
JeremyE
Posted Thursday, August 19, 2010 8:52 PM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 2:07 PM
Points: 2,386,
Visits: 2,656
That should work as long as the transaction log on the principal is large enough to hold all the transactions during the upgrade.
Post #972261
« Prev Topic
|
Next Topic »
16 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
