February 28, 2008 at 3:50 am
Hi All,
I've just been given a DR server on a different domain to my production box. I'm trying to get a heads up on the preferred method of moving data to my new DR server on a nightly basis.
The data in my production database would need to be copied nightly.
SQL 2005
Replication (snapshot, merge ) ??
Mirroring ??
I'm a developer who now has to administer the databases and DBA-ing is not my strong suit, so all suggestions gratefully received.
W
February 28, 2008 at 8:18 am
Well this depends on your business requirements. Do you need automatic failover or is a manual failover okay? How much data loss is acceptable?
Usually for DR or high availability you lean toward mirroring or transactional replication. Snapshot is not truly a high availability solution because it is a point in time. Any transactions that occur after the snapshot is created are potentially lost, until the next snapshot occurs. With merge replication there are too many anomalies that can affect data integrity, such as insert of duplicate keys, updating conflicts and updating non existing rows. To remedy the conflicts you have to use a conflict resolver. While this may be a viable, solution it can lead to conflicts where the system makes a decision about which row to keep and the user wanted to keep another.
Can you define more specifically what you are looking for in a DR solution?
February 28, 2008 at 8:20 am
How many databasea are there and what are thier sizes. If you have limited number of DBs smaller in size do a snapshot replication. Why don't you go for log shipping so that the data loss is always minimal and you can configure it in number of ways.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 8:35 am
Also, are you using Windows or SQL Auth? If it's Windows, you need to be sure that you can failover as the security needs to be setup across domains. Otherwise none of your users will be able to access the DR box.
February 28, 2008 at 8:53 am
This is not a common method - but if you really are doing it just once a day, why not restore from the production backups?
The nice part about that is that it puts no extra load on the production interface, and it allows you to keep a handle on whether your backups are any good.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 10:11 am
Thanks for all the input guys. I'll try and answer some of the points you raised, in no order
1, Theres no requirement for these to be hot swapped on failure , an hour or two of downtime is acceptable this is not an OLTP system.
2, A failure would not be catastrophic as I could load data to the DR box from external data files via ssis if necessary.
3, 4 databases to be replicated 2 x 20mb , 1 x 10GB , 1 x 40GB
4, Windows Authentication between the boxes
I definitely could restore the DR databases nightly from the backup files but is this the preferred method for you guys?
What I'm looking for I suppose is the most low maintenance robust solution I can find as I have enough to do without worrying about things being synched correctly.
I really appreciate the help.
W
February 28, 2008 at 10:22 am
Backups and restores are just one option. Depending on how much activity occurs - it may or may not be what you need. Restoring a 40GB database that hardly doesn't change would be wasteful if done daily. Of course - setting up replication or mirroring on a database that gets torn down and rebuilt completely each day is also a bad idea. Depends on your activity patterns. I don't think there is a "one size fits all" answer.
With such a small amount of DB's with reasonably low activity - look at possibly doing mirroring, and possibly the "high performance" version at that. If a failure occurs, you might not get "up to the minute" recovery, but should give you a small window of data loss.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 11:20 am
You can use replication for your needs.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 1:03 pm
One thing you have not mentioned is how much bandwith do you have available
* Noel
February 28, 2008 at 2:12 pm
Well it seems like you have a lot of flexibility. I would stick with mirroring, transactional replication, or log shipping. All of these options would satisfy your base requirements. You should choose an availability option that will not only work now but scale years down the road.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply