Log Shipping
You can think about log shipping as the opposite of failover clustering, at least from a technology standpoint. It's the low-tech, low-cost way to provide database redundancy, but without any automatic failover. You might be tempted to view log shipping as simply a slow method of database mirroring, but the underlying technologies are completely different. In log shipping, you automate the SQL Server process of backing up transaction logs from a primary server and restoring them to a secondary server. (Database mirroring uses a special endpoint transmission technology, and no intermediate files are involved.)
In SQL Server 2005, you'll find several important changes in log shipping. First, the supported version of log shipping is now available in all editions of SQL Server that support SQL Server Agent, which means in all editions except SQL Server Express. Additionally, SQL Server 2005 log shipping is exclusively stored procedure? and SQL Server Agent?based and doesn't use database maintenance plans. Finally, although a monitor server was required for SQL Server 2000 log shipping, that server is optional in SQL Server 2005.
All of these changes are clearly improvements, but they come at a cost. SQL Server 2000 log shipping can't be directly upgraded to SQL Server 2005, because maintenance plans are no longer used. Instead, you must manually reestablish log shipping on an upgraded set of servers.
SQL Server 2005 log shipping doesn't support automatic failover. If the primary log shipping server fails, you must recover the secondary server yourself, either manually or based on your own custom-coded failure detection.You can set up a system to make role reversals easy, so that controlled failover and failback, although still manual, involve only a few steps.
Like database mirroring, log shipping provides database redundancy only, not server redundancy. So just as with database mirroring, you must ensure that the secondary server is kept in sync with the primary for such matters as logins, permissions, and SQL Server Agent jobs. On the other hand, unlike database mirroring, you can ship logs to multiple secondary servers.
Replication
Replication, which has been available since SQL Server 6.0, is one of the oldest highavailability features in SQL Server.Although providing high availability isn't replication's primary purpose, in many cases, it does so successfully.
Merge replication. Microsoft designed merge replication for use by occasionally connected computers (e.g., laptops), but you can use it between database servers to support high availability. On systems with low to moderate activity, merge replication can provide redundant databases—although not with automatic failover. Merge replication offers two key benefits: It lets you update the same data on both the publisher and a subscriber, and it lets you manage any conflicts automatically. Also, merge replication offers the unique capability of automatic synchronization: When either a publisher or subscriber goes offline or is disconnected, each can work autonomously. When they're reconnected or brought back online, they automatically synchronize with each other. Merge replication can't, however, guarantee transactional consistency when multisite updates of the same data are involved.
Transactional replication. You often see transactional replication used for high availability because its performance can be much better than that of merge replication and because it can guarantee transactional consistency between the publisher and subscribers. Perhaps the most common highavailability scenario for transactional replication occurs when you copy data from one database, the publisher, to one or more subscribers through a distribution server. The subscribers are treated as read-only, and updates occur only on the publisher. If the publisher fails, one of the subscribers can become a read/write server and accept data updates—and even become a publisher to the other subscribers.
Peer-to-peer transactional replication. SQL Server 2005 provides a new form of transactional replication,peer-to-peer,in which each server is both a publisher and a subscriber to the same data set. The replication is essentially two-way, similar to merge replication. Unlike merge replication, however, peer-to-peer transactional replication doesn't provide automatic conflict management. Instead, you must ensure either that updates occur to just one database or that the updates are partitioned so that the same data isn't updated at the same (or nearly the same) time.
Like log shipping, replication is supported in all editions of SQL Server 2005 that support the SQL Agent service, so only SQL Server Express is excluded. If you want to ensure that failover to a subscriber will occur, you need to manually intervene or write custom code to detect a failure and perform the failover procedures. Also, just as in log shipping, you must ensure that the servers are configured appropriately to support failover.
AVAILABILITY IN A HIGHLY CONCURRENT ENVIRONMENT
If another user has locked the data you need, it doesn't matter how sophisticated your failover solutions are, your data is still unavailable. SQL Server 2005 provides a new technology called row-level versioning (RLV) to reduce the effect of locking on data availability. The most far-reaching feature that uses RLV is SQL Server 2005's new snapshot isolation, which Kalen Delaney describes in her article "Keeping Concurrent,-October 2005," InstantDoc ID 47389.
Snapshot isolation. You can enable snapshot isolation as a database setting in all editions of SQL Server 2005. Snapshot isolation lets SQL Server keep track of previous versions of all modified data. Therefore, even though the data is still locked while it's being modified, other transactions can access a previous committed version of the locked data. Data is more available. However, as always, you pay a price.
The older versions of changed rows are stored in the tempdb database, and for systems that have a large amount of modified data, tempdb space requirements can grow dramatically. On any system that employs snapshot isolation, a DBA must carefully monitor the amount of row versioning that occurs and watch the size limits for the tempdb database. You see another cost of using row versioning when many changes are made to the same rows. SQL Server will maintain all changes to any row in a linked list as long as any open transaction or running statement might need the older versions. Additional changes to the same row will cause a new row version to be linked to the front of the list. A query that needs to select older versions of data might need to traverse an increasingly longer version chain, which means that a SELECT statement can take a long time to execute, even though the data is technically available.The data modification operations will also be slower because previous versions of the rows must be added to the linked list. For details about observing the versioned rows, see Kalen Delaney's "RLV with a View," February 2006, InstantDoc ID 48721.
Online index creation. SQL Server 2005's RLV technology also supports another high-availability feature, online index creation, which is available only in the Enterprise and Developer editions.Typically, building or rebuilding an index makes the index unavailable. If you build or rebuild a nonclustered index, no modifications are permitted on the base table because the nonclustered index must be maintained with every data modification. If you rebuild the clustered index, which contains the data itself, the entire table is usually unavailable during the process.
With the new online index creation feature, the table and its indexes are fully available while indexes are being built or rebuilt. You must specifically request online index creation by using either the CREATE INDEX or the ALTER INDEX statement. For example, executing the following statement performs an online rebuild of the clustered index on the Sales.SalesOrderDetail table in the AdventureWorks database:
ALTER INDEX PK_SalesOrderDetail_
SalesOrderID_SalesOrderDetailID
ON Sales.SalesOrderDetail
REBUILD WITH (ONLINE = ON);
Online index creation uses row versioning to keep the original index rows available even while changes are being made to the base table.Anyone selecting from the table sees the values as they were before the rebuild began. As with snapshot isolation, with online index building, you pay a price for the greater data availability. And again, part of that price is the space required in the tempdb database, which can be considerable if you're rebuilding the clustered index on a huge table. (Every row must be versioned as you build the next index, but you also need space to version any rows modified during the index-building process.) In addition, the actual building of the index might take more time than if the building were occurring offline.
Prev. page
1
2
[3]
4
next page 
