Create redundancy for our SQL server?

[reggiep]

We had a nasty fright yesterday when we rebooted our SQL server with our finance and sims on it!

 

So I am in the middle of looking to replace it with something that has redundancy built in.

 

Can anyone suggest who to talk to about this?

 

Thanks

[pantscat]

Sounds like you want some sort of fault tolerant cluster.

 

Is it a physical server?

[jinnantonnixx]

There are various options, but we use log shipping. The logs of our live databases are backed up, copied and restored to a standby server. It's easy to set up one or two using the wizard, but for a hundred I had to write scripts.

 

It works well, we have used it after a failure and the failover time for (from memory, about 10 SIMS databases) was about 15 minutes to get it up and running on the secondary server.

 

About Log Shipping (SQL Server)

 

Another option is database mirroring.

http://msdn.microsoft.com/en-gb/library/ms189852.aspx

Edited June 4, 2014 by jinnantonnixx

[zag]

I'd just virtualize the server then back it up each night.

 

Instant recovery if it ever went wrong.

 

The other option would be to have a 2nd server with SQL on it and just use something like sqlbackupandftp to backup and restore the sql to the 2nd server if something went wrong.

[jinnantonnixx]

I would always keep the OS and data as a separate concern.

 

Sometimes a OS can go disastrously wrong, but your data is OK. Restoring an SQL server as an atomic entity would mean that you'd lose data if you needed to restore to a previously working version of the OS.

 

If the OS and data are on different disks (as they should be!) then the database can be remounted on a new OS quite easily, so this is a reasonable strategy.

Edited June 4, 2014 by jinnantonnixx

[reggiep]

@pantscat Yes it is a physical server. Clustering is something I have never done before but am interested in looking at.

@jinnantonnixx - Thanks I'll look into the log shipping too

@zag - I explained to our business manager that if I recovered from last nights backups that we would lose the days changes and she wasn't happy. I wasn't sure what else we could do to be honest but looking at jinnantonnixx post I may have options now.

 

I've just worked out jinnantonnixx name!

[pantscat]

I think @Jinnatonnixx has highlighted the simplest solution so far. I didn't realise exactly how log shipping worked - that's a nice little elegant solution.

[jinnantonnixx]

If you use log-shipping, do not combine it with any other transaction backup routine on your database (i.e. something else that backs up the logs files). Every transactional backup will truncate your logs. If you're using two backup systems (log shipping + something else), each of these will truncate the logs.

The result will be a useless and broken log-shipping system and a useless and broken backup system. Not an ideal situation. Your heart sinks when you see a broken LSN chain.

 

If you need to make ad-hoc backups/copies of data, be sure to use the COPY ONLY flag on the backup. This feature doesn't touch the logs.

 

It's fine to do, say, full backups at night or at the weekend (full backups don't truncate the logs), but don't mix and match log/transactional backups with log-shipping.

Edited June 4, 2014 by jinnantonnixx

[reggiep]

If you use log-shipping, do not combine it with any other transaction backup routine on your database (i.e. something else that backs up the logs files). Every transactional backup will truncate your logs. If you're using two backup systems (log shipping + something else), each of these will truncate the logs.

The result will be a useless and broken log-shipping system and a useless and broken backup system. Not an ideal situation. Your heart sinks when you see a broken LSN chain.

 

If you need to make ad-hoc backups/copies of data, be sure to use the COPY ONLY flag on the backup. This feature doesn't touch the logs.

 

It's fine to do, say, full backups at night or at the weekend (full backups don't truncate the logs), but don't mix and match log/transactional backups with log-shipping.

 

Looking at step by step guides it does look like a scary big job!

Would you agree?

[jinnantonnixx]

Looking at step by step guides it does look like a scary big job!

Would you agree?

 

I had to script my setup, so that was tricky, I grant you. But don't be worried, if you only have a couple of databases, the Log Shipping wizard thing sorts it out for you. It's OK.

 

Here's a guide that shows you the wizard.

 

Step By Step SQL Server Log Shipping

 

Don't test the water with both feet, test it on a dummy copy of the data or better still in a virtual environment. Make sure you can restore and change-over, this either means changing the database name or point SIMS to the new database (don't forget to restructure the Docstorage folder structure). Do it again and again, until you know it inside out and you're sure you can get a working restore under pressure.

[glennda]

Personally I would virtulise and then either use something for live replication or replicate every 1 hour.

 

IMHO not a lot should be physical these days apart from keeping a physical DC and Virtual Hosts. (this does vary but I can't see a huge amount schools use that requires its own hardware).

[reggiep]

Don't test the water with both feet, test it on a dummy copy of the data or better still in a virtual environment. Make sure you can restore and change-over, this either means changing the database name or point SIMS to the new database (don't forget to restructure the Docstorage folder structure). Do it again and again, until you know it inside out and you're sure you can get a working restore under pressure.

 

The under pressure bit sounds all too familiar!

[jinnantonnixx]

The under pressure bit sounds all too familiar!

 

Yeah, practice practice practice. When you've got it down pat. print out instructions ON PAPER (not left on the root folder of the server which has crashed) and TAPE THEM TO THE WALL not mixed in a pile of magazines next to the bin. Bitter experience is a harsh tutor.

Edited June 4, 2014 by jinnantonnixx

[PotNoodleTech]

Virtualise it and use Veeam backup and recovery to take a reverse incremental every hour, or even every 5 minutes if you need to (though that may be considered over the top).

 

That way, if you lose the HDDs, veeamrestore the entire server to new HDDs. If you lose the entire server, veeamrestore to another server, or even in a total emergency, a pc. Most modern pcs can run vmware, albeit slowly.