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.
Answers
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. |
|
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. |
|
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. |
|
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. |
|
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. |
|
B. You cannot use indexed and partitioned views together. Indexed views require that the tables reside in the same database. |
|
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. |