June 2012 | SansSQL

Monday, June 25, 2012

T-SQL Query to get SQL Server Start time and Uptime

Below query gives the SQL Server Start time and Up time details.
This option is available from SQL Server 2008 and above and requires "VIEW SERVER STATE" permission to execute the query
SELECT sqlserver_start_time AS [SQL Server Start Time]
   ,CAST (DATEDIFF(MINUTE,sqlserver_start_time,GETDATE())/60 AS VARCHAR) + ' Hours ' 
   +CAST (DATEDIFF(MINUTE,sqlserver_start_time,GETDATE())%60 AS VARCHAR) + ' Minutes' AS [Uptime]
FROM sys.dm_os_sys_info

Deadlock - Implicit Conversion

Can ‘Implicit Conversions’ cause deadlocks in SQL Server?

Let me start defining deadlocks –

Deadlock - refers to a specific condition when two or more processes are waiting for the other to release a resource, or more than two processes are waiting for resources in a circular chain.

There can be number of reasons for deadlocks –  one such reason can be ‘Implicit Conversions’

What is Implicit Conversion?

A database system where in some tables has columns defined as a particular data type and a procedure is written with the wrong data type with reference to the same column. 
Consider for example that our table actually has ID defined as an INT, but the developer knowingly or unknowingly developed a stored procedure as if the ID column was a Varchar(20) data type.

So, now every time SQL Server has to look for ID it has to convert @ID from Varchar to INT. This is an implicit conversion of the data type.

How does implicit conversion cause performance bottleneck leading to deadlock?

SQL server internally uses a function ‘CONVERT_IMPLICIT’ to do conversion and when the conversion happens the INDEXES  are not used effectively due to the uncertainty involved in the result of the function – which means it has to convert the value for each and every row. This results in SQL Server scanning the entire table looking for the value. This takes time and, under default locking modes, places a share lock on the entire table preventing other processes from updating records while the scan is taking place.

This hold on the entire set of rows in a table might lead to a deadlock when explicit DML operations are required to be performed on the same set of rows.


To make sure to have the correct Data type defined in all definitions. Happy Reading!

The following execution plan depicts the usage of Implicit Conversion function

Friday, June 22, 2012

Report Builder 3.0 Error - Unable to connect to the server that is specified in the URL

Consider you built a report using Report Builder 3.0 and now you are deploying the same to your report server. Suddenly the Report Builder presents you with a error which says the below

Connection failed.
Unable to connect to the server that is specified in the URL,'http://<servername>/<ReportServer>'.
Make sure the server is running, the URL is correct, and you have permission to use the server. 

Make sure that the SQL Server Version is SQL Server 2008 R2 as Report Builder 3.0 does not support SQL Server 2008 or below.

Big Data Landscape

Big Data is definitely big buzz happening currently. Below is the landscape of the same, very neatly collated by Dave Feinleib (Forbes).


Reference Link: Big Data Landscape

Thursday, June 21, 2012

Contained Database - SQL Server 2012 - New Features

There has been lot of talk about SQL Server 2012 and its features - out of which there are few which are definitely a big leap. One among them is CONTAINED DATABASES

What is Contained Database?

Any Database which is INSTANCE INDEPENDENT with no external dependencies and has self-contained mechanism of authenticating users is referred to as Contained DB.

The advantage of having such database is that they can be moved easily to another server and we can start working on it instantly without the need of any additional configuration since they do not have any external dependencies.

Frequently used terminologies when using contained databases:
  • Application Boundary - It is the boundary between the server instance and the application code

  • Application Model - The application model is the programming surface of a contained database to which SQL Server applications are written. This surface area differs from non-contained databases in that it clearly separates the application from the instance. This separation is made by the application boundary.
Contained database functionality can be classified into -
  • Unchanged and available for use within the application model. This classification includes most SQL Server objects, CREATE and ALTER statements, procedures, and so on.

  • Not available within the application model. This classification includes purely instance-level functions such as instance settings, processor affinity, resource governance, or the creation of databases themselves.

  • Available within the application model with certain changes or restrictions. This classification includes collations and authentication.
The management model includes everything that lies outside of the application model on the instance level. The management model concerns the maintenance and operation of the instance as a whole as it resides outside of a contained database.
  • ContainedIt is a user entity that resides entirely within the application boundary.
  • UncontainedIt is a user entity that crosses the application boundary.
  • Non-contained databaseDatabase whose containment is set to NONE.
  • Fully contained databaseDatabase that does not allow any objects or functions to cross the application boundary.
  • Partially contained databaseDatabase that allows features to cross the application boundary. It is available in RTM

 Contained user

There are two types of users for contained databases.
  1. Contained database user with password which are authenticated by the database.
  2. Windows principals that can directly connect to the database and does not need any login in the master database.

What is the purpose of Contained DBs?

  1. Is there a way to avoid any loss of information during Database movement?
  2. Is there a way to avoid checking for orphan users when we do a DB restoration and end up fixing the same one by one? Saving time J
  3. Is there a way to move all the jobs related to a DB (agent job information) along with the Database movement? Etc.
Answer to all the above questions is CONTAINED Databases feature in DENALI (SQL Server 2012)

What are the contents of Contained DB?

The contained database, keeps all necessary information and objects in the database, for example
  • Tables
  • Functions
  • Constraints
  • Schemas
  • Types
It also stores all application-level objects in the database, including
  • Logins
  • Application-level agent jobs
  • Persisted error messages
  • Linked server information
  • System settings

Implementing\Creating Contained DBs

There are 2 ways of implementing contained DB
  1. Using SQL Server Management Studio (SSMS)
  2. Using system stored procedures\scripts

Using SQL Server Management Studio (SSMS)

Step 1: Enable "Contained Database Authentication" property on the SQL Server instance Level.
Right click on the instance and select 'properties'and then select ‘Advanced’ option to set the Containment property to True.

Step 2: Create a database and set its CONTAINMENT property to Partial.

NOTE: The current RTM version only supports PARTIAL containment
  • Once the DB is created either using SSMS or scripts – set the DB containment property to ‘Partial’
Step 3: Create a Contained User within the newly created contained database.

Step 4: Login to the contained database using the user that exists in Contained Database.

Using system stored procedure

--Enabled Advanced options
Exec sp_configure 'show advanced', 1;

--Enabled Database Containment
Exec sp_configure 'contained database authentication', 1;

Changing Non Contained DB to Contained DB:

A non-contained DB can be set as contained but with few things to take care (mentioned below)
Step 1: To identify the NonContained objects

Step 2: To change the DB property to Partial containment

Step 3: To migrate users associated with Sql server logins
@RENAME = N'testusercontained',

The sp_migrate_user_to_contained stored procedure is needed in order to contain the users that are associated with Sql Server logins. It will convert the Sql Server logins to users with password.

Step 4: We can repeat STEP 1 if there are any more non contained DB objects

How do we backup and restore contained DB?

The normal process of backup and restore will also apply in using contained DB as well. However, if we are restoring the backed up contained DB on any other instance we need to Enable "Contained Database Authentication" property on the SQL Server instance Level which is turned off by Default.

Else we get to see the following error –
Msg 12824, Level 16, State 1, Line 1 The sp_configure value 'contained database authentication' must be set to 1 in order to restore a contained database. You may need to use RECONFIGURE to set the value_in_use. Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Using scripts:

--Enabled Advanced options
Exec sp_configure 'show advanced', 1

--Enabled Database Containment
Exec sp_configure 'contained database authentication', 1;

Can we use CREATE\ALTER Database Statement on Contained DBs?

The normal ALTER command doesn’t work on contained DB. Instead, a new option called ‘CURRENT ‘has been added which ensures that if we move the database to a new instance or change the database name, the command will still work.


Benefits of using contained DBs

There are both Pros and Cons in using contained DBs –


Database Movement
One of the problems facing the existing database model is data being left behind when an application is moved from one instance to another. Ex: Orphan Users

Some data connected with an application, (for example, login information and agent job information) is currently stored within the instance instead of in the database. When you move a non-contained database application from one instance to another instance of SQL Server, this data is left behind. Then you must identify the data left behind and move it with your application to the new instance of SQL Server. This process can be time consuming and difficult.

Initial Application Development and administration
In a contained database, all elements of the application will reside within the database itself, thereby limiting instance-level impacts on the application and instance-level concerns for the developer.

A contained database stores data within the database itself, the lack of a disconnect between the application and the information previously held at an instance level means that the application administrator can easily verify production environment settings that may affect the application, access the necessary permissions to perform required duties, have the ability to backup and restore the database, and so on.

Threats of using contained DBs

There are disadvantages on using contained DBs
Related to Users
The major disadvantage of using contained DBs is to do with users –  

Db_owner and Db_securityadmin roles- Users in a contained database that have the ALTER ANY USER permission, such as members of the db_owner and db_securityadmin fixed database roles, can grant access to the database without the knowledge or permission if the SQL Server administrator. Granting users access to a contained database increases the potential attack surface area against the whole SQL Server instance. 
  • Guest Account - Database owners and database users with the ALTER ANY USER permission can create contained database users. After connecting to a contained database on an instance of SQL Server, a contained database user can access other databases on the Database Engine, if the other databases have enabled the guest account.  
  • Keep away from duplication user and login ids - If a contained database user with password is created, using the same name as a SQL Server login, and if the SQL Server login connects specifying the contained database as the initial catalog, then the SQL Server login will be unable to connect.
For more we can refer to Books Online.

Where can we have contained DBs?

Few scenarios where we find the usage of contained DBs - 

Two SQL Server instances which are being used by different team and different set of SQL Logins created to each team respectively. 
  • The same can be implemented using contained DBs and creating users and not logins (reducing the dependency on logins) 
If we need to create a copy of any of the existing DBs – the process would be to Take backup, restore and verify all the orphan users. 
  • The same can be reduced to just backup and restore – no need to verify the users (if the same set of users need access to the newly created DB)
  • In addition we can create users if any new needed with no login needed at instance level.


Wednesday, June 20, 2012

Understanding .TUF file in log shipping

I am very happy and excited to be part of Sans SQL with this being my first contribution here. So without wasting much time let us get on...

What is .TUF file? What is the significance of the same? Any implications if the file is deleted?

.TUF file is the Transaction Undo File, which is created when performing log shipping to a server in Standby mode.
When the database is in Standby mode the database recovery happens when the log is restored; and this mode also creates a file on destination server with .TUF extension which is the transaction undo file.

This file contains information on all modifications performed at the time backup is taken.

The file plays a important role in Standby mode... the reason being very obvious while restoring the log backup all uncommitted transactions are recorded to the undo file with only committed transactions written to disk which enables the users to read the database. So when we restore next transaction log backup; SQL Server will fetch all the uncommitted transactions from undo file and check with the new transaction log backup whether committed or not.

If found to be committed the transactions will be written to disk else it will be stored in undo file until it gets committed or rolled back.

So... that's it for now! Happy Reading!!

Friday, June 8, 2012

Order of Installing Service Pack on systems hosting Database Mirroring

If you have servers which hosts database mirroring and you want to install the service pack on those systems and you are trying to find out the order in which you need to do it then, here is the order in which you need to install service pack or a hotfix on the systems which hosts Database Mirroring.
  1. Backup the principal
  2. If you are using a witness, remove it from mirroring
  3. Upgrade the mirror
  4. Failover to mirror
  5. Upgrade original principal/current mirror
  6. If you wish to fail back to original principal continue on; otherwise, proceed to step 8
  7. Failover to original principal
  8. If you are using a witness, add it back into mirroring
For more information refer to the following article to know the entire process for upgrading both the principal and mirror servers.

Thursday, June 7, 2012

Error while starting SQL Server Agent in Denali (OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2).)

I had recently installed SQL Server 2012 AKA Denali CTP version on my system and when I tried to start the SQL Server agent, The agent was starting and was immediately getting stopped by displaying the below message.

When I investigated further, I found the below error message from the event viewer.
OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2).                         

Possible Workaround:
I got this working by changing the Log on account of SQL server agent from "NT SERVICE\SQLServerAgent" to "Local System" or a domain account.

Wednesday, June 6, 2012

Recycle error log and SQL Server agent error log (SQLAgent.out) file

Recycle Error log:
When we run the below command, it Closes the current error log file and cycles the error log extension numbers just like a server restart.
Permission Requiredsysadmin fixed server role
USE msdb 
EXEC sp_cycle_errorlog

Recycle SQL Server Agent error log (SQLAgent.out):
When we run the below command, it Closes the current SQL Server Agent error log file and cycles the SQL Server Agent error log extension numbers just like a server restart.
Permission Required: sysadmin fixed server role
USE msdb 
EXEC dbo.sp_cycle_agent_errorlog