July 19, 2017 at 11:35 am
We have a nightly refresh job that updates our database. In order to ensure that there are no queries running when the refresh starts, it's desired to shut down an application service running on a remote server that may be actively using the database.
I could simply kill any active connections, but what I usually see is that services using the database will often re-spawn connections and so killing connections on the SQL side seems to not be a consistent solution.
Instead, we desire to shut down the service itself, thereby preventing the application from hitting the db server at all, so it can focus on the refresh job.
I originally was just adding PowerShell steps to my agent job at the beginning to stop the service on the remote server, and the last step to restart the service. Of course, in order to do such things remotely, there is some WSMAN configuration, etc.. necessary. Not difficult, but I realize that this creates a bad precedent for remotely starting and stopping services. If it's as poorly documented as all our other stuff, it's going to be pretty mysterious for whoever is troubleshooting the server with services that seem to start and stop without explanation.
I decided that I would redo the solution so that there will be more visibility into the issue, so that at least there are a few clues for the next guy..
So what I'm doing is to generate events in the application log on the remote server, where task scheduler objects with event log triggers will do the actual stopping and starting of the service. The end result is that there will be log entries accompanying the service state changes, complete with the source of the log entries (the remote SQL server), and the tasks that actually start and stop the service are on the local machine (and could be disabled there if the action is not desired, whether temporarily or permanently).
I tried using xp_logevent, but that always generates the same event ID (17061), so it's not really useful for this since you can only trigger a single action, in this case, and I need 2 actions: start and stop service..
Instead, I can run a PowerShell command in my Agent Job. Also, I've had some problems using the "PowerShell" job step type, so instead, I'm using an "Operating system (CmdExec)" step type, and giving it the PowerShell executable and the script as an argument. That seems to work better on all server versions.
I created a new application log source using PowerShell with the following command (I was able to run this command remotely):
New-EventLog -ComputerName AppServer.mydomain.com -LogName Application -Source TSQL_EVENT
Then I created a Task Scheduler task. In case you are unfamiliar with Task Scheduler, the account being used to run the script needs permissions to use the PowerShell executable and the also permission to start/stop services and also needs to be added to the group policy "Log on as a batch job":
GPEDIT.msc --> Computer Config->Windows Settings->Security Settings->Local Policies->User Rights Assignment->Log on as a batch job
I configured the task trigger on the event:
Log: Application
Source: TSQL_EVENT
Event ID: 1002
And I configured the task action to stop the service using a PowerShell command:
Program/Script: "C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe"
Argument: "Stop-Service 'MSSQLServerOLAPService'"
Then I added the step to my Agent Job:
sp_add_jobstep @subsystem=N'CmdExec'
,@command=N'Write-EventLog -ComputerName AppServer.mydomain.com -LogName Application -Source TSQL_EVENT -EntryType Information -EventId 1002 -Message "Stop OLAP Service"'
I did basically the same thing to start services, creating a separate task for that, and adding a final job step to my agent job to put in a different event ID that kicks the start service task.
Please let me know what you think of this solution.
July 27, 2017 at 4:32 pm
On the Server A sql agent job, do a remote call to trigger the task on Server B.
Assuming it's a service, you can even use a remote call to check to see if the process is still running before continuing with the nightly build.
Not sure if that is enough exposure when the job starts and stops, but it'll get logged to the Event logs already.
July 28, 2017 at 6:58 pm
Or maybe you could have a script that stops the services then starts the job instead of having the job stop the services.
July 29, 2017 at 6:58 am
Enabling a Task Scheduler job does not trigger it to run. You would have to enable the job and you would still have the problem of trying to trigger it. The only way to trigger a task scheduler job to fire on call is with an event log event being recorded.
Consider that you will not always be the one who ends up troubleshooting systems. If an systems analyst was looking at a server and saw the service starting and stopping, with no explanation because the service was being started and stopped by something running remotely, then they would have to track down that system, and they would have little to go on. With the method I outlined above, that systems analyst can see the event log entries and also see the task scheduler job, and they would be able to disable the task scheduler job themselves to prevent the service state from being changed while they do their troubleshooting work.
July 29, 2017 at 7:14 am
As far as having a script on the remote server that starts/stops the service also run the refresh, that is more work because with SQL Agent Jobs, logging and failure notifications are built in and easy to configure. Also, I have the job configured to go on to the next step, even if the stop service step fails for any reason, so my data gets refreshed regardless of whether or not the remote service call works. It's also nice because I still get the failure notification, even though the job reports success at the end. If I were to do that with a script, then I would have to program all that into it, and if another DBA were covering for me, then he would have no idea how the refresh job is being called, nor how to find the logging.
In many cases, even if there is documentation available, the people troubleshooting system problems may not be aware of the documentation, and, if you are in a critical systems failure, you don't have much time to refer to documentation anyway, but if you have reasonable troubleshooting skills and you know your product well enough, the method above hopefully gives everyone involved the tools they need to understand the process and take appropriate actions. It's one of the reasons I get more sleep than most DBA's..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply