I recently talked to a customer about SnapManager, its recommendation on the number of databases and about a way to prevent the creation of more than 35 databases.
The NetApp recommendation is no more than 35 databases on a single volume. The reason is that SQL Server has to use 4-5 worker threads per database in order to manage the freezing and un-freezing I/O for each database.
Look at this note:
More information on this issue may be found here
To never ask about servers where each day new databases can appear created by other persons (IT group or Support) having rights who don’t know this limit, I proposed to create a policy in SQL Server to be alerted.
What is a Policy-based Management?
It’s a new management feature introduced in SQL Server 2008.
Here is the definition on MSDN:
“Policy-Based Management allows DBAs to define a set of policies that can control many different aspects of SQL Server”.
How to create a policy?
We need 4 steps for this:
- Step 1: Create a Condition
- Step 2: Create a Policy
- Step 3: Evaluate the Policy
- Step 4: Create an alert associated with the policy
There is no property or predefined function to get the number of user databases…
Fortunately, it’s a very simple query which gives the number of user databases:
select COUNT(*) FROM master..sysdatabases WHERE name NOT IN ('master','tempdb','model','msdb','distribution','resource')
Remarks: I excluded all System DBs that’s why I added distribution and resource in the list.
This query is the first point before we create a condition based on it.
Step 1: Create a Condition
A condition contains one or more Boolean expressions that can be combined with AND, OR, and NOT. The conditions can be quite complex.
Run Management Studio and connect to an SQL Server instance.
In the Object Explorer, go to Management>Policy Management>Conditions and with a right-click select “New Condition…”
First, enter a name like “SnapManagerDatabasesLimitation” and click on the button with 3 points(…) in the column Field
Oups! I forgot, before change the Facet to “Default” value.
A facet is a collection of properties for an object such as a table, a stored procedure, or an audit. There are hundreds of Facets.8-O
In the new window, add the query with the Command ExecuteSQL()
The ExecuteSql function has two arguments:
- a character string that defines the type of data being returned
- a character string that contains the SQL query
The Final Query is:
ExecuteSql('Numeric', 'select COUNT(name) FROM master..sysdatabases WHERE name NOT IN (''master'',''tempdb'',''model'',''msdb'',''distribution'',''resource'')')
Select the operator
In the field value, we insert 35 (the maximum defined for SnapManager)
Add a description like the introduction of this document (I don’t have more inspiration8-O)
And we have the condition for the policy…
Right click and select “Script Condition as>Create To” and you have the xml definition of the condition
Step 2: Create a Policy
A policy is a rule based on a single condition and applied to one or more targets.
In the Object Explorer, go to Management>Policy Management>Policies and with a right-click select “New Policy…”
First, enter a name like “SnapManagerDatabasesLimitation” and Select the new condition we just created in step 1 (“SnapManagerDatabaseLimitation”)
In the targets, verify that “Every Default” in “Every Database” is selected
Select “On Schedule” in the Evaluation Mode and in schedule Click New for a new schedule or click Pick if you have an existing schedule
Create a new schedule called “SnapManagerControlSchedule” for every day at 12:00 PM
In the Description tab, select the category.
If you haven’t a category for SnapManager, click on New
Enter Best Practice: SnapManager for the category
To complete the policy, add some description like the introduction of my blog and validate with a click on OK.
And now, we have the policy with a condition.
Step 3: Evaluate my Policy
This function is to test if the new policy is running like I want.
In the Object Explorer, go to Management>Policy Management>Policies>SnapManagerDatabasesLimitation and with a right-click select “Evaluate”
In Policy Selection, verify that the policy SnapManagerDatabasesLimitation is selected.
You have a warning message:
‘SnapManagerDatabasesLimitation’ contains scripts. You should only run policies from a trustworthy source
I consider myself as a trustworthy source:-)
Click on Evaluate button.
A security notice appears for the warning, just click on Run.
In Evaluation Result Tab, we can see the result of the launched evaluation:
With a green check, I can see that my policy has return true (I have no more than 35 user databases).
To see more click on View… in Targets Details
In Results Detailed View, we can see that I have 11 user databases.
To test when the result return false, I don’t create 24 databases, I change the Expected Value in the condition(it’s easy)
We run an evaluation and surprise…
I have a red cross in place of the green check… The policy returned false!
My policy running like I want.
The last action is to enable the Policy
This symbol indicate that the policy is disabled
This symbol indicate that the policy is enabled
The last step is to create an alert for this policy.
Step 4: Create an alert associated to my policy
We cannot prevent database creation, but we can be alerted when the number of database exceeds our limit of 35.
In the Object Explorer, go to SQL Server Agent>Alerts and with a right-click select “New Alert…”
Give a name like dbi_service_Alerte_DBNumberExceed.
The type is a SQL Server event alert and the error number associated to the event “on Schedule” is 3405
For your information:
The error message numbers are different for each of the evaluation modes and are shown below.
|30450||On change: Prevent when the policy is enabled for automatic evaluation|
|30451||On change: Prevent when the policy is set to on demand evaluation|
|30453||On change: Log|
Sent: Tuesday, August 21, 2012 12:01 PM
To: Haby, Stéphane
Subject: SQL Server Alert System: ‘dbi_service_Alerte_DBNumberExceed’ occurred on \Server1Instance1
DATE/TIME: 21/08/2012 12:00:35
JOB RUN: (None)
Now a Support Operator/DBA will be notified if the number of databases exceeds 35.
It will be alerted according to the schedule defined in the policy.
DBA may interfere directly with the application or the database creator who just have created additional databases.
Hoping that the next version of SnapManager removes this limit especially for applications like SharePoint for which the number of databases can quickly explode…!