up vote 0 down vote favorite
share [g+] share [fb]

Good morning all I have been researching various methods of SQL Server recovery options.

The scenario is simple and I want the same solution. I have 2 SQL Server databases on separate machines at separate locations and they have access to each other via a VPN.

The Primary server in this instance is on Win Server 2003 and running SQL Server 2005, the swanky new secondary server is Win Server 2008 R2 and SQL Server 2008.

Regardless of the awesome inconsistencies between the two we still want the secondary server to be up to date and available.

I looked at transaction log shipping and have been for a couple of days but I've come to the realisation that the database is only ready when I apply transaction logs by hand and this is not what we want.

The database should be no more than 24 hours out of date. I'm going to end up with full back up and restores but before we set this up via a script what other options are there.

I looked at mirroring but this doesn't work between varying SQL Server versions.

I looked at replication but it is not viable to change the tables on the database to support replication either.

Any options? :)

Thanks

link|improve this question
1  
Best option: upgrade your "primary" server to SQL Server 2008 as well, and use mirroring – marc_s May 16 '11 at 9:03
1  
I'd second mirroring. One thing though, are you on Enterprise or Standard SQL Server ? Only enterprise supports asynchronous mirroring- so if you link between the servers isn't fast, you can slow down your primary application while the mirroring service waits to commit transactions to both servers. – Runonthespot May 16 '11 at 9:16
The best part of mirroring though is it's one line run on the mirror box to switch it into a live node (assuming you're not using a 3rd server to handle that automatically). – Runonthespot May 16 '11 at 9:17
Finally, consider looking at RedGate- they do a syncing solution, although mirroring has less moving parts and is the quickest/easiest to set up. – Runonthespot May 16 '11 at 9:18
We have Enterprise edition on the new 'secondary' server. I will suggest the updgrade option but they will pretty much say no from awesome fear of even moving the mouse on primary. :) – Robert May 16 '11 at 14:34
feedback

migrated from stackoverflow.com May 18 '11 at 7:45

This question came from our site for professional and enthusiast programmers.

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
or
required, but never shown
discard

Browse other questions tagged or ask your own question.