Replicate one database in one server to 2 other servers SQL2008

replicationsql-server-2008standard-edition

In product
ion, we have a transactional replication between 2 servers with data from one database going from server A to Server B. We now want to add another server C for the short period of time and get replication working from Server A to Server C.
So in short, one database from server A replicated one way to Server B and Server C simultaneously. Is that possible in SQL2008 standard edition ? And if yes then which type of replication would work ?

Best Answer

Yes this is possible, and most types of Replication should allow you to accomplish this, but since you said you only need it to synchronize in one direction then Transactional Replication is probably the most ideal solution. It's also typically the most flexible type of Replication, especially since it supports heterogenous Publishers:

Transactional replication is typically used in server-to-server environments and is appropriate in each of the following cases:

You want incremental changes to be propagated to Subscribers as they occur.

The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.

The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.

The Publisher has a very high volume of insert, update, and delete activity.

The Publisher or Subscriber is a non-SQL Server database, such as Oracle.

By default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that allow updates at the Subscriber.

Since your database on ServerA is already setup as a Publisher, you likely only need to follow the second set of steps of setting up a new subscription per Create a subscription to the transactional publication.

Note that any tables replicated via Transactional Replication require primary keys defined on them, and any indexed views require a unique clustered index.