You work as the database administrator at Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains two database servers named Certkiller -DB01 and Certkiller -DB02. Certkiller -DB01 hosts a database named CK_Orders that stores sales data for the company. The tables in the CK_Orders database are shown in the following database diagram.
A full-text index has been created on the ProductID column of the Products table. You need to move the CK_Orders database from Certkiller -DB01 to Certkiller -DB02. You need to ensure that users can run queries against the CK_Orders database while it is being moved, and that the full-text index can be queried once the database has been moved to Certkiller -DB02. You plan to use the Copy Database Wizard to accomplish this task.
What should you do? (Each correct answer presents part of the solution. Choose THREE.)
A.
Select the Use the SQL Management Object method option when using the Copy Database Wizard.
B.
Select the Use the detach and attach method option when using the Copy Database Wizard.
C.
Install SQL Server Integration Services (SSIS) on Certkiller -DB01 and Certkiller -DB02.
D.
Enable support for ad hoc distributed queries on Certkiller -DB02.
E.
Manually move the full-text catalog to Certkiller -DB02.
F.
Repopulate the full-text index on Certkiller -DB02 once the database has been moved.
Explanation:
The SQL Server Integration Services (SSIS) is required for the Copy Database Wizard and must be installed on both the source and destination servers. To ensure that users can query the database while it is being moved, you must select the Use the SQL Management Object method option when using the Copy Database Wizard. The Use the SQL Management Object method allows active connections to the database while it is being moved. Once the database is moved, you must repopulate the full-text index. The Copy Database Wizard will move the full-text catalog to Certkiller -DB02 if the Use the SQL Management Object method is selected but it does not repopulate the index. Only once the index is repopulated can it be queried.
Incorrect Answers:
B: The Use the detach and attach method option does not allow active connections to the database while it is being moved and thus will not allow the database to be queried while it is being moved.
D: Ad hoc distributed queries are not required to ensure that users can query the database while it is being moved.
E: When the Use the SQL Management Object method option is used, the Copy Database Wizard will move the full-text catalog to Certkiller -DB02 therefore you do not need to move the full-text catalog manually, but you will need to repopulate the index.
Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: Copy Database Wizard