Prerequisite for creation of linked server -
1. Connectivity should be there between the two servers and port 1433 or the port on which destination
SQL SERVER is running should be open in firewall on destination server from source server so that
connection can be established from source to destination server.
2. Account that will be used for connecting from source to destination server should be added in the
destination server with the required rights.
Step 1 : - Connect to source server and go to Server objects -> Linked Server -> New Linked Server
Step 2 :- If your destination server is SQLSERVER then mention the destination server name in the Linked server name. If the Server is running on any other port other than 1433 then please mention the port number also ( Server Name, Port Number).
Step 3 : - Enter the login credentials that will be used to connect to destination SQL SERVER. This will specify how the linked server will be authenticated.
Step 4 :- Click OK and linked server is created. You can view the linked server at Server Objects -> Linked Server.
Step 5 :- Verify if the linked server created is able to access the destination server from source server. Right click the linked server and select test connection. It will be successful if it show connection succeeded.
Step 6 :- For querying the destination server name use the FQDN name.
FQDN name is ServerName.DatabaseName.SchemaName.ObjectName.