Improving Availability and enhancing Programmability - Microsoft SQL Server 2008 R2 Administration - page 233

F The transactional consistency must be maintained with a low latency such as
updates at one server must reach the other server
F The filtering option must be enabled, so that the tables at Subscribers contain only
the data required by the application
F The network throughput should be high to handle the replication of a large number
of transactions
F The database recovery model must be FULL at all the times
F Ensure to create a publication and subscriptions, and then initialize each subscription
F Ensure that adequate disk space and system resources are available across all the
servers involved in this setup
F Ensure that SQL Server agent service is up and running with no errors
The best advantage of SQL Server 2008 version onwards
is the Peer-To-Peer topology wizard that helps the users
to alter the configuration online using the SSMS tool.
How to do it...
Once the prerequisite options are met as per the recipe requirement, follow the steps
mentioned next to improve the replication scalability at multiple sites in the environment.
1. The first option is to configure the distribution that stores the metadata and data
history for replication.
2. Create a snapshot folder, which is used by Publishers and has appropriate
permissions set.
3. All the publishers are authorized to use the Distributor. Size the distribution
database to accommodate the data load having auto-grow enabled.
4. Set the sync with backup option on the distribution database.
5. If the distributor is remotely located, then use the SSMS tool to configure a publisher
to use that remote distributor server instance.
6. The next step is to configure the data publication and database objects.
7. To create a publication, ensure it contains the information about distributor, location
of snapshot files, publication database, and the type of publication to create
(snapshot/transactional/transaction with updatable subscriptions).
8. Using the SSMS tool, create the publication by choosing the data and the relevant
articles (database objects) to include in the publication.
9. Set up the static row and column filters. If it is a merge replication, then set the
parameterized row filters along with join filters.