Contents

2. Configure/Create Policies and Centralize on the Central Management Server. 4

3. Create Database and Database Objects To Store Policy Evaluation Results. 6

4. Configure PowerShell Script. 7

5. Test the PowerShell Script. 8

6. Create SQL Server Agent Job to Automate Execution of Policy Evaluation.. 9

7. Deploy Reports to SQL Server Reporting Services. 11

Appendix A: Additional Considerations. 15

Appendix B: Database Objects. 17

Appendix C: Upgrade from previous version.. 18

Introduction to EPM Framework

The Enterprise Policy Management Framework (EPM) is a solution to extend SQL Server Policy-Based Management to all versions of SQL Server in an enterprise, including SQL Server 2000 and SQL Server 2005. The EPM Framework will report the state of specified SQL Server instances against policies that define intent, desired configuration, and deployment standards.

When the Enterprise Policy Management Framework (EPM) is implemented, policies will be evaluated against specified instances of SQL Server through PowerShell. This solution will require at least one instance of SQL Server 2008. The PowerShell script will run from this instance through a SQL Server Agent job or manually through the PowerShell interface. The PowerShell script will capture the policy evaluation output and insert the output to a SQL Server table. SQL Server Reporting Services (2008 or above) reports will deliver information from the centralized table.

This solution requires the following components to be configured in your environment. All SQL Server requirements listed below may be executed from and managed on the same instance:

SQL Server (2008 or above) instance to store policies
SQL Server (2008 or above) instance to act as the Central Management Server
SQL Server (2008 or above) instance to execute the PowerShell script
SQL Server management database to archive policy evaluation results
SQL Server (2008 or above) Reporting Services to render and deliver policy history reports

Please refer to Microsoft documentation (Features Supported by the Editions of SQL Server) to determine the appropriate editions to support central management server, policy evaluation, SQL Server Agent, and Reporting Services. All components are supported on SQL Server Enterprise and SQL Server Standard.

This document identifies the steps to configure the Enterprise Policy Management Framework objects in a SQL Server environment.

Set up Central Management Server

The EPM Framework requires an instance of SQL Server (2008 or above) designated as a Central Management Server (CMS). The CMS will manage the logical groups of servers. The names of the logical groups are passed into variables in the Enterprise Policy Management Framework for policy evaluation against these groups.

If an instance has not yet been designated as a Central Management Server, execute the following steps:

Open SQL Server Management Studio. Select the View menu. Click Registered Servers.
In Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
In the New Server Registration dialog box, register the instance of SQL Server that you want to become the Central Management Server.
After a SQL Server (2008 or above) instance has been designated as a CMS, create the logical server groups and register SQL Server instances into these logical server group. SQL Server Books Online details the manual steps to register instances into logical server groups: Create a Central Management Server and Server Group. Figure 1 is an example of a Central Management Server (individual server groups and registered instances will differ based on each environment).

Figure 1

Configure/Create Policies and Centralize on the Central Management Server

All policies that will be evaluated using the EPM framework will be stored and managed on the SQL Server instance defined as the Central Management Server. The PowerShell script will loop through these policies during execution.

Policies stored on the Central Management Server should be configured to improve execution scale and minimize/eliminate false policy failures. The following are best practices for designing policies to scale with the EPM Framework.

Categorize policies

The EPM framework PowerShell script must be given a parameter for policy category (example: Microsoft Best Practices: Performance). Therefore all policies should be placed in a category to enable a scalable solution for policy evaluation over a large environment.

Ensure all policies are configured with appropriate server restrictions and defined targets.

Policies may not be relevant for all versions and/or editions of SQL Server. For example, a policy which checks that a database is enabled for Transparent Data Encryption will not be relevant on SQL Server 2000 or SQL Server 2005, or SQL Server 2008 editions other than Enterprise. Furthermore, this policy may not be relevant for every database on an instance. A policy that checks for existing number of data files might be more relevant for tempDB only. Define server restrictions on polices to eliminate evaluation failures due to incompatible editions. Define targets on polices to eliminate false failures for databases which are not relevant to the policy.

If at a given point in time you choose to stop collecting for a specific policy that belonged to a category YY, instead of just deleting that policy (which would not clear its previous result from the reports), we recommend you move it to a new category named “Disabled”, which you would not be collecting results on that category in any job. This way, not only will you keep the policy should you choose to use it again, but also the reports will exclude that specific category and any policies inside, including historic data on any policy inside that “Disabled” category.

Environments may wish to leverage the Best Practices Policies which are installed with SQL Server (Figure 2 below shows the SQL Server 2012 Policies setup) or available from the SQL Server 2008 R2 Feature Pack. If installed already, these policies may be imported to the Central Management Server – for example, SQL Server 2012 includes pre-configured best practice policies typically located at C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Policies\DatabaseEngine\1033. For further information on this topic, see How to: Export and Import a Policy-Based Management Policy.

Figure 2

If you choose to import these policies, you may want to run the script PBM_Fix_ImportedPolicies.sql afterwards for some needed actions. Please read the script notes to understand what is being changed.

Create Database and Database Objects To Store Policy Evaluation Results

The script EPM_Create_Database_Objects.sql will create the database to store the policy history, the policy schema, the tables, views, functions and stored procedure to support the framework. If installing on an instance that supports Database Compression, then Page Compression will be used in this database.

Open the SQLCMD script EPM_Create_Database_Objects.sql in SQL Server Management Studio.
Change the query execution to SQLCMD Mode. In the menu bar select Query -> SQLCMD Mode.
In the query, configure the variable ServerName with the name of the Central Management Server instance.
Configure the variable ManagementDatabase with the name of the database where the policy evaluation history will be created. See Figure 3.

:SETVAR ServerName “WIN2012”:SETVAR ManagementDatabase “MDW”GO:CONNECT $(ServerName)GO

Figure 3

Execute the script.

Policy evaluation results will be stored in a SQL Server database designated for database management purposes. Many environments may choose to use a Management Data Warehouse created for Data Collectors, but this is not required. Sizing this database will depend on how many policies are evaluated, how many properties are evaluated in each policy, how many instances are evaluated, and how long the historical data will be maintained.

Configure PowerShell Script

Update the EPM_EnterpriseEvaluation_4.ps1 PowerShell script variables for the enterprise environment.

Open EPM_EnterpriseEvaluation_4.ps1 in Notepad or a PowerShell script editor.
Update the variables identified below with your values. See Figure 4.

$CentralManagementServer: Required.Centralized location of the SQL Server instance where the policy evaluation history database is located. This is the instance and database where the policy evaluation results are written and policies are stored.

$HistoryDatabase: Required. Name of the history database where the policy evaluation results are written.

$ResultDir: Required. File location to write the policy evaluation results during the PowerShell execution. Results are written to this location temporarily.

# Declare variables to define the central warehouse# in which to write the output, store the policies$CentralManagementServer = “Win2012″$HistoryDatabase = “MDW”# Define the location to write the results of the policy evaluation$ResultDir = “E:\Results\”

Figure 4

Save the script in a folder which can be accessed by the scheduling process.

Test the PowerShell Script

Review the PowerShell script EPM_EnterpriseEvaluation_4.ps1. The scriptrequires three parameters. These parameters will support a granular evaluation strategy.

-ConfigurationGroup: Define the Central Management Server group to evaluate. If an empty string (“”), the PowerShell script will evaluate all servers registered in all of the CMS groups.

-PolicyCategoryFilter: Identifies which category of policies will be evaluated. If an empty string (“”), the PowerShell script will evaluate all polices stored on the Central Management Server.

-EvalMode: Specify the action to take during policy evaluation. Options are Check, Configure. Check will evaluate and report on the evaluation of the policy against the target. Configure will evaluate and report on the evaluation of the policy against the target, and reconfigure any deterministic options that are not in compliance with the policies.

To test the script prior to creating a job, open PowerShell in SQL Server Management Studio.

Open SQL Server Management Studio. Connect to the Central Management Server.
In Object Explorer, right-click on the Server and select “Start PowerShell”
Configure the following commands to your environment. Paste the commands to the SQL Server PowerShell console.

SL “Insert script folder location”.\EPM_EnterpriseEvaluation_4.ps1 -ConfigurationGroup “Insert Central Management Server Group” -PolicyCategoryFilter “Insert Policy Category” –EvalMode “Check”

When the script has completed, run the following statements against the database created in step 3. Results should appear from one or both views.

SELECT * FROM policy.v_PolicyHistoryGOSELECT * FROM policy.v_EvaluationErrorHistoryGO

Create SQL Server Agent Job to Automate Execution of Policy Evaluation

To create the SQL Server Agent job(s):

Open SQL Server Management Studio. Connect to the Central Management Server. Open SQL Server Agent.
Right-click the Jobs folder and select “New Job…”.
Name the job. In the left pane select “Steps”.
Select the “New…” at the bottom.
In the New Job Step window name the Job Step. Select PowerShell from the Type drop-down.
Select the appropriate proxy account in the “Run As” drop-down. This step is required if SQL Server Agent is not running with a domain account that has elevated rights on all instances. See Use a SQL Agent Proxy for Special Tasks for details on configuring a proxy account.
Enter the following PowerShell script in the command window. Replace the sample parameter values.

SL “E:\PowerShell Scripts\”.\EPM_EnterpriseEvaluation_4.0.0.ps1 -ConfigurationGroup “Production” -PolicyCategoryFilter “Microsoft Best Practices: Performance” –EvalMode “Check”

After SL, place the folder location where the EPM_EnterpriseEvaluation_4 is stored from step 4. In the next line, configure the parameters with a Central Management Server Group and a Policy Category. The above is an example which will evaluate all servers in the Production Group and subgroups with the policies in the category “Microsoft Best Practices: Performance”

Select OK to save the job step. See Figure 5 for an example of the job step.
In the New Job window, select “Schedule” in the left pane. Configure the schedule to meet the evaluation requirements.
Select OK to save job.

Figure 5

If you created one job per category, and you want several jobs to execute at once, keep in mind that some job sub systems do not allow more than 2 amount of simultaneous threads, such as the case of PowerShell. As such, you might want to update SQL Agent PowerShell simultaneous threads to a larger number, say 10, using the following example:

UPDATE msdb.dbo.syssubsystemsSET max_worker_threads = 10WHERE subsystem = N’Powershell’GO

Note: Running the above command requires a restart of the SQL Agent on SQL Server 2008 and SQL Server 2008R2.

Note2: The above no longer works in SQL Server 2012 or above, whereas the max_worker_thread value will assume its default value of 2 after a SQL Agent restart.

Deploy Reports to SQL Server Reporting Services

After the PowerShell script was successfully executed and the results of policy evaluation were collected in the management data warehouse, it is very convenient to visualize the data through Reporting Services reports. Steps below describe the process of report configuration and deployment.

Configure the Project properties with SSRS deployment options.

Double-click ..\ 2Reporting\PolicyReports.sln to open the PolicyReports project in Visual Studio, Business Intelligence Development Studio or SQL Server Data Tools.
In the right Solution Explorer pane, right-click PolicyDW.rds in the Shared Data Sources folder and select Open.
Configure the connection string to the Central Management Server instance and database which stores the policy history. Select OK to save.
Right click on the PolicyReports project and select Properties. See Figure 6. Set the appropriate TargetServerUrl, TargetReportFolder and TargetDataSourceFolder properties of the “PolicyReports” project. These will align with the SQL Server Reporting Services instance.

Figure 6

The visual indicators in the Last Execution Status table and the Failed Policy % By Month chart of the PolicyDashboard report will dynamically change color (see Figure 7) based on thresholds configured in hidden parameters of the report.

Figure 7

Double-click the PolicyDashboard report in the Reports folder to open.
In the menu, select View -> Report Data.
Open the Parameters folder in the left Report Data tab. See Figure 8.

Figure 8

Right-click PolicyThresholdWarning and select Parameter Properties.
In the Report Parameter Properties dialog (see Figure 9), select Default Values.

Figure 9

Change the value to the appropriate value for your environment. The default is 0.5 which will cause the report objects to use a Red color when the failed policy average falls below 50%. Select OK to save.
Right-click PolicyThresholdCaution and select Parameter Properties.
In the Report Parameter Properties dialog, select Default Values.
Change the value to the appropriate value for your environment. The default is 0.17 which will cause the report objects to use an orange color when the failed policy average falls between 17% and 50%. Select OK to save.
When the failed policy average falls between 17% a Yellow color is used.
In the right pane Solution Explorer, right-click on the PolicyReports project and select Deploy.

Appendix A: Additional Considerations

Security

When polices are evaluated through PowerShell, they will execute the policy evaluation in the context of the user issuing the evaluation. This account will require access to all instances and database objects the script will evaluate. The level of permissions will depend on what the policy is evaluating. This extends to the execution account used in a scheduling agent. In SQL Server, the SQL Server Agent job step executes in the context of a specific user. This user may be a proxy account. The account that is specified in the SQL Server Agent step must have access to all objects on all instances that the PowerShell script will be evaluating.

Prior to setting up the SQL Server Agent job, be sure to set up a Proxy Account that has the appropriate rights on the remote instances to evaluate the policies. See Use a SQL Agent Proxy for Special Tasks for details on configuring a proxy account.

Execution Strategies

Prior to creating the SQL Server Agent job(s), determine the execution strategy for the EPM framework PowerShell script EPM_EnterpriseEvaluation_4.ps1 based on scale, execution time and security. Create SQL Server Agent jobs to execute the policy evaluation according to the strategy.

Administrators managing larger environments may want to implement policy evaluation through multiple parallel jobs that guarantee evaluation completion in the desired scope of time. A large environment that is challenged with a small maintenance window to execute the evaluation may design an execution strategy where the total number of concurrent jobs equals the total number of cores available for the instance.

EPM_EnterpriseEvaluation_4.ps1 may be configured with multiple SQL Server Agent jobs for each policy category and/or each group of servers.
- EPM_EnterpriseEvaluation_4.ps1 may be configured with a single SQL Server Agent job and multiple steps for each policy category and/or each group of servers.

The script may also be configured to evaluate all policies against all instances (groups) by passing blank strings in as the –ConfigurationGroup and –PolicyCategoryFilter parameters. This configuration is not recommended for environments with a large number of instances and a large number of policies.

The EPM Framework version 4 supports nested server groups defined in the Central Management Server. Note in Figure 10 that the group “Production” has three nested groups. When a parent group is used as the parameter –ConfigurationGroup, all instances registered to the parent group and all instances registered to child groups will be included in the evaluation.

Figure 10

Appendix B: Database Objects

Tables

policy.PolicyHistory: Stores evaluation results from the PowerShell script. The XML results are shred into the table PolicyHistoryDetail. This data is not used for reporting and may be archived and/or purged as deemed necessary.

policy.PolicyHistoryDetail: Stores the shredded results from the EvaluationResults column in policy.PolicyHistory table. This table is the source for all policy result views.

policy.EvaluationErrorHistory: Stores the following types of errors:

Server connection errors
Evaluation process cannot write to the PolicyHistory table
Policy cannot evaluate against an object

Views

policy.v_PolicyHistory : Returns the full historical dataset. Depends on policy.PolicyHistoryDetail.

policy.v_PolicyHistory_Rank: Ranks the policy results for each object on each server by date. Depends on policy.v_PolicyHistory.

policy.v_PolicyHistory_LastEvaluation: Returns the last execution status for each policy evaluation against each object on each server. Depends on policy.v_PolicyHistory_Rank.

policy.v_EvaluationErrorHistory: Returns all evaluation errors from the EvaluationErrorHistory table combined with the PolicyHIstoryDetail table.

policy.v_EvaluationErrorHistory_LastEvaluation: Returns the ranked errors from the v_EvaluationErrorHistory view. The last error for each evaluation is returned when the query includes a filter on EvaluationOrderDesc = 1.

policy.v_ServerGroups: Returns the server groups. Used in reports to drive parameters.

Stored Procedure

policy.epm_Load_PolicyHistoryDetail: Executed during the PowerShell evaluation. Shreds the PolicyHistory XML results and stores in policy.PolicyHistoryDetail and policy.EvaluationErrorHistory.

Function

policy.pfm_ServerGroupInstances: Table-valued function to return the instances registered in a specified Central Management Server group.

Appendix C: Upgrade from previous version

Database Objects

If upgrading from the previous version of EPM, simply execute EPM_Upgrade_Database_Objects.sql to add or change the required objects, without loss of data to your current deployment.

PowerShell Script

Replace EPM_EnterpriseEvaluation_3.0.0.ps1 with EPM_EnterpriseEvaluation_4.ps1, and change the reference in all your jobs accordingly. Refer to step 4 (Configure PowerShell Script) for further information on the script usage.

Reports

Deploy the new reports as detailed in step 7 (Deploy Reports to SQL Server

PBM_Conditions

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Database_Maintenance’, @description=N”, @facet=N’IDatabaseMaintenanceFacet’, @expression=N’ Bool EQ 2 Bool DataAndBackupOnSeparateLogicalVolumes Bool True Bool 0 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Database_Performance’, @description=N”, @facet=N’IDatabasePerformanceFacet’, @expression=N’ Bool EQ 2 Bool CollationMatchesModelOrMaster Bool True Bool 0 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Database2012′, @description=N”, @facet=N’Database’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool EQ 2 Bool AutoClose Bool False Bool 0
Bool EQ 2 Bool AutoCreateStatisticsEnabled Bool True Bool 0

Bool EQ 2 Bool AutoShrink Bool False Bool 0

Bool EQ 2 Bool AutoUpdateStatisticsAsync Bool False Bool 0

Bool EQ 2 Bool AutoUpdateStatisticsEnabled Bool True Bool 0

Bool EQ 2 Bool CaseSensitive Bool False Bool 0

Bool EQ 2 Bool IsAccessible Bool True Bool 0

Bool EQ 2 Bool IsUpdateable Bool True Bool 0

Bool EQ 2 Numeric PageVerify Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.PageVerify String System.String Checksum

Bool EQ 2 Numeric RecoveryModel Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.RecoveryModel String System.String Simple

Bool GE 2 Numeric SpaceAvailable Numeric System.Double 1024

Bool EQ 2 Numeric UserAccess Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseUserAccess String System.String Multiple

Bool 1 Bool OR 2 Bool OR 2 Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseStatus String System.String Normal Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseStatus String System.String Restoring Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseStatus String System.String Standby
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Database2016+’, @description=N”, @facet=N’Database’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool EQ 2 Bool AutoClose Bool False Bool 0
Bool EQ 2 Bool AutoCreateIncrementalStatisticsEnabled Bool False Bool 0

Bool EQ 2 Bool AutoCreateStatisticsEnabled Bool True Bool 0

Bool EQ 2 Bool AutoShrink Bool False Bool 0

Bool EQ 2 Bool AutoUpdateStatisticsAsync Bool False Bool 0

Bool EQ 2 Bool AutoUpdateStatisticsEnabled Bool True Bool 0

Bool EQ 2 Bool CaseSensitive Bool False Bool 0

Bool EQ 2 Bool IsAccessible Bool True Bool 0

Bool EQ 2 Bool IsUpdateable Bool True Bool 0

Bool LE 2 Numeric MaxDop Numeric System.Double 8

Bool EQ 2 Numeric PageVerify Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.PageVerify String System.String Checksum

Bool EQ 2 Numeric RecoveryModel Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.RecoveryModel String System.String Simple

Bool GE 2 Numeric SpaceAvailable Numeric System.Double 1024

Bool EQ 2 Numeric UserAccess Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseUserAccess String System.String Multiple

Bool 1 Bool OR 2 Bool OR 2 Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseStatus String System.String Normal Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseStatus String System.String Restoring Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.DatabaseStatus String System.String Standby
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’DataFile2008′, @description=N”, @facet=N’DataFile’, @expression=N’ Bool AND 2 Bool AND 2 Bool GE 2 Numeric AvailableSpace Numeric System.Double 5120
Bool EQ 2 Numeric GrowthType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.FileGrowthType String System.String KB

Bool EQ 2 Bool IsOffline Bool False Bool 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’DataFile2012+’, @description=N”, @facet=N’DataFile’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool GE 2 Numeric AvailableSpace Numeric System.Double 5120
Bool EQ 2 Numeric GrowthType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.FileGrowthType String System.String KB

Bool EQ 2 Bool IsOffline Bool False Bool 0

Bool GE 2 Numeric VolumeFreeSpace Numeric System.Double 10240
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Endpoint’, @description=N”, @facet=N’Endpoint’, @expression=N’ Bool EQ 2 Numeric EndpointState Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.EndpointState String System.String Started ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’LogFile2008′, @description=N”, @facet=N’LogFile’, @expression=N’ Bool AND 2 Bool EQ 2 Numeric GrowthType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.FileGrowthType String System.String KB
Bool EQ 2 Bool IsOffline Bool False Bool 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’LogFile2012+’, @description=N”, @facet=N’LogFile’, @expression=N’ Bool AND 2 Bool AND 2 Bool EQ 2 Numeric GrowthType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.FileGrowthType String System.String KB
Bool EQ 2 Bool IsOffline Bool False Bool 0

Bool GE 2 Numeric VolumeFreeSpace Numeric System.Double 5120
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Login-Type’, @description=N”, @facet=N’Login’, @expression=N’ Bool OR 2 Bool OR 2 Bool EQ 2 Numeric LoginType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.LoginType String System.String SqlLogin
Bool EQ 2 Numeric LoginType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.LoginType String System.String WindowsUser

Bool EQ 2 Numeric LoginType Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.LoginType String System.String WindowsGroup
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’OS2008′, @description=N”, @facet=N’Server’, @expression=N’ Bool AND 2 Bool AND 2 Bool EQ 2 String OSVersion String System.String 6.1 (7601)
Bool GT 2 Numeric PhysicalMemory Numeric System.Double 8000

Bool GT 2 Numeric Processors Numeric System.Double 1
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’OS2012+’, @description=N”, @facet=N’Server’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool EQ 2 String OSVersion String System.String 6.3 (14393)
Bool GT 2 Numeric PhysicalMemory Numeric System.Double 8000

Bool GT 2 Numeric PhysicalMemoryUsageInKB Numeric System.Double 2000000

Bool GT 2 Numeric Processors Numeric System.Double 1
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Configuration’, @description=N”, @facet=N’IServerConfigurationFacet’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool EQ 2 Bool AllowUpdates Bool False Bool 0
Bool EQ 2 Bool DefaultBackupCompressionEnabled Bool True Bool 0

Bool EQ 2 Numeric NetworkPacketSize Numeric System.Double 4096

Bool EQ 2 Numeric UserConnections Numeric System.Double 0

Bool 1 Bool OR 2 Bool GE 2 Numeric FillFactor Numeric System.Double 90 Bool EQ 2 Numeric FillFactor Numeric System.Double 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Installation 2008′, @description=N”, @facet=N’IServerSetupFacet’, @expression=N’ Bool AND 2 Bool AND 2 Bool EQ 2 Numeric LoginMode Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServerLoginMode String System.String Mixed
Bool LIKE 2 String TempdbLogPath String System.String H%MSSQL10%MSSQL%Data%

Bool LIKE 2 String TempdbPrimaryFilePath String System.String H%MSSQL10%MSSQL%Data%
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Installation 2012′, @description=N”, @facet=N’IServerSetupFacet’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool LIKE 2 String DefaultFile String System.String E%MSSQL11%MSSQL%DATA%
Bool LIKE 2 String DefaultLog String System.String F%MSSQL11%MSSQL%DATA%

Bool EQ 2 Numeric LoginMode Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServerLoginMode String System.String Mixed

Bool LIKE 2 String TempdbLogPath String System.String H%MSSQL11%MSSQL%DATA%

Bool LIKE 2 String TempdbPrimaryFilePath String System.String H%MSSQL11%MSSQL%DATA%
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Installation 2016′, @description=N”, @facet=N’IServerSetupFacet’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool LIKE 2 String DefaultFile String System.String E%MSSQL13%MSSQL%DATA%
Bool LIKE 2 String DefaultLog String System.String F%MSSQL13%MSSQL%DATA%

Bool EQ 2 Numeric LoginMode Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServerLoginMode String System.String Mixed

Bool LIKE 2 String TempdbLogPath String System.String H%MSSQL13%MSSQL%DATA%

Bool LIKE 2 String TempdbPrimaryFilePath String System.String H%MSSQL13%MSSQL%DATA%
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Version 2008′, @description=N”, @facet=N’Server’, @expression=N’ Bool LE 2 Numeric VersionMajor Numeric System.Double 10 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Version 2012′, @description=N”, @facet=N’Server’, @expression=N’ Bool EQ 2 Numeric VersionMajor Numeric System.Double 11 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Version 2012+’, @description=N”, @facet=N’Server’, @expression=N’ Bool GE 2 Numeric VersionMajor Numeric System.Double 11 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Version 2016′, @description=N”, @facet=N’Server’, @expression=N’ Bool EQ 2 Numeric VersionMajor Numeric System.Double 13 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server Version 2016+’, @description=N”, @facet=N’Server’, @expression=N’ Bool GE 2 Numeric VersionMajor Numeric System.Double 13 ‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server2008′, @description=N”, @facet=N’Server’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool LIKE 2 String BackupDirectory String System.String G%Sqlbackup%Database%
Bool LIKE 2 String ErrorLogPath String System.String D%MSSQL10%MSSQL%Log%

Bool LIKE 2 String RootDirectory String System.String C:\Program Files\Microsoft SQL Server\MSSQL10%MSSQL%

Bool EQ 2 Bool IsCaseSensitive Bool False Bool 0

Bool EQ 2 Bool IsSingleUser Bool False Bool 0

Bool EQ 2 String Language String System.String English (United States)

Bool LIKE 2 String MasterDBLogPath String System.String D%MSSQL10%MSSQL%DATA%

Bool LIKE 2 String MasterDBPath String System.String D%MSSQL10%MSSQL%DATA%

Bool EQ 2 Numeric ServiceStartMode Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServiceStartMode String System.String Auto

Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServerStatus String System.String Online

Bool EQ 2 Bool TcpEnabled Bool True Bool 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server2012′, @description=N”, @facet=N’Server’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool LIKE 2 String BackupDirectory String System.String G%Sqlbackup%Database%
Bool LIKE 2 String DefaultFile String System.String E%MSSQL11%%MSSQL%DATA%

Bool LIKE 2 String DefaultLog String System.String F%MSSQL11%%MSSQL%DATA%

Bool LIKE 2 String ErrorLogPath String System.String D%MSSQL11%MSSQL%Log%

Bool LIKE 2 String RootDirectory String System.String C:\Program Files\Microsoft SQL Server\MSSQL11%MSSQL%

Bool EQ 2 Bool IsCaseSensitive Bool False Bool 0

Bool EQ 2 Bool IsSingleUser Bool False Bool 0

Bool EQ 2 String Language String System.String English (United States)

Bool LIKE 2 String MasterDBLogPath String System.String D%MSSQL11%MSSQL%DATA%

Bool LIKE 2 String MasterDBPath String System.String D%MSSQL11%MSSQL%DATA%

Bool EQ 2 Numeric ServiceStartMode Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServiceStartMode String System.String Auto

Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServerStatus String System.String Online

Bool EQ 2 Bool TcpEnabled Bool True Bool 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’Server2016′, @description=N”, @facet=N’Server’, @expression=N’ Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool AND 2 Bool LIKE 2 String BackupDirectory String System.String G%Sqlbackup%Database%
Bool LIKE 2 String DefaultFile String System.String E%MSSQL13%MSSQL%DATA%

Bool LIKE 2 String DefaultLog String System.String F%MSSQL13%MSSQL%DATA%

Bool LIKE 2 String ErrorLogPath String System.String D%MSSQL13%MSSQL%Log%

Bool LIKE 2 String RootDirectory String System.String C:\Program Files\Microsoft SQL Server\MSSQL13%MSSQL%

Bool EQ 2 Bool IsCaseSensitive Bool False Bool 0

Bool EQ 2 Bool IsSingleUser Bool False Bool 0

Bool EQ 2 String Language String System.String English (United States)

Bool LIKE 2 String MasterDBLogPath String System.String D%MSSQL13%MSSQL%DATA%

Bool LIKE 2 String MasterDBPath String System.String D%MSSQL13%MSSQL%DATA%

Bool EQ 2 Numeric ServiceStartMode Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServiceStartMode String System.String Auto

Bool EQ 2 Numeric Status Numeric Enum Numeric 2 String System.String Microsoft.SqlServer.Management.Smo.ServerStatus String System.String Online

Bool EQ 2 Bool TcpEnabled Bool True Bool 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N’SurfaceAreaConfiguration’, @description=N”, @facet=N’ISurfaceAreaFacet’, @expression=N’ Bool AND 2 Bool EQ 2 Bool RemoteDacEnabled Bool True Bool 0
Bool EQ 2 Bool XPCmdShellEnabled Bool False Bool 0
‘, @is_name_condition=0, @obj_name=N”, @condition_id=@condition_id OUTPUT
Select @condition_id

PBM Create Categories

use msdb
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2008’, 1, 13
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2012’, 1, 14
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2016’, 1, 15
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2019’, 1, 16
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2008_db’, 1, 17
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2012_db’, 1, 18
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2016_db’, 1, 19
go
EXEC [dbo].[sp_syspolicy_add_policy_category] ‘dbDooT-2019_db’, 1, 20
go

PBM_Policies

Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N’Database_2012_ObjectSet’, @facet=N’Database’, @object_set_id=@object_set_id OUTPUT
Select @object_set_id

Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N’Database_2012_ObjectSet’, @type_skeleton=N’Server/Database’, @type=N’DATABASE’, @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id