Convert Existing Live to Secondary in Log Shipping

Question

Convert Existing Live to Secondary in Log Shipping

BSql_Dev

SSC Eights!

Points: 875
More actions
December 3, 2010 at 3:04 am

#388358

Hi,
I have current Live server which from now I want to use as Secondary for Log shipping. and then setup a new server as Primary.
But I don't know how to convert current live to take in NoRecovery Mode without data loss.
I am very confused in this.
Can someone help.
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

S. Kusen SSChampion Points: 10936 More actions · Answer 1

I would think you should be able to pick a brief maintenance window, take your application offline, point your application connections over to the secondary instance (the log shipped instance), then set the database to read only mode on the primary instance. Restore the database with recovery on the secondary instance, and then bring the application back online.

Any reason why log shipping is being used instead of mirroring?

With mirroring, you could do a failover and have no data loss, and you could put in the secondary instance into the connection string (failover partner option) if you're using the Native Client driver. When you do the database failover, the user impact would (as designed) be eliminated and the end user would never know whether they were on the primary or secondary. Once on the secondary, you could change your connection strings to use the secondary as the new primary (or only instance at this point). When your new "primary" arrives and is stood up, you could then set that up as the mirrored host, and repeat the process to failover to that.

The steps would look like this:

1. Primary mirrors to secondary

2. Failover to secondary

3. Update connection strings to remove reference to old primary.

4. Receive new primary.

5. Set up mirroring from secondary to new primary

6. Failover from secondary to new primary

7. Reverse connection string logic to use new primary as the primary data source, and the failover partner is again set up as the secondary instance.

Log shipping, to me, makes the process a bit more involved compared to mirroring.

Regards,

Steve

WayneS SSC Guru Points: 95460 More actions · Answer 2

disable all connections on the (current) primary.

perform a tail log backup.

restore the tail-log backup on the primary with "no recovery"

restore the tail-log backup on the secondary with "recovery"

as previously mentioned, point applications to the new secondary.

you should be up and running.

BTW, if you're doing log shipping, you should regularly practice and document this. You should not be needing to come here to figure out how to do this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

BSql_Dev SSC Eights! Points: 875 More actions · Answer 3

@ S.K , WayneS

Thanks for replies.

@S.K

I am quite new to this backup setups...I don't know I decided right or not, but problem is I need to have a backup setup of not only databases, we need to setup a complete backup environment, IIS, applications, databases so any type of crash handled at one server we just switch totally to other one. and servers are at very far distant locations that was also the factor and not that good speed I have between the servers. That's why I decided log shipping then.

@waynes

I am new to this and setting first time Log Shipping I have done now practice many times and did switch roles many times. But had just started at that time so was very confused that's why asked here 🙂

thanks again for help...

Best Regards/