PM contains a plethora of concepts and definitions. To help explain these, we’ll consider a standard naming convention scenario as an example. Suppose that the development management team has come out with guidelines describing the proper naming convention to be used in your organization, with the following specific requirements:
Keeping this scenario in mind, let’s start reviewing the key components that make up PM.
A managed target defines which entity you want to manage. These entities that PM is able to manage in SQL Server 2008 all reside within a SQL Server instance. Examples of entities include the instance of SQL Server itself, a database, a table, or a schema. Note that these entities by themselves can form a natural hierarchy.
As an example, a database contains objects like tables and stored procedures. Thus, if you define a database as a target, you have the ability to apply a set of target filters to obtain a more specific target, like “only databases that are greater than 1GB in size.”
In the example, you would like a filtered database target that is described as “all tables within the Staging’ database.” You can create this filter using a PM condition, as described shortly.
Each managed target has various properties and behaviors associated with it. For example, a database has properties that describe things like collation, default filegroup, whether it’s a snapshot, and its last backup date. PM exposes these properties through a management facet. Facets not only model properties of a target, but they sometimes also model behaviors. For example, a behavior within the Database Maintenance facet is Data AndBackup OnSeparate Logical Volumes, which returns whether or not the database has its data and backup files on separate logical volumes.
Table lists all the available facets and examples of some of the facet properties. To see a complete list of properties of a facet, select to view the properties of a facet in SQL Server Management Studio.
In the standard naming convention example, you are concerned with the names of tables and the schemas where the table resides. You want a facet that exposes a name property and schema property for a table. Looking at the list of available facets in Table, you can see that the Multipart Name facet fits this description. This facet exposes these properties from any of these managed targets: stored procedures, synonyms, tables, user-defined functions, user-defined types, views, and XML Schema collections. Now that you have found your facet, it is time to create a condition.
Facets by themselves state only the facts. For PM to be useful, we want to define certain conditions that are interesting to us as administrators, such as “when database file size is greater than 5GB” or “when schema is equal to developer.” For this, we have conditions, which are Boolean expressions defined on a specific management facet. They specify a set of valid states of the management facet.
To create a new condition, connect to SQL Server using SQL Server Management Studio and navigate down to the Policy Management node, under the Management node of the server instance. Right-click this node and choose to create a new condition. You will see the New Condition dialog box, as shown in Figure.
Creating a new PM condition
The Facet drop-down list shows all the facets listed in Table. In order to define a condition, you must specify a facet. For this example, selecte Multipart Name as the facet, and you will see the options @Name and @Schema in the grid’s Field column, as shown in Figure.
Field column showing properties of selected facet
The Field column in the grid is populated with properties available for theselected facet. If you had selected the Backup Device facet instead, for example, the Field list would change to those properties that are exposed by the Backup Device facet. This dialog box is also smart enough to know which kinds of values apply to which fields. For example, if you selected @BackupDeviceType, the Value column would be populated with possible backup device types.
Value column showing properties of selected field
Returning to the sample table name condition, you want to specify the @Name field and change the operator to LIKE, since you want a partial string match, rather than an equality. Next, put the value tbl% in the Value cell of the grid.
At this point, you could click OK, and you would have a condition that checks only if the first three characters of the Name property are tbl. However, for the example, you need a more complex expression. You want to make sure the first three characters of the Name property are tbl and that the schema of the table is only within the developer schema or the test schema. To add the first schema restriction, click the AndOr cell on the next line in the grid and select AND. In the Field cell, select @Schema, and put 'developer' in the Value cell, as shown in Figure. Then click the AndOr cell below that, select OR, select @Schema in the Field cell, and enter 'test' in the Value field.
Building an expression using expression grid
It is also possible to create groups of expressions. In the example, you also need to group both @Schema fields, since the logic would be incorrect if you did not. This grouping can be done by selecting both the @Schema rows, right-clicking, and selecting Group Clauses, as shown in Figure.
Grouping expression clauses
At this point, you have created a condition whose Boolean expression evaluates to true when the name of a table contains tbl as the first three characters and the schema for the table is either in the developer schema or the test schema. The final configuration for the condition is shown in Figure.
A configured condition
A policy binds a condition against its expected behavior. To create a policy, right-click the Policies node in Object Explorer and select New Policy. For this example, enter the name Table Name Policy for the policy and select Table Name Condition for the check. You will be presented with a list of objects to apply this policy against, as shown in Figure.
Creating a new policy
This list in the Against Targets box is based on the facet you selected for the condition. If you had selected a condition that was checking the database file size, for example, you would only be allowed to apply this policy against database targets.
Since this example is concerned only with tables within a database, check the Every Table check box. The Every words in the Against Target box are links that reveal a drop-down list that allow for further filtering. Click the Every link beneath the check box you just selected to see the list of available conditions for a database.
Options to filter targets within the policy
Since you want to apply this policy only for the Staging database, select New Condition. You will see the dialog box for creating a new condition, with the Database facet already selected. Finish the creation of this condition by specifying @Name = 'Staging'.
Condition for restricting the name of the database
At this point, your Against Targets box should look like Figure.
Specifying the targets for the new policy
In addition to creating your own conditions, you can use one of the predefined conditions that come as part of the SQL Server 2008 sample policies, which are described in the next section. The Execution Mode option beneath the Against Targets list controls when the policy is applied. The choices are as follows:
For this example, choose On Change – Prevent to abort the transaction if it violates the policy.
By default, this policy can run on any server to which it is applied. If this policy should run only on certain types of servers (for example, only when the server instance is SQL Server 2000 or only when the instance is an Enterprise Edition), you can apply a predefined condition or create your own. Use the Server Restriction drop-down list to pick the desired condition for the server type, if any.
The Create New Policy dialog box also contains a page called Description, where you can provide some extra information that will be presented when the policy has been violated.
Adding a policy description
As a policy editor, you are free to put whatever text you deem suitable in the Description box. Obviously, one might not want to be as candid as in Figure if this policy is defined on a live server.
You can also add a hyperlink as part of the policy violation error. This hyperlink will be displayed along with the description text.
To complete your policy definition for this example, check the Enabled check box on the General page of the dialog box. Click OK, and your policy is created and actively in use.
You can now test the policy by issuing the following commands:--Test Case: Create a table in the dbo schema with name violation
issuing this CREATE TABLE command yields the following:
The transaction ended in the trigger. The batch has been aborted.
If we corrected the table name and called it tbl_Orders instead of Orders and left the schema of developer unchanged, the command would execute successfully, since both criteria are satisfied. We would also get this same policy violation error if the name were acceptable but the schema were not developer or test.
Policies can be deployed across multiple servers by native import and export support. Basically what is provided is the ability to script out in the policy. The resulting script is an XML document, which can easily be imported on any destination server.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.