Although you can set up linked servers and login mappings by directly executing these stored procedures, they can also be set up easily through Enterprise Manager.
To create a linked server, follow these steps:
Open Enterprise Manager.
Click the plus sign next to the local SQL Server.
Click the plus sign next to the Security folder.
Right-click Linked Servers and choose New Linked Server (see Figure 19.5).
Figure 19.5. Creating linked servers through Enterprise Manager.
The General tab of the New Linked Server property box is displayed. The fields in the Server Type section are filled in the same way as the corresponding arguments for sp_addlinkedserver . The fields in the Server Options section at the bottom of this tab correspond to sp_serveroption arguments (see Figure 19.6).
Figure 19.6. The General tab of the New Linked Server property box.
The Security tab of the New Linked Server property box allows you to map remote logins (see Figure 19.7). The code behind this dialog box actually runs the sp_addlinkedsrvlogin and sp_droplinkedsrvlogin stored procedures.
Figure 19.7. The Security tab of the New Linked Server property box.
The Server Options tab of the New Linked Server property box lets you specify the server options (see Figure 19.8). The code behind this dialog box actually runs the sp_serveroption stored procedure.
Figure 19.8. The Server Options tab of the New Linked Server property box.
Turns out that when you recycle a server that is involved in a linked or remote server definition, the link gets broken (after all, it is a continuous connection from one server to another). And, it doesn't always re-establish itself. It is a good practice to periodically verify the health of these links to guarantee that they remain intact. It has been on more than one occasion that linked server definitions have had to be removed and readded following a server recycle. Thanks, www.backroads.com, for uncovering this critical issue.