LINKED SERVER problem with MSSQL server configured as HA

Category: sql server mirroring

Question

SoonPyo,Jeong on Thu, 22 Mar 2018 05:14:51


LINKED SERVER problem with MSSQL server configured as HA



1. Configure the server to which the HA configuration is connected.

  I have a problem and have switched to Standby, but the LINKED SERVER is not functioning abnormally.





I created it on the LINKED SERVER created by ATCIVE

If you perform a connection test on STANDBY

An error occurred while decrypting. (Microsoft SQL Server, Error: 15466).





If you attempt to create a new one, an error will occur as shown below.

Could not open database "XXX" requested from login. Login failed.

User "sa" could not sign in. (Microsoft SQL Server, error: 4060)



How do I use LINKED SERVER normally when HA configuration of MSSQL is done?



DB server information 2008R2.

Replies

Uri Dimant on Thu, 22 Mar 2018 06:30:46


Do you have AG configuration? Have you created a linked server on the secondary replica?

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence


SoonPyo,Jeong on Thu, 22 Mar 2018 07:35:32


no.
Active-Standby system.
This is the situation when TAKE OVER from ACTIVE to STANDBY.

A linked server is for connecting with Oracle.

Uri Dimant on Thu, 22 Mar 2018 07:53:46


>>>>Active-Standby system.

You need to create the same linked server on standby server as well

SoonPyo,Jeong on Thu, 22 Mar 2018 08:05:21


high availability is Storage is taken over from Active to Standby.
Because you use the same DB, you can not configure the same DB LINK separately.


Uri Dimant on Thu, 22 Mar 2018 08:31:13


What HA are you using? Log shipping? Mirroring?

SoonPyo,Jeong on Thu, 22 Mar 2018 08:43:23




Uri Dimant on Thu, 22 Mar 2018 09:32:54


Do you have SQL Server installed on Node2?

https://technet.microsoft.com/en-us/library/ms188716%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396


Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence


SoonPyo,Jeong on Thu, 22 Mar 2018 23:57:47


Of course!
It is installed in each node.

Uri Dimant on Mon, 26 Mar 2018 04:39:47


If you try to create a linked server on the second node what error are you getting?

SoonPyo,Jeong on Mon, 26 Mar 2018 05:19:11


I misunderstood the system configuration.
It is being used in an MCCS (HA) configuration and is being replicated to the standby server.

Uri Dimant on Mon, 26 Mar 2018 05:33:42


So your problem is solved?

SoonPyo,Jeong on Mon, 26 Mar 2018 05:43:14


I'm checking it now.