Blog

If your boss wants you to make your SQL Server more reliable, there’s a lot of confusing options. I’ll walk you through the pros and cons of each method and show you how they can help make queries go faster too. We’ll finish with a list of books and blogs I’d recommend for each option.

This 30-minute session is for production DBAs who haven’t implemented clustering, replication, or log shipping before, and aren’t sure where to start.

HA-and-DR-worksheetChoosing the Right Technology with Our Worksheet

Should you use a cluster, replication, or AlwaysOn Availability Groups? How often should you do backups? Figure it all out with our HA/DR planning PDF worksheet.

Clustering Resources

Clustering had a bad reputation for being difficult to configure and manage, but Microsoft’s made dramatic improvements in recent versions of both Windows and SQL Server.  Whether you’re just getting started with clustering for the first time, or you still think clusters require heartbeat networks, your first resource should be Pro SQL Server 2008 Clustering by Allan Hirt (@SQLHA).

Database Mirroring Resources

For the first couple of years that I used Database Mirroring, I thought it was brain-dead-simple: just Next-Next-Next your way through a wizard, and you’re in business.  I couldn’t imagine how anyone could write an entire book about mirroring.  That, however, was before I found out about Pro SQL Server 2008 Mirroring, which I’d definitely recommend.  The book’s coauthors are Robert Davis (@SQLSoldier) and Ken Simmons (@KenSimmons).

For some quick free learning, check out Robert’s posts tagged with database mirroring.

Replication Resources

Replication’s been out for several SQL Server versions now, and it’s slowly changed with each version.  As a result, there’s a ton of good free info out there, but it’s not all up-to-date.  My favorite place to start is the Books Online topic for Designing and Implementing Replication, and then move on to:

Log Shipping Resources

Log shipping is more of a technique than a technology: it just means restoring your backups continuously on another server.  When database administrators first started doing this, they did it with their own scripts.  Third party backup compression tools like LiteSpeed started offering cool easy-to-manage user interfaces for it too.

These days, the log shipping and backup compression built into SQL Server 2008 R2 Standard Edition put log shipping within everybody’s reach.  The easiest place to get started is with the Books Online section on Log Shipping.  If you use a third party backup compression tool, poke around in their documentation too.

SAN/VM Replication Resources

These solutions are all vendor-specific, and unfortunately a lot of vendors lock their documentation behind support sites that require logins. However, there’s one very good place to start: Mike Laverick’s free ebook Administering VMware Site Recovery Manager 4.  This isn’t the most current information available, but don’t worry about that – just read Chapter 1 for an excellent vendor-neutral introduction to disaster recovery planning using SAN and VMware replication.

If your SAN happens to be covered in his book, skim through that chapter too just to get an idea of the difficulty level for these solutions.  These don’t have the ease-of-use of, say, SQL Server log shipping, but it’s one pane of glass to manage all of your disaster recovery needs regardless of the operating system and application.

For more learnin’, check out Veeam’s free whitepaper section.  Veeam is a vendor that sells their own virtualization backup and recovery software, and I’m not affiliated with ‘em or getting paid to say this, but I’m a big fan of vendors that give away free education to the community.

AlwaysOn Availability Groups Resources

This is a brand new feature of SQL Server 2012, so we don’t have a lot of good community resources out there yet.  There’s a few screenshot walkthroughs, but they don’t really tell you anything you can’t figure out in five minutes of playing around.  For now, the place to go is the Books Online section on Availability Enhancements.

No matter how you do HA or DR, you need backups.

There is no more critical task for a DBA than to back up the business data – except perhaps restoring it to ensure validity. A disaster – in many forms – can strike at any time. SQL Server offers many backup and restore options, to help you meet business requirements.

In Jes Schultz Borland’s 2-hour video class, you’ll learn about:

  • Simple, Full, and Bulk logged recovery models
  • Full, differential, and log backups
  • Restore options
  • Best practices

It always helps when you see something in action, so she’ll demonstrate

  • Full backups
  • Differential backups
  • Log backups
  • Restores

Buy it now.

↑ Back to top
  1. I love this video. I just sent a link to this to a client, and couldn’t help watching it again myself.

    Sorry for all the pro-replication propaganda at the end. I’m a replication apologist at times!

  2. Great as always, Brent!

    Wanted to get your take on combing Failover cluster instance ( FCI ) with availability groups. I know it does not allow automatic failover while AGs provides additional redundency due to non-shared storage.

    I think most of the benefits of FCI are provided by AGs in a simpler environment plus it combines HA/DR capabilities in a single cluster.

    Does it ( combing FCI + AGs ) not unneccessrily complicate the environment to maintain?

    Thanks in advance!

    • Amir – honestly, EITHER solution (FCIs or AGs) alone can be more complex than a team can maintain. There’s plenty of moving parts in both solutions.

      • Thats correct Brent. But if one has to choose between the two keeping the new features available in 2012 in mind, which one would you prefer.

        I see that most of the benefits of FCI are available in AGs, but it also provides additional features e.g. RO replicas and seems simpler to maintain.

        The only -ve is the server level object synching but that can be achieved through manual processes.

        Let me know your thoughts…

        Thanks a bunch!!!

        • Amir – there’s no one answer for all situations. In order to get to the right technology, first I learn about the business that I’m working with. Of course, this is beyond the scope of a blog comment, but if you’d like to talk about your business needs, shoot us an email at Help@BrentOzar.com and we can set up a consulting call.

  3. In the Clustering segment, can you briefly explain what the Virtual server versus the SQLNode1 is? My guess is that the SQLNodes have the SQL server instances/services intact but that is as far as I get.

  4. Great video! As an Informix DBA crossing over to SQL, this was really helpful.

    Questions:
    In any of the HA / DR options, when you talk about having servers in different geographical locations (different timezones), what do we need to consider?
    For example, if the primary is on an O/S in Chicago timezone, and Sqlserver in Chicago timezone, and secondary O/S and Sqlserver in New York timezone, does the New York (being secondary) need to be set to Chicago timezone at O/S or Sqlserver level? if not, what types of complications can you envision with timestamped data (current_timestamp)?
    Even with log shipping – what if the servers (with timestamped data) are in different timezones? Do the O/S’s and Sqlservers need to be set to the same timezone of the primary regardless of what physical timezone the secondaries are in?

    thanks for your advice.

    • Hi, Norma. This is a little beyond the scope of something I can address quickly in a blog comment. There’s a lot of things to consider when storing dates in databases – you could use UTC time on the servers and store UTC in the database, for example, or you might use local time on the servers and store UTC in the database.

  5. Hi Brent,

    I saw your video it’s was very nice video and really easy to understand.

    I have been dba for a couple years I still feeling like a begginer when I saw this kind of videos and post, but I’m doing my best, I have some cuestions about designing the best way to perform a solutions for high availabiltiy, I know depends of the bussiness and the resources you have.

    In my Job we have an 3rd party tool named Informatica Power Shell this tool make replication with dts from the diferent sql server to the central sql server.

    Yep and my biggest problem it’s we have everything and SQL 2000 and I have troubles with people about making the change to new versions.

    What do you think about Informatica like HIgh Availabilty tool or it just better with only replication.

    Thank you.

    • Rafael – we haven’t used Informatica Power Shell (and I haven’t heard it and can’t find it in Google) so I wouldn’t be able to give you good guidance on it. Sorry about that!

  6. I really sorry I made a Mistake it’s Informatica Power Exchange, I add the official link http://www.informatica.com/us/products/enterprise-data-integration/powerexchange/
    I never had heard of this tool either befor I Arrived this Job, It’s a tool that works for a lot of things here. thank you.

css.php