November 17, 2011 at 3:32 am
We have a requirement to create similar health check jobs for 20 servers now which are in a clustered environment .
Is there any better option than to create than to create it individually for all 20 servers ?
Is it pos to create jobs and schedule it in the registered servers panel ?
Please suggest .
\m/
November 17, 2011 at 3:52 am
are all the health checks different for each server?
if they are then it would be a case of creating the individual jobs on each server
if not you could look at a central management server, to which you can register your servers and execute the same queries across the board, this would allow you to create the same jobs with the same commands and schedules, but you would have to do it in T-SQL as I dont think jobs created on a CMS replicate to its registered members
CSM http://msdn.microsoft.com/en-us/library/bb934126.aspx
if your running 2k8 R2 across the board, you could impliment utility control point, which is the management data warehouse of 2005 and 2008, but centralised, otherwise it would be a case of configuring MDW on each server and pulling the information into a centralised area if you wanted to do estate reporting
November 17, 2011 at 4:18 am
Its helpful to get the results of a query for n number of instances in a registered server list or as you mention in Central management servers list .
But i need to create a job and schedule it on a daily basis .
I think creating policies might help but its getting complex out here 🙁
Thanks
November 17, 2011 at 4:31 am
You could use multi server jobs, see a tutorial here:
http://www.sqlservercentral.com/articles/Administration/multiserveradministration/675/
Ive tried them but found them a bit buggy and dont have time to resolve all the bugs.
Ensure you set the security correctly or they wont work:
http://msdn.microsoft.com/en-us/library/ms365379.aspx
I had issues creating job steps also (com errors) that i dont have time to resolve.
Another method would be:
1. create the job on one of the servers.
2. script out the job and all components necessary to run the job.
3. via registered servers, run the script against all of the servers in question.
4. via another query against the reistered servers, you can kick off the job with sp_start_job sproc.
November 17, 2011 at 4:35 am
You can create a job via the central management server across all servers which are members of the CMS, but you need to do it in T-SQL, you cant use the wizard to create the job and push it to 20 servers.
One thing you can do is on one server, generate the job using the wizard, then have it script to a new query window, that way you have the script that you can execute against all of the servers in CMS.
I dont know about bulk upload of policies but there is the import policy option in CMS where you can give it the XML file of the policies you want to send to the servers, but as I have never done this, I cant say if it does upload to all member servers or just to the one you do the import task on.
February 28, 2012 at 1:23 am
Create a linked server for all your connected servers.
Create job in central server using Cursor.
BEGIN
SET NOCOUNT ON;
Declare @LinkedServer varchar(20)
DECLARE server_name_cursor Cursor READ_ONLY For
SELECT name from sys.servers
Declare @cur_stat_Server smallint
select @cur_stat_Server = CURSOR_STATUS('global','server_name_cursor')
if @cur_stat_Server = -1
OPEN server_name_cursor
Try_ServerName:
FETCH Next From server_name_cursor Into @LinkedServer
Begin Try
WHILE @@Fetch_status =0
Begin
Declare @Query nvarchar(2000)
set @Query = 'your code here'
exec sp_executesql @Query
Fetch Next from server_name_cursor Into @LinkedServer
End
End Try
Begin Catch
GoTo Try_ServerName
End Catch
CLOSE server_name_cursor
DEALLOCATE server_name_cursor
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply