[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2013-01-14
2
Medium Priority
?
606 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 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach 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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

650 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