Tips
Configuring Alerts for SQL Server Policy Based Management
I have configured Policy-Based Management to enforce standards on my SQL Servers. This is fine for the policies where you can prevent the changes; however, many policies can only be logged and not prevented. How can I receive notifications when the policies I have implemented have been violated so I don't have to manually look at each server?
Enable Data File Growth for all the Databases Using Policy Based Management
There is an easy way to quickly enable data file growth for all the databases in SQL Server 2008. In this tip we cover the step by step process for setting up a policy to Enable Data File Growth for all user databases using Policy Based Management.
Enable SQL Server Transaction Log Growth for All Databases
There is an easy way to quickly enable transactional log file growth for all the databases in SQL Server 2008. In this tip we cover the step by step process for setting up a policy to enable transactional log file growth for all the databases using Policy Based Management.
Enforce SQL Server Database Naming Conventions Using Policy Based Management
In many environments you may want to enforce specific database naming conventions. For example, you may want to prevent others from naming their databases using special characters like dash or period. These and other poor naming decisions can wreak havoc on both internal and custom modules that (perhaps wrongly) make assumptions about database names. It is not possible to catch these issues at the time the name is implemented, because database create and rename events are not transactionable - and thus cannot be captured in DDL triggers. In this tip we look at using PBM to enforce database naming conventions.
Evaluate and Implement Policies in Multiple SQL Server Instances
In relation to the tip on Policy-Based Management in SQL Server 2008, we would like to implement policies on multiple SQL Server 2008 and even SQL Server 2005 instances. Can you give us a detailed explanation of how we go about doing this?
Find all SQL Server columns of a specific data type using Policy Based Management
SQL Server 2008 introduced Policy-Based Management (PBM), a way to centralize checking the compliance of policies that you can dictate. Unfortunately the facets and conditions that ship out of the box will not always cover the scenario(s) you are interested in evaluating. Check out this tip to learn how to extend the Policy Based Management.
Find unused SQL Server indexes with Policy Based Management
In SQL Server, indexes can be a double-edged sword. Sure, they can make queries run faster, but at the same time, their maintenance can have a negative impact. You can improve your server's overall performance by only maintaining useful indexes - but finding the ones you don't need can be quite a manual process. In this tip, we cover how PBM can assist with this process.
Identify Databases Not in SQL Server 2008 Compatibility Mode
You need to periodically ascertain if there are any databases in your production environment which are not in SQL Server 2008 Compatibility Level. In this tip we cover the step by step process for setting up a policy to identify databases that are not in SQL Server 2008 Compatibility Level.
Identify SQL Server Databases Not in Full Recovery Model
You need to periodically ascertain if there are any databases in your production environment which are not using the Full Recovery Model. In this tip we cover the step by step process for setting up a policy to identify databases that are not using full recovery model.
Identify SQL Server Databases Not Recently Backed Up Using Policy Based Management
As the DBA, you need to determine if there are any databases in your production environment which have not had a full backup in the last 24 hours. In this tip we cover the step by step process for setting up a policy to identify all the databases which were not backed up in the last 24 hours.
Import SQL Server 2008 Database Engine Policies with Policy Based Management
SQL 2008 introduced Policy Based Management (PBM), allowing for database administrators to have greater control over their environment through the use of policies. SQL 2008 comes with over fifty (50) policies, but none are installed by default. You need to import these policies manually in order to benefit from their use. In this tip I will walk you through the process of importing these default policies.
Internal SQL Server Certificates have expired is this an issue
We decided to create a Policy to check for expired certificates within our SQL Server instance. Once we created the policy and ran it we noticed that some of the internal SQL certificates were expired. We weren't sure if this was an issue or not, so we contacted Microsoft to find out. In this tip we cover what was found and the feedback from Microsoft.
Monitor SQL Server Agent Jobs with Policy Based Management
I need to monitor all the SQL Agents to find out if a job failed in the last 24 hours. Could this be done with policy based management? If so, can you show me how to create the policy? In this tip we go step by step how to set this up.
Monitor Your SQL Server Linked Server Configurations with Policy Based Management
You need to quickly ascertain if any linked servers for your SQL Servers have had recent configuration changes. In this tip we cover the step by step process for setting up a policy to check for when there have been any linked server changes to your servers.
Monitor Your SQL Server Virtual Log Files with Policy Based Management
SQL Server splits your physical transaction log file into logical chucks, called virtual log files (VLF). While your transaction log file may look like one file, logically it is fragmented and the level of fragmentation can result in performance degradation. You can take action to correct the situation, but first you need to know if the situation exists In this tip I will walk you through how to identify this issue.
Policy Based Management
The are a few features that DBA's have been waiting years for. and there are few things that make a Production DBA's shoulders slump more than the subject of Governance.
Renaming SQL Server Policy Based Management Policies
I have created several policies using Policy-Based Management and need to rename some of them. I don’t see an option in the GUI to rename a Policy. In this tip we cover how this can be done.
SQL Server Policy Base Management Has Clustered Index Condition
As SQL Azure gains in popularity, more and more people find themselves thinking about having their databases migrated to SQL Azure. Microsoft has published a list of general guidelines at http://msdn.microsoft.com/en-us/library/ee336245.aspx. One of those guidelines details how every table in a SQL Azure database must have a clustered index. Therefore prior to any migration to SQL Azure you must verify that all tables in your database have clustered indexes.
SQL Server Policy Management to Enforce Database Settings in Development
We have a fairly active development SQL Server with databases getting created or restored quite frequently. There are certain settings we wish to enforce, but none of us want to walk through each database by hand to determine if the databases are meeting the appropriate settings. We're using SQL Server 2008 and I've heard policy management can help. What do I need to do to use it to enforce my settings? I'm looking at settings like who is the owner, is the recovery model set to simple, is autoclose and autoshrink disabled and the like.
Using Mandate Database with SQL Server Policy Based Management
There seems to be a lot of confusion around the Mandate Database option when managing categories in Policy-Based Management. In this tip I will show you how you can take advantage of the Mandate Database option to allow database owners to subscribe to only the policies applicable to thier database while still enforcing universal policies as well.
Using Policy Based Management in SQL Server 2008
As we are reviewing the new features in SQL Server 2008, we found one that looks really interesting - Policy-Based Management. Could you help us to understand how this works and provide some examples? Can you please explain each of the components and how to manage them in the interface and with commands?
Validate SQL Server Backups Exist with Policy Based Management
I recently submitted a tip regarding the existence of your backup files using the undocumented system stored procedure xp_fileexist. But how would you go about executing that script against all your SQL Server 2005 and SQL Server 2008 instances? I took that script and decided to go one step further using Policy Based Management to create a custom policy that you can then run against all your SQL Server 2005 and SQL Server 2008 instances.
Why SYSPOLICY_PURGE_HISTORY job fails in SQL Server 2008 Failover Cluster Instance
I have completed the installation of SQL Server 2008 on a Windows Server 2008 cluster. However, I have noticed that SYSPOLICY_PURGE_HISTORY SQL Server Agent Job has started failing. I am new to SQL Server 2008 Failover Clustering and would like to know the steps which I need to follow in order to resolve SYSPOLICY_PURGE_HISTORY Job failure.
Top 10
Using Policy Based Management in SQL Server 2008
As we are reviewing the new features in SQL Server 2008, we found one that looks really interesting - Policy-Based Management. Could you help us to understand how this works and provide some examples? Can you please explain each of the components and how to manage them in the interface and with commands?
Monitor SQL Server Agent Jobs with Policy Based Management
I need to monitor all the SQL Agents to find out if a job failed in the last 24 hours. Could this be done with policy based management? If so, can you show me how to create the policy? In this tip we go step by step how to set this up.
Enforce SQL Server Database Naming Conventions Using Policy Based Management
In many environments you may want to enforce specific database naming conventions. For example, you may want to prevent others from naming their databases using special characters like dash or period. These and other poor naming decisions can wreak havoc on both internal and custom modules that (perhaps wrongly) make assumptions about database names. It is not possible to catch these issues at the time the name is implemented, because database create and rename events are not transactionable - and thus cannot be captured in DDL triggers. In this tip we look at using PBM to enforce database naming conventions.
Configuring Alerts for SQL Server Policy Based Management
I have configured Policy-Based Management to enforce standards on my SQL Servers. This is fine for the policies where you can prevent the changes; however, many policies can only be logged and not prevented. How can I receive notifications when the policies I have implemented have been violated so I don't have to manually look at each server?
SQL Server Policy Base Management Has Clustered Index Condition
As SQL Azure gains in popularity, more and more people find themselves thinking about having their databases migrated to SQL Azure. Microsoft has published a list of general guidelines at http://msdn.microsoft.com/en-us/library/ee336245.aspx. One of those guidelines details how every table in a SQL Azure database must have a clustered index. Therefore prior to any migration to SQL Azure you must verify that all tables in your database have clustered indexes.
Find unused SQL Server indexes with Policy Based Management
In SQL Server, indexes can be a double-edged sword. Sure, they can make queries run faster, but at the same time, their maintenance can have a negative impact. You can improve your server's overall performance by only maintaining useful indexes - but finding the ones you don't need can be quite a manual process. In this tip, we cover how PBM can assist with this process.
Identify Databases Not in SQL Server 2008 Compatibility Mode
You need to periodically ascertain if there are any databases in your production environment which are not in SQL Server 2008 Compatibility Level. In this tip we cover the step by step process for setting up a policy to identify databases that are not in SQL Server 2008 Compatibility Level.
Internal SQL Server Certificates have expired is this an issue
We decided to create a Policy to check for expired certificates within our SQL Server instance. Once we created the policy and ran it we noticed that some of the internal SQL certificates were expired. We weren't sure if this was an issue or not, so we contacted Microsoft to find out. In this tip we cover what was found and the feedback from Microsoft.
Validate SQL Server Backups Exist with Policy Based Management
I recently submitted a tip regarding the existence of your backup files using the undocumented system stored procedure xp_fileexist. But how would you go about executing that script against all your SQL Server 2005 and SQL Server 2008 instances? I took that script and decided to go one step further using Policy Based Management to create a custom policy that you can then run against all your SQL Server 2005 and SQL Server 2008 instances.
Identify SQL Server Databases Not Recently Backed Up Using Policy Based Management
As the DBA, you need to determine if there are any databases in your production environment which have not had a full backup in the last 24 hours. In this tip we cover the step by step process for setting up a policy to identify all the databases which were not backed up in the last 24 hours.
Last 10
Internal SQL Server Certificates have expired is this an issue
We decided to create a Policy to check for expired certificates within our SQL Server instance. Once we created the policy and ran it we noticed that some of the internal SQL certificates were expired. We weren't sure if this was an issue or not, so we contacted Microsoft to find out. In this tip we cover what was found and the feedback from Microsoft.
Enforce SQL Server Database Naming Conventions Using Policy Based Management
In many environments you may want to enforce specific database naming conventions. For example, you may want to prevent others from naming their databases using special characters like dash or period. These and other poor naming decisions can wreak havoc on both internal and custom modules that (perhaps wrongly) make assumptions about database names. It is not possible to catch these issues at the time the name is implemented, because database create and rename events are not transactionable - and thus cannot be captured in DDL triggers. In this tip we look at using PBM to enforce database naming conventions.
Monitor SQL Server Agent Jobs with Policy Based Management
I need to monitor all the SQL Agents to find out if a job failed in the last 24 hours. Could this be done with policy based management? If so, can you show me how to create the policy? In this tip we go step by step how to set this up.
Find unused SQL Server indexes with Policy Based Management
In SQL Server, indexes can be a double-edged sword. Sure, they can make queries run faster, but at the same time, their maintenance can have a negative impact. You can improve your server's overall performance by only maintaining useful indexes - but finding the ones you don't need can be quite a manual process. In this tip, we cover how PBM can assist with this process.
SQL Server Policy Base Management Has Clustered Index Condition
As SQL Azure gains in popularity, more and more people find themselves thinking about having their databases migrated to SQL Azure. Microsoft has published a list of general guidelines at http://msdn.microsoft.com/en-us/library/ee336245.aspx. One of those guidelines details how every table in a SQL Azure database must have a clustered index. Therefore prior to any migration to SQL Azure you must verify that all tables in your database have clustered indexes.
Enable SQL Server Transaction Log Growth for All Databases
There is an easy way to quickly enable transactional log file growth for all the databases in SQL Server 2008. In this tip we cover the step by step process for setting up a policy to enable transactional log file growth for all the databases using Policy Based Management.
Enable Data File Growth for all the Databases Using Policy Based Management
There is an easy way to quickly enable data file growth for all the databases in SQL Server 2008. In this tip we cover the step by step process for setting up a policy to Enable Data File Growth for all user databases using Policy Based Management.
Identify SQL Server Databases Not Recently Backed Up Using Policy Based Management
As the DBA, you need to determine if there are any databases in your production environment which have not had a full backup in the last 24 hours. In this tip we cover the step by step process for setting up a policy to identify all the databases which were not backed up in the last 24 hours.
Renaming SQL Server Policy Based Management Policies
I have created several policies using Policy-Based Management and need to rename some of them. I don’t see an option in the GUI to rename a Policy. In this tip we cover how this can be done.
Using Mandate Database with SQL Server Policy Based Management
There seems to be a lot of confusion around the Mandate Database option when managing categories in Policy-Based Management. In this tip I will show you how you can take advantage of the Mandate Database option to allow database owners to subscribe to only the policies applicable to thier database while still enforcing universal policies as well.
