Simon Worth’s SQL Server Weblog

SQL Server and its many complexities

Posts Tagged ‘SQL Server 2008

SQL Server 2008 Service Pack 2

leave a comment »

The team at Microsoft have released Service Pack 2 for SQL Server 2008.  The big enhancement from this SP for me is Reporting Services integration with SharePoint 2010. 

The link to the Service pack is here.  As always, test any new service packs before promoting to a production environment – and have fun.

The list of new features are as follows (directly from the Service Pack page)

  • 15K partitioning Improvement. Introduced support for a maximum of 15,000 partitions in tables and indexes in Microsoft SQL Server 2008 Service Pack 2 in the Enterprise, Developer and Evaluation Editions. For details on how this support can be enabled and disabled, along with recommended operation paramaters, see the white paper Support for 15000 Partitions.
  • Reporting Services in SharePoint Integrated Mode. SQL Server 2008 SP2 provides updates for Reporting Services integration with SharePoint products. SQL Server 2008 SP2 report servers can integrate with SharePoint 2010 products. SQL Server 2008 SP2 also provides a new add-in to support the integration of SQL Server 2008 R2 report servers with SharePoint 2007 products. For more information see the “What’s New in SharePoint Integration and SQL Server 2008 Service Pack 2 (SP2)” section in What’s New (Reporting Services).
  • SQL Server 2008 R2 Application and Multi-Server Management Compatibility with SQL Server 2008.
  • SQL Server 2008 Instance Management.With SP2 applied, an instance of the SQL Server 2008 Database Engine can be enrolled with a SQL Server 2008 R2 Utility Control Point as a managed instance of SQL Server. For more information, seeOverview of SQL Server Utility in SQL Server 2008 R2 Books Online.
  • Data-tier Application (DAC) Support.Instances of the SQL Server 2008 Database Engine support all DAC operations delivered in SQL Server 2008 R2 after SP2 has been applied. You can deploy, upgrade, register, extract, and delete DACs. SP2 does not upgrade the SQL Server 2008 client tools to support DACs. You must use the SQL Server 2008 R2 client tools, such as SQL Server Management Studio, to perform DAC operations. A data-tier application is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.

Written by Simon Worth

September 29, 2010 at 12:59 pm

Creating Policies with Policy Management

leave a comment »

One thing I really like about SQL Server 2008 is the newly introduced Policy based Management. This is a central place where policies on the server can be enforced and examined for compliance of corporate business rules – either for the current instance or across multiple servers. Examples of policies could be

  • No table can be created without a clustered index
  • Database object naming must follow development standards
  • Logins must have password policies enforced

These are just a few examples of the many facets that can be evaluated in SQL Server. Let’s review Policy based management and how it works.
Policy Management can be found within SQL Server Management Studio under the Management > Policy Management folder within the Object Explorer pane.

Figure 1 – Policy Management Folder in SSMS

Before diving directly into Policies for SQL Server 2008 there are some key terms and concepts to understand before proceeding:

  • Facet – Managed targets that have exposed properties to the user and can be evaluated within conditions. Examples of facets would be Table, Database, Server, User, Application Role, etc. In the case of a table, just a few of the facet properties exposed (and therefore can be evaluated in a condition) would be:
    – Filegroup
    – HasClusteredIndex
    – IndexSpaceUsed
    – RowCount
    – Schema
  • Conditions – These are evaluations that take place on one or more properties of a single facet. So you could have a condition that checks the “User Defined Function” facet, but within the defined condition you can be checking more than 1 property of the user defined function – such as:
    – @FunctionType = Scalar
    – @ImplimentationType = TransactSQL
  • Policies – these are the implementation of a defined Condition (contains 1 or more evaluations of a facet) applied to Facets in order to make an evaluation. So in the case of “No table can be created without a clustered index” you would create a Condition (see figure below) on the “Table” Facet which evaluates the @HasClusteredIndex property to make sure the property’s value is true.

Policies are defined using conditions, and conditions are defined using facets. So a policy is simply the “container” of a condition and the condition is the evaluations of a single facet’s properties.

Figure 2 – Creating a condition using the Table Facet

As far as I can tell you cannot create a policy that contains more than 1 defined condition – so you could not create a policy that checks more than 1 facet (and I’m not sure how helpful checking more than 1 facet at a time would be). Keep in mind the defined condition can evaluate more than 1 property of a facet – so while you can’t add many conditions to the policy, the condition you have defined can check more than 1 property of a Facet.
Using the clustered index example if you wanted to check if a table has a clustered index using a condition as well as check the naming convention of that table – both of those evaluations would have to exist in the same defined condition – rather than having a condition for “Has Clustered Index” as well as a condition for “Table Meets Naming Conventions”. They would both be defined within the same condition and used in a policy created called “Is Table in Compliance”.

Figure 3 – Multiple checks within 1 defined condition

Once the condition has been created with the properties exposed that you are interested in evaluating – you create the policy in much the same way as the condition.
By right clicking on the “Policies” folder in SSMS and then clicking “New Policy…” you are presented with a dialog box for defining the policies characteristics.
Give the new policy a name that makes sense and if you want the policy to be enabled make sure the “Enabled” checkbox has a checkmark in it. Enabling a policy in this way uses policy automation and will be reviewed in a moment. See execution mode description below for more details.

Figure 4 – New Policy dialog box

Figure 4 shows the various components of the General tab, below is a list of each component.

  1. General tab indicator – used to navigate through general setup of the policy.
  2. Description – Used to define categories of the policy, descriptions of the policy, and text to display when policies are violated, as well as created and modified meta data.
  3. Name – pretty self explanitory.
  4. Check Condition – This drop down menu contains the defined conditions on the server and also allows for creating new conditions inside the policy editor.
  5. Against Targets – used to define the targets that the policy will evalulate. In the case of our example “Is Table In Compliance”, the targets would be All Tables in All Databases.
  6. Execution Mode – On Demand, On Schedule, On Change – Log Only, On Change – Prevent.
  7. Server Restriction – This allows the policy to be evaluated against a condition defined at the server level facet. So if you were interested in only applying the policy against Development or Enterprise editions you can define a Condition on the server facet that evaluates the @EngineEdition property and set the condition evaluation value to EnterpriseOrDeveloper.

A little more about Execution Mode. As stated, there are 4 execution modes that can be assigned to the policy you create. Here is what each one does:

  • On Demand – Policy does not run unless specifically caused to do so by executing the “Evaluate” option for the policy.
  • On Schedule – The policy is run on a pre-defined schedule (defined in SQL Agent) and logs the results.
  • On Change – Log Only – Allows changes to be made – such as an out of compliance table creation, but logs the action.
  • On Change – Prevent – Any changes that violate a policy are not allowed by the server. Error messages are delivered to the client making the changes.

Once you have the General tab components configured to your liking – move to the Description tab to configure the category of the policy, description of what the policy is enforcing, and additionally add help text to display to the client when in violation of the policy. You may also enter an address that will take the user to a web site if the link is clicked after policy evaluation.

Figure 5 – Policy dialog box Description tab

If the “Execution Mode” for the policy is set to “On Change – Prevent”, the text you enter in the “Text to display” area will be returned to the client – such as Figure 6.

Figure 6 – Text to display returned to query editor

In the case of our Table In Compliance example, one thing I didn’t like was when I tried to create a table that was out of compliance using the GUI table designer, I just got an error message saying “‘tbl_testTable’ table – Unable to create table. The transaction ended in the trigger. The batch has been aborted.”.
This isn’t nearly as helpful as when I tried to create the table using the query editor – where the help text we defined was returned to me to let me know exactly why the table could not be created. In the case of the GUI editor, I have no idea why the table could not be created, all I know is it was not created and that a transaction ended in the trigger. I won’t however dwell on that as I am still using the Feb CTP and things could change in the next release.

Now that both the General tab and the Description tab have been configured we hit OK to create the policy on the server. Now that it is created it will show up in the Policies folder in SSMS. If you right click the policy you created you can click evaluate to see what objects on the server are out of compliance and try to create tables that do not meet the standard.

That’s all for now on Policy based management in SQL Server 2008.

Written by Simon Worth

June 2, 2008 at 6:45 am

SQL Server 2008 Deprecated Features

leave a comment »

With each release of SQL Server there are deprecated features – and 2008 is no exception. Deprecated features are features that will not be supported past the current version of the product. In the case of SQL Server 2008 there are many features found in 2005 that will still work in 2008 but will not work any further than that.

While there are many web sites that list the deprecated features in SQL Server 2008, it is difficult to look at all of them and figure out which ones will have impact. This is especially true in the case of T-SQL – simply because of the amount of code we put into it.

With SQL Server 2008 there is a new performance object called “SQLServer:Deprecated Features”. This performance object contains many counters – each containing a feature marked for removal in the next version.

Knowing what features are going to disappear in a future release is valuable from an upgrade path point of view. Knowing what needs to change in order to be error free in the next release will aid the transition to the newest product.

In order to use the SQL Server performance object of deprecated features it’s as easy as opening performance monitor and adding the counters you are interested in. Open the run dialog box and type in “Perfmon” and click OK. This will open Performance Monitor (depending on the OS you are using you may have to navigate around a bit to get to the counters display area – I’m looking at you Vista!). To add the deprecated Features object, right click on the graph and hit “Add Counters…”. Once the counter dialog box is open, scroll through the available counters until you reach “SQLServer:Deprecated Features” and click on it. This will expose the counters available for that performance object (there are many).

Microsoft has a good list of the available counters here – listing the counter name and a description of what it captures. Below is a list of features that will be removed from the product in a future release that I can see having an impact on current development.

  • ‘#’ and ‘##’ as the name of temporary tables and stored procedures
    • Currently you can create a temporary table just using # or ## (local or global) sign. Not good development practice to just use the # sign without any other characters, but if it can be done you can bet someone will do it.
  • ‘::’ function calling syntax
    • This one actually always bugged me in 2000. In 2005 you could exclude the ‘::’ function calling which made more sense to me. Adding the ‘::’ just seemed like a level of complexity that was not needed and a level of abstraction was removed to the call.
  • @’ and names that start with ‘@@’ as Transact-SQL identifiers
    • If you try to create a table called @TableName you would receive an error indicated incorrect syntax near @TableName, however, if you enclosed @TableName in double quotes the statement would execute and you would in fact have a table called @TableName. That would get very confusing when debugging code.
  • String literals as column aliases
    • You will no longer be able to write things like this ‘MyColumn’ = Column1 FROM…
  • DBCC ShowContig
    • Used to view index fragmentation information for tables – will be replaced by dynamic management view dm_db_index_physical_stats.
    • Nice feature where you can set how many rows you want returned for a SQL Statement. No longer allowed for INSERT, UPDATE, or DELETE statements. Was once useful for getting rid of duplicate records. TOP can be used instead.
  • Table hint without WITH
    • Easy enough, just add WITH – so instead of SELECT * FROM sys.Objects (NOLOCK), it now becomes SELECT * FROM sys.Objects WITH (NOLOCK)
  • System catalog calls using old style prefix of sys
    • such as sysobjects and syscolumns, rather than sys.objects and sys.columns.
  • Many ‘sp_’ stored procedures will be deprecated in 2008.

Those are just a few of the deprecated features in 2008 for the database engine. You can find a complete list of deprecated features for

Database Engine =

Replication =

Integration Services =

Analysis Services =

Written by Simon Worth

May 27, 2008 at 9:26 pm