Posts Tagged ‘Business Continuity and Disaster Recovery’

SQL Clustering or SQL Mirroring

Written by Cornelius J. van Dyk on . Posted in Blog

One of my readers, Steve, posed a good question to me asking which option would be the preferred option between SQL Clustering and SQL Mirroring.

As with all technology decisions, there isn’t any one answer that fits all.  There is only a decision that’s the right decision for any given company with a given culture, given specific constraints at a precise moment in time.  When the culture, technology (hardware or software) or any constraints (like budgets, available skill sets etc.) changes, the original decision may not remain the right decision and the evaluation *should* be recalculated.  I say should because it’s not always practical to revisit technology decisions frequently.  That being said, how often have you posed the question “Why is it being done like this?” and received the answer “Because that’s how we’ve always done it.” or “Because we’ve done it that way for years.”?  Given the fast pace at which technology changes, every major technology decision should be accompanied by a clear timeline for Return on Investment (ROI).  I believe at the end of that timeline, plus 50%, the original decision should be re-evaluated.  If not enough has changed in the equation to warrant an altering of the original decision, then savings continue post ROI.  If however factors have shifted dramatically, then it should be time to re-evaluate.  Change is the only constant…

OK, OK.  I’ll get off my soap box now and reflect, based on my own personal experience, on Steve’s question.  It is a very good question indeed and I’m just going to reflect my abbreviated view here.

  1. Hardware cost.  Clustering has greater hardware cost due to the shared storage requirement of the cluster.  Clustering generally means SAN storage is involved.  If you don’t already have a SAN, or have plans and budget for a SAN, clustering will be an expensive option. 
    Mirror + 1
  2. Idle hardware.  I’ve never met a CFO who liked the idea of having a Extra hardware idle, giving no benefit to the organization, or should I say active benefit.  That’s usually the tunnel vision view of the bean counters.  With Mirroring, the passive node is just waiting for failover and while we as technologists know and understand that in itself, that is an important function, CFOs generally want to see more Active/Active use of their hardware.
    Cluster + 1
  3. Automatic failover.  Yes, both solutions provide automatic failover… sort of.  Clustering is true hardware level failover without requiring applications to do anything because the instance is virtualized.  Mirroring on the other hand requires application code to be using ADO.NET or SQL Native Client in order to do Client Redirect properly and create the automatic failover ability. 
    Cluster + 1
  4. Clustering doesn’t do load balancing or scaling.  You have to manually spread your SQL instances across the cluster nodes in order to load balance.  Peer-to-peer mirroring allows data access from any of the peers, resulting in better scaling. 
    Mirror + 1
  5. Update conflicts.  Mirroring doesn’t resolve update conflicts resulting from multi peer updates mentioned in #4, out of the box.  As a result, the benefit in scaling listed in #4 could be offset by having to deal with update conflicts. 
    Mirror – 1
  6. Expertise required.  Clustering is very complex and most DBAs avoid it if they can.  If you’re going to cluster, you have to have people with the skill set on hand to maintain it.  Mirroring on the other hand is a less complex topic that can be mastered by most DBAs.  Mirror + 1
    Mirror + 1
  7. Assuming per #3 that not all your applications are “Designed for Mirror”, security and update patching can become a problem when you’re running something like a 99.999% uptime SLA.  A cluster backup node can be patched and failover forced to the patched/backup node.  You can then patch the primary node and let the system run on the backup node until the next time patching needs to be done.  At that point in time, the patch process will fail the cluster back over to the primary node. 
    Cluster + 1
  8. Storage redundancy.  A Mirror has redundant storage.  A Cluster has shared storage.  True, SAN technology will have RAID redundancy built in, but at least a RAID5 redundancy design is used in all data storage, so it stands to reason that all members of a Mirror will have some form of redundancy anyway.  The question is how far apart your mirror members are.  It’s not likely that a Cluster will survive a complete Datacenter loss, but a Mirror with an off site member could. 
    Mirror + 1

As you can see, there are many pros and cons to each solution and what is the right solution for you, will depend on your given factors in the equation.

If you can afford both the hardware and the technical expertise involved in doing clustering, then it has been the tried and true method to use.  True, mirroring is making inroads on this title, but most DBAs will still opt for clustering if given the choice. From personal experience, I’ve encountered less problems with *PROPERLY* run SQL Clusters than with Mirrors.



Cheers
C




image

Series – Building your business on Microsoft technologies (Part 0 – Roadmap)

Written by Cornelius J. van Dyk on . Posted in Blog

Many people launch new businesses or expand small businesses to the point where IT starts to play a role.  It is at that precipice where the question about which software to use and build on becomes evident.  As happens in most companies, a software package that most closely does what is more urgently needed, is installed by someone and it starts to gain user traction.  This repeats over and over again until at some point, someone has to figure out how to untangle the spaghetti mess that resulted.

If only someone had planned the expansion and use of software beforehand, it would have saved tons of time for whoever ends up with that project.  And that… is where this series comes into play…

I’ve built my career over the last 10 years or so, on Microsoft technologies.  There’s always someone out there who’s done what you need, IF you understand what you need.  That’s what Enterprise Architects to best.  Understanding the business need and marrying that up with technology decisions that will help drive the business forward.  I intend this series to provide a road map for anyone who needs to build a business on technology that’ll allow less rework down the road.  I will cover all the topics as one may encounter them from the perspective of small (or even one man/woman) IT departments where budgets are tight (especially in the current economy) and getting high priced consultants isn’t always an option.  The most expensive thing that’s done in IT, is rework.  Doing the same thing over and over again because it wasn’t done properly the first time.

My vision for this series is to be a guide that most IT personnel could follow to deploy technologies within their company that’ll be properly positioned to support company growth in the future, requiring little to no rework at any point in time.  So without any further delay… here is my Roadmap for this series… Please note that I’ll be updating the Roadmap as time goes on and I write the corresponding articles and link to them.  It may be a good idea to Bookmark/Favorite this post for future reference.

  1. Installation – Windows Server 2008 R2.  Since Windows Server 2008 R2 is the latest and greatest server operating system from Microsoft, we’ll use it as the basis for all our servers.
  2. Configuration – Creating the Primary Domain Controller – Enabling the Active Directory Domain Services Role on Windows Server 2008 R2.  Once we have our first server with an operating system installed, it’s time to create our company domain.  We’ll be using Active Directory authentication for our environment.
  3. Business Continuity – Enabling and Testing the Windows Server Backup Feature on Windows Server 2008 R2.  No progress can or should be made until we’re sure we can recover from absolute disaster.  That means our server is completely dead and we have to restore onto new metal.  Backup and Restore functionality must be tested before we do anything else.
  4. Configuration – Enabling the Hyper-V Role on Windows Server 2008 R2.  Getting ready for virtualization is a key action here.  In a small business, there is seldom money for multiple servers so we have to stretch our resources to the max by employing virtualization.  Since running absolutely everything on one single server is not only NOT recommended as a Best Practice but also detrimental to scaling with business growth, virtualization is a perfect solution.  We will be using Microsoft’s Hyper-V technology to host all our servers on the same physical box.
  5. Installation – SQL Server 2008 R2 on Hyper-V.  Since absolutely everything we’ll do requires a SQL Server database, and since SQL Server 2008 R2 is Microsoft’s latest and greatest database server product, we’ll build on it.  Initially we’re not going to cluster or scale the SQL Server, but that will be the first point of scaling once volume and traffic increase.
  6. Business Continuity – Configuring and Testing Disaster Recovery for Hyper-V Servers.  Since our SQL Server was the first Hyper-V server we built, we have to test the Backup and Restore of our Hyper-V server before proceeding.
  7. Installation – Exchange Server 2010 on Hyper-V.  Now that we have a domain and a database server, we need email.  We’ll be building on Microsoft’s latest email server for that.
  8. Business Continuity – Testing Disaster Recovery for the Exchange Server 2010 server on Hyper-V.
  9. Installation – SharePoint Server 2010 on Hyper-V.  After establishing email for the company, we need to work on the web site and collaboration between employees.  We’ll use the latest version of SharePoint for that.
  10. Business Continuity – Testing Disaster Recovery for the SharePoint Server 2010 server on Hyper-V.
  11. Etc.

And so the list will grow and continue over time.  I am going to endeavor to post a new chapter in the series every week to two weeks so stay tuned.



Cheers
C




image