Microsoft SQL Server on Windows Azure Virtual Machines

The writers of this blog are a part of the Microsoft SQL Server team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Server components on Windows Azure Virtual machines

Scheduled Policy Evaluation on multiple servers using MSX/ TSX in SQL Agent

Scheduled Policy Evaluation on multiple servers using MSX/ TSX in SQL Agent

  • Comments 1

One of our customer was looking for ways to schedule a policy evaluation on multiple servers using their existing MSX/TSX infrastructure.  I thought it would be better to blog an article that could help others looking for similar information

Before we get into details, here are some basics.  Please read reference material. If you have any specific issues, send us an email / post comments in this blog entry.

SQL Agent MSX TSX  for distributing jobs to multiple servers  - http://msdn.microsoft.com/en-us/library/ms180992.aspx 

Policy Based Management - http://msdn.microsoft.com/en-us/library/bb510667.aspx 

SQL Powershell : http://msdn.microsoft.com/en-us/library/cc281954.aspx

SQL Agent  token substitution: http://msdn.microsoft.com/en-us/library/ms175575.aspx 

Note: T-SQL scrript to create a policy, condition and a job is there at http://blogs.msdn.com/cfs-file.ashx/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-01-40-41-Scripts/1057.EvaluatePolicy_5F00_MSXTSX.sql )

1) Create PBM Policy and condition:

Policy to check if “Integrated security”  the only security mode that is is enabled for SQL instance

Create a condition:

   1: -- Create a PBM condition 
   2: Declare @condition_id int
   3: EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Check if Integrated Security is enabled', @description=N'', @facet=N'IServerSecurityFacet', @expression=N'<Operator>, @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
   4:   <TypeClass>Bool</TypeClass>
   5:   <OpType>EQ</OpType>
   6:   <Count>2</Count>
   7:   <Attribute>
   8:     <TypeClass>Numeric</TypeClass>
   9:     <Name>LoginMode</Name>
  10:   </Attribute>
  11:   <Function>
  12:     <TypeClass>Numeric</TypeClass>
  13:     <FunctionType>Enum</FunctionType>
  14:     <ReturnType>Numeric</ReturnType>
  15:     <Count>2</Count>
  16:     <Constant>
  17:       <TypeClass>String</TypeClass>
  18:       <ObjType>System.String</ObjType>
  19:       <Value>Microsoft.SqlServer.Management.Smo.ServerLoginMode</Value>
  20:     </Constant>
  21:     <Constant>
  22:       <TypeClass>String</TypeClass>
  23:       <ObjType>System.String</ObjType>
  24:       <Value>Integrated</Value>
  25:     </Constant>
  26:   </Function>
  27: </Operator>'
  28: Select @condition_id
  29:  
  30: GO

Create a Policy, refer to condition created above

   1:  
   2:  
   3: -- Create a PBM policy
   4: Declare @object_set_id int
   5: EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'f_ObjectSet', @facet=N'IServerSecurityFacet', @object_set_id=@object_set_id OUTPUT
   6: Select @object_set_id
   7:  
   8: Declare @target_set_id int
   9: EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'f_ObjectSet', @type_skeleton=N'Server', @type=N'SERVER', @enabled=True, @target_set_id=@target_set_id OUTPUT
  10: Select @target_set_id
  11:  

 

2) Create SQL Agent Job

Create a SQL Agent  job  with a PowerShell script to evaluate policy;  use token substitution to replace server name , instance name during job execution

 

   1:  
   2: -- Create a job that evaluates policy
   3: USE [msdb]
   4: GO
   5:  
   6: /****** Object:  Job [PolicyCheck Job - Check if Integrated Security is enabled]    Script Date: 11/19/2010 11:35:00 AM ******/
   7: BEGIN TRANSACTION
   8: DECLARE @ReturnCode INT
   9: SELECT @ReturnCode = 0
  10: /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 11/19/2010 11:35:00 AM ******/
  11: IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
  12: BEGIN
  13: EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
  14: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  15:  
  16: END
  17:  
  18: DECLARE @jobId BINARY(16)
  19: EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'PolicyCheck Job - Check if Integrated Security is enabled', 
  20:         @enabled=1, 
  21:         @notify_level_eventlog=0, 
  22:         @notify_level_email=0, 
  23:         @notify_level_netsend=0, 
  24:         @notify_level_page=0, 
  25:         @delete_level=0, 
  26:         @description=N'No description available.', 
  27:         @category_name=N'[Uncategorized (Local)]', 
  28:         @owner_login_name=N'REDMOND\seths', @job_id = @jobId OUTPUT
  29: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  30: /****** Object:  Step [Check Policy]    Script Date: 11/19/2010 11:35:01 AM ******/
  31: EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check Policy', 
  32:         @step_id=1, 
  33:         @cmdexec_success_code=0, 
  34:         @on_success_action=1, 
  35:         @on_success_step_id=0, 
  36:         @on_fail_action=2, 
  37:         @on_fail_step_id=0, 
  38:         @retry_attempts=0, 
  39:         @retry_interval=0, 
  40:         @os_run_priority=0, @subsystem=N'PowerShell', 
  41:         @command=N'# Note update this variable for a different policy name'$(ESCAPE_SQUOTE(INST))'' -eq ''MSSQLSERVER'') {$instname = ''\DEFAULT''} ELSE {$instname = ''''};'SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))'' + $instname'Policy automation is not enabled on instance'' 'There were one or more policy evaluation failures. Please check agent logs and policy evaluation histories'' , 
  42: $policyToEvaluate = "Check if Integrated Security for SQL Server"
  43:  
  44: if ('
  45:  
  46: $policiesPSPath = '
  47:  
  48: # Check if policy Automation is enabled
  49: $policyManagement = Get-Item $policiesPSPath
  50: $policyManagement.Refresh()
  51:  
  52: if( $policyManagement.Enabled -eq $False)
  53: {
  54:     throw "Policy automation is not enabled on instance" 
  55: };
  56:  
  57: # Get specific policy and evaluate
  58: $result = $policyManagement.Policies |  where { $_.Name -eq $policyToEvaluate}   | Invoke-PolicyEvaluation -AdHocPolicyEvaluationMode 2 -TargetServerName $(ESCAPE_NONE(SRVR))
  59:  
  60: # if there were any failures throw
  61: if( $result.Result -eq $False)
  62: {
  63:     throw "There were one or more policy evaluation failures. Please check agent logs and policy evaluation histories"
  64: };
  65:  
  66: # print evaluation results
  67: $result
  68: $result.ConnectionEvaluationHistories
  69:  
  70: '
  71:         @database_name=N'master', 
  72:         @flags=48
  73: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  74: EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
  75: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  76: EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
  77: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  78: COMMIT TRANSACTION
  79: GOTO EndSave
  80: QuitWithRollback:
  81:     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
  82: EndSave:
  83:  
  84: GO
  85:  

 

Launch SQL Server Management Studio, in Object explorer verify if  policy, condition, job was created

image

 

3) Run SQL Agent job – Verify  job history and policy history

image

4) Configure MSX TSX – ( refer http://msdn.microsoft.com/en-us/library/ms180992.aspx )
5) Create Policy and condition on all servers. ( follow step #1) MSX TSX infrastructure does not distribute PBM policies
6) Set Agent job to be distributed to multiple servers – this sets the job as “multi-server” job.

If needed you can also associate a schedule. associated schedule will be distributesdto all Target servers

image

 

7) Verify job distribution on all target servers . On Target servers, distributed job will be read-only. If you need to make any changes you must do it on job that is in master server. all job changes will be replicated to all applicable target servers
image
8) Start the job on MSX (master) server
9) Check job logs on master server – You will see  one row per target server

For Demo purpose, “SEATTLE” instance was enabled with mixed mode security  and “MIRROR” instance was enabled with “Integrated Security”

image

 

10) Detailed Logs – connect to respective instance and look at the job history, policy history

 

 

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post
  • Looks great, and aims to do exactly what I want - but somehow your script doesn't appear to be quoted correctly.

    Would be great if it were functional...

    Cheers

    Rich

Page 1 of 1 (1 items)