Posts Tagged ‘Security’

SQL Server: Columnstore Index on Temp Tables June 20th, 2014

Vinod Kumar

Recently I was talking to a customer during performance testing and they had an interesting twist to using Columnstore Index. Since they were on SQL Server 2012, I had suggested them to use Non-Clustered Columnstore index for their workload. He started to quiz me with a number of questions and I thought it would be appropriate to point them to some resources I had already written. So I took the liberty to send them the two articles:

  1. SQL Server 2012: ColumnStore Characteristics
  2. SQL Server 2012: ColumnStore Part II

The fundamentals and concepts have not changed but where worth a mention and read. Having said that, the counter question after about an hour of their research and reading the contents. “Will it work with Temp Tables? We are doing a reporting workload and are using Temp tables extensively.”

I thought, wow, interestingly I have not written on it till date and is worth a mention though. The easy answer is “YES”, it is quite possible. Why didnt you try was my answer :)

Columnstore Index on Temp Tables

To drive home the concept, in the below example I have created a simple temp table called as #Temp_Account. When I query the table, the execution plan shows as “RowStore” under storage as shown below.

The next logical step is to create our Non-Clustered Columnstore Index. I am using the standard syntax as shown below.

If we try to insert into this temp table, it is still readonly because non-clustered columnstore index has been defined in the table.

Msg 35330, Level 15, State 1, Line 19
INSERT statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, and then rebuilding the columnstore index after INSERT has completed.

After index creation, if we make a simple query we can see the change in Execution Plan. The storage is now showing as “ColumnStore” as shown in the execution plan below.

The above query is simple in nature and hence the execution mode is “Row”. In future posts I will show how this can be changed to Batch mode.

Continue reading...


Troubleshooting with Policy Based Management (PBM) May 26th, 2014

Vinod Kumar

SQL Server 2008 introduced a feature called Policy Based Management that allows a database administrator to manage one or more instances or SQL Server 2008 through policies. Depending on the policy and how it is defined, Policy Based Management can either audit compliance of the policy or enforce the compliance of the policy.  Policy Based Management allows database administrators to create policies and determine how they should be applied to either audit or enforce the policy.  This gives database administrators a powerful a new feature to administer one or more instances of SQL Server 2008 and above.

I have recommended using PBM in some form to most of my customers atleast to keep tab on their environment and make sure the standards / best practices have been followed. Moreover it also gives a great way for Administrators to keep an eye on their environment. Recently an administrator asked be during a session, “Vinod, is there a way to keep track on all the work I am doing with PBM?”. I got down to search my script arsenal from my sessions on PBM immediately. I got a great set of diagnostics script which I felt was worth sharing over blog – infact I was wondering how I forgot to share it with you folks :).

/* What policies exist on the server? */

SELECT as ‘policy_name’, p.policy_id, p.is_enabled, p.execution_mode, as ‘condition_name’, c.facet, p.date_created, p.created_by,

p.modified_by, p.date_modified

FROM msdb.dbo.syspolicy_policies p

INNER JOIN msdb.dbo.syspolicy_conditions c

ON p.condition_id = c.condition_id



/* What conditions exist on the server? */

SELECT as ‘condition_name’, c.condition_id, c.facet, c.description,

c.expression, c.date_created, c.created_by, c.modified_by

FROM msdb.dbo.syspolicy_conditions c



/* What is the health of the policies ON the server? */

SELECT sh.health_state_id, as ‘policy_name’, sh.policy_id,

sh.last_run_date, sh.result, sh.target_query_expression

FROM msdb.dbo.syspolicy_system_health_state sh

INNER JOIN msdb.dbo.syspolicy_policies p

ON sh.policy_id = p.policy_id

ORDER BY sh.health_state_id


SELECT sh.health_state_id, as ‘policy_name’, sh.policy_id,

sh.last_run_date, sh.result, sh.target_query_expression, as

‘condition_name’, c.expression

FROM msdb.dbo.syspolicy_system_health_state sh

INNER JOIN msdb.dbo.syspolicy_policies p

ON sh.policy_id = p.policy_id

INNER JOIN msdb.dbo.syspolicy_conditions c

ON c.condition_id = p.condition_id

ORDER BY sh.health_state_id


/* What is the count of policy health results? */

SELECT, sh.policy_id, sh.result, COUNT(*) as ‘count’

FROM msdb.dbo.syspolicy_system_health_state sh

INNER JOIN msdb.dbo.syspolicy_policies p

ON sh.policy_id = p.policy_id

GROUP BY, sh.policy_id, sh.result

ORDER BY count(*) DESC


/* What categories and subscriptions exist ON the server? */

SELECT * FROM msdb.dbo.syspolicy_policy_categories

SELECT * FROM msdb.dbo.syspolicy_policy_category_subscriptions


/* What is the execution history of policy violations */

SELECT h.history_id, as ‘policy_name’, pp.policy_id,

pp.execution_mode, h.result, h.start_date, h.end_date, datediff(ss,

h.start_date, h.end_date) as ‘exec_time_ss’, d.target_query_expression,

cast(d.result_detail as XML) as ‘result_detail’, d.exception,


FROM msdb.dbo.syspolicy_policies AS pp

INNER JOIN msdb.dbo.syspolicy_policy_execution_history AS h

ON h.policy_id=pp.policy_id

INNER JOIN msdb.dbo.syspolicy_policy_execution_history_details AS d

ON d.history_id=h.history_id

ORDER BY h.history_id


/* What is the count and execution times of execution history policy

violations */

SELECT as ‘policy_name’, p.policy_id, COUNT(*) as ‘count’,

AVG(datediff(ss, h.start_date, h.end_date)) as ‘avg_exec_time_ss’,

MAX(datediff(ss, h.start_date, h.end_date)) as ‘max_exec_time_ss’

FROM msdb.dbo.syspolicy_policies AS p

INNER JOIN msdb.dbo.syspolicy_policy_execution_history h

ON p.policy_id = h.policy_id

GROUP BY, p.policy_id

ORDER BY count(*) DESC , avg_exec_time_ss DESC, max_exec_time_ss DESC


/* What exceptions have occurred? */

SELECT h.history_id, as ‘policy_name’, p.policy_id, h.start_date,

h.end_date, h.result, h.exception, h.exception_message

FROM msdb.dbo.syspolicy_policies AS p

INNER JOIN msdb.dbo.syspolicy_policy_execution_history h

ON p.policy_id = h.policy_id

WHERE datalength(h.exception) > 1

ORDER BY h.start_date DESC, h.end_date DESC

So feel free to use them to troubleshoot and know about your PBM environment. Will share other scripts as I find them from my arsenal of scripts.

Continue reading...


SQL Security– Demystifying Principals, Securables and Permissions November 5th, 2013

Vinod Kumar

I have written other blog posts around SQL Server: Security Practices and Checklist before. But many of my customers come back to basics and ask questions around what Principals, securables and permissions are. This blog post is inspired to those folks who want to understand from the basic. In future posts, I will dwell into more details into each of these security topics.

What are Principals?

A principal is any authenticated identity that can be given permission to access an object in the database system. SQL Server documentation distinguishes between indivisible principals, which are single identities (such as logins), and collection principals, which are collections of identities (such as fixed server roles).

Principals exist at three levels: Windows, SQL Server, and database. The types of principals possible at each of these levels are shown in the following table.




Windows local user account

Windows domain user account

Windows group

SQL Server

SQL Server login

SQL Server role


Database user

Database role

Database group

Application role

What are Securables?

Securables are the resources to which the SQL Server authorization system controls access. Securables are arranged in nested hierarchies called scopes, which can also be secured. The three securable scopes are server, database and schema. Securables at the Windows level include files and registry keys.

The Server Scope

Securables contained in the server scope include:

  1. Logins
  2. HTTP endpoints
  3. Certificates
  4. Event notifications
  5. Databases
The Database Scope

Securables contained in the database scope include:

  1. Users
  2. Roles
  3. Application roles
  4. Assemblies
  5. Message types
  6. Service contracts
  7. Services
  8. Full-text catalogs
  9. DDL events
  10. Schemas
The Schema Scope

Securables contained in the schema scope include:

  1. Tables
  3. Functions
  4. Procedures
  5. Queues
  6. Types
  7. Rules
  8. Defaults
  9. Synonyms
  10. Aggregates

What are Permissions?

Permissions are the rules that govern the level of access that principals have to securables. Permissions in a SQL Server system can be granted, revoked, or denied. Each of the SQL Server securables has associated permissions that can be granted to each principal. SQL Server 2005 introduces the ability to grant permissions at the server scope. In previous releases of SQL Server, server-level permissions were managed using fixed server roles.

The specific permissions associated with individual securables vary depending on the kinds of actions that a securable supports. The following table lists a few example permissions at various scopes. For a full list of permissions, see SQL Server Books Online. Below is a sample set:






Connect to the server.



Create a login.



Alter any login in the server scope.



Full system administrative control.



Alter the login.



Impersonate the login.



Create table in the database.



Alter any user in the database.



Full control of the database.



Alter the specified user.



Select rows from any object in the schema.



Alter any object in the schema.



Take ownership of the schema.



Select rows from the table.



Alter the table.



Full control of the table.

From SQL Server 2005 we support inherited permissions for securables contained in a given scope. For example, a principal granted CONTROL permission on a database object will automatically inherit CONTROL permission on all securables contained in that database and all securables contained in the schemas within that database

As I sign off, want to understand how many of you take security seriously in your SQL Server deployments? How many out there still use the SA accounts in your environments and application connections? Please understand, Security is not an after thought but something part of your fundamental application deployments.

Continue reading...


SQL Server 2012: ColumnStore Part II June 5th, 2013

Vinod Kumar

I wrote about ColumnStore Indexes before and those were around the basic restrictions and things to consider while working with ColumnStore Index with SQL Server 2012. In this blog post let me take a moment to look at the boundaries, use with SSMS and other diagnostics information. Obviously some of the restrictions have been relaxed for next version of SQL Server 2014. That will be for a different blog post.

Datatype restrictions

In SQL Server 2012, columnstore index creation is supported only for the following data types:

  1. char, varchar, nchar and nvarchar (except varchar(max) and nvarchar(max))
  2. decimal (and numeric)
  3. int, bigint, smallint, and tinyint
  4. float, real
  5. bit
  6. money and smallmoney


There is now a new keyword COLUMNSTORE in the CREATE INDEX DDL which is required to create a columnstore index. More about it can be read from MSDN.

CREATE COLUMNSTORE INDEX <Name> ON <tableName>(<Cols>)

Index hint

We can force an index hint to use the columnstore index in a query if required:

… FROM mytable WITH (INDEX (mycsindex)) …

Query hint

We can either use a table hint to force the use of a different index or we can use a new query hint: IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX (MSDN). This new hint will prevent the use of any nonclustered columnstore indexes in the query. Below is an example of using the hint to prevent use of any nonclustered columnstore index in a query:

SELECT DISTINCT (SalesTerritoryKey)
FROM dbo.FactResellerSalesCopy


There are a number catalog views which will show different values for ColumnStore. I am listing a few here:


type : 6



data_compression: 3

data_compression_desc: COLUMNSTORE


key_ordinal: 0 (not a key column; columnstore index does not have a search key like a row-based index)
is_descending_key: 0 (default value; not a key column and index is not "sorted" like a row-based index)
is_included_column: 1 for every column mentioned in the create columnstore index ddl.


page_compression_success_count – Has a value of 0 for columnstore index.

Showplan Enhancement

Three additional properties have been added to the showplan data for columnstore indexes:

  1. Storage – row or columnstore
  2. EstimatedExecutionMode : row or batch
  3. ActualExecutionMode : row or batch

SSMS Enhancements for Columnstore

I am outlining some of the visible enhancements for columnstore with SQL Server 2012 Management Studio. Firstly, Columnstore indexes are represented by a different icon than traditional row based indexes.


The next UI changes is for creating a New Columnstore Index.


The third part is around adding columnstore columns as we create the index.


Final Words

These are some of the basic enhancements with SQL Server 2012 I thought was worth sharing. Do let me know if you have used columnstore index in your production environments and what are the scenarios you found using this type of index useful.

Continue reading...


Upgrading to SQL Server 2012 May 17th, 2012

Vinod Kumar

As new versions of software get released, companies and products also get into this natural transition of upgrading and supporting the latest and greatest. And in the same space, SQL Server is no exception -almost as an predictable cycle, every 3 years we can expect a version of SQL Server getting released. This has been a trend since the SQL Server 2005 version for sure. As part of my job in meeting customers, I have to give recommendations and best practices to move to the latest versions. There are so many nuances to keep in mind as we make a successful upgrade. Let me take a moment to give you some of the suggestions I give to customers. Hope these will be helpful for you too.

Bible to start

You need to always start with preparation phase. This phase involves multiple steps and here are some of them for you to start.

  1. Keep the SQL Server 2012 Upgrade Technical Guide handy with you always. This is a very long document to read but it is worth the reference. I am outlining this as the FIRST reference because I will refrain from what this document covers as content. It is exhaustive and as I said with ~450 pages has a lot of material on how to upgrade. Also for reference SQL Server 2005 to 2008 Upgrade Whitepaper.
  2. If you still want to know WHY UPGRADE? I am attaching a link to some of the new features document for your reference. Knowing the new features is an important step before you upgrade. Later, you must be looking at how you can start maximizing the new features introduced with the new version.

Just upgrading by keeping the compatibility level at a older version is not real upgrade. Evaluate at using some of the new features to increase productivity, performance, maintainability and scalability.

As management takes the decisions for upgrade, DBA’s and developers need to also get themselves set for this task. Any upgrade process cannot be a simple overnight activity but more of a calculated informed step as line-of-business applications are at stake and it can bring down the efficiency of the teams working inside the organizations.

  1. Take a BACKUP of your databases. This is the first step irrespective of what you do next. A backup is required to make sure we have a fallback mechanism if things go wrong. Also make sure if you are using any specific system databases and objects in those DB, these are appropriately backed-up or scripted out.
    1. Take FULL Backups
    2. Transaction Log Backups
    3. Backup Database Master Keys
    4. Backup Service Master Keys
    5. Backup any external resource used (CLR DLLs, Extended SP) and anything that is required for the application outside the database for proper functioning.
  2. Before the backup process, just run a DBCC CHECKDB across the databases to make sure there is no corruption before the backup and they are consistent. This step can reduce any heartburns at a later point in time post upgrade :).
  3. As the upgrade talks happens, get started with tracking if any deprecated features are being used. There are multiple ways to do this – I highly recommend using the “SQL Server: Deprecated Features” Perfmon counter to track it like a background task. As your application runs, this gives you a rough idea of how many deprecated features you might be using inside your application that might have an impact to your upgrade process. There is also a Trace Event for capturing Deprecated features with SQL Server Profiler which you can use but that will be to nail down which code is causing that perfmon spike.
  4. Before initializing the upgrade process, please start by running the Upgrade Advisor as a first step. This surely guides you of potential problems upfront and is one of the essential step too for any SQL Server upgrade process.
  5. Keep track of the Supported Versions and Editions as you plan for Upgrades. With changes in licensing plans, LPIM (lock pages in memory), AWE gone etc. It is critical to keep track of how we upgrade between environments.
  6. Take a typical workload using profiler and keep it handy for a later test. Create a separate environment, bring the databases via a backup / restore operation to the new edition. Look for warnings, errors, changes in functionality and performance bottlenecks. Make sure to replay the workload collected before to see if anything unusual behaviors or errors. Document and then make sure these are mitigates during the actual run.
  7. Also, review the KB article that talks about “How to move databases between computers running SQL Server”. Especially, when we move between servers during upgrades we might need to change the location of files and hence have to account for the same. Also on a new setup, make sure the drivers for SAN or other hardware components are done properly and are the latest as suggested by the vendor.
    1. There are interesting ways to install as we move forward with SQL Server 2012 like, you can install SQL Server with SMB Fileshares as storage options.
    2. Another, is to install it on a Windows Server Core. This is interesting because now you don’t have an UI to manage SQL Server and it also reduces the surface area of attack and patch management is easy too.
    3. Considerations for SysPrep install for SQL Server – read documentation.
  8. Also, since this is an upgrade to a new box, make sure you are having enough space allocated for atleast 2-3 years of growth in mind for the database locations. Now is a good time for you to reassess some of the values defined for the auto-growth settings. Ideally, I suggest never allow SQL Server to grow but you pre-allocate it as part of your administration window cycle by properly monitoring SQL Server Data file free space.
  9. Before you install the Database on a new server, make a note of the Collation requirements for your applications. This is an important step and you don’t want get into a server which is Case-Sensitive while the application is built assuming case-insensitive.
  10. Don’t forget some of the critical addendum functions that you might have configured like replication, mirroring, clustering, service broker, maintenance plans, local user accounts, SQL Server Logins, service accounts, Linked servers, common backup path locations, configuration settings, DR strategies etc. All these need to be accounted and have to be changed accordingly.
  11. Whatever step you take, document them meticulously and run the document through a junior tester on a fresh SQL Server environment. This will test your upgrade process documentation and at the same time make sure that you are not missing any step to avoid last minute glitches. Document every single error encountered and the solution for the same – it is better to be prepared than to fail not being prepared.
  12. When I call out documentation, look out for hidden gems in your process like – Startup Processes, SQL Server Trace Flags that were enabled, Error Log settings, External DLL / CLR assemblies used, powershell scripts, any application Windows services that have been installed, external third party backup tools etc. Also make sure the server is secure by default and make sure any unnecessary services (SQL Browser, IIS, virus scanners, spooler etc) if running have been stopped.
  13. If you are planning to move into Virtualized environment, know what does it take to run SQL Server in Hyper-V Environment (though little outdated, is a worthy read). Also, read about running SQL Server with Dynamic Memory in HyperV Environments. I saw a session video in NA TechEd on this very topic which can be a good watch: Microsoft SQL Server Consolidation and Virtualization: Myths and Realities.
  14. More documents worthy for reading on the Virtualization subject are: Hyper-V is the Best Virtualization Solution for SQL Server, High Performance SQL Server Workloads on Hyper-V, Planning, Implementing, and Supporting SQL Server Virtualization with Windows Server 2008 R2 Hyper-V and Live Migration and Support Policy for SQL Server Virtualization.
  15. What are your plans for upgrade: in-place upgrade, side-by-side on the same box or side-by-side on a different box? Irrespective of the method used Keep in mind Point 1 :).
  16. Have a small test-stub workload where you are aware of the parameters and the expected results from the server. Make sure you run this pre and then post the upgrade and debug any anomalies based on the output. If you have automated test scripts, these are the best to be run against the server for a quick test with expected values.
  17. Have a failsafe strategy if things don’t go as per the plan. Your backup can come handy here and be careful especially if you plan to do a in-place upgrade as there is not much choice to come back other than using the backup route.

Know the fine prints

Please make sure you are aware of the specific call-outs mentioned in the Upgrade Technical reference mentioned above. During upgrade there can be specific features or settings that might need special attention. I am calling out some of them as I have seen at multiple customer upgrade process.

  1. The default setting for max worker threads in SQL Server 2000 is 255. When you upgrade from an instance of SQL Server 2000, the database engine keeps setting the value of max worker threads. However, it is recommended you change the value of max worker threads to 0 before the migration, to allow the Database Engine calculates the optimal number of threads.
  2. Changing the Compatibility Level while users are connected to the database can generate incorrect results for sets of active queries. To avoid inaccurate results, it is recommended to change the Compatibility Level with the database in order to a SINGLE_USER mode and then back to MULTI_USER. Do this post the upgrade activity.
  3. Look at server side settings from the older version, as you plan to move to new hardware’s some of these old values doesn’t make sense. Keep special note to MAXDOP settings, number of TempDB files etc.
  4. Just like the recommendation on Server Core, I highly recommend not to install any new components that you may not be planning to use in the immediate future like – Data Quality Services, PowerPivot, Master Data Services, Analysis Services etc.
  5. Look at the Security considerations and the protocols used for communication as part of upgrade. Each new version there are some changes and analyze the ports you have as standards open at the new environment. Security considerations during Install is documented and worth reading for every version upgrade.
  6. In earlier versions of SQL Server, the values ​​for the table and index row counts and page may be incorrect. Thus, databases that were created in versions prior to SQL Server 2005 may contain incorrect counts. After you upgrade a database from SQL Server 2000, it is recommended that you perform the DBCC UPDATEUSAGE to correct any invalid counts. This DBCC statement corrects the count of rows, used pages, reserved pages, leaf pages and data for each partition in a table or index.
  7. Update statistics for all Indexes after the Upgrade process. This is one of the most important step. Keep in mind also the Plan guides and hints that you have used in your application. Test these sections for any potential performance problems. Given the upgrade, SQL Server engine would have also changed and hence testing them matters. In case of Full-text catalogs, make sure these are also updated.
  8. As a sanity check, make sure to run a DBCC CHECKDB on each of the databases which have been part of the upgrade process.
  9. Over and above this, I also recommend checking each of the database properties which are critical. Check the recovery models, Auto-Close, Auto-Create-Statistics, Auto-Shrink, Auto-Update-Statistics and Auto-Update-statistics-Asynchronously. Also in this list, I highly recommend setting value of CHECKSUM for the Page Verify property at the DB properties level.
  10. Know and document the changes that need to be done on the application to start pointing to the new upgraded server. This might sometimes involve Connection String changes in Web Server deployment. Also, track if any reporting server and Sharepoint installations are pointing to the old servers as data sources. Even these need to be changed as part of movement.
  11. After all this, don’t forget to take a Backup of your system. You don’t want to do all the hard work and find out the HDD crashed the next day :). Backup and secure on a different media and location.

I think this very much sums-up what I had in mind based on various implementations that I have seen. These steps are very much towards migration of the SQL Server Relational engine but doesn’t talk about the SSAS, SSIS, SSRS workloads. I would again reiterate to look at the Upgrade Reference Guide mentioned above as a good guide to start.

A successful upgrade uses a cycle of: Plan, Document process, Test, Refine process, Test, Plan upgrade window, execute, verify upgrade and then opens for business.

Now that we have upgraded into the next version of SQL Server, now start looking actively at how we can start using the new capabilities available inside the next new release. I am just calling out some of them that are worth mentioning here for SQL Server 2012 release:

SQL Server: AlwaysOn Technologies

xVelocity in-memory technologies of PowerView and PowerPivot

BI Sematic Model

File Tables

SQL Server 2012: Express LocalDB

SQL Server ColumnStore Index

Data Quality Services

Security Enhancements – Contained Databases

Big Data Analytics and Hadoop Connectors

Microsoft SQL Server JDBC Driver 3.0

Microsoft Drivers 3.0 for SQL Server for PHP

Links worth keeping track as you upgrade:

Deprecated features:

Discontinued features:

Breaking changes in 2012:

Behavior changes in 2012:

Full-Text Search Backward Compatibility in SQL 2012:

Hope these links and resources will be of help for you as you plan your upgrade and movement to yet another version of SQL Server. I would love to get your inputs and understand if there are any glaring steps that I would have missed mentioning as part of the checklist mentioned above.

Continue reading...