How to create a linked server using SQL Server Management Studio (SSMS) and T-SQL.

To Creating a linked server in SQL Server allows you to access external data sources such as other SQL Server instances, or other OLE DB data sources(e.g MYSQL,ORACLE,PLSQL etc). Here is a step-by-step guide on.

1.Using SQL Server Management Studio (SSMS):

• Open SQL Server Management Studio (SSMS).
• Connect to the SQL Server instance where you want to create the linked server.
• In the Object Explorer, expand the Server Objects .
• Right-click on Linked Servers and select New Linked Server.
• In the New Linked Server dialog box:

• Linked server: Enter the name of the linked server.
• Server type: Select SQL Server or Other data source depending on the type of external data source.
• Data Source: Enter the name or network address of the SQL Server instance you want to link to.

For Other Data Source:

• Provider: Select the appropriate OLE DB provider.
• Product name: Enter the name of the product.
• Data source: Enter the network name of the external data source.
• Provider string: Optional connection string specific to the provider.
• Location: Optional location of the data.
• Catalog: The initial database to connect to.

Security Page:

• Configure the security settings for the linked server.
• You can choose to impersonate a login or specify remote login and password.
• Click OK to create the linked server.
• Right click on newly created linked server and test connection if success then connection created sucessfully else failed.

2.Using T-SQL:

Here is an example T-SQL script to create a linked server:

Linked server configuration:
EXEC sp_addlinkedserver
@server = 'test',
@srvproduct = '',
@provider = 'provider name',
@datasrc = 'Remote server name or ip address';

Linked server security configuration:
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'test', 
    @useself = 'false', 
    @locallogin = NULL, 
    @rmtuser = 'sa', 
    @rmtpassword = '*****';
Verify linked server:
SELECT * FROM sys.servers WHERE name = 'test';

Notes:

• Ensure the appropriate OLE DB(32 bit or 64 bit) provider is installed and configured on the SQL Server instance ..
• For security reasons, consider creating a SQL Server login with minimal privileges for use with the linked server.
• Test the linked server connection to ensure it works correctly(Right click and test connection).

By following these steps, you can create and configure a linked server in SQL Server, allowing you to query and interact with external data sources directly from your SQL Server session.