Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a client with a small business server 2008 Premium installation. This obviously includes SQL Server 2008 standard on a separate 2008 server. On the SQL we run several line of business applications.

The client has got the the size where if we were to have a failure it would cripple the business. We have ample backups protecting the data, but it would obviously take time to get a new server up and running etc.

What we want is a stand by server in a remote office, but I need to know the best way of replicating the SQL server. I see there are different ways, snap shot or transactional etc, but I haven't found a decent article describing the pros and cons of them.

I dont want to put much more stress onto the SQL server as it is quite busy with both reads and updates. I don't want the primary server waiting on the secondary server to update before it commits a write.

Also the table structure and views change fairly regularly as new features/updates are added to the LOB applications.

I'm wondering if anyone has any opinion on what method may help.

Also wondering if we would be better off virtualising the server and mirroring this, which I believe is quite good with the new hyper V.

edit (OP, once you have associated your accounts, please incorporate this further information to make a single, cohesive question.)

sorry for the delay.

I suppose in terms of data loss, i suppose an hour wouldnt be the end of the world.

Hardware, wouldnt be identical as its currently on an hp ml350 g6

i was talking about hyper-v 2012s replication to a completely seperate server.

I guess i am concerned about disk io as one of the lob dbs is pretty hefty and slow, although we got the developers to re-write some of the worst bit to make it better.

I guess my biggest concern is if when writing data it has to wait for both the local and remote server to update. Does log shipping need do this?

The remote server will be on remote ADSL connection, and i would like to avoid having to replicate it twice.

Also the SQL server is part of Small business server 2008 premium, but was brought as OEM, are we allowed to either move the second windows server license onto another machine (the main server will remain on the origional hw) or can we move just the SQL onto a new windows 2012 box? as this would mean we can move the sql onto better hw, and use the old one as the DR box.

share|improve this question
1  
@StuartEdwardJeffrey How much data loss is acceptable and what kind of hardware is the standby server having - same as source server ? –  Kin Aug 7 '13 at 13:30
    
Kin's point is key, the two main criteria for this kind of query are a) how much data can you afford to lose during a disaster and b) how quickly do you need to be back up. Be realistic with both –  Paul Aug 7 '13 at 13:35
add comment

migrated from stackoverflow.com Aug 7 '13 at 12:33

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

1 Answer

I would implement Log Shipping, which is just a process of taking transaction log backups at one site, and restoring them at another site. This operates at the database level, and it will inherently handle all the schema changes because backups operate at a level above that.

There's really no other sane solution on Standard Edition because the requirement of having an asynchronous process severely limits the available options. (If you're just getting started in this area, don't even think about any kind of SQL Server replication.)

Note also that as far as I can tell (and I could be wrong about this), Windows Server 2012 storage virtualization (aka Storage Spaces) is meant as an enhanced RAID controller given a set of local disks; it's not for replicating virtual machines across data centers. I believe to do that requires specific storage hardware that supports it, and I don't think you're looking to go down that path right now.

I dont want to put much more stress onto the SQL server as it is quite busy with both reads and updates.

Quantify this. What kind of options do you have to upgrade the hardware (I'm assuming it's several years old at this point)? Is the bottleneck CPU or I/O?

Regardless, if you're legitimately maxing out the available resources right now, virtualizing is a bad idea, as it adds inherent overhead to the system. Virtualization is great, and it may be appropriate here for other reasons, but definitely not if you're concerned about performance on your current physical hardware.

I don't want the primary server waiting on the secondary server to update before it commits a write.

I'm assuming this is because there's a slow link between the sites? If that's the case, and you want to protect against hardware failure, consider implementing a synchronous site-local mirror (on a separate physical box using Database Mirroring) for high availability, and then an asynchronous remote site using Log Shipping for disaster recovery.

Any kind of mirroring or replication is going to add some latency or load to the primary server. How much depends on the hardware and the workload. This is the tradeoff you have to make to protect against failures. It isn't completely free, but with proper hardware and design, it can be minimized to the point that it's irrelevant.

share|improve this answer
    
+1 for suggesting log shipping as the OP mentioned table structure and views change fairly regularly, so Replication would be a headache to maintain, doing snapshots, etc and Mirroring has its own overhead - synchronous especially. Good answer ! –  Kin Aug 7 '13 at 14:42
    
+1 going with Jon's advice for sure. If your app isn't 'customer facing' and you can get a MS Silver subscription with the core data requirements, you could use SQL Server Enterprise internally without the licensing overhead but it looks like you're not in that situation. –  Ali Razeghi Aug 8 '13 at 19:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.