Review Questions

1.	What stored procedure is used to add login mappings for linked servers? sp_addlogin sp_addlinkedlogin sp_addlinkedsrvlogin sp_addloginmapping
2.	You are managing your company’s Clinical SQL Server 2005 server at its headquarters in New Jersey. Your company has three other locations configured as linked servers with the headquarters server: Auburn, Washington; Fort Worth, Texas; and Orlando, Florida. Each is connected to the Clinical server over a virtual private network. Several users have noticed that queries run against the Auburn linked server sometimes time out. Which of the following strategies is likely to resolve the timeout issue for the users? Decrease the Auburn linked server connection timeout value property. Increase the Auburn linked server connection timeout value property. Increase the linked server connection timeout value property for all linked servers. Increase the linked server connection timeout value property at the Clinical server.
3.	Which of the following are conditions that need to be met to enable a linked server strategy to be available for self-mapping? (Choose all that apply.) SQL Server authentication is used on both the local and linked servers. Windows authentication is used on both the local and linked servers. Windows authentication is used on the local server, and SQL Server authentication is used on the linked server. Windows accounts are not trusted for delegation. Windows accounts are trusted for delegation.
4.	You are managing your company’s Clinical SQL Server 2005 server at its headquarters in New Jersey. Your company has three other locations configured as linked servers with the headquarters server: Auburn, Washington; Fort Worth, Texas; and Orlando, Florida. The Orlando and Auburn servers also are configured as linked servers on the Fort Worth server. With this linked server configuration, which of the following scenarios is true? Users can configure a query on the Clinical server that uses data from the Fort Worth and Auburn servers. Users can configure a query on the Fort Worth server that uses data from the Orlando and Clinical servers. Users can configure a query on the Orlando server that uses data from the Fort Worth and Clinical servers. Users can configure a query on the Auburn server that uses data from the Fort Worth and Orlando servers.
5.	You have created a linked server solution for your users. One of your users is getting an error when trying to create a distributed query to the client’s table. The query contains the clientID, FirstName, and LastName columns. The columns of the Clients table are as follows: ClientID int FirstName nvarchar(50) LastName nvarchar(50) Demographics xml DateModified Datetime Which of the following is the cause of the error? The user must return all the columns from the Clients table in the query. The user is trying to return too many columns in the query. The table contains a datetime column. The table contains an xml column.
6.	You recently set up a snapshot replication strategy between two servers of your company. The configured servers use a domain account that is subject to a 30-day maximum password age policy. Today you realize that the snapshot replication is not functioning. What should you do to restore replication? Choose the best solution. You must create a different domain account that will not expire and reconfigure replication. You must reset the password of the domain account. You must delete the subscription. You must reinitialize the publication.
7.	You want to create a replication strategy that will allow users in four branch offices to have a fresh copy of data from the database in the central office every day at 5 a.m. for their daily reports. Although there are many changes to the data during the course of a day, the entire publication is not that large. Which replication strategy should you use? Transactional replication Peer-to-peer replication Merge replication Snapshot replication
8.	You are in charge of the databases for a pharmaceutical company that has just purchased a smaller clinical partner. Your company is using SQL Server 2005, and the acquired company is using DB2; however, until you merge your data, you want to be able to create reports to combine your data. The production division has requested some data from the SQL Server 2005 and DB2 databases be combined so that their reports can be created using SQL Server’s Reporting Services. Which of the following is the best solution to accomplish this task? Configure the DB2 server as a linked server on the SQL Server 2005 server, and create a distributed query for the data set for the reports. Import the data from the DB2 server to SQL Server 2005 using SSIS. Use a query of the combined data as the data set for the reports. Export the data from SQL Server 2005 to the DB2 server using SSIS. Use a query of the combined data as the data set for the reports. Configure the SQL Server 2005 server as a linked server on the DB2 server, and create a distributed query for the data set for the reports.
9.	Which of the replication agents is in charge of moving the newly created snapshot jobs and transactions that are held in the distribution database to each subscriber for synchronization in a replication? Merge Agent Queue Reader Agent Distribution Agent Log Reader Agent
10.	You are concerned with the amount of latency of the subscribers in the branch offices of your transactional replication topology. What can you use to evaluate the amount of latency at each of the branch offices? Latency metric in Profiler Latency metric in System Monitor Latency metric in Replication Monitor Tracer tokens in Replication Monitor
11.	The PhaseITrial table is part of the publication of the Clinical database. The TrialCode column contains a CHECK constraint to ensure that trial codes are entered using the proper format. However, you want to ensure that the CONSTRAINT is not applied during the replication of the table. How could you accomplish this with the least amount of configuration? Delete the CONSTRAINT at the publisher, and apply it at the PhaseITrial table at each subscriber. Configure the CHECK constraint on the PhaseITrial table at the publisher as NOT FOR REPLICATION. Configure the CHECK constraint on the PhaseITrial table at the subscriber as WITH ENCRYPTION. Configure the CHECK constraint on the PhaseITrial table at the subscriber as NOT FOR REPLICATION.
12.	You have configured a replication topology that is using 10 subscribers for your branch offices. None of the subscribers needs to update to the publisher. How can you efficiently set up the replication strategy so that one server is not overburdened? Create the distributor on the publisher and all the agents on the distributor. Use push subscriptions. Create a separate distributor and put all the agents on the distributor. Use push subscriptions. Put the agents on the subscribers. Use pull subscriptions. Put the agents on the subscribers. Use push subscriptions.
13.	You have decided to create a new agent profile for the Queue Reader Agent using Transact-SQL. Which of the following system stored procedures should you use? sp_add_agent_profile sp_help_agent_profile sp_add_queue_agent_profile sp_add_profile
14.	Which of the replication agents takes the messages from a queue and applies them to the appropriate publication? Merge Agent Queue Reader Agent Distribution Agent Log Reader Agent
15.	Your data processing department at H and S MedX has informed you that at the end of the month it will be processing a large amount of data to the HSNJSales database. You are concerned because that database is replicated to four different locations throughout North America. What is the most efficient solution to handle this large amount of data to be replicated at one time? You really don’t need to do anything. SQL Server can handle any large throughput of data by autoresizing the replicated data. Delete all the subscribers, and create them using pull subscriptions. After the new data have been entered and replicated, delete them again, and change them back to push subscriptions. Delete all the subscribers. After the new data have been entered, create all the subscribers, and start the replicating process again. Reinitialize the subscribers with a new snapshot.
16.	Sequel Sails uses merge replication between its main office and its marinas along the Jersey shore. You are creating a report for the sales managers at the main office. You intend to run the report using a trigger; however, since the report is meant only for managers at the main office, you do not want the report to be replicated to the marinas. How can you accomplish this with the least amount of effort? Create the trigger using the WITH ENCRYPTION option. Create the trigger using the NOT FOR REPLICATION option. Create the trigger using the DROP REPLICATION option. Create the trigger using the INSTEAD OF option.
17.	You want to be able to replicate data from your Sales server to your Reporting server in “real time”; that is, you want to have minimum latency for the report writers. Which type of replication should you use? Transactional replication Merge replication Snapshot replication Peer-to-peer replication
18.	The Contacts table is part of your AWContacts publication. You need to modify the foreign key constraints to ensure that they will not be replicated by the replication agents. Which of the following can you use to modify the foreign key constraints accordingly? (Choose all that apply.) Use Replication Monitor to modify the Enforce for Replication property of the foreign key. Use Object Explorer in SSMS at the Contacts table, and modify each foreign key in the Enforce for Replication property in Table Designer. Use the Articles page of the AWContacts publication, and under the heading Copy Objects and Settings to Subscribers, set Copy Foreign Key Constraints to False. Right-click the subscription properties in Object Explorer, and under the heading Subscription Options change the Prevent Transaction Looping to True.
19.	You have created a transactional replication strategy with updating subscriptions. You have decided to keep the default conflict resolution policy. Which of the following is the policy you have chosen to use? Keep the Subscriber Change Keep the Publisher Change Reinitialize the Subscription Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver
20.	Where are agent profiles stored? Distributor Subscriber Publisher Publisher and subscriber

Answers

1.	C. You can use the sp_addlinkedsrvlogin stored procedure to create or update a mapping between a login on the local instance of SQL Server and a security account on a remote server. You can use sp_addlogin to create a new SQL Server login that allows a user to connect to an instance of SQL Server by using SQL Server authentication. There are no system stored procedures named sp_addlinkedlogin or sp_addloginmapping.
2.	B. Increasing the connection timeout will give the distributed query more time to be resolved. Since only queries to the Auburn server are having timeout problems, the Auburn server is the only one that needs to have the configuration change.
3.	B, E. Windows authentication must be used on both the local server and the linked server, and Windows accounts must be trusted for delegation. You need to have Windows authentication on both servers to enable self-mapping; furthermore, the accounts of the login users and the servers must be able to be trusted for delegation.
4.	A. Users can configure a query on the Clinical server that uses data from the Fort Worth and Auburn servers. Linked servers supply data only in a single direction; they are not bidirectional. The only servers that users can issue distributed queries on are the Clinical and Fort Worth servers; however, the Fort Worth server can use data only from the Orlando and Auburn servers, not the Clinical server.
5.	D. Tables with xml columns cannot be queried even if the query does not involve the xml column. Querying some, all, or the datetime column would not give an error.
6.	B. You must reset the password of the domain account. The password has expired and must be reset to resolve the problem. You should not create a different account since it will need to be associated with the replication configuration. You should not create an account that does not expire, since such an account might be against company policy. Deleting a subscription or reinitializing the publication is not a solution since nothing will work until you take care of the password.
7.	D. You should use snapshot replication. Each day a new snapshot can be created and published to the branch offices for their daily reports. Transactional replications would create unnecessary overhead in I/O and network usage when all you need is one fresh copy of the data each day. Since you are not sending data back from the subscribers, merge and peer-to-peer replications do not fit the requirements.
8.	A. Using a linked server solution is the best solution. The local server must be the SQL Server 2005 server, not the DB2 server. A solution using SSIS is not necessary and would require extra storage and maintenance of the data.
9.	C. The Distribution Agent is in charge of moving the snapshots and transactions to each subscriber. The Merge Agent moves and applies changes during merge replication. The Queue Reader Agent takes the messages from the queue and applies them to the appropriate publication. The Log Reader Agent monitors the transaction log for transactional replications.
10.	D. You need to insert tracer tokens in Replication Monitor to measure the amount of latency between the nodes. Tracer tokens are small amounts of data used to evaluate the amount of time in seconds it takes for the data to travel between the given nodes. None of the other options is valid.
11.	B. Configure the CHECK constraint on the PhaseITrial table at the publisher as NOT FOR REPLICATION. The NOT FOR REPLICATION option needs to be set at the publisher, not the subscriber; the CHECK constraint is set at the publication. CHECK constraints cannot be configured WITH ENCRYPTION. Finally, if you delete the CHECK constraint at the publisher, it will not be applied, so A will certainly not work.
12.	C. Putting the agents on the subscribers will alleviate the burden from the distributor holding the agents. If the subscriber holds the agents, then the subscriptions must be pulled by the subscribers. None of the other options takes the burden off the publisher or the distributor.
13.	A. The sp_add_agent_profile stored procedure allows you to create a new agent profile of the type specified in the input variable. The sp_help_agent_profile stored procedure will modify or remove an existing agent profile. The remaining options are not valid system stored procedures.
14.	B. The Queue Reader Agent takes the messages from the queue and applies them to the appropriate publication. The Distribution Agent is in charge of moving the snapshots and transactions to each subscriber. The Merge Agent moves and applies changes during merge replication. The Log Reader Agent monitors the transaction log for transactional replications.
15.	D. The most efficient method and least amount of work is to reinitialize the subscribers by creating a new snapshot. There will be many changes anyway, so just send a new snapshot to the subscribers and reinitialize the subscriptions. All other options involve needless effort. There is no autoresizing the replicated data.
16.	A. The WITH ENCRYPTION option keeps the trigger from being replicated. The NOT FOR REPLICATION option is used to keep the trigger from firing in response to replication events. The INSTEAD OF option is used when a trigger is to intervene in a process rather than after a process had completed. In this scenario, you need to create the report and use the trigger to run it, so you need an AFTER trigger, not an INSTEAD OF trigger. There is no DROP REPLICATION option.
17.	A. To have minimum latency, you must configure transactional replication from the Sales server to the Reporting server. Snapshot replications are only up-to-date for the moment the snapshot is taken. Mere replication is trigger-based. Both merge and peer-to-peer are bidirectional solutions, which do not fit your scenario.
18.	B, C. You should Use Object Explorer in SSMS at the Contacts table and modify each foreign key in the Enforce for Replication property in Table Designer. A second method, for all foreign keys, is to use the Articles page of the AWContacts publication, and under the heading Copy Objects and Settings to Subscribers set Copy Foreign Key constraints to False.
19.	B. The default is Keep the Publisher Change. The other two choices for a transactional replication strategy with updating subscriptions are Keep the Subscriber Change and Reinitialize the Subscription. Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver is a custom resolver for merge replication.
20.	A. Agent profiles are stored on the distributor. Previous versions of SQL Server used the SQL Server Agent service account to run the agents. SQL Server 2005 uses individual replication accounts with profiles to give more granular control over the agent processes.