Lesson 1
Accessing Remote Data Sources
Up until now, you have been accessing data that resides on only one server, and possibly in only one database.
Did you know that you can access data from tables that are not on the same server?
This is possible. In fact, you can also execute stored procedures located in another database on another server. This module shows you how to do all that.
This is possible. In fact, you can also execute stored procedures located in another database on another server. This module shows you how to do all that.
Learning objectives
After completing this module, you will be able to:
In the next lesson, you will learn how remote data access is achieved in SQL Server 2012.
- Set up servers for remote data
- Execute remote stored procedures
- Construct and execute queries from remote data sources
In the next lesson, you will learn how remote data access is achieved in SQL Server 2012.
Using Remote Data Sources
If the report retrieves data from a remote database server, verify the following:
- The credentials provided to the database server are valid. If you are using Windows user credentials, make sure that the user has permission to the server and database.
- Ports used by the database server are open. If you are accessing SQL Server relational databases on external computers, or if the report server database is on an external SQL Server instance, you must open port 1433 and 1434 on the external computer. Be sure to restart the server after you open ports. For more information, see Configure a Windows Firewall for Database Engine Access.
- Remote connections must be enabled. If you are accessing SQL Server relational databases on external computers, you can use SQL Server Configuration Manager tool to verify that remote connections over TCP are enabled.
Distributed Query Concepts
Linking to an external data source is nothing more than configuring the name of the linked server, along with the necessary location and login information, so that SQL Server can access data on the linked server.
Linking is a one-way configuration, as illustrated in Figure 6-1. If Server A links to Server B, then it means that Server A knows how to access and log into Server B. As far as Server B is concerned, Server A is just another user. If linking a server is a new concept to you, then it could easily be confused with registering a server in Management Studio. As illustrated in Figure 6-1, Management Studio is only communicating with the servers as a client application. Linking the servers enables SQL Server instance A to communicate directly with SQL Server instance B.
Links can be established in Management Studio or with T-SQL code (which could, of course, be created by configuring the link in Management Studio and then generating a script.) The latter has the advantage of repeatability in case a rebuild is necessary, although building the links in code requires more steps.
While SQL Server can handle the technical problems of querying external data, if the two systems are in fact two separate applications, then directly accessing an external data store will likely violate the principle of encapsulation; and coupling the two data stores reduces the flexibility of the architecture. In many IT shops this practice would not be approved. Instead, consider using a middle tier that communicates with each of the two databases.
A linked server is a one-way direct connection and is not dependent on Management Studio registering the servers. In this diagram, SQL Server instance A sees SQL Server instance B as a linked server, so A can access B's data.
Links can be established in Management Studio or with T-SQL code (which could, of course, be created by configuring the link in Management Studio and then generating a script.) The latter has the advantage of repeatability in case a rebuild is necessary, although building the links in code requires more steps.
While SQL Server can handle the technical problems of querying external data, if the two systems are in fact two separate applications, then directly accessing an external data store will likely violate the principle of encapsulation; and coupling the two data stores reduces the flexibility of the architecture. In many IT shops this practice would not be approved. Instead, consider using a middle tier that communicates with each of the two databases.
A linked server is a one-way direct connection and is not dependent on Management Studio registering the servers. In this diagram, SQL Server instance A sees SQL Server instance B as a linked server, so A can access B's data.
