• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

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

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
MattyS82
Asked:
MattyS82
1 Solution
 
Ryan McCauleyCommented:
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
 
MattyS82Author Commented:
Clear, concise and a great help especially for those who are new to SQL
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now