Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Compliance is one of the non-negotiable in this era

  • Compliance is one of the non-negotiable in this era. SQL Server has had so many options like C2 Auditing since the SQL Server 2000 days. With the advent of Policy Based Management inside SQL Server 2005 - What are the different aspects involving PBM one needs to know?

    Posted on 01-25-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

5  Answers  

Subscribe to Notifications
  • Score
    8

    Policy-Based Management allows DBAs to define a set of policies that can control many different aspects of SQL Server which can be applied to a single server or to groups of servers. Typically, these policies are capable with company’s group policy and therefore may vary from company to company. For instance, some companies allow users to log on to SQL Server using SQL Server Authentication Mode but some only allow Windows Authentication Mode.

    You may need to combine your existing policies, and redefine them using Policy-Based Management feature introduced in SQL Server 2008. These policies can be defined at different levels, such as the entire server, a specific database, even a specific type of object (such as the name of all stored procedure needs start with usp_); and also can be at different feature, such as security, maintenance (reindex), performance and so on.

    The American Heritage Dictionary of the English Language (Fourth Edition) defines facet as: One of numerous aspects, as of a subject. The definition of Aspect (from the same dictionary) is: Appearance to the eye, especially from a specific vantage point. The definition of Facet in SQL Server Books Online is: A set of logical properties that model the behavior or characteristics for certain types of managed targets. The number and characteristics of the properties are built into the facet and can be added or removed by only the maker of the facet. A target type can implement one or more management facets, and a management facet can be implemented by one or more target types. Some properties of a facet can only apply to a specific version. For example, the Database Mail property of the Surface Area Configuration facet only applies to SQL Server 2005 and later versions.

    Though Policy-Based Management relies heavily on facets,

    • Application Role
    • Asymmetric Key
    • Audit
    • Backup Device
    • Broker Priority
    • Broker Service
    • Certificate
    • Credential
    • Cryptographic Provider
    • Data File
    • Database
    • Database Audit Specification
    • Database Ddl Trigger
    • Database Maintenance
    • Database Option
    • Database Performance
    • Database Role
    • Database Security
    • Default
    • Endpoint
    • File Group
    • Full Text Catalog
    • Full Text Index
    • Full Text Stop List
    • Index
    • Linked Server
    • Log File
    • Login
    • Login Options
    • Message Type
    • Multipart Name
    • Name
    • Partition Function
    • Partition Scheme
    • Plan Guide
    • Remote Service Binding
    • Resource Governor
    • Resource Pool
    • Rule
    • Schema
    • Server
    • Server Audit
    • Server Audit Specification
    • Server Configuration
    • Server Ddl Trigger
    • Server Information
    • Server Performance
    • Server Security
    • Server Settings
    • Server Setup
    • Service Contract
    • Service Queue
    • Service Route
    • Statistic
    • Stored Procedure
    • Surface Area
    • Surface Area for AS
    • Surface Area for RS
    • Symmetric Key
    • Synonym
    • Table
    • Table Options
    • Trigger
    • User
    • User Defined Aggregate
    • User Defined Data Type
    • User Defined Function
    • User Defined Table Type
    • User Defined Type
    • User Options
    • View
    • View Options
    • Workload Group
    • Xml Schema Collection

    http://blogs.msdn.com/b/sqlpbm/archive/2008/05/24/facets.aspx

    http://www.sql-server-performance.com/2009/Using-the-SQL-Server-2005-SAC-Tool/

    http://dotnetslackers.com/articles/sql/Policy-Based-Management-Create-Evaluate-and-Fix-Policies.aspx

    Replied on Jan 25 2012 11:51AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    4

    PBM was a feature which was introduced in SQL Server 2008 and not SQL Server 2005. PBM deals with defining policies for SQL Server within the enterprise.

    Defining policies allows the DBA to proactively choose how SQL Server instances, databases, and objects are configured. These policies allow the senior DBA (or whoever else is responsible for internal standards) to establish standards that others have to follow when they interact with the organization's SQL Servers, ensuring that consistency is maintained.

    I find PBM very much useful to set standards for SQL Server across my enterprise and example would be -

    "I want to ensure that Auto Close should be turned OFF for all my 100 servers across my environment",Using policy based management and central management servers I can easily accomplish this task.

    PBM gives you the ability to - Define Standard Policies as mentioned in the above example Selectively Enforce Policies Automate Policy Checking and Enforcement Ability to Fix Out of Policy Conditions with the Click of a Button (This is my favorite option)

    Over all PBM gives great control for a DBA and it can be wisely used to get the full potential.

    Replied on Jan 25 2012 2:38PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    8

    What is Policy-Based Management?

        Policy-Based Management is a new feature in SQL Server that allows 
        administrators to define rules for one or more SQL Servers and enforce them. The 
        goal of this feature is to make it easier for an administrator to manage one or 
        more servers by preventing servers from being out of compliance with his or her 
        policies. Policies can be used in a variety of ways. For example, you can use 
        policies to enforce naming conventions in a database. You may have several 
        servers that you want to have the same settings of various configuration 
        options. Policy-Based Management will allow you to discover when one of those 
        servers goes out of compliance. Even the use of SQL Server features can be 
        managed with Policy-Based Management – you can use Policy-Based Management to 
        ensure that SQLMail is not enabled on any server in your enterprise. Policies 
        can be applied to a single server or many servers.
    

    Why Use Policy-Based Management?

        The only tool that database administrators have had in the past to control the 
        setup of servers and databases is a paper-based policy manual. Discovering 
        policy violations and who was responsible for them was a manual task. It was 
        often difficult to discover why a policy had been violated, and even more 
        difficult to determine who violated it. Making sure that a large number of 
        servers were all set up in the same way was a major headache.</p>
    
        The introduction of Policy-Based Management in SQL Server 2008 solves this 
        problem and can be a significant time saver. It is now possible to define how 
        servers are to be configured and have SQL Server reason over these policies to 
        enforce the rules.
    

    Following are the different aspects involving PBM

    Facet Name

    Evaluation Modes permitted

    Target(s)

    Properties

    Datatypes

    Application Role

    On change: Prevent

    On Change: Log

    On Schedule

    Application role

    CreateDate

    DateLastModified

    ID

    DefaultSchema

    Name

    datetime

    datetime

    int

    sysname

    sysname

    Asymmetric Key

    On Change: Prevent

    On Change: Log

    On Schedule

    Asymmetric key

    ID

    KeyEncryptionAlgorithm

    KeyLength

    Owner
    PrivateKeyEncryptionType 
    PublicKey

    SID

    Thumbprint
    ProviderName

    Name

    int

    char(4)

    int

    sysname

    char(2)

    varbinary(max)

    varbinary(85)

    varbinary(32)

    nvarchar(120)

    sysname

    Audit

    On Schedule

    Audit

    CreateDate

    DateLastModified
    DestinationType

    Enabled
    FileName
    FilePath
    Guid 
    ID
    MaximumFileSize
    MaximumFileSizeUnit
    MaximumRolloverFiles
    OnFailure  
    QueueDelay
    ReserveDiskSpace
    Name

    datetime

    datetime

    char(2)

    bit

    nvarchar(260)

    nvarchar(260)

    uniqueidentifier

    int

    bigint

     

    int

    tinyint

    int

    int

    sysname

    Backup Device

    On Schedule

    Backup device

    BackupDeviceType
    PhysicalLocation
    SkipTapeLabel
    Name

    tinyint

    nvarchar(260)

    bit

    sysname

    Broker Priority

    On Schedule

    Broker priority

    ContractName
    ID
    LocalServiceName
    PriorityLevel
    RemoteServiceName
    Name

    sysname

    int

    sysname

    tinyint

    sysname

    sysname

    Broker Service

    On Schedule

    Broker service

    ID
    IsSystemObject
    Owner
    QueueName
    QueueSchema
    Name

    int

    bit

    sysname

    sysname

    sysname

    Certificate

    On Schedule

    Certificate

    ActiveForServiceBrokerDialog
    ExpirationDate
    ID
    Issuer
    Owner
    PrivateKeyEncryptionType
    Serial
    Sid
    StartDate
    Subject
    Thumbprint
    LastBackupDate
    Name

    bit

    datetime

    int

    nvarchar(442)

    sysname

    char(2)

    nvarchar(64)

    varbinary(85)

    datetime

    nvarchar(4000)

    varbinary(32)

    datetime

    sysname

    Credential

    On Schedule

    Credential

    CreateDate
    DateLastModified
    ID
    Identity
    MappedClassType
    ProviderName
    Name

    datetime

    datetime

    int

    nvarchar(4000)

    nvarchar(100)

     

    sysname

    Cryptographic Provider

    On Schedule

    Cryptographic provider

    AsymmetricKeyExportable
    AsymmetricKeyImportable
    AsymmetricKeyPersistable
    AsymmetricKeySupported
    AuthenticationType
    DllPath
    Enabled
    ID
    ProviderGuid
    SymmetricKeyExportable
    SymmetricKeyImportable
    SymmetricKeyPersistable
    SymmetricKeySupported
    Name

    bit

    bit

    bit

    bit

    char(4)

    nvarchar(512)

    bit

    int

    uniqueidentifier

    bit

    bit

    bit

    bit

    sysname

    Data File

    On Schedule

    Data file

    AvailableSpace
    FileName
    Growth
    GrowthType
    ID
    IsPrimaryFile
    MaxSize
    Size
    UsedSpace
    IsOffline
    IsReadOnly
    IsReadOnlyMedia
    IsSparse
    Name

    float

    nvarchar(260)

    float

    nvarchar(10)

    int

    bit

    float

    float

    float

    bit

    bit

    bit

    bit

    sysname

    Database

    On Schedule

    Database

    ActiveConnections
    AutoClose
    AutoShrink
    CompatibilityLevel
    CreateDate
    DataSpaceUsage
    DboLogin
    DefaultFileGroup
    DefaultSchema
    ID
    IndexSpaceUsage
    IsAccessible
    IsDbAccessAdmin
    IsDbBackupOperator
    IsDbDatareader
    IsDbDatawriter
    IsDbDdlAdmin
    IsDbDenyDatareader
    IsDbDenyDatawriter
    IsDbOwner
    IsDbSecurityAdmin
    IsFullTextEnabled
    IsSystemObject
    LastBackupDate
    LastDifferentialBackupDate
    LastLogBackupDate
    Owner
    PrimaryFilePath
    ReplicationOptions
    Size
    SpaceAvailable
    Status
    UserName
    AnsiNullDefault
    AnsiNullsEnabled 
    AnsiPaddingEnabled
    AnsiWarningsEnabled
    ArithmeticAbortEnabled
    AutoCreateStatisticsEnabled
    AutoUpdateStatisticsEnabled
    CaseSensitive
    CloseCursorsOnCommitEnabled
    Collation
    ConcatenateNullYieldsNull
    DatabaseOwnershipChaining
    IsUpdateable
    LocalCursorsDefault
    NumericRoundAbortEnabled
    PageVerify
    QuotedIdentifiersEnabled
    ReadOnly
    RecoveryModel
    RecursiveTriggersEnabled
    UserAccess
    Version
    AutoUpdateStatisticsAsync
    BrokerEnabled
    DatabaseGuid
    DatabaseSnapshotBaseName
    DateCorrelationOptimization
    DefaultFullTextCatalog
    IsDatabaseSnapshot
    IsDatabaseSnapshotBase
    IsMailHost
    IsMirroringEnabled
    IsParameterizationForced
    IsReadCommittedSnapshotOn
    IsVarDecimalStorageFormatEnabled
    LogReuseWaitStatus
    MirroringFailoverLogSequenceNumber
    MirroringID
    MirroringPartner
    MirroringPartnerInstance
    MirroringRedoQueueMaxSize
    MirroringRoleSequence
    MirroringSafetyLevel
    MirroringSafetySequence
    MirroringStatus
    MirroringTimeout
    MirroringWitness
    MirroringWitnessStatus
    RecoveryForkGuid (uniqueidentifier)
    ServiceBrokerGuid
    SnapshotIsolationState
    Trustworthy 
    ChangeTrackingAutoCleanUp
    ChangeTrackingEnabled
    ChangeTrackingRetentionPeriod
    ChangeTrackingRetentionPeriodUnits
    DefaultFileStreamFileGroup
    EncryptionEnabled
    HonorBrokerPriority 
    IsManagementDataWarehouse
    Name

    int

    bit

    bit

    tinyint

    datetime

    float

    bit

     

     

    int

    int

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    datetime

    datetime

    datetime

    sysname

     

     

    float

    float

    nvarchar(60)

    sysname

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    sysname

    bit

    bit

    bit

    bit

    bit

    nvarchar(60)

    bit

    bit

    nvarchar(60)

    bit

    nvarchar(60)

    int

    bit

    bit

    uniqueidentifier

    sysname

    bit

    sysname

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    nvarchar(60)

     

    uniqueidentifier

     

     

    int

    int

     

    int

     

    int

     

     

    uniqueidentifier

    uniqueidentifier

     

    bit

    bit

    bit

    int

     

    sysname

    bit

    bit

    bit

    sysname

    Database Audit Specification

    On Schedule

    Database audit specification

    AuditName
    CreateDate
    DateLastModified
    Enabled
    Guid
    ID
    Name

    sysname

    datetime

    datetime

    bit

    uniqueidentifier

    int

    sysname

    Database DDL Trigger

    On Schedule

    Database DDL trigger

    AnsiNullsStatus
    AssemblyName
    BodyStartIndex
    ClassName
    CreateDate
    DateLastModified
    ExecutionContext 
    ExecutionContextUser
    ID
    ImplementationType
    IsEnabled
    IsEncrypted
    IsSystemObject
    MethodName
    NotForReplication
    QuotedIdentifierStatus
    Text
    Name

    bit

    sysname

    int

     

    datetime

    datetime

     

     

    int

     

    bit

    bit

    bit

     

    bit

    bit

     

    sysname

    Database Maintenance

    On Schedule

    Database

    RecoveryModel
    ReadOnly
    PageVerify
    Status
    LastBackupDate
    LastLogBackupDate 
    DataAndBackupOnSeparateLogicalVolumes

    nvarchar(60)

    bit

    nvarchar(60)

    nvarchar(60)

    datetime

    datetime

    bit

    Database Option

    On Change: Log

    On Schedule

    Database

    AnsiNullDefault
    AnsiNullsEnabled
    AnsiPaddingEnabled
    AnsiWarningsEnabled
    ArithmeticAbortEnabled
    AutoClose
    AutoCreateStatisticsEnabled 
    AutoShrink
    AutoUpdateStatisticsAsync
    AutoUpdateStatisticsEnabled
    BrokerEnabled
    ChangeTrackingAutoCleanUp
    ChangeTrackingEnabled 
    ChangeTrackingRetentionPeriod
    ChangeTrackingRetentionPeriodUnits
    CloseCursorsOnCommitEnabled
    Collation
    CompatibilityLevel
    ConcatenateNullYieldsNull
    CreateDate 
    DatabaseOwnershipChaining
    DatabaseSnapshotBaseName
    DateCorrelationOptimization
    DefaultFileGroup
    DefaultFileStreamFileGroup
    EncryptionEnabled
    HonorBrokerPriority
    ID
    IsParameterizationForced
    IsReadCommittedSnapshotOn
    IsSystemObject
    IsUpdateable
    LocalCursorsDefault
    Name
    Owner
    NumericRoundAbortEnabled
    MirroringTimeout
    PageVerify
    PrimaryFilePath
    QuotedIdentifiersEnabled
    ReadOnly
    RecoveryModel
    RecursiveTriggersEnabled
    Trustworthy
    UserAccess

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    int

     

    bit

     

    tinyint

    bit

    datetime

    bit

    sysname

    bit

    sysname

    sysname

    bit

    bit

    int

    bit

    bit

    bit

    bit

    bit

    sysname

    sysname

    bit

    int

    nvarchar(60)

     

    bit

    bit

    nvarchar(60)

    bit

    bit

    nvarchar(60)

     

    Database Performance

    On Schedule

    Database

    AutoClose
    AutoShrink
    Size
    DataAndLogFilesOnSeparateLogicalVolumes
    CollationMatchesModelOrMaster
    IsSystemObject
    Status

    bit

    bit

    float

    bit

    bit

    bit

     

    Database Role

    On Change: Prevent

    On Change: Log

    On Schedule

    Database role

    CreateDate
    DateLastModified
    ID
    IsFixedRole
    Owner
    Name

    datetime

    datetime

    int

    bit

    sysname

    sysname

    Database Security

    On Schedule

    Database

    Trustworthy
    IsOwnerSysadmin

    bit

    bit

    Default

    On Schedule

    Default

    CreateDate
    ID
    Schema
    Name

    datetime

    int

    sysname

    sysname

    Endpoint

    On Schedule

    Endpoint

    EndpointState
    EndpointType
    ID
    IsAdminEndpoint
    IsSystemObject
    Owner
    ProtocolType 
    Name

    nvarchar(60)

    nvarchar(60)

    int
    bit
    bit
    sysname
    nvarchar(60)
    sysname

    File Group

    On Schedule

    File group

    ID (System.Int32)
    IsDefault
    ReadOnly
    Size
    IsFileStream
    Name

    Int
    bit
    bit
    float
    bit
    sysname

    Full Text Catalog

    On Schedule

    Full text catalog

    ErrorLogSize
    FullTextIndexSize
    HasFullTextIndexedTables
    ID
    ItemCount
    PopulationCompletionDate
    PopulationStatus
    RootPath 
    UniqueKeyCount
    FileGroup
    IsAccentSensitive
    IsDefault
    Owner
    Name

    int
    int
    bit
    int
    int
    datetime

    nvarchar(260)
    int
    sysname
    bit
    bit
    sysname
    sysname

    Full Text Index

    On Schedule

    Full text index

    CatalogName
    ChangeTracking
    IsEnabled
    PopulationStatus
    UniqueIndexName
    DocumentsProcessed
    ItemCount
    NumberOfFailures
    PendingChanges
    FilegroupName 
    StopListName
    StopListOption

    sysname

    bit

    sysname

    int

    int

    int

    int

    sysname

     

     

    Full Text Stop List

    On Schedule

    Full text stop list

    ID
    Owner (System.String)
    Name

    int

    sysname

     

    Index

    On Schedule

    Index

    DisallowPageLocks

    DisallowRowLocks

    FileGroup

    FillFactor
    IgnoreDuplicateKeys
    IndexKeyType
    IsClustered 
    IsFullTextKey
    IsSystemNamed
    IsSystemObject
    IsUnique
    NoAutomaticRecomputation
    PadIndex
    SpaceUsed
    IsDisabled
    IsPartitioned
    IsXmlIndex
    ParentXmlIndex
    PartitionScheme
    SecondaryXmlIndexType
    BoundingBoxXMax
    BoundingBoxXMin
    BoundingBoxYMax
    BoundingBoxYMin
    CellsPerObject
    FileStreamFileGroup
    FileStreamPartitionScheme 
    FilterDefinition (System.String)

    HasCompressedPartitions (System.Boolean)
    HasFilter
    IsSpatialIndex

    Level1Grid

    Level2Grid
    Level3Grid
    Level4Grid
    SpatialIndexType
    Name

    bit

    bit

    sysname

    tinyint

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    float

    bit

    bit

    bit

    sysname

    sysname

     

    float

    float

    float

    float

    int

    sysname

    sysname

    nvarchar(max)

    bit

    bit

    bit

     

     

     

     

     

    sysname

    Linked Server

    On Schedule

    Linked server

    Catalog
    DataAccess
    DataSource
    DistPublisher

    Distributor
    ID 
    Location
    ProductName
    ProviderName
    Publisher
    Rpc
    RpcOut

    Subscriber
    CollationName
    ConnectTimeout
    LazySchemaValidation

    QueryTimeout
    UseRemoteCollation

    DateLastModified
    IsPromotionofDistributedTransactionsForRPCEnabled
    Name (System.String) i

     

    bit

     

    bit

    bit

    int

     

     

     

    bit

    bit

    bit

    bit

     

    int

    bit

    int

    bit

    datetime

    bit

    bit

    Log File

    On Schedule

    Log file

    FileName
    Growth
    GrowthType
    ID
    MaxSize
    Size
    UsedSpace
    IsOffline
    IsReadOnly
    IsReadOnlyMedia
    IsSparse
    Name

     

    float

     

    int

    float

    float

    float

    bit

    bit

    bit

    bit

    sysname

    Login

    On Schedule

    Login

    CreateDate
    DateLastModified
    DefaultDatabase
    DenyWindowsLogin
    HasAccess
    IsSystemObject
    Language
    LanguageAlias
    LoginType
    Sid
    WindowsLoginAccessType
    AsymmetricKey
    Certificate
    Credential
    ID
    IsDisabled
    IsLocked
    IsPasswordExpired
    MustChangePassword
    PasswordExpirationEnabled
    PasswordPolicyEnforced
    Name

    datetime

    datetime

    sysname

    bit

    bit

    bit

    sysname

     

    nvarchar(60)

    varbinary(85)

     

    sysname

    sysname

    sysname

    int

    bit

    bit

    bit

    bit

    bit

    bit

    sysname

    Login Options

    On Change: Prevent

    On Change: Log

    On Schedule

    Login

    AsymmetricKey
    Certificate 
    CreateDate
    Credential
    DefaultDatabase
    ID
    IsDisabled 
    IsLocked
    IsSystemObject
    Language
    LanguageAlias
    MustChangePassword
    Name (System.String)

    PasswordExpirationEnabled
    PasswordPolicyEnforced

    sysname

    sysname

    datetime

    sysname

    sysname

    int

    bit

    bit

    bit

    sysname

     

    bit

    sysname

    bit

    bit

    Message Type

    On Schedule

    Message type

    ID
    IsSystemObject
    MessageTypeValidation
    ValidationXmlSchemaCollection
    ValidationXmlSchemaCollectionSchema
    Name

    int

    bit

     

    sysname

    sysname

    sysname

    Multipart Name

    On Change Prevent

    On Change: Log

    On Schedule

    Function

    Procedure

    Synonym

    Table

    Type

    View

    Name
    Schema

    sysname

    sysname

    Name

    On Schedule

    Application role

    Asymmetric key

    Certificate

    Database role

    Default

    Index

    Rule

    Schema

    SQL Assembly

    Stored procedure

    Symmetric key

    Synonym

    Table

    Trigger

    User

    User defined function

    User defined type

    View

    XML Schema Collection

    Name

    sysname

    Partition Function

    On Schedule

    Partition function

    CreateDate
    ID
    NumberOfPartitions
    RangeType
    Name

    datetime

    int

    int

     

    sysname

    Partition Scheme

    On Schedule

    Partition scheme

    ID
    NextUsedFileGroup
    PartitionFunction
    Name

    int

    sysname

    sysname

    sysname

    Plan Guide

    On Schedule

    Plan guide

    Hints
    ID 
    IsDisabled
    Parameters
    ScopeBatch
    ScopeObjectName
    ScopeSchemaName
    ScopeType
    Statement
    Name

     

    Int

    Bit

     

     

     

     

     

     

    sysname

    Remote Service Binding

    On Schedule

    Remote service binding

    CertificateUser
    IsAnonymous
    Owner
    RemoteService
    Name

    sysname

    bit

    sysname

    sysname

    sysname

    Resource Governor

    On Schedule

    Resource governor

    ClassifierFunction
    Enabled
    ReconfigurePending

    sysname

    bit

    bit

    Resource Pool

    On Change: Prevent

    On Change: Log

    On Schedule

    Resource pool

    ID
    IsSystemObject
    MaximumCpuPercentage
    MaximumMemoryPercentage
    MinimumCpuPercentage
    MinimumMemoryPercentage
    Name

    int

    bit

    int

    int

    int

    int

    sysname

    Rule

    On Schedule

    Rule

    CreateDate
    ID
    DateLastModified
    Schema
    Name

    datetime

    int

    datetime

    sysname

    sysname

    Schema

    On Change: Prevent

    On Change: Log

    On Schedule

    Schema

    ID
    Owner
    Name

    int

    sysname

    sysname

    Server

    On Schedule

    Server

    AuditLevel
    BackupDirectory
    BuildNumber
    DefaultFile
    DefaultLog
    ErrorLogPath
    InstallDataDirectory
    IsCaseSensitive
    IsFullTextInstalled
    Language
    MailProfile
    MasterDBLogPath
    MasterDBPath
    MaxPrecision
    NumberOfLogFiles
    OSVersion
    PerfMonMode
    PhysicalMemory
    Platform
    Processors
    Product
    RootDirectory
    ServiceName
    VersionMajor
    VersionMinor
    Collation
    Edition
    EngineEdition
    InstanceName
    IsClustered
    IsSingleUser
    NetName
    ProductLevel
    ServerType
    Status
    TapeLoadWaitTime
    VersionString
    BrowserServiceAccount
    BrowserStartMode
    BuildClrVersionString
    CollationID
    ComparisonStyle
    ComputerNamePhysicalNetBIOS
    InstallSharedDirectory
    NamedPipesEnabled
    ResourceLastUpdateDateTime
    ResourceVersionString
    ServiceAccount
    ServiceInstanceId
    ServiceStartMode
    SqlCharSet
    SqlCharSetName
    SqlSortOrder
    SqlSortOrderName
    TcpEnabled
    FilestreamLevel
    FilestreamShareName
    SqlDomainGroup

     

     

    Int

     

     

     

     

    Bit

    Bit

     

     

     

     

    Tinyint

    Int

     

     

    Int

     

    Int

     

     

     

    Int

    Int

    sysname

    sysname

    sysname

    sysname

    Bit

    Bit

     

    sysname

     

     

     

     

     

     

    sysname

    int

     

    sysname

     

    bit

    datetime

     

     

     

     

    smallint

     

    smallint

     

    bit

     

     

     

    Server Audit

    On Schedule

    Server audit

    DefaultTraceEnabled
    C2AuditTracingEnabled
    LoginAuditLevel

    bit

    bit

     

    Server Audit Specification

    On Schedule

    Server audit specification

    AuditName
    CreateDate
    DateLastModified
    Enabled
    Guid
    ID

    Name

    sysname

    datetime

    datetime

    bit

    uniqueidentifier

    int

    sysname

    Server Configuration

    On Change: Log

    On Schedule

    Server

    AdHocRemoteQueriesEnabled
    AffinityMask
    Affinity64Mask
    AffinityIOMask
    Affinity64IOMask
    AgentXPsEnabled
    AllowUpdates
    AweEnabled
    BlockedProcessThreshold
    C2AuditTracingEnabled
    ClrIntegrationEnabled
    CommonCriteriaComplianceEnabled
    CostThresholdForParallelism
    CrossDBOwnershipChainingEnabled
    CursorThreshold
    DatabaseMailEnabled
    DefaultTraceEnabled
    DefaultFullTextLanguage
    DefaultLanguage
    DisallowResultsFromTriggers (
    FillFactor
    FullTextCrawlBandwidthMin
    FullTextCrawlBandwidthMax
    FullTextNotifyBandwidthMin
    FullTextNotifyBandwidthMax
    FullTextCrawlRangeMax
    InDoubtTransactionResolution
    IndexCreateMemory
    LightweightPoolingEnabled
    DynamicLocks
    MaxDegreeOfParallelism
    MaxServerMemory
    MaxWorkerThreads
    MediaRetention
    MinMemoryPerQuery
    MinServerMemory
    NestedTriggersEnabled
    NetworkPacketSize
    OleAutomationEnabled
    OpenObjects
    PrecomputeRank
    PriorityBoost
    ProtocolHandlerTimeout
    QueryGovernorCostLimit
    QueryWait
    RecoveryInterval
    RemoteAccessEnabled
    RemoteDacEnabled
    RemoteLoginTimeout
    RemoteProcTransEnabled
    RemoteQueryTimeout
    ReplicationMaxTextSize
    ReplicationXPsEnabled
    ScanForStartupProcedures
    ServerTriggerRecursionEnabled
    SetWorkingSetSize
    ShowAdvancedOptions
    SmoAndDmoXPsEnabled
    SqlMailEnabled (System.Boolean)
    TransformNoiseWords
    TwoDigitYearCutoff
    UserConnections
    UserInstanceTimeout
    UserInstancesEnabled
    UserOptions
    WebAssistantEnabled
    XPCmdShellEnabled
    DefaultBackupCompressionEnabled
    ExtensibleKeyManagementEnabled
    FilestreamAccessLevel
    OptimizeAdhocWorkloads

    bit

    int

    int

    bit

    bit

    bit

    int

    bit

    bit

    bit

    bit

    bit

    int

    bit

    int

    bit

    bit

    int

    int

    bit

    int

    int

    int

    int

    int

    int

     

    int

    bit

    int

    int

    int

    int

    int

    int

    int

    bit

    int

    int

    int

    bit

    bit

    int

    int

    int

    int

    bit

    bit

    int

    bit

    int

    int

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    int

    int

    int

    int

    int

    bit

    bit

    bit

    bit

    tinyint

    bit

    Server DDL Trigger

    On Schedule

    Server DDL trigger

    AnsiNullsStatus
    AssemblyName
    BodyStartIndex
    ClassName
    CreateDate
    DateLastModified
    ExecutionContext
    ExecutionContextLogin
    ID
    ImplementationType
    IsEnabled
    IsEncrypted
    IsSystemObject
    MethodName
    QuotedIdentifierStatus
    Text (System.String)
    Name

    bit

    bit

    int

    sysname

    datetime

    datetime

     

    sysname

    int

     

    bit

    bit

    bit

    sysname

    bit

    varbinary(max)

    sysname

    Server Information

    On Schedule

    Server

    Collation
    Edition
    ErrorLogPath
    IsCaseSensitive
    IsClustered
    IsFullTextInstalled
    IsSingleUser
    Language
    MasterDBLogPath
    MasterDBPath
    MaxPrecision
    NetName
    OSVersion
    PhysicalMemory
    Platform
    Processors
    Product
    ProductLevel
    RootDirectory
    VersionString
    EngineEdition
    VersionMajor
    VersionMinor
    BuildClrVersionString
    BuildNumber
    CollationID
    ComparisonStyle
    ComputerNamePhysicalNetBIOS
    ResourceLastUpdateDateTime
    ResourceVersionString
    SqlCharSet
    SqlCharSetName
    SqlSortOrder
    SqlSortOrderName (System.String)

    sysname

    sysname

     

    bit

    bit

    bit

    bit

    sysname

     

     

    tinyint

    sysname

     

    string

     

    int

     

    sysname

     

     

     

    int

    int

    sysname

    int

    int

    int

    sysname

    datetime

    sysname

    tinyint

    sysname

    tinyint

    sysname

    Server Installation

    On Schedule

    Server

    ServiceName
    EngineServiceAccount
    ServiceStartMode
    InstanceName
    ServiceInstanceIdSuffix
    FilestreamLevel
    FilestreamShareName
    UserInstancesEnabled
    Collation
    SqlDomainGroup
    WindowsUsersAndGroupsInSysadminRole
    LoginMode
    InstallDataDirectory
    BackupDirectory
    DefaultFile
    DefaultLog
    TempdbPrimaryFilePath
    TempdbLogPath
    AgentStartMode
    AgentServiceAccount
    AgentDomainGroup
    NamedPipesEnabled
    TcpEnabled
    InstallSharedDirectory
    BrowserStartMode
    BrowserServiceAccount

     

     

     

    sysname

     

    tinyint

     

    sysname

    sysname

     

     

     

     

     

     

     

     

     

     

     

     

    bit

    bit

     

     

     

    Server Performance

    On Schedule

    Server

    Affinitymask
    Affinity64mask
    Affinityiomask
    Affinity64iomask
    BlockedProcessThreshold
    DynamicLocks
    Lightweightpoolingenabled
    MaxDegreeofParallelism
    CostThresholdforParallelism
    MaxWorkerThreads
    NetworkPacketSize
    Openobjects

    int

    int

    int

    int

    int

    int

    bit

    bit

    int

    int

    int

    int

    Server Security

    On Schedule

    Server

    PublicServerRoleIsGrantedPermissions
    LoginMode
    XPCmdShellEnabled
    CrossDBOwnershipChainingEnabled
    CommonCriteriaComplianceEnabled
    CmdExecRightsForSystemAdminsOnly
    ProxyAccountIsGrantedToPublicRole
    ReplaceAlertTokensEnabled
    ProxyAccountEnabled

    bit

     

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    Server Settings

    On Schedule

    Server

    AuditLevel
    BackupDirectory
    DefaultFile
    DefaultLog
    LoginMode
    MailProfile
    NumberOfLogFiles
    PerfMonMode
    TapeLoadWaitTime

     

     

     

     

     

    sysname

    int

     

    int

    Service Contract

    On Schedule

    Service contract

    ID
    IsSystemObject
    Owner
    Name

    Int

    Bit

    Sysname

    sysname

    Service Queue

    On Schedule

    Service queue

    ActivationExecutionContext
    CreateDate
    DateLastModified
    ExecutionContextPrincipal
    FileGroup
    ID
    IsActivationEnabled
    IsEnqueueEnabled
    IsRetentionEnabled
    IsSystemObject
    MaxReaders
    ProcedureDatabase
    ProcedureName
    ProcedureSchema
    RowCount
    Schema
    Name

    sysname

    datetime

    datetime

    sysname

    sysname

    int

    bit

    bit

    bit

    bit

    smallint

    sysname

    sysname

    sysname

    bigint

    sysname

    sysname

    Service Route

    On Schedule

    Service route

    Address
    BrokerInstance
    ExpirationDate
    ID
    MirrorAddress
     Owner
    RemoteService
    Name

     

     

    datetime

    int

     

    sysname

     

    sysname

    Statistic

    On Schedule

    Statistic

    FileGroup
    ID
    IsAutoCreated
    IsFromIndexCreation
    LastUpdated
    NoAutomaticRecomputation
    FilterDefinition
    HasFilter
    Name

    sysname

    int

    bit

    bit

    datetime

    bit

    varbinary(max)

    bit

    sysname

    Stored Procedure

    On Change: Prevent

    On Change: Log

    On Schedule

    Stored procedure

    AnsiNullsStatus
    CreateDate
    ForReplication
    ID
    ImplementationType
    IsEncrypted
    IsSystemObject
    Owner
    QuotedIdentifierStatus
    Recompile
    Startup
    AssemblyName
    ClassName
    ExecutionContext
    ExecutionContextPrincipal
    IsSchemaOwned
    MethodName
    Schema
    Name

    bit

    datetime

    bit

    int

     

    bit

    bit

    sysname

    bit

    bit

    bit

    sysname

    sysname

     

    sysname

    bit

    sysname

    sysname

    sysname

    Surface Area

    On Change: Log

    On Schedule

    Server

    AdHocRemoteQueriesEnabled
    DatabaseMailEnabled
    ClrIntegrationEnabled
    OleAutomationEnabled
    RemoteDacEnabled (System.Boolean)
    SqlMailEnabled (System.Boolean)
    WebAssistantEnabled
    XPCmdShellEnabled
    ServiceBrokerEndpointActive
    SoapEndpointsEnabled

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    Surface Area for AS

     

    Analysis services

    AdHocDataMiningQueriesEnabled
    LinkedObjectsLinksToOtherInstancesEnabled
    LinkedObjectsLinksFromOtherInstancesEnabled
    UserDefinedFunctionsEnabled
    ListenOnlyOnLocalConnections

    bit

    bit

    bit

    bit

    bit

    Surface Area for RS

     

    Reporting services

    WebServiceAndHTTPAccessEnabled
    ScheduleEventsAndReportDeliveryEnabled
    ReportManagerEnabled

    bit

    bit

    bit

    Symmetric Key

    On Schedule

    Symmetric key

    CreateDate
    DateLastModified
    EncryptionAlgorithm
    ID
    IsOpen
    KeyGuid
    KeyLength
    Owner
    ProviderName
    Name

    datetime

    datetime

    nvarchar(60)

    int

    bit

    uniqueidentifier

    int

    sysname

    nvarchar(120)

    sysname

    Synonym

    On Schedule

    Synonym

    BaseDatabase
    BaseObject
    BaseSchema
    BaseServer
    BaseType
    CreateDate
    DateLastModified
    ID
    IsSchemaOwned
    Owner
    Schema
    Name

    sysname

    sysname

    sysname

    sysname

    sysname

    datetime

    datetime

    int

    bit

    sysname

    sysname

    sysname

    Table

    On Schedule

    Table

    CreateDate
    DataSpaceUsed
    FakeSystemTable
    FileGroup
    HasClusteredIndex
    ID
    IndexSpaceUsed
    IsSystemObject
    Owner
    Replicated
    RowCount
    TextFileGroup
    AnsiNullsStatus
    HasAfterTrigger
    HasDeleteTrigger
    HasIndex
    HasInsertTrigger
    HasInsteadOfTrigger
    HasUpdateTrigger
    IsIndexable
    QuotedIdentifierStatus
    DateLastModified
    IsPartitioned
    IsSchemaOwned
    IsVarDecimalStorageFormatEnabled
    PartitionScheme
    ChangeTrackingEnabled
    FileStreamFileGroup
    FileStreamPartitionScheme
    HasCompressedPartitions
    LockEscalation
    TrackColumnsUpdatedEnabled
    Schema
    Name

    datetime

    float

    bit

    sysname

    bit

    int

    float

    bit

    sysname

    bit

    bigint

    sysname

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    datetime

    bit

    bit

    bit

    sysname

    bit

    sysname

    sysname

    bit

     

    bit

    sysname

    sysname

    Table Options

    On Change: Prevent

    On Change: Log

    On Schedule

    Table

    AnsiNullsStatus
    ChangeTrackingEnabled
    CreateDate
    FakeSystemTable
    ID
    IsSchemaOwned
    IsSystemObject
    LockEscalation
    Name
    Owner
    QuotedIdentifierStatus
    Replicated
    Schema
    TrackColumnsUpdatedEnabled

    bit

    bit

    datetime

    bit

    int

    bit

    bit

    bit

    sysname

    sysname

    bit

    bit

    sysname

    bit

    Trigger

    On Schedule

    Trigger

    AnsiNullsStatus
    CreateDate
    Delete
    DeleteOrder
    ID
    ImplementationType
    Insert
    InsertOrder
    InsteadOf (System.Boolean)
    IsEnabled
    IsEncrypted (System.Boolean)
    IsSystemObject
    NotForReplication
    QuotedIdentifierStatus
    Update
    UpdateOrder
    AssemblyName
    ClassName
    DateLastModified
    ExecutionContext
    ExecutionContextPrincipal
    MethodName
    Name

    bit

    bit

    bit

     

    int

     

    bit

     

    bit

    bit

    bit

    bit

    bit

    bit

    bit

     

    sysname

    sysname

    datetime

     

    sysname

    sysname

    sysname

    User

    On Schedule

    User

    CreateDate
    DateLastModified
    HasDBAccess
    ID
    IsSystemObject
    Login
    LoginType
    Sid
    UserType
    AsymmetricKey
    Certificate
    DefaultSchema
    Name

    datetime

    datetime

    bit

    int

    bit

    sysname

     

    varbinary(85)

     

    sysname

    sysname

    sysname

    sysname

    User Defined Aggregate

    On Schedule

    User defined aggregate

    AssemblyName
    ClassName
    CreateDate
    DateLastModified
    ID
    IsSchemaOwned
    Owner
    Schema
    Name

    sysname

    sysname

    datetime

    datetime

    int

    bit

    sysname

    sysname

    sysname

    User Defined Data Type

    On Schedule

    User defined data type

    AllowIdentity
    Default
    DefaultSchema
    ID
    Length
    MaxLength
    Nullable
    NumericPrecision
    NumericScale
    Owner
    Rule
    RuleSchema
    SystemType
    VariableLength
    Collation
    IsSchemaOwned
    Schema
    Name (System.String)

    bit

    sysname

    sysname

    int

    int

    int

    bit

    int

    int

    sysname

    sysname

    sysname

    sysname

    bit

    sysname

    bit

    sysname

    sysname

    User Defined Function

    On Change: Prevent

    On Change: Log

    On Schedule

    User defined function

    AnsiNullsStatus
    CreateDate
    FunctionType
    ID
    ImplementationType
    IsDeterministic
    IsEncrypted
    IsSchemaBound
    IsSystemObject
    Owner
    QuotedIdentifierStatus
    TableVariableName
    AssemblyName
    ClassName
    ExecutionContext
    ExecutionContextPrincipal
    IsSchemaOwned
    MethodName
    ReturnsNullOnNullInput
    Schema
    Name

    bit

    datetime

     

    int

     

    bit

    bit

    bit

    bit

    sysname

    bit

    sysname

    sysname

    sysname

     

    sysname

    bit

    sysname

    bit

    sysname

    sysname

    User Defined Table Type

    On Schedule

    User defined table type

    Collation
    CreateDate
    DateLastModified
    ID
    IsSchemaOwned
    IsUserDefined
    MaxLength
    Nullable
    Owner
    Schema
    Name

    sysname

    datetime

    datetime

    int

    bit

    bit

    smallint

    bit

    sysname

    sysname

    sysname

    User Defined Type

    On Schedule

    User defined type

    AssemblyName
    BinaryTypeIdentifier
    ClassName
    Collation
    ID
    IsBinaryOrdered
    IsComVisible
    IsFixedLength
    IsNullable
    IsSchemaOwned
    MaxLength
    NumericPrecision
    NumericScale
    Owner
    UserDefinedTypeFormat
    Schema
    Name

    sysname

    varbinary

    sysname

    sysname

    int

    bit

    bit

    bit

    bit

    bit

    int

    int

    int

    sysname

     

    sysname

    sysname

    User Options

    On Change: Prevent

    On Change: Log

    On Schedule

    Asymmetric key user

    Certificate user

    Group user

    SQL user

    Windows user

    AsymmetricKey
    Certificate
    CreateDate
    DefaultSchema
    ID
    IsSystemObject
    Login
    LoginType
    Name
    Sid
    UserType

    sysname

    sysname

    sysname

    sysname

    int

    bit

    bit

     

    sysname

    varbinary(85)

    View

    On Schedule

    View

    AnsiNullsStatus
    CreateDate
    HasColumnSpecification
    ID
    IsEncrypted
    IsSchemaBound
    IsSystemObject
    Owner
    QuotedIdentifierStatus
    HasAfterTrigger
    HasDeleteTrigger
    HasIndex
    HasInsertTrigger
    HasInsteadOfTrigger
    HasUpdateTrigger
    IsIndexable
    DateLastModified
    IsSchemaOwned
    ReturnsViewMetadata
    Schema
    Name

    bit

    datetime

    bit

    int

    bit

    bit

    bit

    sysname

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    bit

    datetime

    bit

    bit

    sysname

    sysname

    View Options

    On Change: Prevent

    On Change: Log

    On Schedule

    View

    AnsiNullsStatus
    CreateDate
    ID
    IsEncrypted
    IsSchemaBound
    IsSchemaOwned
    IsSystemObject
    Name
    Owner
    Schema
    QuotedIdentifierStatus
    ReturnsViewMetadata

    bit

    datetime

    int

    bit

    bit

    bit

    bit

    sysname

    sysname

    sysname

    bit

    bit

    Workload Group

    On Change: Prevent

    On Change: Log

    On Schedule

    Workload group

    GroupMaximumRequests
    ID
    Importance
    IsSystemObject
    MaximumDegreeOfParallelism
    RequestMaximumCpuTimeInSeconds
    RequestMaximumMemoryGrantPercentage
    RequestMemoryGrantTimeoutInSeconds
    Name

    int

    int

     

    bit

    int

    int

    int

    int

    sysname

    Xml Schema Collection

    On Schedule

    XML schema collection

    CreateDate

    DateLastModified

    ID

    Text

    Schema

    Name

    datetime

    datetime

    int

    nvarchar(max)

    sysname

    sysname

    Refrence Link :: http://blogs.msdn.com/jenss/archive/2009/04/18/getting-a-list-of-all-facets-and-its-properties.aspx

    Thanks

    Yogesh

    Replied on Jan 25 2012 10:20PM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    9

    Policy-Based Management is a system for managing one or more instances of SQL Server 2008. When SQL Server policy administrators use Policy-Based Management, they use SQL Server Management Studio to create policies to manage entities on the server, such as the instance of SQL Server, databases, or other SQL Server objects.

    Policy-Based Management has three components:

    • Policy management

    Policy administrators create policies.

    • Explicit administration

    Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly make the targets comply with a policy.

    • Evaluation modes

    There are four evaluation modes, three of which can be automated

    On demand This mode evaluates the policy when directly specified by the user.

    On change:prevent This automated mode uses DDL triggers to prevent policy violations.

    On change: log only This automated mode uses event notification to evaluate a policy when a relevant change is made.

    On schedule This automated mode uses a SQL Server Agent job to periodically evaluate a policy.

    When automated policies are not enabled, Policy-Based Management will not affect system performance.

    Policy-Based Management Terms and Concepts

    Policy-Based Management managed target

    Entities that are managed by Policy-Based Management, such as an instance of the SQL Server Database Engine, a database, a table, or an index. All targets in a server instance form a target hierarchy. A target set is the set of targets that results from applying a set of target filters to the target hierarchy, for example, all the tables in the database owned by the HumanResources schema.

    Policy-Based Management facet

    A set of logical properties that model the behavior or characteristics for certain types of managed targets. The number and characteristics of the properties are built into the facet and can be added or removed by only the maker of the facet. A target type can implement one or more management facets, and a management facet can be implemented by one or more target types. Some properties of a facet can only apply to a specific version. For example, the Database Mail property of the Surface Area Configuration facet only applies to SQL Server 2005 and later versions.

    Policy-Based Management condition

    A Boolean expression that specifies a set of allowed states of a Policy-Based Management managed target with regard to a management facet.

    Policy-Based Management policy

    A Policy-Based Management condition and the expected behavior, for example, evaluation mode, target filters, and schedule. A policy can contain only one condition. Policies can be enabled or disabled.

    Policy-Based Management policy category

    A user-defined category to help manage policies. Users can classify policies into different policy categories. A policy belongs to one and only one policy category. Policy categories apply to databases and servers. At the database level, the following conditions apply:

    • Database owners can subscribe a database to a set of policy categories.
    • Only policies from its subscribed categories can govern a database.
    • All databases implicitly subscribe to the default policy category.

    At the server level, policy categories can be applied to all databases.

    Effective policy

    The effective policies of a target are those policies that govern this target. A policy is effective with regard to a target only if all the following conditions are satisfied:

    • The policy is enabled.
    • The target belongs to the target set of the policy.
    • The target or one of the targets ancestors subscribes to the policy group that contains this policy.

    For Detail info, check here.

    Replied on Jan 30 2012 10:42PM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    8

    Policy-based management has been introduced in SQL server 2008 not in 2005. However, it is arguably a single most significant new feature for the DBA.Policy-Based Management is a new feature in SQL Server 2008 that allows administrators to define rules for one or more SQL Servers and enforce them. The goal of this feature is to make it easier for an administrator to manage one or more servers by preventing servers from being out of compliance with his or her policies.

    Policy-based management terms Policy-based management is the tool of choice in ensuring consistent SQL Server configuration, and like Active Directory, its value is magnified in environments with large numbers of server instances.

    There are several new terms used when discussing policy-based management: targets, facets, conditions, and policies.

    Targets- A target is the entity managed by a policy. Depending on the policy, targets may be SQL Server instances, databases, tables, and so forth.

    Facets - A facet is the name given to a group of configurable properties that are appropriate for a certain number of targets.

    Conditions- A condition is created to specify the required state of one or more facet properties.

    Policies-Putting it all together, a policy contains a condition, a target, and an evaluation mode, which defines how the policy conditions will be enforced.

    Evaluation modes

    On Change – Prevent—This mode ensures policy violations are prevented through the use of DDL triggers that roll back changes that violate policy. The mechanism used for the rollback (DDL trigger) limits the situations in which this evaluation mode can be used.

    On Change – Log Only—This mode logs violations when a change occurs that violates an enabled policy. Corresponding alerts can then be set up as appropriate.

    On Schedule—Using SQL Agent jobs, the On Schedule evaluation mode will periodically check policy compliance, and log violations if appropriate. This mode is useful in reducing the performance impact of a large number of enabled policies.

    On Demand—This evaluation mode is used when creating ad hoc checks. The policies are created as disabled and, as such, have no performance impact on a running instance.

    Advanced policy-based management

    There are something like advanced policy-based management as below:

    ExecuteWql() and ExecuteSql() The ExecuteWql() function permits you to use Windows Management Instrumentation (WMI), specifically the WMI Query Language (WQL), to query the operating system for information.

    Directly above ExecuteWql() is ExecuteSql(), which you can use to run a traditional T-SQL query. In a similar manner to ExecuteWql(), this function can run any T-SQL code, and can therefore be used to create flexible and powerful policies.

    Both the ExecuteSql() and ExecuteWql() functions, fully documented in SQL Server BOL, enable you to create policies with almost limitless flexibility, and could potentially be used to check policy compliance of items completely unrelated to SQL Server.

    Windows PowerShell

    SQL Server 2008 is Windows PowerShell aware and exposes its management interface via its own cmdlets. Earlier, we briefly covered the ability to evaluate policies against earlier versions of SQL Server. For example, by registering a SQL Server 2005 instance with the 2008 Management Studio tool, you can right-click 2005 objects and manually evaluate policies. What you can’t do (without using Windows PowerShell) is store policies within a 2005 instance for scheduled evaluation as you can with a 2008 instance.

    The -Policy option is used to specify the required policy to execute. An alternate use of this option is to supply a comma-separated list of policies, allowing multiple policies to be executed as part of the one command.

    The gci option allows Invoke-PolicyEvaluation to receive input from a pipe. For example, the following code will evaluate every policy in the current directory against the specified server.

    gci | Invoke-PolicyEvaluation -TargetServer "BNE-SQL-PR-01\SQL2005"

    -OutputXml allows you to direct the output of the evaluation to a file for later inspection. This option is particularly useful when running scheduled evaluations.

    -AdHocPolicyExecutionMode "Configure" implements the policy conditions.

    In summary, the combination of policy-based management, central management servers, and Windows PowerShell cmdlets enables a whole new level of powerful management possibilities for the enterprise DBA.

    Best practice considerations: policy-based management

    Policies (and their evaluation history) are stored in the MSDB database. You should back up this database on a regular basis, or at least each time a policy definition changes.

    Implement proactive DBA checks as automated policies where possible. As well as saving time that can be spent on other tasks such as performance baselining, creating policies enables you to configure new servers faster and more reliably than with manual methods.

    Create central management servers to maximize the power of policy-based management. Where classes of servers exist, such as production, test, and development, use individual central management server groups to check groups of servers in a single action.

    If using the On Change - Prevent mode, ensure the policies are tested in a load testing environment for their potential performance impact. If a measurable impact is detected, implement policies as On Schedule.

    Consider creating alerts for policy violations. All policy failures are logged to the SQL Server error log with an error code of 34050 through 34053.

    If you use the ExecuteSQL() function to create custom policy conditions, ensure the permissions of the ##MS_PolicyTsqlExecutionLogin## account are set to the minimum required, particularly if you’re using the MSDB PolicyAdministratorRole.

    Replied on Jan 31 2012 1:35AM  . 
    Latheesh NK
    51 · 4% · 1178

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.