You work as a database administrator at Domain.com. The Domain.com network consists of a single Windows 2000 domain named Domain.com. All servers on the Domain.com network run Windows 2000 Server and all client computers run Windows XP Professional. Domain.com currently uses an ODBC-compliant proprietary Relational Database Management System (RDBMS) to store business information.
You have been instructed to migrate some of the databases in the proprietary RDBMS to SQL Server 2005. You need to ensure that Domain.com users can access data in the RDBMS databases during the migration process. You deploy SQL Server client tools on all Domain.com client computers. You want to ensure that Domain.com users can access data in both database systems by running ad hoc queries against SQL Server.
What should you do?
A.
Create a linked server and a data source name (DSN) for the proprietary RDBMS system.
B.
Create a linked server and use the OLE DB Provider for SQL as a data source.
C.
Create a local portioned view of the databases on the RDBMS system in SQL Server 2005.
D.
Create stored procedures in SQL Server 2005 to query the RDBMS system.
Explanation:
To run distributed queries with a proprietary database system, you must create a linked server definition for the proprietary database system. As the proprietary database system is ODBC-compliant, you can create a data source name (DSN) for the proprietary database system that can be used to identify the proprietary database system.
Incorrect Answers:
B: The OLE DB Provider for SQL was used in SQL Server 2000. It is not used in SQL Server 2005 and has been replaced by the SQL Native Client OLE DB Provider.
C: A partitioned view must be derived from the same SQL Server instance. It cannot be created on remote databases.
D: Stored procedures can be run against linked server. However, the Domain.com users must be able to use ad hoc queries rather than stored procedures.
Reference:
Microsoft SQL Server 2005 Books Online (2007), Index: linked servers [SQL Server], about linked servers