Restart Services dependant on SQL on Remote Servers

[jmikow]

We have our central SQL Server 2000 SP3 installation that all of our application access. What we would like to have is a way for when our SQL Server Goes down and comes back up that it will send some sort of event to the other servers and restart the services that are dependent upon SQL. Is there a way to do this? We're just looking into it as we have had some issues with our SQL Server when we are not around and our website needs to be up 24/7 and we'd like for it to fix itself if we're not here. We're running SQL on a Windows 2000 Server machine.

Thanks,

Josh

 

[ClaireHCM]

Schedule job ,seperate stop service and start service into several steps in the steps select Type = "CmdExec",In the command for stopping mssqlserver service query

osql -SserverA -Q"xp_cmdshell 'net start mssqlserver'" -Usa -Pxxx
osql -SserverB -Q-Q"xp_cmdshell 'net start mssql$sqlserver2000'" -Usa -Pxxx


for starting mssqlserver just change stop to start

Same thing query for sqlserveragent and msdtc,mssearch....

I seperate start service and stop service into 2 steps.So if you have 3 services related sql server need to be stop and start.Totally come up 6 steps.But in one step can execute for all the servers

 

[jmikow]

Thanks for the example. I am slightly confused. Do the machines that are dependant upon the SQL server need to have an sql server on them, or will this simply stop any process I want on the remote machine?

Also, is there a way to schedule a process to be everytime the SQL Server starts? How would I detect that?

Josh

 

[ClaireHCM]

No...you can stop any services that are running on the remote server.
In command prompt type "net start" you will see all the existing services

There is a stored procedure called "sp_procoption" which wil allow you to execute the job whenever your sql server service starts

 

[jmikow]

I have been trying to do this but have been unable to stop a service on a remote computer.

I am running this from a Windows XP Pro machine, the remote is Windows 2000 Professional.

Remote Machine Name: collections
Service to Stop: messenger
SQL Server Name: josh

Here is what I am trying to run, either from a command prompt or a job:

osql -Scollections -Q"xp_cmdshell 'net stop messenger'" -Usa -Pxxx

I replace the xxx with the appropriate password.

I can get it to stop the local messenger service, but how do I tell it to stop the messenger service on the remote machine?

Josh

 

[ClaireHCM]

use osql -L to check all the existing servername
And try again.
Because for me ,it does work!

 

[jmikow]

I did an osql -L like you suggested. I realize I need to put the SQL servername after the -S. However, on the execute command part, how do I tell it to stop the messenger service that is on a remote computer, not the local one?

Josh

 

[ClaireHCM]

Login the QA of that remote server and execute "select @@servername" then put this servername afer -S.

osql -Sservername means it's going to execute on the server where you specify