CAD/MCSE/MCDBA Self-Paced Training Kit [Electronic resources]: Microsoft SQL Server 2000 Database Design and Implementation, Second Edition (Exam 70-229)

Microsoft Corporation

نسخه متنی -صفحه : 223/ 116
نمايش فراداده

Objective 2.3: Alter database objects to support replication and partitioned views.

SQL Server 2000 allows you to replicate, copy, and modify data across SQL Server servers in your enterprise. SQL Server 2000 supports snapshot, transactional, and merge replication. SQL Server 2000 also includes several methods and options for replication design, monitoring, and administration that are needed for distributing data while keeping data consistent. SQL replication has many benefits, such as allowing different sites to keep a copy of the same data, allowing separate online transaction processing (OLTP) and online analytical processing (OLAP) databases, allowing users to work in disconnected environments with changes propagated when reconnected, increasing aggregate read performance, and providing support for standby databases. You should think about SQL Server replication when you have to solve one of the following problems: copy or replicate data to one or more sites, distribute data on a scheduled basis, distribute changes to other databases, allow multiple sites to change the data and then merge the changes, or use databases in an online/offline environment.

Snapshot replication distributes data as it appeared at the moment when the snapshot was taken. It does not monitor changes made between the replications; rather, it copies the whole data. Snapshot replication is best for infrequently replicated data where latency is not a problem. Snapshot replication is helpful when data is mostly static and does not change often, when it is acceptable if the data is out of date, or when replicating small amounts of data.

Transactional replication uses snapshots the first time to push the data to the subscribers, and then when changes are made at the publisher, the changes get captured and sent to the subscribers. Transactional replication is helpful when you want changes to be propagated to the subscribers as they occur, when you need transactions to support the ACID ( atomicity, consistency, isolation, and durability) properties, and when subscribers are reliably connected to the publisher. In transactional replication, only the publisher data is changed-the subscribers should be read-only.

Merge replication works like transactional replication, but both the publisher and the subscriber can change the data. Changes will be merged together at the publisher. Merge replication allows sites to work autonomously and, at a later time, merge the updates into a single database at the publisher. After the changes are rolled up, the changes will be pushed to all subscribers. Merge replication is useful when multiple subscribers need to modify the data, when subscribers need to receive data and make changes offline and later synchronize the changes, and when you do not expect any conflicts when data is updated at multiple sites.

Objective 2.3 Questions

1.

70-229.02.03.001

You are working for an e-commerce company. You want to ease the load on SQL Server, and you decide to add additional servers for read-only operations. The Products list is changed quite infrequently and should be rolled out as a single transaction. What kind of SQL Server replication would you use?

Transactional replication

Snapshot replication

Merge replication

2.

70-229.02.03.002

You are working for a roadside service company. Data centers are located all over the country and changes are made only to the corporate database, but customer information is read on all locations. Changes to the database must be replicated as soon as possible to the subscriber servers. What kind of replication would you use?

Transactional replication

Snapshot replication

Merge replication

3.

70-229.02.03.003

You are developing a database design and implementation for a sales system. The requirement is that a subset of the database will be downloaded into the salesperson's laptop and will be changed there. When the salesperson reconnects, his or her changes must be merged into the corporate database. What kind of replication would you use?

Transactional replication

Snapshot replication

Merge replication

4.

70-229.02.03.004

You are scaling out your database and you add a second server. On both servers you have a table called Orders. You create a partitioned view called vOrders. RegionID is the column you have used to partition the data on, and it is part of the primary key. What do you have to do with the tables to set up partitioning correctly?

Create indexes on both tables on the RegionID column.

Create a constraint on both tables to set the acceptable values for both tables.

Execute sp_EnablePartitioning on (Server1.MyDB.Orders, Server2.MyDB.Orders).

You have to create a partitioned index on the vOrders view as follows: CREATE NONCLUSTERED INDEX ndx_vOrders ON (RegionID) PARTITIONED ON Server1.MyDB.Orders values in (1 to 5), Server2.MyDB.Orders values in (6 to 10).

5.

70-229.02.03.005

What configuration do you have to set up on a database server to enable partitioning and get it to perform correctly?

Create remote servers in each server pointing to the other servers in the federated server cluster and enable lazy schema validation.

Create linked servers in each server pointing to other servers in the federated server cluster and enable lazy schema validation.

Create linked servers in each server pointing to other servers in the federated server cluster and disable lazy schema validation.

Create remote servers in each server pointing to the other servers in the federated server cluster and disable lazy schema validation.

6.

70-229.02.03.006

You have a partitioned view. You want to create an index on this partitioned view so that you will have an indexed view. Which statement is correct?

You can create an indexed view on a partitioned view.

You cannot create an indexed view on a partitioned view.

You can create an indexed view on a partitioned view as long as the index is clustered.

7.

70-229.02.03.007

You want to create indexed views. Which of the following SQL Server editions support indexed views? (Choose all that apply.)

SQL Server Developer Edition

SQL Server Standard Edition

SQL Server Enterprise Edition

Answers

1.

B. Snapshot replication is the correct answer because you do not need to have the changes as they happen. Transactional replication would introduce unnecessary processing on the servers. Merge replication would be also an incorrect answer because changes will only happen on the publisher server.

2.

A. Transactional replication is used to propagate changes to the subscribers as soon as they happen. Key features of the requirement are that changes are made only at the corporate office and changes must be replicated as soon as possible.

3.

C. Merge replication is the correct answer because it allows the data to be changed both at the publisher and at the subscribers. It will fulfill all the requirements in the scenario.

4.

B. You have to create a constraint on the RegionID columns in both databases on the Orders tables. The query engine will use these values to evaluate which servers it has to access to retrieve data to satisfy the query.

5.

B. You have to create linked servers and use the sp_serveroption to enable lazy schema validation. This optimizes performance by ensuring that the query processor does not request metadata for any of the linked tables until data is actually needed from the remote member table.

6.

B. You cannot use indexed and partitioned views together. Indexed views require that the tables reside in the same database.

7.

A. SQL Server Developer Edition and SQL Server Enterprise Edition are the only editions that support indexed views.

C. SQL Server Developer Edition and SQL Server Enterprise Edition are the only editions that support indexed views.