Microsoft® SQL Server® 2008 For Dummies®

Chapter 15. Replicating Data across Multiple Servers

Sometimes a single database server isn't sufficient to meet all your business requirements. You may need to distribute your data among multiple servers to meet growing demand. Fortunately, SQL Server provides several replication options to help you keep the contents of multiple databases synchronized.

Some scenarios in which you might want to employ replication include:

• You have multiple, geographically separated sites that need access to information on your SQL Server database. Connections between the sites are slow and expensive, so you want to host a local copy of the database at each site.

• You want to provide travelling users with an offline copy of a portion of your database for use on the road.

• Your organization has complex reporting needs, and you want to provide the reporting group with an offline copy of your database that the group can use without affecting the performance of your production SQL Server environment.

Understanding Replication

Replication allows you to transport copies of your databases between different SQL Server instances and keep those copies up-to-date as the database changes. SQL Server provides several different replication technologies, each of which uses different techniques to provide varying levels of currency in the data.

Server roles

In any SQL Server replication environment, there are three server roles that must be filled: the publisher, the distributor, and the subscriber.

Figure 15-1 illustrates ...