Using SQL Server Replication
You can use SQL Server replication to synchronize the database servers in your installation. SQL Server replication options include snapshot replication and transactional replication. You should consider using SQL Server replication to do batch updates if your application logic is aware of the nature of database access.
For example, you can update the Product Catalog system in Commerce Server primarily in batch mode, and have all client access be read-only. Another example is creating and changing the OrderForm object a few times from known pipeline components. OrderForm objects are frequently read from other pipeline components in the site during check out, for operations like tax and shipping calculations. In both examples, you might consider having a small number of stores updated with reads directed to a bank of read-only servers.
You should also consider using SQL Server replication to keep geographically distributed servers synchronized.
The following figure illustrates how you might cluster SQL Servers in the database tier.
.gif "SQL Server cluster")
If you place Network Load Balancing in front of a group of read-only servers running SQL Server, you can use the same server name for the entire cluster. The benefit of doing this is that you can then remove any server in the cluster from service with minimal impact to users. You can also enable replication from the online transaction processing (OLTP) environment to the cluster. Use transactional replication if you require higher transactional consistency to minimize latency between the OLTP server and the load-balanced, read-only servers.
Consider the following for installations of Network Load Balancing query clusters:
With any installation of Network Load Balancing, there are at least two dedicated IP addresses (one per server) and one virtual IP address. For replication between the OLTP environment and the cluster to work correctly, replication must always be made directly to the dedicated IP addresses (or individual server names) and never be made directly to the virtual IP address or to the cluster name.
Because you use Network Load Balancing to balance the load across the cluster, replicating directly to the virtual IP address (or cluster name) would cause transactional inconsistency between servers in the cluster.
Although you can use either the push or pull metaphor for this configuration, best practice is to push all replication to servers in the cluster. The reason for using the push metaphor is to allow you to manage the distribution jobs centrally and to provide a means of alerting you if one of the servers in the cluster stops responding. (The replication job will fail for the server that stops responding.)
Because replication is occurring between the OLTP environment and the cluster, you cannot set databases on servers in the cluster to read/write. As a result, you must use login permissions to maintain read-only security. All client connections to servers in the cluster must connect through the virtual IP address and have read-only permissions. It is also necessary to ensure that all servers in the read-only cluster include the same subset of logins.
If you want to apply a snapshot to subscribers in the cluster, the best practice is to disconnect servers individually from the cluster, apply the snapshot, and then reconnect each server to the cluster. This produces maximum availability and maintains transactional consistency between servers in the cluster.
See Also
Copyright © 2005 Microsoft Corporation.
All rights reserved.