SQL Server General Quiz 2012 - Compliance is one of the non-negotiable in this era

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.

SQL Server General Quiz 2012 - Home

1st Prize - Nokia Lumia 710

Win 31 copies of

Extremeexperts.com

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

72 · 2% · 775

Previous Question: Any database design can include only the Normal forms
Next Question: As part of performance tuning exercise our developer always suggested to use SET based operations

5 Answers

Subscribe to Notifications

Score

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% · 3078

Score

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

208 · 1% · 224

Score

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

140 · 1% · 349

Score

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

129 · 1% · 391

Score

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

55 · 4% · 1125

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.

SQL Server General Quiz 2012 - Home

1st Prize - Nokia Lumia 710

Win 31 copies of

Recent Posts

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

5 Answers

What is Policy-Based Management?

Why Use Policy-Based Management?

Your Answer