Question 3) 2005 Implementation and Maintenance

You are the database administrator for a consumer goods manufacturing company that has its corporate headquarters in London and sales offices in New York and Paris. All the three offices have computers using SQL Server 2005 Enterprise Edition. The three server instances for London, New York, and Paris are named Sql_lon, Sql_ny, and Sql_par, respectively. The London office has an Oracle 9i database server called Oracle_lon. All the frequently updated order processing data from the Sql_ny and Sql_par servers should be consolidated into the Sql_lon server in London. The Sql_lon server will then send the read-only product inventory information to the Sql_ny and Sql_par servers.

The Sql_lon, Sql_ny, and Sql_par servers are the only servers that participate in the replication topology used by the company. The Sql_lon database server is the subscriber, and the Sql_ny and Sql_par servers are publishers.

Tutorial: Transactional replication is the correct option because this scenario requires frequent data changes to be propagated to the subscriber as they occur. The scenario also requires support for the central subscriber topology. Transactional replication is most appropriate for propagating incremental changes to subscribers in a server-to-server environment. When you use transactional replication, minimum time is consumed in replicating the changes from the publisher to the subscriber. This scenario demands that the changes made to the Sql_ny and Sql_par servers be consolidated to the Sql_lon server. No other type of replication enables you to replicate the changes to the subscriber when they occur on the publisher.

Snapshot replication is an incorrect option because snapshot replication is not suitable during frequent data change. When you implement a snapshot replication, it is not mandatory to have consistent data at all times. The data changes may not be replicated to the subscribers as soon as they occur on the publisher. Compared to the time required for transactional replication, the time required for snapshot replication is more. Snapshot replication is typically used to provide the initial set of data for different publications.

Merge replication is an incorrect option because merge replication does not support central subscriber topology. The central subscriber topology implements a central subscriber, and the changes are replicated from the remote publishers to the subscriber. In this scenario, London is the central subscriber, and New York and Paris are the publishers. Merge replication is used in scenarios where the application requires conflict resolution or filters that provide each remote site with a unique set of data.

Oracle replication is an incorrect option because in the given scenario you are not using an Oracle server in the replication process. Only the SQL Server 2005 Enterprise servers are participating in the replication process. SQL Server 2005 Enterprise Edition supports replication of data from an Oracle server to a SQL Server. This type of heterogeneous replication model is commonly called as Oracle replication. Only transactional and snapshot replications are supported in Oracle replication.