Redirect Connections to a SQL Instance

Question

I have a LAN in which clients connect to the SQL Server installed on a server with the IP address of 192.168.1.10 (SERVER_A).

I have another SQL Server instance installed on another server with the IP address of 192.168.3.11 (SERVER_B).

The servers are routed to each other so I can connect to one instance by SSMS from the other server.

The problem is I need to connect from the clients in the LAN (192.168.1.0) to the instance installed on SERVER_B.

Is there a way to create an alias instance on SERVER_A which points to the instance installed on SERVER_B? So I can connect to SERVER_A and it redirects me to SERVER_B?

I have an application and I can't change how it talks to the database. So I have to create all the Tables, Views and SPs again on SERVER_A and redirect them to tables, views, and SPs on SERVER_B. I want to use something like aliasing. However the problem is I can connect to the instance locally but not remotely when I use alias instance name.

Answer 1

You can setup a linked server on SERVER_A to SERVER_B and access the databases using four part naming (SERVER_B.database.schema.table). See How to Create a Linked Server in the documentation.

You can also just add a secondary IP address to SERVER_B.

If Server_B is on the same VLAN as Server_A, you can add an alias to its IP address at the client level. But what you want to do is not very feasible.