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.
Production Remote Server
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.