The Linked Servers option in Microsoft SQL Server Management Studio allows you to connect to another instance of SQL Server running on a different server.
If you have two or more database servers (target database servers) then you need to link them to the source database server. This allows Copy by Family in the Molded Forms task to support retrieving or copying data from different servers.
Add User Account on Target Database Server
-
Expand the listing under target database server in Object Explorer.
-
Expand the listing under Security.
-
Right-click Logins, and select New Login from the shortcut menu.
-
On the Login - New dialog box, enter the user account in the Login name text box.
-
Select SQL Server authentication, and create a password.
-
Select Server Roles under the Select a page heading to display the Server Roles list.
-
In the Server Roles list, select the sysadmin check box.
-
Click Ok.
The new user account created is displayed under Security > Logins of the target database server.
Link Target Database Server to Source Database Server
-
Connect to the target database server using the new user account you created. Change the password when prompted.
-
Expand the listing under for your source database server in Object Explorer.
-
Expand the listing under Server Objects.
-
Right-click Linked Servers, and select New Linked Server from the shortcut menu.
-
On the New Linked Server dialog box, enter the target database server name in the Linked Server text box.
-
Select SQL Server under Server Type.
-
Select Security under the Select a page heading to display security options.
-
Under For a login not defined in the list above, connections will:, select Be made using this security context:, and enter the new user account information you created.
-
Click Ok.
The target database server added is displayed under Server Objects > Linked Servers of the source database server.
Test Connection
Right-click the target database server that you linked to the source database server, and select Test Connection from the shortcut menu.
"The test connection to the linked server succeeded." dialog box is displayed.