Search code examples
sql-serverlinked-server

SQL Server linked server configuration

I want to create a connection from my local SQL Server to the production enviroment SQL Server so that I can perform certain tasks.

is that possible ?

Solution

  • Yes it is possible. What you need to do is to setup a linked server environment. Assuming the prod environment you are referring to uses uses SQL Server, you can set it up as follows: 

    USE [master]  
GO  
EXEC master.dbo.sp_addlinkedserver   
    @server = N'YourServerName\InstanceNameIfAny',   
    @srvproduct=N'SQL Server' ;  
GO

    To test the linked server you just setup, run this: 

    SELECT name FROM [YourServerName\InstanceNameIfAny].master.sys.databases

    This will return the names of the databases on the linked server.

    If you are more comfortable with GUI, here is what you can do instead:

    In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”)