SQL server redirect
-
Hi,
Is it possible to redirect all the incoming request on InstanceA\DatabaseXYZ to InstanceB\DatabaseXYZ ? if yes how? What sorts of permission do we need to do this - I ahev sysadmin right on both server.
Background - We are decommissioning an old server and moving all the databases to a new server. But the application team needs sometime to change the db connection string as they need to reinstall it on all the systemsShould I set up a linked server connection? or Alias - or is there a different way to achieve this?
I tried setting up alias on old server and pointed to new server but I get login errors - although I'm sys admin.... is this the way?
https://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/Old SQL Instance - SQL server 2008
New Sql Instance - SQL server 2014Thanks for your help & suggestions.
S
- proxybon 7787 - Thursday, September 13, 2018 9:33 AM
Off the top of my head....two ways to solve this.
1.
DNS
(assumption that InstanceA and InstanceB are on different physically named machines/VMs)
Drop A-record for server where InstanceA resides and create CName for server where InstanceA resides pointing to Server IP for InstanceB.
No changes will be needed for applications pointing to the old server. They will automatically redirect to the new server.2.
SQL Server Config Alias (kinda like DNS but for SQL Server CLIENT connections only)
Go into the SQL server Configuration manager for each CLIENT and create Aliases pointing Server/InstanceA to Server1/InstanceB (again assuming instanceA and instanceB are on different physical machines/VMs)
NOTE this only works on the machine that you set this configuration on (thus the name SQL Native CLIENT xx Configuration.
Don't forget to do this for both the 32 and 64 bit client configuration(s).
As a final mention I would offline the databases on the oldserver just in case you have some legacy applications using IP based connections to your old databases. You want them to fail so someone asks "Hey my stuff isnt working..." so you can get it fixed. - Smendle - Thursday, September 13, 2018 10:02 AMproxybon 7787 - Thursday, September 13, 2018 9:33 AM
Off the top of my head....two ways to solve this.
1.
DNS
(assumption that InstanceA and InstanceB are on different physically named machines/VMs)
Drop A-record for server where InstanceA resides and create CName for server where InstanceA resides pointing to Server IP for InstanceB.
No changes will be needed for applications pointing to the old server. They will automatically redirect to the new server.2.
SQL Server Config Alias (kinda like DNS but for SQL Server CLIENT connections only)
Go into the SQL server Configuration manager for each CLIENT and create Aliases pointing Server/InstanceA to Server1/InstanceB (again assuming instanceA and instanceB are on different physical machines/VMs)NOTE this only works on the machine that you set this configuration on (thus the name SQL Native CLIENT xx Configuration.
Don't forget to do this for both the 32 and 64 bit client configuration(s).
As a final mention I would offline the databases on the oldserver just in case you have some legacy applications using IP based connections to your old databases. You want them to fail so someone asks "Hey my stuff isnt working..." so you can get it fixed.Thanks Smendle this is exactly what I was looking for.
I followed all the steps you mentioned but I run into this error - Cannot open the database requested by the user...- login failed this user has admin rights on the server & database - proxybon 7787 - Thursday, September 13, 2018 10:48 AMSmendle - Thursday, September 13, 2018 10:02 AMproxybon 7787 - Thursday, September 13, 2018 9:33 AM
Off the top of my head....two ways to solve this.
1.
DNS
(assumption that InstanceA and InstanceB are on different physically named machines/VMs)
Drop A-record for server where InstanceA resides and create CName for server where InstanceA resides pointing to Server IP for InstanceB.
No changes will be needed for applications pointing to the old server. They will automatically redirect to the new server.2.
SQL Server Config Alias (kinda like DNS but for SQL Server CLIENT connections only)
Go into the SQL server Configuration manager for each CLIENT and create Aliases pointing Server/InstanceA to Server1/InstanceB (again assuming instanceA and instanceB are on different physical machines/VMs)NOTE this only works on the machine that you set this configuration on (thus the name SQL Native CLIENT xx Configuration.
Don't forget to do this for both the 32 and 64 bit client configuration(s).
As a final mention I would offline the databases on the oldserver just in case you have some legacy applications using IP based connections to your old databases. You want them to fail so someone asks "Hey my stuff isnt working..." so you can get it fixed.Thanks Smendle this is exactly what I was looking for.
I followed all the steps you mentioned but I run into this error - Cannot open the database requested by the user...- login failed this user has admin rights on the server & databaseIs the login on the new server?
are you trying to connect from the old server? Remember the client configuration must be done on EACH machine you want to connect to the new server with. - Smendle - Thursday, September 13, 2018 10:58 AMproxybon 7787 - Thursday, September 13, 2018 10:48 AMSmendle - Thursday, September 13, 2018 10:02 AMproxybon 7787 - Thursday, September 13, 2018 9:33 AM
Off the top of my head....two ways to solve this.
1.
DNS
(assumption that InstanceA and InstanceB are on different physically named machines/VMs)
Drop A-record for server where InstanceA resides and create CName for server where InstanceA resides pointing to Server IP for InstanceB.
No changes will be needed for applications pointing to the old server. They will automatically redirect to the new server.2.
SQL Server Config Alias (kinda like DNS but for SQL Server CLIENT connections only)
Go into the SQL server Configuration manager for each CLIENT and create Aliases pointing Server/InstanceA to Server1/InstanceB (again assuming instanceA and instanceB are on different physical machines/VMs)NOTE this only works on the machine that you set this configuration on (thus the name SQL Native CLIENT xx Configuration.
Don't forget to do this for both the 32 and 64 bit client configuration(s).
As a final mention I would offline the databases on the oldserver just in case you have some legacy applications using IP based connections to your old databases. You want them to fail so someone asks "Hey my stuff isnt working..." so you can get it fixed.Thanks Smendle this is exactly what I was looking for.
I followed all the steps you mentioned but I run into this error - Cannot open the database requested by the user...- login failed this user has admin rights on the server & databaseIs the login on the new server?
are you trying to connect from the old server? Remember the client configuration must be done on EACH machine you want to connect to the new server with.Configuration is on old server (so that any incoming request should be redirected to new server)
Is the login on the new server?
Me: User account has login on both server.
Me: Using a dot net application I'm sending request (read table data) to old server so that it can redirect to new server.are you trying to connect from the old server?
Me: Yes, Using a dot net application I'm sending request (read table data) to old server so that it can redirect to new server.Remember the client configuration must be done on EACH machine you want to connect to the new server with.
Me:Thanks - I have only 1 old server and 1 new server. -
ensure you are using the sql native client driver
- proxybon 7787 - Thursday, September 13, 2018 11:31 AMSmendle - Thursday, September 13, 2018 10:58 AMproxybon 7787 - Thursday, September 13, 2018 10:48 AMSmendle - Thursday, September 13, 2018 10:02 AMproxybon 7787 - Thursday, September 13, 2018 9:33 AM
Off the top of my head....two ways to solve this.
1.
DNS
(assumption that InstanceA and InstanceB are on different physically named machines/VMs)
Drop A-record for server where InstanceA resides and create CName for server where InstanceA resides pointing to Server IP for InstanceB.
No changes will be needed for applications pointing to the old server. They will automatically redirect to the new server.2.
SQL Server Config Alias (kinda like DNS but for SQL Server CLIENT connections only)
Go into the SQL server Configuration manager for each CLIENT and create Aliases pointing Server/InstanceA to Server1/InstanceB (again assuming instanceA and instanceB are on different physical machines/VMs)NOTE this only works on the machine that you set this configuration on (thus the name SQL Native CLIENT xx Configuration.
Don't forget to do this for both the 32 and 64 bit client configuration(s).
As a final mention I would offline the databases on the oldserver just in case you have some legacy applications using IP based connections to your old databases. You want them to fail so someone asks "Hey my stuff isnt working..." so you can get it fixed.Thanks Smendle this is exactly what I was looking for.
I followed all the steps you mentioned but I run into this error - Cannot open the database requested by the user...- login failed this user has admin rights on the server & databaseIs the login on the new server?
are you trying to connect from the old server? Remember the client configuration must be done on EACH machine you want to connect to the new server with.Configuration is on old server (so that any incoming request should be redirected to new server)
Is the login on the new server?
Me: User account has login on both server.
Me: Using a dot net application I'm sending request (read table data) to old server so that it can redirect to new server.are you trying to connect from the old server?
Me: Yes, Using a dot net application I'm sending request (read table data) to old server so that it can redirect to new server.Remember the client configuration must be done on EACH machine you want to connect to the new server with.
Me:Thanks - I have only 1 old server and 1 new server.You cannot redirect the call this way...it is not possible to have ServerA receive a connection request (regardless of instance on that server) - and redirect that request to ServerB.
From ClientA where your application code is running - you need to connect to ServerB, not ServerA. The only way to do that is to update DNS so that the name 'ServerA' points to the same IP address as 'ServerB', or modify the client making the call so it resolves to correct IP address.
You can do that with an entry to the hosts file - or create an alias on every workstation\server.
Note: if updating DNS you must shut down ServerA or change its name as you cannot have the same name associated with different IP addresses.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams 3188 - Thursday, September 13, 2018 12:39 PMproxybon 7787 - Thursday, September 13, 2018 11:31 AMSmendle - Thursday, September 13, 2018 10:58 AMproxybon 7787 - Thursday, September 13, 2018 10:48 AMSmendle - Thursday, September 13, 2018 10:02 AMproxybon 7787 - Thursday, September 13, 2018 9:33 AM
Off the top of my head....two ways to solve this.
1.
DNS
(assumption that InstanceA and InstanceB are on different physically named machines/VMs)
Drop A-record for server where InstanceA resides and create CName for server where InstanceA resides pointing to Server IP for InstanceB.
No changes will be needed for applications pointing to the old server. They will automatically redirect to the new server.2.
SQL Server Config Alias (kinda like DNS but for SQL Server CLIENT connections only)
Go into the SQL server Configuration manager for each CLIENT and create Aliases pointing Server/InstanceA to Server1/InstanceB (again assuming instanceA and instanceB are on different physical machines/VMs)NOTE this only works on the machine that you set this configuration on (thus the name SQL Native CLIENT xx Configuration.
Don't forget to do this for both the 32 and 64 bit client configuration(s).
As a final mention I would offline the databases on the oldserver just in case you have some legacy applications using IP based connections to your old databases. You want them to fail so someone asks "Hey my stuff isnt working..." so you can get it fixed.Thanks Smendle this is exactly what I was looking for.
I followed all the steps you mentioned but I run into this error - Cannot open the database requested by the user...- login failed this user has admin rights on the server & databaseIs the login on the new server?
are you trying to connect from the old server? Remember the client configuration must be done on EACH machine you want to connect to the new server with.Configuration is on old server (so that any incoming request should be redirected to new server)
Is the login on the new server?
Me: User account has login on both server.
Me: Using a dot net application I'm sending request (read table data) to old server so that it can redirect to new server.are you trying to connect from the old server?
Me: Yes, Using a dot net application I'm sending request (read table data) to old server so that it can redirect to new server.Remember the client configuration must be done on EACH machine you want to connect to the new server with.
Me:Thanks - I have only 1 old server and 1 new server.You cannot redirect the call this way...it is not possible to have ServerA receive a connection request (regardless of instance on that server) - and redirect that request to ServerB.
From ClientA where your application code is running - you need to connect to ServerB, not ServerA. The only way to do that is to update DNS so that the name 'ServerA' points to the same IP address as 'ServerB', or modify the client making the call so it resolves to correct IP address.
You can do that with an entry to the hosts file - or create an alias on every workstation\server.
Note: if updating DNS you must shut down ServerA or change its name as you cannot have the same name associated with different IP addresses.
Jeff is correct. I missed the 'redirect' part of your post, well I saw it, read it, but wasn't thinking about it.
The DNS way as I stated above will work for what you want.
The Client Configuration Alias is only for connections INTIATED from that same client machine. Sorry about my dis-information there.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply