1

I connect to a VPN.

I open SSMS from my local computer.

I connect my local computer SSMS to the SQL instance on the VPN

I execute a long running SQL Update from my local computer, and during this a VPN error occurs.

All progress now with the SQL Update is lost.

Is there a way to use local computer to connect to remote SQL instance, and get the SQL Update to finish even though I get disconnected.

  • 4
    Can you have it run in an Agent job? – Erik Darling Mar 24 '17 at 6:39
  • That is a great idea - thank you, looking into it now – Peter PitLock Mar 24 '17 at 7:35
  • Cool, let me know if it works. – Erik Darling Mar 24 '17 at 11:47
  • Which authentication are you using windows or Sql? – McNets Mar 24 '17 at 12:24
  • @McNets Windows Authentication – Peter PitLock Mar 24 '17 at 12:30
2

As far as I know, if the connection between the computer issuing the query batch and the server executing the batch is broken, the server will terminate the batch.

As the comments have noted, your best solutions are executing the command from a computer that won't be likely to lose its connection; executing the commands from the server itself is, unsurprisingly, about as safe as you can get.

As sp_BlitzErik recommended, executing your batch from a SQL Server job is good, when the query can safely be run as a fully unattended process works, and shouldn't consume too much of the server's resources.

If a process needs to be monitored above and beyond simple success/failure on completion, then the most common solution is probably a remote desktop connection. Depending on our organization's security restrictions, you could hopefully be allowed to remote into another computer on the same LAN as the server (if not the server itself, if it wouldn't constitute too much of a resource drain).

If your VPN connection breaks, the session on the remote server keeps running - you just lose your visibility into it. When you re-establish your VPN connection, you can reconnect to the remote server, and see what progress has happened while you were disconnected.

As noted, you want to be sure that your remote connection is not consuming resources needed by your server, and that all necessary security concerns are dealt with. You do not necessarily need to be on the server itself; as I said, any computer on the same network as the server will do, as long as it has the software you need to issue your query.

Over the course of the past few years, I have worked on a few projects that required long weekends hours every other weekend, or two out of three even, for months. Remote desktop connections from home made life much easier (and was much more appropriate than basically living in my cubicle :-) ).

1

2 options:

Apart from the SQL Agent job (which is fine, as long as you do not have to follow the current status, nor debug), I use a desktop PC in the office, which is not far from the server room. I use Remote Desktop to work on the Desktop PC, when working from home via VPN.

In my previous job, we had a Citrix solution, which also worked great, as long as there are not too many upgrades to SSMS.

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.