1. | What stored procedure is used to add login mappings for linked servers?
|
|
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?
|
|
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.)
|
|
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?
|
|
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?
|
|
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.
|
|
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?
|
|
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?
|
|
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?
|
|
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?
|
|
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?
|
|
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?
|
|
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?
|
|
14. | Which of the replication agents takes the messages from a queue and applies them to the appropriate publication?
|
|
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?
|
|
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?
|
|
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?
|
|
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.)
|
|
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?
|
|
20. | Where are agent profiles stored?
|
|
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. |