Skip to main content

Policy Based Management (PBM) in SQL Server

 1: To restrict users to prefix "sp_"  for User Defined Stored procedure

To restrict users to prefix "sp_"  for User Defined Stored procedure due to following reasons:

  • For performance:

The master db will always be checked first because of the prefix of sp_ which is time consuming.  All system defined stored procedure name starts with prefix "sp_".

  • Possible conflict of user created stored procedures with system stored procedures:

If a user-defined stored procedure has the same name as a system stored proc, the user-defined procedure will never be executed.

2. To follow a particular naming convention for procedures Eg ‘usp_’

3. A company policy prohibits enabling Database Mail or SQL Mail. A policy is created to check the server state of those two features. An administrator compares the server state to the policy. If the server state is out of compliance, the administrator chooses the Configure mode and the policy brings the server state into compliance.

 4. The AdventureWorks2012 database has a naming convention that requires all stored procedures to start with the letters AW_. A policy is created to enforce this policy. An administrator tests this policy and receives a list of stored procedures that are out of compliance. If future stored procedures do not comply with this naming convention, the creation statements for the stored procedures fail.

5. Enforce SQL Server Database Naming Conventions. Standard naming convention for databases would likely include the following rules:

  • Does not include any special characters CREATE DATABASE [embedded.period]
  • Does not start with a number; Eg: CREATE DATABASE [1_start_number];
  • Does not have embedded or trailing spaces; CREATE DATABASE [trailing_space ]
  • On demand policy to ensure that all the databases have the Auto Shrink option set to True. By default, a database that is created has Auto Shrink set to False, as shown in the figure below.

Benefits: 

  • Policy Based Management is considered as one of ‘standard’ method to administer database proactively.
  • It allows DBAs to create various policies to enforce certain rules to single database, to group of databases or to whole server.
  • It is possible to combine more than one condition into one policy.
  • Policy based management is loaded with several advantages. It can help you implement various policies for reliable configuration of the system.
  • It also provides additional administration assistance to DBAs and helps them effortlessly manage various tasks of SQL Server across the enterprise.

Limitation:

  •  Policies can affect how some SQL Server features work.For example, change data capture and transactional replication both use the systranschemas table, which does not have an index. If you enable a policy that all tables must have an index, enforcing compliance of the policy will cause these features to fail.
  •  Certain policies can’t be enforced i.e. they will not have the option “On Change : Prevent” applied to them and so you won’t get the option in evaluation Mode drop down at the bottom
Labels:

Comments

Post a Comment

Popular posts from this blog

SQL Server script Error: Insufficient result space to convert uniqueidentifier value to char.

Error:    G etting below error while trying to copy data from one table to another: Msg 8170, Level 16, State 2, Line 1004  Insufficient result space to convert uniqueidentifier value to char.   Fix: UserID column which would be having Unique identifier data require more space (36 character). Size is specified as 15 here. To fix it just specify 36 as varchar length or specify Uniqueidentifier as data type for UserID  column.
Post a Comment
Read more

While executing Select query to pull data from a different server, SQL Server is showing following error:

Msg 7202, Level 11, State 2, Line 1 Could not find server 'LAPTOP-09O6NE3K\SQLEXPRESS03' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Completion time: 2024-02-05T17:18:24.6869756+05:30 Solution: Step1:  Check if the server is available in the sys.servers table SELECT name from sys.servers Step2:  If no, add the new server to the sys.servers table EXEC sp_addlinkedserver @server = 'server name' Eg" EXEC sp_addlinkedserver @server = 'LAPTOP-09O6NE3K\SQLEXP RESS03' Step3:  Recheck if the new server name is added to the sys.servers table. If yes, try then rerunning the select query which throwed the error. Now it should pull data from the table in the new server successfully.    
Post a Comment
Read more

Overcoming Performance Issues with Memory Optimized tables - SQL Server 2014 CPT2

Overcoming Performance Issues with Memory Optimized tables - SQL Server 2014 CPT2 1.        Problem Tuning    databases so that they provide the best possible performance is one of the most important responsibilities of database administrators (DBAs).   SQL Server was originally designed at a time when it could be assumed that main memory was very expensive, so data needed to reside on disk except when it was actually needed for processing.   As the amount data    within an organization increases, accumulation of data affects the productivity of DBAs, who must spend considerable time and effort on achieving the high levels of database performance. 2.        Solution Nowadays memory is much cheaper and a working data set can be stored entirely in memory. The assumption that main memory was very expensive is no longer valid as memory prices have dropped enormously. Memory-opti...
Post a Comment
Read more