Geeks With Blogs
Gary Pronych 1 Part .NET Developer, 2 Parts Personal Developer; 100% Canadian

SQL server high availability comes in 2 basic categories.

1) Clustering using a SAN drive and multiple physical servers attached to the disk storage
2) Stand alone equipment acting in parallel

A SAN configuration is an expensive solution; typically a disk storage array needs to be purchased with high speed communication channels (typically fibre).
Therefore #2 is often the recommended configuration for smaller shops and less critical databases.
Awareness of this topic is important for developers to be able 'talk the talk' and provide recommendations so I will expand on this topic today.

First, let me define some of the terminology used so we are all on the same page

Redundant - Not to be confused with high availability (see redundant); meaning having more than necessary (ie. Redundant power supplies mean there are 2 power supplies or more).
High Available - Meaning available often, unaffected by failures and downtime.
High Performance Mode (asynchronous) - Transactions are commit without waiting for the mirrored servers to commit, which maximizes performance.
High Safety Mode (synchronous) - A committed transaction is committed on all systems in replication before the next transaction is processed, but at the risk of increased transaction latency (I have tested up to a 30% performance hit).
Replication - Making a copy
Failover - The capability of switching to another system
Quorum - A group of servers required; in this topic, a Principal, Partner and Witness are required
Principal - The server or database instance that is processing transactions
Partner - The server that is being replicated to and is available for failover
Witness - Required to decide if a failover is required (participates in a quorum 'vote')
Split Brain - Principal and Partner databases become out of sync
Load Balancing - Share the transaction load between multiple servers
Automatic Recovery - System is able to process any missed transactions during failover / downtime

 

Principal and Partner

Pros: High Availability, both servers could be available for queries (great for Business Intelligence(BI))
Cons: Manual Failover* (see programming considerations), manual recovery is required

Description:
2 servers are required; you can replicate to as many servers as you wish.
I know about environments replicating to 10+ environments to provide Slowly Changing Dimensions (SCD)  to remote sites; I will cover my recommendations on using the ADO.NET Sync Services in a future post and a community presentation.
In this diagram, YTSdb1 is the principal server and is replicating to YTSdb2.

This model suffers from split brain, as it is can allow transactions written directly to the partner.
Recovery from split brain (because of failures, downtime, etc) is a manual process as you can see the information flow is one-way; from the principal to the partner.

*Note: You cannot query the Partner database if you are using the Mirror configuration as it is unavailable. Only when using replication methods such as log shipping are you able to access the replicated tables.
Log Shipping allows you to replicate to many databases as the Partner database consumes transaction log files.

Peer-to-Peer (P2P) Replication

Pros: Allows load balancing (manual), Automatic Recovery
Cons: Asynchronous transactions, Manual failover* (see programming considerations), not the next P2P Kazaa

Description:
2 or more servers act in asynchronous replication, each database is known as a node.
In a non-clustered environment, this is the only method that can provide load balancing. Load balancing would be configured by the developer utilizing different servers in their connection strings.
Transactions are asynchronous; if you are using this method for load balancing there is a risk that not all the servers have been replicated to all servers.
Recovery is automatic; once the failed server comes back online, the missed transactions are replicated.

 

Principal, Partner and Witness Quorum

Pros: Can run in Safety or Performance Mode, Automatic Failover* (see programming considerations), Automatic Recovery
Cons: Witness is required but I can be any version SQL, Partner is not available for queries

Description:
This is the most powerful configuration for High Availability.
YTSdb1 is the principal in this diagram, if it fails, a vote is taken place to see if the Partner should be promoted. Once YTSdb1 is back online, it will be given all the missed transactions and it will assume the role of Partner. If YTSdb2 then fails, then YTSdb1 will then be restore back to the Principal.

 

Programming Considerations

ADO.NET 2.0+ (Visual Studio 2005+) supports a Failover Partner configuration in the connection string as shown below.

Data Source=SQLA\INST1;Failover Partner=SQLB\INST1;Initial Catalog=DBMTest;Integrated Security=True

This attribute allows both manual and automatic failover since the application then becomes aware of the principal / partner configuration.

Automatic failover will happen when connected to a Quorum configuration.
If even there is no Partner set in the configuration file, the ADO.NET provider will be sent the partner connection information and cache it. If a failure happens during application processing, the application will then failover to the new principal once the failover has been completed. My tests have shown a 20 second delay in failover; this may depend on your timeout configuration.
You can prevent any transactions from being lost by implementing retry logic into your applications, otherwise there is a risk of losing 1 transaction per client.

 

Summary:

All the above I have tested so these are my notes on my research and tests.
I completed these tests using SQL Server 2008; I would suspect most of these configurations can apply to SQL 2005.

I can certainly go into more depth and include additional strategies, but the purpose of the post is for awareness of these methods.

Hope this help you in your SQL Server configurations,
Gary Pronych

Posted on Friday, January 9, 2009 10:20 AM .NET , IT Pro , Architecture | Back to top


Comments on this post: Affordable SQL Server High Availability

# re: Affordable SQL Server High Availability
Requesting Gravatar...
Good point, let me clarify.
My picture says 'Mirror Configuration', when in a mirror configuration, you cannot query the Partner server it is unavailable.
When using Log Shipping (which also works asynchronous) you can query the database as it is available.

Yes, this configuration will use twice the disks compared to a SAN.
SAS disks that are typically used in standalone servers, are inexpensive.

There are significant costs to purchase a SAN and the fibre channels to connect your servers to the SAN.
The premise of this article is 'affordable'.
Thanks for you feedback.
Left by Gary Pronych on Jan 09, 2009 3:11 PM

Your comment:
 (will show your gravatar)


Copyright © Gary Pronych | Powered by: GeeksWithBlogs.net