How do you access remote Data?

Microsoft's preferred method for accessing remote data is to use a linked server. For the legacy version of SQL-Server 7.0, Microsoft's preferred method for accessing remote data was to use a linked server. Does this still hold true for SQL-Server 2019?

While linked servers^[1] are still a valid and supported option in SQL Server 2019, they are not necessarily the preferred method for accessing remote data in all scenarios. There are newer and often more suitable alternatives depending on your specific needs. Here's a breakdown:

1. Pros:
   • Familiar and well-established approach supported since SQL Server 7.0.
   • Can be used with various data sources, not just SQL Server instances.
   • Offer flexibility for complex queries involving data from multiple servers.
2. Cons:
   • Can be complex to set up and configure, especially with security considerations.
   • Can introduce performance overhead due to network traffic and additional processing.
   • May not be the most secure option, as they often rely on less secure authentication methods.

1. Distributed Queries:
   • Introduced in SQL Server 2008, allows querying remote servers directly within T-SQL statements.
   • Simpler syntax and often better performance compared to linked servers.
   • Requires specific configuration on both source and target servers.
2. Azure Data Services:
   • Leverage cloud-based solutions like Azure SQL Database or Azure Synapse Analytics.
   • Offer scalability, high availability, and built-in security features.
   • May require additional migration or data transformation efforts.

Linked servers are still a viable option for SQL Server 2019, especially for legacy applications or complex scenarios. However, for new development or simpler remote access needs, consider distributed queries or Azure data services for potentially improved performance, security, and ease of use. Ultimately, the best method depends on your specific requirements, technical expertise, and environment.

Although linking servers is Microsoft’s preferred method for accessing remote data, it is not the only method. You can also use a remote server, which is an older technology that allows for only stored procedures to be executed against another server. This technology is supported in SQL Server 2012 for backward compatibility only.
This course covers only linked servers.

In most production environments, SQL Server will be running on a remote server, one probably locked away in a secure and controlled area, possibly where the only people allowed in are hardware engineers. There probably is not even a remote access program installed, as this could give unauthorized access to these computers. SQL Server will run quite happily and, with any luck, never give an error.
Question: But what if one day there is an error? If SQL Server is running as a program, you will have to make some sort of decision. Even if SQL Server crashes, there at least has to be some sort of mechanism to restart it. This means another process needs to be run, a monitoring process, which in itself could result in a whole ream of problems. However, as a service, SQL Server is under Windows control. If a problem occurs, whether with SQL Server, Windows, or any outside influence, Windows is smart enough to deal with it through the services process. If you do log in to the computer, as you likely will while working through this book, then you can use this Windows user ID for SQL Server to also log in and start its service. The Windows user ID that SQL Server uses is known as a local system account.
On the other hand, you can create a Windows login that exists purely for SQL Server, and this is the correct behavior to implement in production. You avoid it in this book only to keep things simple while you are learning SQL Server. Your Windows account should be set up so that the password expires after so many days, or so that it locks out after a number of incorrect password attempts. This is to protect your computer and the network, among many other things. However, SQL Server should use a separate account that also has an expiring password and the ability to lock the account after a number of unsuccessful attempts. The account will also be set up to access only specific resources both on the local computer as well as network-related. This kind of non 1) user-specific, 2) generic account removes the link between SQL Server and a person within an organization. If you are looking at the domain account option as shown earlier in Figure 6-2, this account is likely to be in a network environment or a production environment. There is an option to define a different account for each service. That ability is crucial when in a corporate environment because of the security implications that you must deal with.

To access remote data with a linked server, follow these steps:

1. Add the linked server.
2. Log in to the server using a remote login.
3. Access the remote data using a query or qualified path.

Remote data access (RDA) in Microsoft SQL Server Compact 3.5 lets an application access data from a remote SQL Server database table. It can also store, read, and update that data in SQL Server Compact 3.5, and then update the original SQL Server table.
The topics in this section provide information about using remote data access (RDA) in SQL Server Compact 3.5.

A linked server can be a SQL server or any other data source with either an OLE DB provider or ODBC drivers. Distributed queries can select data and modify it (INSERT, UPDATE, DELETE), according to the features of the OLE DB provider or ODBC driver. SQL Server queries can reference external data by referring to the preconfigured linked server or specifying the link in the query code. Note: In this module, I refer to the two data sources as local and external . Other descriptions of distributed queries might refer to the same two servers as local and remote, or sending and receiving.
In a sense, linking to an external data source only moves declaring the link from the query code to a server administration task. Because queries can refer to the named link without concern for the location or security particulars of the link, queries that use linked servers are more portable and easier to maintain than queries that declare the external data source in the query code. If the database is moved to a new server, then once the database administrator creates the appropriate links, the queries will work without modification. In the case of a distributed query, SQL Server is the client process receiving the results from the external data source. Distributed queries can either pull the data into SQL Server for processing or pass the query to the external data source for processing.
In the next lesson, you will learn how to add a linked server.

[1]: