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?

  • Is SSIS an option for you ? Instead of linked server, it would be much better option.
    – Kin Shah
    Apr 13 '16 at 15:17
  • Or the 'Export Data' (still SSIS technically) option in SSMS? Apr 13 '16 at 15:20

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.

  • so, I need to create linked server between [acceptance---->listener] or [Acceptance---->primary node]?
    – Dom
    Apr 13 '16 at 16:10
  • create a linked server to Acceptance on server1, server2, server3. Connect to Production listener and run script importing data from Acceptance to Production.
    – IanG
    Apr 13 '16 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.