Many developers working with SQL Server Management Studio (SSMS) 2008 R2 and later versions face a common scenario: their stored procedures reference linked servers by IP address (like 192.168.xxx.xxx) during development, but need to point to different server names (like 'myserver') in production environments.

While you could modify DNS or hosts files, this approach has limitations:

• Requires administrative privileges
• Affects all applications on the machine
• Doesn't solve the problem for other team members

SQL Server provides a native way to create logical names for linked servers:

-- First, remove the existing linked server if it exists
IF EXISTS (SELECT 1 FROM sys.servers WHERE name = 'OriginalIPLinkedServer')
EXEC sp_dropserver 'OriginalIPLinkedServer', 'droplogins'

-- Create the linked server with your desired alias name
EXEC sp_addlinkedserver
    @server = 'myserver', -- This is your alias
    @srvproduct = 'SQL Server',
    @provider = 'SQLNCLI',
    @datasrc = '192.168.xxx.xxx' -- The actual IP or server name

After creating the alias, you need to ensure login mappings work:

-- Add login mapping (assuming Windows authentication)
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'myserver',
    @useself = 'true',
    @locallogin = NULL

-- For SQL authentication:
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'myserver',
    @useself = 'false',
    @locallogin = NULL,
    @rmtuser = 'remote_username',
    @rmtpassword = 'remote_password'

Verify the alias works with a simple distributed query:

-- Test the linked server alias
SELECT TOP 10 * FROM myserver.master.sys.databases

If your stored procedures reference objects across databases on the linked server, use four-part naming with your alias:

-- Example using the alias in a four-part name
SELECT * FROM myserver.AdventureWorks.HumanResources.Employee

When moving to production:

• Script all linked server definitions (including your aliases)
• Document any special security requirements
• Test thoroughly in a staging environment first

In SQL Server 2016 and later, consider using synonyms for more flexibility:

-- Create a synonym that abstracts the linked server reference
CREATE SYNONYM dbo.RemoteEmployees 
FOR myserver.AdventureWorks.HumanResources.Employee

-- Now your code can just reference:
SELECT * FROM dbo.RemoteEmployees

When working with SQL Server linked servers in development environments, we often face this scenario: Your stored procedures reference a linked server by its IP address (e.g., 192.168.1.100), but for production deployment, you need these references to point to a friendly server name (e.g., "ProdDBServer"). Hardcoding IP addresses in procedures creates maintenance headaches and deployment risks.

SQL Server provides a native way to handle this through the sp_addlinkedserver and sp_setnetname system stored procedures. Here's the technical approach:

-- Step 1: Create the linked server (if not already exists)
EXEC sp_addlinkedserver 
    @server = 'MyServerAlias', 
    @srvproduct = '',
    @provider = 'SQLNCLI', 
    @datasrc = '192.168.xxx.xxx';

-- Step 2: Map the network name
EXEC sp_setnetname 'MyServerAlias', '192.168.xxx.xxx';

-- Step 3: Add login mapping (if needed)
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'MyServerAlias',
    @useself = 'false', 
    @locallogin = NULL,
    @rmtuser = 'remote_user',
    @rmtpassword = 'password';

For a more infrastructure-level solution, you can:

1. Create a DNS CNAME record mapping "MyServer" to the IP address
2. Or modify the Windows hosts file (C:\Windows\System32\drivers\etc\hosts):

192.168.xxx.xxx   MyServer  # Development SQL Server

If you can't modify the linked server definition, consider dynamic SQL in your procedures:

DECLARE @LinkedServerName NVARCHAR(50) = 
    CASE WHEN @@SERVERNAME = 'DEV-SERVER' 
         THEN '192.168.xxx.xxx' 
         ELSE 'ProdServerName' 
    END;

EXEC('SELECT * FROM [' + @LinkedServerName + '].MyDB.dbo.MyTable');

• Test thoroughly after making changes to linked server configurations
• Document all aliases in your deployment runbooks
• Consider using SQL Server synonyms for object-level references
• For complex environments, investigate SQL Server Central Management Servers

After implementation, verify with:

-- Check linked servers
EXEC sp_linkedservers;

-- Test connectivity
SELECT TOP 1 * FROM MyServerAlias.master.dbo.sysdatabases;