+ Post New Thread
Results 1 to 14 of 14
Windows Server 2008 R2 Thread, Create redundancy for our SQL server? in Technical; We had a nasty fright yesterday when we rebooted our SQL server with our finance and sims on it! So ...
  1. #1
    reggiep's Avatar
    Join Date
    Apr 2008
    Location
    In the vast area of space and time
    Posts
    1,548
    Thank Post
    517
    Thanked 56 Times in 50 Posts
    Rep Power
    29

    Create redundancy for our SQL server?

    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

  2. #2

    Join Date
    Oct 2005
    Posts
    829
    Thank Post
    51
    Thanked 111 Times in 101 Posts
    Rep Power
    64
    Sounds like you want some sort of fault tolerant cluster.

    Is it a physical server?

  3. #3

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,976
    Thank Post
    113
    Thanked 495 Times in 337 Posts
    Blog Entries
    2
    Rep Power
    284
    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
    Last edited by jinnantonnixx; 4th June 2014 at 09:08 AM.

  4. 2 Thanks to jinnantonnixx:

    pantscat (4th June 2014), reggiep (4th June 2014)

  5. #4
    zag
    zag is online now
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,768
    Thank Post
    899
    Thanked 418 Times in 351 Posts
    Blog Entries
    12
    Rep Power
    87
    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.

  6. #5

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,976
    Thank Post
    113
    Thanked 495 Times in 337 Posts
    Blog Entries
    2
    Rep Power
    284
    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.
    Last edited by jinnantonnixx; 4th June 2014 at 09:40 AM.

  7. #6
    reggiep's Avatar
    Join Date
    Apr 2008
    Location
    In the vast area of space and time
    Posts
    1,548
    Thank Post
    517
    Thanked 56 Times in 50 Posts
    Rep Power
    29
    @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!

  8. #7

    Join Date
    Oct 2005
    Posts
    829
    Thank Post
    51
    Thanked 111 Times in 101 Posts
    Rep Power
    64
    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.

  9. #8

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,976
    Thank Post
    113
    Thanked 495 Times in 337 Posts
    Blog Entries
    2
    Rep Power
    284
    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.
    Last edited by jinnantonnixx; 4th June 2014 at 11:40 AM.

  10. #9
    reggiep's Avatar
    Join Date
    Apr 2008
    Location
    In the vast area of space and time
    Posts
    1,548
    Thank Post
    517
    Thanked 56 Times in 50 Posts
    Rep Power
    29
    Quote Originally Posted by jinnantonnixx View Post
    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?

  11. #10

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,976
    Thank Post
    113
    Thanked 495 Times in 337 Posts
    Blog Entries
    2
    Rep Power
    284
    Quote Originally Posted by reggiep View Post
    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.

  12. #11

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,810
    Thank Post
    272
    Thanked 1,135 Times in 1,031 Posts
    Rep Power
    349
    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).

  13. #12
    reggiep's Avatar
    Join Date
    Apr 2008
    Location
    In the vast area of space and time
    Posts
    1,548
    Thank Post
    517
    Thanked 56 Times in 50 Posts
    Rep Power
    29
    Quote Originally Posted by jinnantonnixx View Post
    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!

  14. #13

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,976
    Thank Post
    113
    Thanked 495 Times in 337 Posts
    Blog Entries
    2
    Rep Power
    284
    Quote Originally Posted by reggiep View Post
    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.
    Last edited by jinnantonnixx; 4th June 2014 at 12:44 PM.

  15. Thanks to jinnantonnixx from:

    reggiep (4th June 2014)

  16. #14
    AButters's Avatar
    Join Date
    Feb 2012
    Location
    Wales
    Posts
    473
    Thank Post
    142
    Thanked 107 Times in 82 Posts
    Rep Power
    42
    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.

  17. Thanks to AButters from:

    reggiep (4th June 2014)

SHARE:
+ Post New Thread

Similar Threads

  1. CPU configuration for our backup server
    By superatticman in forum Hardware
    Replies: 4
    Last Post: 22nd May 2013, 03:57 PM
  2. How many CALS do we need for our Terminal Server?
    By farquea in forum Licensing Questions
    Replies: 38
    Last Post: 13th July 2010, 01:29 PM
  3. Replies: 1
    Last Post: 11th February 2010, 10:58 AM
  4. ESXi disk setup for SQL server VMs - advice sought
    By fox1977 in forum Thin Client and Virtual Machines
    Replies: 2
    Last Post: 29th January 2009, 11:34 AM
  5. Not enough space for SQL server 2005 install
    By zag in forum MIS Systems
    Replies: 4
    Last Post: 10th October 2007, 08:02 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •