One of my clients in the insurance industry has an application that has two types of users: online users who directly access the application's database,ApplicationDB, via the LAN and mobile users who access a local copy of the database on their laptops and then use SQL Server merge replication to synchronize the changes with the central database via VPN connections.
The client's IT department observed that application performance for the LAN users was slow, and numerous tests showed that the application would work much faster if only LAN users used it.The merge replication operations running in the background were significantly affecting online performance. Migrating the application to SQL Server 2005 had resolved many performance issues for mobile users, but online users continued to experience long response times for their queries.
Fortunately, we were able to point online and mobile users to different instances of the application database on different servers to boost application performance for online users and still let the company benefit from the real-time data transfer that merge replication offers. Here's how we did it.
Using Transactional Replication
We can achieve separation of online and mobile users by placing a copy of ApplicationDB
on a separate server for online users and connecting the copy to a merge replication
publisher database. Online users then query their own database, and a data exchange
mechanism synchronizes the data entered by the mobile and online users.
There are three possible scenarios for keeping the separate databases synchronized:
- Adding the online database as another subscriber in merge replication.
- Using SQL Server 2005 Integration Services (SSIS—or DTS in SQL Server
2000).
- Using bidirectional transactional replication to sync up the data between
an online transaction processing (OLTP) database for the online users and
the merge replication publisher database. I call the server that runs the
merge replication publisher database the bridge server to reflect the fact
that it connects the online and mobile users.
The first possibility,adding the online database as another subscriber,won't solve the problem. The same trigger-based mechanism will affect the performance of online operations.
Using SSIS, the second possible method, will add latency to the data synchronization process, and it requires designing a special "loopback prevention" mechanism (to prevent transactions from being sent back to the originating server).This method would also probably require the creation of additional data-staging areas on both the OLTP and merge replication sides.
Bidirectional transactional replication is the best choice for our needs. It
has a built-in loopback detection mechanism. It transmits data much more quickly
than merge replication, and its effect on online operations is minimal because
the log reader in transactional replication operates with already committed
transactions in the database log. Figure 1
shows the architecture for a bidirectional transactional replication solution.
For additional information about bidirectional replication, see "How to: Configure
Nonpartitioned,Bidirectional,Transactional Replication (Replication Transact-SQL
Programming)" in SQL Server 2005 Books Online.
There are a couple of difficulties in dealing with bidirectional replication in this situation: We must create a conflict-resolution mechanism on both sides, and the merge replication publisher must accept the transactional replication load. Let's look at how to address these issues.
Conflict-Resolution Mechanism
We can resolve conflicts that occur when users make simultaneous changes to
the different copies of the database by modifying three custom stored procedures.
BOL contains this description of the stored procedures: "By default transactional
replication makes all data changes at Subscribers through stored procedures
that are generated by internal procedures for each table article in the publication.The
three procedures (one each for inserts, updates, and deletes) are copied to
the Subscriber and execute when an insert, update, or delete is replicated to
the Subscriber." We must modify the procedures for each article in each publication.
One column of type datetime is required in each article to implement the conflict
resolution mechanism in bidirectional transactional replication. If a table
doesn't have such a column that stores the Universal Time Coordinate (UTC) time
at which the transaction was created or modified, we must add the column to
the table scheme. We'll name this column Version. The default value for this
column should be getUTCdate().
To ensure that the value of column Version always changes when the record is updated, we must create a simple update trigger on each table. (This step might not be required if our developers confirm that each update statement also updates the value in the Version column.)
For this particular client, the rules needed to specify that when an online
transaction and a mobile transaction were delivered to the bridge server at
about the same time, the online transaction should be carried out. This preference
for the online transaction reflects the fact that in most cases, the mobile
transaction will have occurred earlier in time than the online one that arrives
on the bridge server at about the same time—thus the online transaction
will probably better reflect the current business situation.
You can see that the conflict-resolution stored procedures need to be different on the bridge and online sites—the two servers will have opposite conditions for accepting or rejecting incoming transactions.We must also allow for server time difference and replication latency in our conflict-resolution stored procedures. We might say that two records were updated at the same time if the difference between the transaction times is less than or equal to 15 seconds.
We could express the condition to reject incoming changes on the OLTP server as
IF DATEDIFF (ss, @curr_Version,
@Version) <= 15
where @curr_Version is the value in the Version column in the current record on the destination server (the OLTP server, in this case) and @Version is the value in the Version column in the new record that was replicated to the OLTP server.
The opposite condition would exist for rejecting an incoming change on the
bridge server, as in
IF DATEDIFF (ss, @Version,
@curr_Version) > 15
In this expression, @curr_Version is the value in the Version column in the
current record on the destination server (the bridge server, in this case) and
@Version is the value in the Version column in the new record that was replicated
to the bridge server.
Prev. page
[1]
2
3
next page 
