Home » Microsoft » MB6-886 » Which action should you take?
You are a database administrator on an instance of SQL Server. You have users in many geographical regions.
You need to provide data access to all of your regional offices. Each regional office updates its own regional data. You want each regional office to be responsible for backing up and maintaining its own data. Each regional office has access to only its data. However, at the company headquarters, you need to be able to access data from all regions. Which action should you take?
A. At each regional office, add a linked server for a single server at company headquarters.
B. Use snapshot replication.
C. Use peer-to-peer replication.
D. Implement a distributed partitioned view.
Correct Answer: D
Explanation/Reference:
The server at each regional office would contain data for the individual region. Each regional office could administer its own server independently. This would offload processing and administration effort to the regional offices. You could still access the data collectively from the company headquarters using a distributed partitioned view. Partitioned views are used when you have similar data stored in multiple tables and want to create a view to allow access to all of the data as if it were stored in a single table. The tables referenced in the view can reside on the same server or on different servers, such as at different regional offices in this scenario. To create a distributed partitioned view at the company headquarters, you could add a linked server for each region’s server, and then create a view to access the data from each region. The view definition would use the fully qualified name for each SELECT statement and the UNION ALL operator to combine the results.
You should not add a linked server at each regional office. This would allow each regional office to access data across the enterprise, but would not offload the processing and administration to each regional office, or allow the company headquarters to access data across all regional offices.
You should not use snapshot or peer-to-peer replication. Replication is used to provide access to data across the enterprise while allowing the data to be synchronized. In this scenario, regional users only need to access data for their region, so no synchronization would be required. If you needed data synchronization between all locations, you could implement transactional replication using the central subscriber model.
