Sql-server – Query on Linked Server in SQL Active-Passive

sql serversql-server-2012windows-server

We have plan to implement Active-Passive SQL server cluster with shared storage with SQL 2014 version.
A-P: There will be two DB servers (DB1 and DB2) and one will be working (DB1) and the other (DB2) does not operate until there is any problem in the first one. Once the working cluster SQL server fails (fail – reboot/hardware failure/OS corruption, etc.), the other server (DB2) takes over from it and starts functioning as regular SQL server.

Just I want to know behaviour of linked server in A-P environmnet during below cases.

  1. We have configured linked server on DB1 (active node) to fetch remote SQL data. If we perform failover from DB1-DB2, Will linked server work automatically on DB2 active node? or Is there any manual modifications are required to work linked server properly?

  2. When we failback from DB2 to DB1, Will linked server work automatically on DB1 active node? or Is there any manual modifications are required to work linked server properly?

  3. Can we use SQL Virtual IP in linked server configuration in A-P environment?

Best Answer

  1. No extra configuration is needed for the linked server to work on DB2 and that's one of the Benefits of a Failover Cluster Instance:

In case of a failure (hardware failures, operating system failures, application or service failures), or a planned upgrade, the resource group ownership is moved to another WSFC node. This process is transparent to the client or application connecting to SQL Server and this minimize the downtime the application or clients experience during a failure. [...]

  • Zero reconfiguration of applications and clients during failovers
  1. Yes, it will work automaticaly on DB1 when it fails back and the reason is that:

Only one of the nodes in the FCI owns the WSFC resource group at a time.

  1. In fact you must use that virtual ip address or network name in order for the failover to work transparently:

An FCI runs in a WSFC resource group with one or more WSFC nodes. When the FCI starts up, one of the nodes assume ownership of the resource group and brings its SQL Server instance online. The resources owned by this node include:

  • Network name
  • IP address
  • Shared disks
  • SQL Server Database Engine service
  • SQL Server Agent service
  • SQL Server Analysis Services service, if installed
  • One file share resource, if the FILESTREAM feature is installed

This image from Derek Seaman's IT Blog might help you understand it better:

SQL 2012 Failover Cluster

You never configure your applications (or linked servers) to reference DB1 (NodeA) or DB2 (NodeB), you must always reference the virtual ip address or network name (VirtualNode1) and it shall transparently address the active node of the cluster.

Related Question