Solved

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

Posted on 2013-01-14
2
602 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

724 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