Capture deadlock with Extended Events

if the extended event is created in the default location sys.fn_xe_file_target_read_file works without the full path of the file, just the file name is required.

This works fine;
XEvent.value(‘(@timestamp)[1]’, ‘datetime’) as UTC_event_time,
XEvent.query(‘(data/value/deadlock)’) AS deadlock_graph
SELECT CAST(event_data AS XML) as [target_data]
FROM sys.fn_xe_file_target_read_file(‘system_health_*.xel’,NULL,NULL,NULL)
WHERE object_name like ‘xml_deadlock_report’
) AS [x]
CROSS APPLY target_data.nodes(‘/event’) AS XEventData(XEvent)

Step 2:

Open one of the deadlock graph and save as XML document.
Open the .XML file in SQL-Sentry to see the processes in detail.


Dealing with SQL Azure Database

** Performing Backup to Azure **

1. Need a Storage Account at Azure.
2. In the storage, need a Container (kinda folder) for separating backup file for other files. 
	>> Container size can't exceed 1 TB.
3. Need Access Keys (can be copied from Manage Access Keys at Azure)
4. Define a "Credentials" under "Security". 
	>> Identity can be name of Storage Account and Password will be the access key.
5. In backup task, 
	>> choose "URL" as destination (Sample URL -
	>> Provide backup file name
	>> choose "Credential" from available list. (refernce point 4)
	>> provide container name (refernce point 2)

	Note - Under Media Options >> Overwrite Media option and its suboptions are not supported when "URL" is a backup destination.
** Restoring Backup from Azure **

One Way - Download .bak from Azure store at local drive and restore it.

Another Way (Directly from Azure, Restore to create a new database)

1. Choose Device as a Source and click on Browse button
2. Select URL in the Backup Media Type and hit Add button
	>> Specify the Storage Account 
	>> Add the Access Key
	>> Select the existing credentials
	>> you will see the available containers for correct path of backup file.
3. Click Ok to start validation.
4. Once the file validated, then other restore options are made available to choose from.
5. Choose the appropriate option to finish the restore.

** Backup and Restore Azure SQL Databases via Management Portal **

Note - There is no option to take backup, configure SQL Jobs in Management Portal when dealing with Azure hosted DB's. 
So, creating a copy of DB is considered as "Backup". 

>> For Backup operation, connect to SQL Azure from your local Management Studio and execute the command 

1. Make a copy on same server in SQL Servers pool.

	>> Sample Command - Create Database SchoolCopy AS COPY OF MyAzure.School

2. Make a redundent copy on the different server in SQL Servers pool

	>> Export the database using (.bacpac) file
	>> Select the BLOB storage account
	>> Select the Container 
	>> Provide Login and password to create a deployable package.

	>> Select another server from Azure Server pool
	>> In Databases, choose (+ New) option from lower left corner (as Create a SQL database don't give import option)
	>> Data Services >> SQL Database >> Import (from BLOB storage account)
	>> Specify the URL for (.bacpac) file and click Open
	>> Select a server to import on.
	>> Provide Login and password to import the database.

SQL Audit logs shows wrong time on the Events

I have implemented SQL audit for  SQL Server 2012 one one cluster instance.

When i view audits logs, wondering why does it display wrong time.i goggled and found this is a known bug.

SQL server audits are logged with UTC times.

Workaround for this issues is to use the below queries to get the correct result.Auditlog_incorrect time stamp

as corrected_time,
action_id ,
server_instance_name ,
database_name ,
schema_name ,
object_name ,
statement ,
file_name FROM sys.fn_get_audit_file( ‘F:\Audit\*.sqlaudit’ , DEFAULT , DEFAULT)

Additional info :

Continue reading

Installation mistake : SQL Server on mount points:

The other day my colleague told that he is facing permission error while doing the installation. On checking found below error. It is a common mistake that we do make assuming the root of the mount point as a folder 🙂 .


This issue occurs when you are trying to install SQL Server to the root of the mount point. Avoid that, instead create and install into a folder under the mount point to allow the correct propagation of permissions. The permissions that are applied to the root drive file system will not be propagating to the mount point file systems, they are controlled separately.

Another option is to add sql service account which we are using for installation as owner of the ‘’ (under system volume information/advanced security settings) of the mentioned mount point in the error. But, it’s a time consuming process and changing system configurations is bit risky at a later stage. It’s always better to create a folder under the mount point for smooth installation.

SQL Server Index

The most important factor in Sql Server to achieve good query performance is indexing.

The index provides a fast way to look up data based on the values within those columns.For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.

Note: PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a non-clustered index is not specified when you create the PRIMARY KEY constraint.


An index is made up of set of pages (index nodes) that are organized in a B-tree structure.

When a query fires against an index column, the query engine starts at the root node and navigates down through the intermediate nodes. It continues down through the index nodes until it reaches the leaf node.

For example if you are searching for the value 123 in an index column, query engine would look for the root node to determine which page to reference in the top intermediate level. In this example the first page points the value 1-100 and second point to value 101-200, so the query engine would go to the second page on that level. From there, the query engine would determine to go the next intermediate level. From there query engine would navigate to the leaf node to for value 123.

The leaf node will contain either the entire row of data or a pointer to that row, depending on whether the index is clustered or non-clustered.

Clustered Index:

Cluster index

A cluster index store the actual row of data on it its leaf node. According to the above example, that would mean that entire row of data associated with the key value 123 is found on its leaf node.

  • When a cluster index defined on a table, it tells the Sql Server how to order the table data pages. It organizes the data pages into a B-tree structure where root pages at the highest level and leaf pages at the lowest level.
  • Index pages in the root and intermediate level contain the cluster key value and a page pointer down into the next level of the B-tree. This pattern continues till it reaches the leaf page. Leaf level of the cluster index is where the actual data stored.
  • In cluster index, index values are sorted either on ascending or descending order.

Non-clustered Index:

It’s impossible to physically arrange same data in 2 different ways without having the separate structure that store   information. This is where non clustered index comes into picture.

Non-clustered index tree structure is same as the cluster index where instead of having the base data at the bottom or leaf level of tree, we have set up pointer or references back to the base table data. With this type of structure we can use any index key order we want because orders are independent of the base table data. This entire structure called non-cluster index.

  • Index pages in the root and intermediate level contain the key value and a page pointer down into the next level of the B-tree. This pattern continues till it reaches the leaf page.
  • Non-cluster index leaf pages store both the index key value AND a pointer to locate the actual data rows in table where actually data stored.
  • Whereas in the clustered index the leaf level contains the actual data rows, in a non-clustered index, the leaf level contains the clustered key or Row ID (RID), which SQL Server uses to find the rest of the data.

Let’s says we have defined a non-clustered index on the EmployeeID column in Employee table, if we are running a query to retrieve data (empname, empaddress, etc) for an EmployeeID 18, it uses the cluster index to retrieve other column data since actual data stored in the leaf level of cluster index.

Non-Cluster IndexSummary of clustered and non-clustered index:

  1. A table can have multiple non-clustered indexes where as a table can have only one clustered index.
  2. Non clustered index store both a value and a pointer to the actual row that holds that value. Cluster index actually stores the row- level data in its leaf nodes.
  3. Cluster index speeds up the data retrieval since physical data stored on the disk is sorted in the same order as the index. A non-clustered has no effect on which order of the rows will be stored.
  4. Frequent updates on cluster index column cause performance issues.
  5. Non-clustered index is the optimal choice for exact match queries by using the cluster key value or row ID (RID) depending upon the underlying table order.
  6. Non-clustered index is considered for queries that do not return large result sets.
  7. Use of covering index eliminates accessing the table or cluster index together.
  8. It is important to define the cluster index key with as few columns as possible because the non-cluster index entries contain the clustering key.
  9. Cluster index considered for queries that return a range of values using operator such as BETWEEN,>,>=, < and <=.
  10. Considered for column that are accessed sequentially and queries that return large result set.
  11. Cluster index eliminates the need for Sql Server to sort the data when there is GROUP BY and ORDER BY clause in a query. Since data is already sorted in a cluster index hence improve query performance.

Install SQL Server 2012 Cluster on a Windows Server 2012 -Part 1

Prerequisite for SQL Server Cluster Installation

 SQL Server 2012 – Run Failover Clustering Validation tool.
 Two instances of SQL Server or 1 and if 2 then both should have different cluster group.
 .NET Framework 3.5 with Service Pack 1 & Windows Installer 4.5.
 The nodes for the cluster and their ip addresses and access to both nodes.
 OS version & Edition – (Windows Server 2008 R2)
 SQL Server version & edition – (Enterprise or Standard)
 Verify Storage SAN/iSCSI
 Configure the quorum disk
 Create a Local Disk for Page File
 Verify that anti-virus software is not installed on your MSCS cluster
 Verify that you have disabled NetBIOS for all private network cards before beginning SQL Server Setup
 Remote Administration must be enabled
 The SQL Server installation product key.
 The SQL Server components to be installed.
 The SQL Server virtual server name.
 Default or named instance?
 The shared disks to select.
 The SQL Server virtual IP address.
 The subnet mask.
 The dedicated domain group for the SQL Server services and dedicated domain account to be added in admin group for running SQL server Engine and SQL Server Agent services.
 The dedicated domain accounts for each of the services if needed, to be installed (SSAS, SSIS, and/or SSRS).
 The SQL Server Collation to select mostly default.
 Windows or Mixed authentication mode
 The list of users to be the SQL Server administrator for the instance.
 The default locations for the data and log files for the user databases and tempdb and also the location for the backup directory.
 Create Domain Group with SQL Admin permissions and use that group for cluster security and management.
 MSDTC is to be installed prior to install sql server clustering with dedicated MSDTC drive and IP address.
 Cluster nodes should not be a part of domain controller.
Infrastructure Details:
 Ethernet LAN –
 Primary iSCSI LAN –
 Secondary iSCSI LAN –
 Heartbeat LAN –

 Node 1 LAN –
 Node 1 iSCSI1 –
 Node 1 iSCSI2 –
 Node 1 Heartbeat –

 SQL Node 2 LAN –
 SQL Node 2 iSCSI1 –
 SQL Node 2 iSCSI2 –
 SQL Node 2 Heartbeat –

 SQL Server:
 Authentication Mode – Mixed
 Database mode – SQL 2012 Compatible
 Management Cluster Name: SQLCLSTADMIN
 SQL Management Cluster IP –

 Virtual Cluster Name –
 Virtual Cluster IP –
 SQLCLST Cluster IP –

SQL Server Accounts Info
 Use sql Domain group
 SQL Database Engine domain group ID – Domain\sqladmin
 SQL server Agent domain group ID – Domain\sqlagent
Drive Usage :-
Data – L-Drive

Transaction Log:
Log file: P Drive
Data – N-Drive
Backup Drive:
Backup: G:\
Windows Server 2012 Validate Cluster Configuration
Word of caution: do not skip any error message that this wizard generates in the final report. Doing so would simply mean that our configuration going forward will be unsupported. We only need to run this wizard on either of the nodes.
To run the Validate Cluster Configuration Wizard:
1. Open the Failover Cluster Management console
2. Under the Management section, click the Validate a Configuration link. This will run the Validate a Configuration Wizard
Cluster Validation1
3. In the Select Servers or a Cluster dialog box, enter the host names of the nodes that you want to add as members of your cluster and click Next.

Cluster Validation2

4. In the Testing Options dialog box, click Next to run all the necessary tests to validate whether or not the nodes are OK for clustering. If this is the first time to run the wizard, we must run all the tests for validation. For succeeding runs, especially when adding hardware like disk subsystems ot network cards on your cluster nodes, we can selectively choose which tests to run as long as we have initially validated our hardware by running all tests.

Cluster Validation3

5. In the Confirmation dialog box, click next. This will run all the necessary validation tests.
In the Summary dialog box, verify that all the report returns successful.

Cluster Validation 4

Cluster Validation 5

Fail over Cluster Resource dependencies in sql server
When you install Microsoft SQL Server on a cluster as a SQL Server fail over cluster instance, a specific set of SQL Server resources that have dependencies on other resources in the cluster group are created.
Dependency Report:
Step 1: Right click on sql server  more actions Show Dependency Report


Depedency report

SQL Server Upgrade

Prerequisite for upgrading SQL Server from one version to another for a smooth upgrade process.

Step 1: We should be aware of the Microsoft supported upgraded paths.

Example: SQL server 2005 X64 Enterprise Edition can’t be upgraded to SQL Server 2008 R2 standard edition. Only option to upgrade to SQL server 2008 Enterprise edition or Datacentre Edition.

Step 2: Run the upgrade advisor
It’s a free utility tool that analyses existing SQL Server deployments and highlighted the issues that need to be addressed prior to performing an upgrade.

Step 3: Verify the hardware and software requirements

It’s very important to verify that existing SQL Server meet the entire hardware requirement for running SQL Server.

Step 4: Perform a Full Backup

Take full backup prior to perform a SQL Server upgrade. Upgrade process usually go smoothly however sometime may go wrong.
Hence it’s important to have a backup so that we can revert to its previous state if the upgrade does not go as planned.

Step 5: Two main options for an upgrade in SQL Server.

 In place Upgrade

An in-place upgrade is the fastest and easiest upgrade method because it upgrades all system and user databases and settings for you. In addition, you do not have to update client applications to connect them to a new instance of the relational Database Engine. However, an in-place upgrade is an all-or-nothing approach. In the unlikely event that an in-place upgrade of the relational Database Engine fails, you cannot quickly roll back to SQL Server 2000 or SQL Server 2005.

 Side by side upgrade

With a side-by-side upgrade, the SQL Server 2008 R2 relational Database Engine is installed as a second instance and the original SQL Server 2000 or SQL Server 2005 relational Database Engine remains installed. Then move or copy one or more SQL Server 2000 or SQL Server 2005 user databases to the SQL Server 2008 R2 instance (each moved database is automatically upgraded).
This approach help maximize ability to quickly roll back to the prior instance whenever difficulties arise. A side-by-side upgrade might also result in simpler testing scenarios because both versions are available at the same time.

Step 6: Things to consider while upgrading the database engine.

If you are running analysis service, make sure you upgrade analysis service before database engine. The analysis service must be upgraded first.

Before upgrade temporarily disable stored procedure .During the course of upgrade, various SQL- related services will be stopped and restarted. If there are procedures configured to run when services start, there may be chance those stored procedure will interfere with upgrade.

Check the master, model, msdb and tempdb databases and verify that they are set to auto grow.

Disable database replication prior to performing an upgrade.

Even though Microsoft SQL Server 2008R2 is designed to preserve max worker thread, Microsoft recommend settings the MAX worked thread value to 0.


Post upgrade run DBCC UPDATEUSAGE on all databases to ensure database integrity.

Attached one Microsoft upgrade document for complete detail.