Solved

SQL 2008 Full quorum automatic failover and log shipping server to run SQL Queries

Posted on 2013-01-14
2
583 Views
Last Modified: 2013-01-20
Hi all,

I am looking to obtain some advice on a SQL configuration. We are looking to implement a full quorum configuration with high safety for automatic failover.

What I would like to know is does this mean that in the event that if the Database on the principal instance is unavailable will it automatically be switched over the mirrored instance or does switching only happen in the event that the whole server is unavailable? Basically what I am asking here is, is automatic failover at a database level or is it on a whole server level?

Also, is it possible to have a full quorum with a witness in high safety mode and include a logging server as well? The logging server would be used to run queries against to ensure the principal server does not get overloaded. Not sure if this is possible and I would like some guidance on this, please.  Or would it better to run these queries against the mirror server, again not sure if this is possible as it is my understanding that the mirrored instance is in a constant recovery mode.

I have included a visio diagram that depicts the setup that I am referring too.

Thanks for all your help with this.
Drawing1.vsd
0
Comment
Question by:MattyS82
2 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 38783799
You have a couple of choices here. The layout you've shown here would work, though you can't do replication from a mirror server in traditional mirroring configuration. You can set up your mirror in high-safety mode, where the transaction must be completed in both places before the client is notified, but I'm not sure how log shipping would work in that configuration - since you'd have to ship logs from your active database, that could be either server at any given time.

As far as the mirroring failover, it happens when connectivity to a single database is lost, and failover is handled a single database at a time. If the whole server fails, all mirrored databases will fail over, but independently. Since you're doing high-safety, they should all be consistent to each other (in a high-performance scenario, it's possible that one database could be 5 seconds behind and another could be minutes behind, in certain scenarios or under load), but consistency between databases isn't guaranteed.

However, you might be a great candidate for SQL Server 2012's "AlwaysOn" mirroring, which marries up traditional mirroring with database grouping and a single point of client contact managed by windows clustering. If you go with AlwaysOn, you'd have the following advantages:

 - Readable secondaries - you can expose your standby server to clients for read-only access. This can serve to balance the load, perform log shipping to a third location, and perform backups that don't impact production.
 - Fail groups of databases together - in traditional mirroring, you set it up for each database and access is controlled on a by-database level. In AlwaysOn, you set up "Availability Groups" that can include multiple databases, and they fail over from principle to mirror as a group. This way, you can ensure consistency between the databases.
 - Single point of client contact (windows clustering) - In traditional mirroing, the client connection has to be aware of the mirror configuration and know how to use it properly (like the SQL Native Client), and the connection string needs to list the mirror server explicitly. In AlwaysOn, there's a single point of contact presented by Windows Clustering - an IP address and port that's always listening, owned by any one of the database servers at any given time. That way, you can add new servers to the availability group without changing any connection strings or client configurations, and you can take advantage of the mirror without using the native client (something like Sharepoint uses the OLEDB client, which isn't mirror-aware).

I hope that addresses your questions - I'd encourage you to check out AlwaysOn (assuming you can deploy SQL 2012 as an option).
0
 
LVL 1

Author Closing Comment

by:MattyS82
ID: 38799119
Clear, concise and a great help especially for those who are new to SQL
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the fileā€¦

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now