SQL Server DR, mirroring or virtualise and mirror

Question

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.

Answer 1

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.