Two database exist on two different SQL Server instances. They are linked servers, but they cannot communicate due to network issues.

Someone solves the network issues, and connectivity is restored. I want to run a stored procedure as soon as possible after connectivity has been restored. How can I accomplish that?

  • 1
    What is the idea behind doing what you are ... there might be better solutions if you tell us the reason/idea about what you are doing ? – Kin Shah Mar 25 '15 at 21:32
  • Like I said in the question: "so that we can begin recovering any data that might not have made it across before the connection was lost". Is there a particular reason why you think recovering data might not be a useful thing to do? The data isn't exactly going to send itself across. – Rainbolt Mar 25 '15 at 21:33
  • 1
    Have you considered T-Replication or AlwaysON for DR ? I dont think pulling data using Linked server is a good option for data recovery. – Kin Shah Mar 25 '15 at 21:38
  • 1
    sp_testlinkedserver is the closest you can implement. Aaron has a good script here. You can schedule it using sql agent job - to check if the connection can be made or not and depending on failure or success it can alert. – Kin Shah Mar 25 '15 at 21:48
  • 1
    A linked server is not a persistent connection. It is only "up" when necessary. Your "sync" code just needs to have retry logic built in. If you describe what and why you're doing thIs, as @Kin asked, we might be more inclined to help provide a comprehensive solution. – Hannah Vernon Mar 25 '15 at 22:17

Your Answer

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

Browse other questions tagged or ask your own question.