次の方法で共有

Have you heard of the EPM Framework?

  • [アーティクル]

EPM stands for Enterprise Policy Management, and it’s a framework designed with the objective of allowing a scalable use of PBM (Policy Based Management) feature. Also, I’m writing this post because the EPM Framework was just updated to v4.

Sometimes overlooked, PBM is an excellent tool for any DBA to keep track of any standards and policies enforcement in the SQL Server environment. While it was introduced back in SQL Server 2008, even in SQL Server 2014 it’s implementation is part of the product.

SQL Server has several default policies you can import, your own needs will dictate what policies you want to create for your SQL Server environment, and report on those. You may want to enforce a certain standard in object naming, or you may want to know when any sp_configure option has changed from what you deemed the standard in your environment, just to name a couple of examples. There’s little you can’t do with PBM – do keep in mind it’s NOT a monitoring solution, but rather an auditing solution (not to be confused with security auditing or the CDC feature, which have different goals).

A couple remarks usually come up when talking about PBM:

  • I still have some SQL Server 2000 and 2005 in my environment, so PBM is limited for me.
  • I have 300 SQL Server instances in my company, so PBM is not easy to use in my case.

If you’re still with me, that is why the EPM Framework came to be by the hand of Lara Rubbelke (Twitter | Blog) – aiming to extend PBM usage to SQL Server versions 2000 and 2005, as well as ease the task of using PBM in a large enterprise in a way that PBM itself wouldn’t be yet another task that the DBA has to handle in the many servers in the environment.

EPM Framework v4 is tested from SQL Server 2000 to 2014, and includes the following updates:

  • For enhanced support of large environments:
  • Reviewed database design, including views and indexing
  • Redesigned data load procedure

PowerShell execution now deletes XML files as soon as load is done – improves space usage on temp folder

Redesigned reports

Note that an upgrade script for all the relevant database objects is provided, supporting direct upgrade from v3. Please check the documentation for further information.

Please give this new version of EPM Framework a spin and let us know what you think.

We are also providing a set of scripts as an extension to the base set of Microsoft provided policies, and assumes the user has previously imported these Microsoft provided policies, as described by the "Configure/Create Policies and Centralize on the Central Management Server" section of the EPM Configuration Documentation. These extra policies include:

  • Determining the if SQL Server instances are at the recommended SP level. Note that the condition has to be updated with proper build numbers for the policy to be current.
  • Determining the if SQL Server instances are at the recommended CU or Hotfix. Note that the condition has to be updated with proper build numbers for the policy to be current.
  • Do I have log backups older than 24h?
  • Do I have full backups on read-write, full RM databases?
  • The Service accounts must not match between the several services, so what is the current status?
  • Is AutoUpdateStats Disabled and AutoUpdateStats Async Enabled? Using SSMS gives you no warning if you’ve enabled this scenario, but if you think you are using AutoUpdateStats Async, guess again.
  • Check for database status that prevent database access, like Emergency mode or Suspect.
  • Are there Non-unique clustered indexes? This might be something you wouldn’t want as a rule-of thumb.
  • Are there tables with non-clustered IXs but no clustered IX? This might hint you to evaluate your application query activity against heaps.
  • Do I have log growth in percentage, and it’s already over 1GB?
  • Do I have more VLFs than my rule-of-thumb? This has a 100 VLF threshold – change as appropriate.
  • Am I using Enterprise SKU features? Maybe I need to move a database to another server, and if it’s not an Enterprise Edition, so I must account for this?
  • Is Maximum Server Memory set at default? You will want to set this setting different from default.
  • Is Server Memory set at a fixed value?
  • Are DB Compatibility levels same as engine version?
  • Is Tempdb number of files appropriate? Regarding number of schedulers and if is multiple of 4?
  • Do TempDB data file sizes match?
  • Is MaxDOP setting at the recommended value?

Can’t end without directing you to the now retired SQL Server PBM blog, which has some good info around the PBM feature that you must check out. Also, and older but still current whitepaper “Enterprise Policy Management Framework with SQL Server 2008” is available, and I recommend you read it to fully understand how you can use PBM with EPM to unlock PBM potential in your enterprise.

Comments

  • Anonymous
    September 21, 2014
    Como reflexão, qual a mais valia do EPM face, digamos, a sp_Blitz de Brent Ozar ?