Enterprise Data Management in SQL Server 2005

This chapter is from the book

SQL Server 2005 Distilled

Learn More Buy

This chapter is from the book

This chapter is from the book 

SQL Server 2005 Distilled

Learn More Buy

High Availability for the Masses

Changes in the IT world have dictated new definitions of availability. Once upon a time there was an acceptable amount of planned downtime for system maintenance. With businesses being open 24/7, however, system maintenance windows are being squeezed. Moreover, unplanned downtime is catastrophic for organizations whose livelihoods depend on the availability of the IT infrastructure. This is nothing new. SQL Server 2005 delivers new technologies for availability and enhancements to already-common technology.

High-Availability Solutions

SQL Server gives you many approaches to creating high availability. There really isn’t a silver bullet. Looking at the landscape of customers, you can find that each application and scenario has required much creativity on the part of the IT teams. Microsoft has improved its core high-availability (HA) technology, called failover clustering. Microsoft introduces a new technique for log shipping: database mirroring. Log backup shipping is still supported, but little significant work has been done. Table 3-3 examines the three core technologies and shows how they affect overall availability. This table is not exhaustive, but it covers the meat of the issue. Remember that one of the goals of HA solutions is to mask hardware and software failure. Database mirroring and failover clustering are completely different in that way. A failover clustering solution requires two identical systems. Database mirroring runs on commodity hardware. Log backup shipping is a warm standby solution that really only provides a solution when geographic distance provides an added level of security.

Table 3-2 New Performance and Scalability Features

Merge Replication

Concurrent phase agents: Merge replication performance is increased by running the download phase and the upload phase of the merge agent in parallel. This is particularly beneficial when the merge agent is being run on a local area network (LAN) or is using the high-volume server-to-server profile.

New download-only articles: Article types provide a new performance optimization for customers who only have data that is changed only at the publisher.

New well-partitioned articles provide a new performance optimization for customers who make changes to only their subscribing partition. Because the data is well partitioned, internal steps of the merge reconciliation process can be skipped, providing a performance improvement during the upload phase.

Partition groups are a new performance optimization for filtered merge replication publications. When a subscriber synchronizes with a publisher, the publisher must evaluate the subscriber’s filters to determine which rows belong to that subscriber’s data set. Without merge partition groups, partition evaluation must be performed for each change uploaded to the publisher since the last time the Merge Agent ran for a specific subscriber. If the publisher is running on SQL Server 2005 and uses merge partition groups, each subscriber evaluates only the changes that meet the subscriber’s filter criteria. This can lead to significant performance gains when a publication has a large number of changes, subscribers, or articles in the publication. There are some new restrictions on filters when using merge partition groups. But if these restrictions are met, performance can be significantly improved.

Transaction Replication

Multiple distribution streams performance is improved by using multiple connections to retrieve and apply commands. This is particularly beneficial when the Distribution Agent has not run for a long period of time or if a large spike occurs in the number of commands to be replicated.

Snapshot Replication

Parallel snapshot preparation processes several articles while scripting schema or bulk-copying data within the Snapshot Agent. Administrators do not need to configure any special options to take advantage of this new functionality, because it is now embedded in the process of preparing snapshots. This feature allows SQL Server to prepare snapshots with greater speed and efficiency than was possible with earlier versions of SQL Server.

Table 3-3 Comparison of High-Availability Solutions

Additionally, you should consider the operating system when planning your HA solution. Table 3-4 shows the differences between Standard Edition and Enterprise Edition.

Table 3-4 -Differences Between Standard Edition and Enterprise Edition for HA Solutions

Failover Clustering

SQL Server 2005 failover clustering provides HA support for server-wide failure. With failover clustering, the operating system and SQL Server work together to provide failure protection by providing redundant hardware and an automated mechanism to move the database server to secondary hardware if the primary fails. Failover clustering supports up to eight nodes, depending on which edition of Microsoft Windows Server 2003 is running on the server.

SQL Server has extended the capabilities of failover clustering to SQL Server Analysis Services, Notification Services, and SQL Server Replication. With SQL Server 2000, SQL Server Agent and other job management and processing capabilities were not covered by failover clustering. Now, the technologies are clustering-aware. SQL Server failover clustering is now a more complete server-level redundancy solution, although clustering solutions are difficult to implement well and are expensive. For most customers, failover clustering is used only with their most valuable transactional databases.

Looking back at Table 3-3, you see a couple of failover clustering features that make it the best HA technology. First, failover clustering participates at the operating system level. This means that failover clustering is aware of operating system process failures. Some operating system-level processes may interfere with SQL Server or block it from answering a connection request. Moreover, failover clustering covers all the databases on the server that are participating in clustering. This means that the master is protected. Additionally, SQL Server 2005 has a hidden database called System Resource that is covered in clustering but that is inaccessible in database mirroring.

On the other hand, having two synchronized servers, with one doing "nothing," is very expensive and difficult to manage. As I’ve said before, if it’s mission-critical, it has to be failover-clustered. Other solutions will just prove to be a nightmare. Another thing worth noting is that all the "goods" (so to speak) are in Enterprise Edition, so the best use of that level of licensing is failover clustering.

Database Mirroring

SQL Server introduces a new technology set that allows you to create a hot standby database that maintains close synchronization with a primary database. Database mirroring lets you create hot standby databases that provide rapid failover with no loss of data saved by committed transactions. You can think of database mirroring as real-time log shipping. If the primary system fails, applications can reconnect to the database on the secondary server almost immediately, without waiting for recovery to finish.

Database mirroring capabilities are affected by which edition of SQL Server you are running. If you choose to use mirroring with Standard Edition, your failover options are limited, as shown in Table 3-5. That said, if you can live with manual failover, Standard Edition may suffice. Although Developer Edition has all the features of Enterprise Edition, it is not licensed for use in production environments. Any of the SQL Server editions can be a witness, including Express Edition.

Table 3-5 Data Mirroring Capabilities

Of these differences, the most notable is the lack of Database Snapshot, which doesn’t affect the deployment of mirroring. Also, parallel redo may affect large databases. Interestingly, Standard Edition doesn’t support high-performance mode. Microsoft may have left many of the mirroring features out of the Standard Edition simply to sell more Enterprise Editions because high-performance mode is preferable in a number of scenarios. I’d include any application that has high transaction volumes and applications wherein geographic distance may allow the business to have acceptable levels of loss/redo.

Here are some cases in which you might need to use database mirroring:

• An administrator needs to improve the availability of a SQL Server database that supports several critical applications. The administrator wants solutions that automatically fail over quickly, require no shared storage components or special controllers, and automatically resynch with a primary database after a failover. By configuring database mirroring with primary, secondary, and witness servers, the administrator can implement a system that automatically fails over in the event of a server failure or lost connection to the primary database.
• An administrator wants to protect non-mission-critical but important databases. Database mirroring provides a good solution for Notification Services, Reporting Services, and SQL Server Service Broker databases.
• If you design your application to use many smaller Service-Oriented Architecture (SOA) databases, using database mirroring provides a cost-effective means to daisy-chain the servers, providing a revolving series of failover database servers.

At this point it’s really important to understand the application and database implications of database mirroring. Database mirroring is a single-database technology. Failover clustering by design includes all the system databases, users, roles, and SQL Agent jobs, just to name a few, but database mirroring does not.

If your application uses linked servers to make cross-database calls to objects outside the mirror database, the application is not a good candidate for mirroring. When you are looking at database mirroring, spend a significant time understanding the database scalability scenarios. Although it’s true that mirroring doesn’t require exactly the same hardware, not using parity servers can cause possible issues with customer satisfaction and performance.

Database Mirroring Concepts and Confusion

You must master a number of new vocabulary words and concepts. At the most basic level, database mirroring requires only two "partners"—the principal and the mirror. But, depending on your needs, you may also choose to have a witness server. Moreover, you need to make a decision about safety of transactions versus performance. Let’s look at the basic concepts in depth.

A database mirroring system requires three servers that are running SQL Server. Each server has a specific architectural role:

• Principal. The server on which applications connect and where transactions are processed.
• Mirror. The target of transaction log records, which can be applied either synchronously or asynchronously. The mirror server exists in a state that does not allow direct read access to the data. As transaction log records are generated on the principal server, they are continuously replayed on the mirror server, which produces a state in which the mirror server is normally behind the principal server by only the time it takes to replay the log written in a single log write. This provides a duplicate of the data at a point in time.
• Witness. An arbiter within the architecture, providing the tie-breaking "vote" in determining which server is the principal and which is the mirror. Two servers in the architecture must agree in order for a server to be designated the principal and thus the target of all transactions. From a client application point of view, the failover from one server to the next is automatic and nearly instantaneous. The witness server is needed only when automatic failover is needed. For failover to happen automatically, the partners (the mirror or principal and the witness) must reach a quorum. When the principal fails to respond to the ping from the witness, the mirror is promoted to principal and takes over the job. However, the witness doesn’t e-mail the DBA that the principal is down!

One of the most important things about database mirroring, besides the extremely low latency provided during a failover from the principal to the mirror, is the capability to synchronize changes in both directions. If the principal goes offline and the application fails over to the mirror, the mirror becomes the principal server in the architecture. When the failed server comes back online, it is designated the mirror. Transaction log records from the principal are applied to it to bring it into synchronization with the state of the database at that point in time.

Databases participating in database mirroring have three modes of partnering. As an administrator, you can choose between High-Availability mode, High-Protection mode, and High-Performance mode. In High-Performance mode, transactional safety is turned off. Basically, this means that when set to "off," the principal does not wait for the mirror to acknowledge receipt or hardening of a transaction. In this case, the principal throws the transaction over the wire and continues. Depending on volume, this could mean a little latency or a lot. During testing, you should try to understand the risk level.

In High-Protection mode, transactional safety is ensured by having the principal wait for the mirror to acknowledge receipt of transactions. This is similar to High-Availability mode, with one exception. High- Availability mode provides the possibility of automatic failover, whereas High Protection does not. You can look at database mirroring as a synchronization technology too. By that, I mean for High-Availability and High-Protection modes, transactions are synchronized across the servers; in High-Performance mode, they aren’t necessarily synchronized.

When getting ready to use database mirroring, you have some important considerations:

• The principal database must use the FULL recovery backup model. This means that the principal should be doing a lot of bulk insert work, because the log files resulting from bulk operations aren’t shared with the mirror. Why? Too much bandwidth and log file traffic would keep mirroring from working well.
• The mirror database must be initialized from a current backup of the principal database using the NORECOVERY mode, followed by restoring the transaction logs sequentially from the principal’s transaction log. This process ensures data consistency and synchronization of the initial term of the relationship. Without this, the mirror catches up too much.
• The database names for the mirroring partners must be the same.
• You cannot directly access the mirror during the partnership session. Use a Database Snapshot if you need to access the database.

Database Mirroring States at a Glance

Database states are kept during the database mirroring session. A session begins when two servers are partnered. The state can be found in the sys.database mirroring catalog view. You can review all the state numbers and their descriptions by reading about the topic in Books Online. Moreover, several server concepts are noteworthy. These phrases help you understand how the session is doing:

• Exposed is when the principal is processing transactions but no log data is being sent to the mirror. When this state occurs, mirroring doesn’t work. Essentially, you are not in a protected state from a transactional point of view.
• Cannot serve the database is when the principal doesn’t allow any user to connect to the database or any transactions to be processed.
• Isolated is when a server cannot contact any of the other servers in the mirroring session, and they cannot contact it. This state is really a lack of communication.
• Synchronized is when safety is set to full, the records are successfully passed to the mirror, and the mirror hardens the logs.
• Synchronizing occurs when safety is set to OFF and the logs are always in a catch-up state.
• Suspended is the state that occurs when the session is broken. When the session is paused or the mirror has redo errors, the session state is set to SUSPENDED.

For a complete and thorough discussion of database mirroring and states, see Books Online.

Automatic Client-Side Redirect

One of the key ingredients of database mirroring success is the use of automatic client redirect. This is a new technique for caching the location of the principal and mirroring for client connections. You can use client redirect with only the new data access protocols: ADO.NET 2.0 and SQL Native Client. It’s relatively simple to use: In the connection string, you provide the name or IP address of the principal and mirror databases. When a client connects, the client access code checks to determine which machine is available and then connects appropriately. (Here’s an example of a connection string: "server=Partner_A; failover partner=Partner_B; database=AdventureWorks".) For more information about automatic client redirect, see SQL Server Books online and search for the topic "Client Connections to a Mirrored Database."

So that using database mirroring is extremely easy, set up a mirroring system via the Database Properties dialog box. (See Chapter 2, "What Everyone Should Know About Security," for security issues related to database mirroring.)

Log Backup Shipping

Log backup shipping has an interesting history. It was possible to do log shipping with SQL Server 2000. Originally Microsoft didn’t support it, but customer demands for a warm standby technology pushed the technology into a supported mode.

In reality, log shipping is really a backup log-shipping technology. Log shipping allows you to automatically send transaction log backups from one database (known as the primary database) to a secondary database on another server (known as the secondary server). At the secondary server, these transaction log backups are restored to the secondary database, keeping it closely synchronized with the primary database. An optional third server, known as the monitor server, records the history and status of backup-and-restore operations and optionally raises alerts if these operations fail to occur as scheduled.

In comparison to database mirroring, which is a real-time log- shipping technology, backup log shipping is an adequate solution for non-mission-critical applications that can stand some data loss. Additionally, log-shipping servers can be located a great distance apart, and they provide a solution in situations where Mother Nature may cause a headache. With SQL Server 2005, little work was done to increase this feature’s performance or reliability. In SQL Server 2005, you cannot upgrade log shipping from SQL Server 2000. Instead, you have to migrate the log-shipping databases by applying the upgrade to each server—the primary and the secondary—while each is offline. (You can read about how to accomplish this by visiting SQL Server 2005 Books Online and searching for the topic "Upgrading a SQL Server 2000 Log Shipping Configuration.") You can also set up log shipping by opening the Database Properties dialog and, in the Transactional Log Shipping dialog, provide the needed information.