You maintain a database named Sales on a SQL Server 2008 instance named SQL1. This SQL server is located at your company’s main office in Chicago. The Sales database contains sales and inventory-related data for your company.
Your company recently acquired another company that maintains its sales and inventory data in an Oracle database. You want employees of the acquired company to continue to use Oracle database tools to maintain and access sales and inventory-related data.
On a weekly basis, you need to produce historical sales reports that query and aggregate data from both databases. The following requirements must also be met:
• Authorized users at the main office must be able to run frequent queries against both databases to determine available inventory quantities.
• No employees of the recently acquired company should be able to access data stored at the main office.
Which action should you take?
A. Configure SQL1 as a linked server using SQL Server Management Studio.
B. Configure the Oracle server as a linked server using the sp_addlinkedserver system stored procedure, and use distributed queries to generate the weekly sales report and provide query access to users at the main office.
C. Create an SSIS package to extract data from the Oracle server and schedule the package to run weekly.
D. Create a query that uses the OPENDATASOURCE function and use SQL Server Agent to execute the query weekly to produce the weekly sales report.
Correct Answer: B
Explanation/Reference:
A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked servers offer the following advantages:
• Remote server access.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
• The ability to address diverse data sources similarly.
After a linked server is created by using sp_addlinkedserver, the server can be accessed by using the following:
• Distributed queries that access tables in the linked server through SELECT, INSERT, UPDATE, and DELETE statements by using a linked server-based name.
• Remote stored procedures executed against the linked server by using a four-part name.
You should not create a query that uses the OPENDATASOURCE function and use SQL Server Agent to execute the query weekly to produce the weekly sales report. The OPENDATASOURCE function can be used to create an ad hoc connection and access remote data sources without defining a linked server when specific data options are set to allow it. However, when using the OPENDATASOURCE function, the connection information and credentials must be specified with each function call, and the OPENDATASOURCE function should only be used for infrequent ad hoc access. You can also use the OPENROWSET function to access remote data, but OPENDATASOURCE and OPENROWSET should not be used for frequent access.
Because users at the main office must frequently execute queries against both data sources, you should configure the Oracle server as a linked server to allow the users to execute the required distributed queries.
You should not create an SSIS package to extract data from the Oracle server and schedule the package to run weekly. SQL Server Integration Services (SSIS) allows you to create packages to retrieve data from multiple data sources, transform and cleanse data, and load data into a variety of data sources. You could use an SSIS package to extract data from the Oracle server and load it into a SQL Server database. However, in this scenario, you did not want to transfer data from the Oracle server to SQL1.
You should not configure SQL1 as a linked server using SQL Server Management Studio. When you configure a server as a linked server, data on the server can be accessed remotely by other SQL Server instances. In this scenario, you did not need to provide remote users with access to data stored on SQL1
