There are different ways you can create an exact online copy of you BizTalk databases. The most well-known is replication, in which somewhere on your network runs another SQL server that has subscribed to the part of the database, or the whole one, you make available for publication.
1. Create a publication for the entire database. Be sure you make it transactional, so it stays synchronized with the source.
2. Create a Pull subscription on the other SQL server.
3. Make at least one backup a day, since bad news never comes alone.
While using replication as a means to ensure that you have an actual copy of the BizTalk databases, it is not its intended purpose. Replication, using the Publish-Subscribe method, is use to distribute data among multiple independent applications. Once the subscriber gets the data from a publication, it is no longer under the control of the publisher. Another application of replication is to have multiple places in your network, especially if data has to travel over slow links, from where the content of the database can be inquired, thereby overcoming the slow communication.
Replication does require additional processing power—in the worse case, more than double—so it can cause significant problems when the load on the SQL server is high.You need a thorough understanding of the how replication works, and the different types of replication infrastructures that are possible. In addition, understand how the primary database is used in terms of load. It is highly recommended that you execute some performance tests on the replication infrastructure to determine how replication behaves under high loads. For more information on replication, read Chapter 12 of Designing SQL Server 2000 Databases for .NET Enterprise Servers.
Another way to create online copies is with Log shipping. As the name implies, the transaction logs of a BizTalk database are shipped to another server that runs SQL Server. That server has a copy of the BizTalk database running, and every time it receives a transaction log, it replays it on the database, thereby synchronizing the copy with the original database. The copying of the transaction log can be done every time you back up the transaction log. Again, do not truncate the transaction logs.
Was this article helpful?