I had three servers(server1, server2, server3) in availability group. And the listener name is "Production". I have a server name "Acceptance". I need to run a script that which will copy the tables from acceptance to production. Unfortunately, due to some reasons the only option left with me is running the script by linking the acceptance server with production.
1.How can I link the "acceptance" with "Production"?
2.Is it same as just linking 2 servers or is there anything different to link a server with the listener of the availability group?
3.If we copy the tables by linking server with listener, is these tables gonna save in all the three servers which are in availability group?
Best Answer
You will need to create an identical linked server on each of your 3 Availability Group nodes (server1, server2, server3). Linked servers (like logins, alerts, and jobs) are instance level objects so do not sync between the Availability Group nodes. You need one on each node in case of a failover, and you need to keep them in sync manually if they ever change.
Once you import the data, referencing the linked server in your script on your primary node (Production listener) and preferably with a synonym, it will sync to the secondary node DBs in the Availability Group automatically. Synonyms are a Best Practice when using linked servers. If your linked server name changes you just update the linked server, not the code that imports the data.