Always-On in SQL Track May 26th, 2007

Vinod Kumar

hmmm … By now you must be wondering why is Vinod not talking about SQL in TechMela. You bet, I kept the best for the last :). Hehehe … As guessed, I would be owning the “DB & BI” track too. Super excited and most importantly, I managed to get some rockstar cool speakers to deliver too. So I am excited. As I have done for the other posts, here in this post I will concentrate on one aspect session on the SQL Track. Let me start with the enterprise credible High Availability session + Always On session.

In this topic we can talk on various HA options inside of SQL Server. The common ones being:

  1. Failover clustering
  2. Database mirroring
  3. Log shipping
  4. Replication

Let me quickly talk on each of them.

Failover clustering: Failover clustering provides high-availability support for an entire instance of SQL Server. A failover cluster is a combination of one or more nodes, or servers, with two or more shared disks. An application can connect to the failover cluster instance by referencing the failover cluster instance name. The application does not have to know which node hosts the failover cluster instance. However, a failover cluster does not protect against disk failure.

Database mirroring: New with SQL Server 2005 SP1, DM is primarily a software solution to increase database availability by supporting almost instantaneous failover . It can be thought as a mixture of replication and log shipping: all transactions are moved (log shipping) on a transaction level (replication) to a mirrored copy of the database.

Each database mirroring configuration involves a principal server that contains the principal database, and a mirror server that contains the mirror database.
 You can also have an optional witness server in the High-Availability mode for aiding in automatic fail over question. So we have 3 modes in DM as:

High Availability: This operating mode option allows you to synchronize transaction writes on both servers and enables automated failover . For this option to work, you must also have a witness server. Even a SQLExpress Edition can be used for Witness and a single witness can be used for monitor multiple DM servers.

High Protection: This option allows you to synchronize transaction writes on both servers, but failover is manual.

High Performance: This option does not care if the writes are synchronized on both servers, therefore offering some performance gains.

To support automatic failover, the MDAC provides a new parameter on the connection string that allows for automatic redirection when a failure occurs. A typical connection string would look like:

“Server=MyServerA; Failover Partner=OtherServerB; Database=TechMela; Integrated Security=True;”

There is more to talk here, but I will write them as a separate article on my website (I promise :)).

Log shipping: Old and a much proven method in the market as far as SQL Server is concerned. Like database mirroring, log shipping operates at the database level. Log shipping can be used to maintain one or more warm standby databases, referred to as secondary databases, for a corresponding production database that is referred to as the primary database. Each secondary database is created by restoring a database backup of the primary database with no recovery, or with standby.

Log shipping has its own characteristics that makes it different from DM like it involves a user-modifiable delay between when the primary server creates a log backup of the primary database and when the secondary server restores the log backup. Before a failover can occur, a secondary database must be brought fully up-to-date by manually applying any unrestored log backups.

Although similar in concept, asynchronous database mirroring and log shipping have key differences.

  • Supports multiple secondary databases on multiple server instances for a single primary database.
  • Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.

An advantage of database mirroring over log shipping is that high-safety mode is a no data loss configuration that is supported as a simple failover strategy and moreover immediate transparent client redirection.

Replication: Most commonly used since the SQL Server 2000 days. Replication uses a publisher-subscriber model, allowing a primary server, referred to as the Publisher, to distribute data to one or more secondary servers, or Subscribers. Replication allows real-time availability and scalability across these servers. It supports filtering to provide a subset of data at Subscribers, and also allows partitioned updates. Subscribers are online and available for reporting or other functions, without query recovery. SQL Server offers three types of replication: snapshot, transactional, and merge. Transactional replication provides the lowest latency and is most commonly used for high availability. Something new that was introduced with SQL 2005 is the non-SQL Server subscribers, Oracle publishing, and peer-to-peer transactional replication.

Peer-to-peer transactional replication is designed for applications that might read or modify the data at any of the databases participating in replication. All nodes in a peer-to-peer topology are peers: each node publishes and subscribes to the same schema and data. Changes (inserts, updates, and deletes) can be made at all nodes. Replication recognizes when a change has been applied to a given node, preventing changes from cycling through the nodes more than one time.

…… I can keep on going. But let me take a stop and not take away the thunder we will have in the session at TechMela. You will see them in action …

In general this is a cool topic and guess what, we will have almost 3/4th of the time for demo’s in the session talking about Always-on Technologies. So that is the plan … So dont miss on this session and the event in less than 18 days to go :) … Countdown has begun …

More on the SQL track coming your way soon …

Share this article

Tags: , ,

This entry was posted on Saturday, May 26th, 2007 at 17:14 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply