| Replication is a tried and true method for moving data between databases and servers. SQL Server 2005 supports three significant variations of replication. These replication methods have been part of the SQL Server platform for quite a while: SQL Server 2005 also supports a new concept called Peer-to-Peer Replication. This concept is actually a series of servers that are configured to use Transactional Replication to move data between servers. Of the methods available for transferring databases, replication tends to be the least easily understood as well as the most difficult to design and maintain. Both the source and the target, referred to as the publisher and subscriber in replication lingo, must be set up correctly to replicate data. One of the advantages of replication over the previously described data transfer methods is that some variations of replication support writable targets and conflict resolution. Complete coverage of the various methods of replication and the many configurations they each can employ is outside the scope of this book. For full information, see the SQL Server Books Online topic "Peer-to-Peer Transactional Replication." However, we will step through setting up Peer-to-Peer Replication between two servers so you can get a feel for the requirements for setting up replication in your environment. Important  | Look closely at the requirements for replication before deciding which of the data transfer methods is the best solution for your environment. |
Configuring Peer-to-Peer Replication 1. | Open SQL Server Management Studio.
| 2. | Right-click the Replication node in Object Explorer and select Configure Distribution from the context menu. This will start the Configure Distribution Wizard, which you will need to run on both servers if you have not already configured them as distributors. In this exercise, you can accept the defaults in the wizard.
| 3. | Next you will need to create a publication. Pick one of the servers on which to set up a publication. Expand the Replication node in Object Explorer and right-click on the Local Publications folder. Select New Publication from the context menu to run the New Publication Wizard. This wizard will allow you to pick the database and set data you want to replicate. There are many options to set. You should specify the Publication Type as Transactional Publication, and then select one table for publication (for this example, select Address).
| 4. | On the Snapshot Agent page, check the Create A Snapshot Immediately checkbox.
| 5. | On the Agent Security page, enter a valid user for the Snapshot Agent to use when running.
| 6. | Check the Create The Publication checkbox on the Wizard Actions page, then specify a name for your publication on the Complete The Wizard page.
| 7. | Click the Finish button to finish the publication process. Now you have a publication ready.
| 8. | Once you have a publication set up, you can configure it for Peer-to-Peer Replication. Select the Local Publications node in Object Explorer. You should now see your newly created publication displayed in the righthand window.
| 9. | Right-click your new publication and select Properties from the context menu.
| 10. | In the Publication Properties dialog box, select the Subscription Options page. Once here, you can set the Allow Peer-To-Peer Subscriptions option to True, as shown below. Click the OK button to close this dialog box.
 | 11. | The next step is to make sure the schemas are the same on all of the databases. You can do this by creating a backup of the database on the source server and then restoring the database on the target server using the procedures explained earlier in this chapter.
| 12. | Next you need to configure the peer-to-peer topology. You can do this by right-clicking your new publication and selecting Configure Peer-To-Peer Topology from the context menu. This will start a wizard that will set up the publications and subscriptions on the configured servers.
|
Once your SQL Servers are set up to participate in Peer-to-Peer Replication, any changes to data at either server will be replicated at the other server. These are the basics of setting up Peer-to-Peer Replication. Snapshot and Transactional Replication are set up in similar ways. This information should give you an idea of how replication is configured and how it behaves so you can compare it with the other methods for transferring data described in this chapter. |
