SQL Server 2012 Features:5: 5. Windows server core support

In continuation of previous blog-

Server Core is a minimalistic Microsoft Windows server installation option.

You can install SQL Server on a Server Core installation of Windows Server 2008 R2 SP1 or Windows Server 2012. This option of windows server 2012 provides a minimal environment for running specific server roles. This helps to reduce maintenance and management requirements and the attack surface for those server roles. Starting with Windows Server 2012, it is possible to transition between Full Server and Server Core without reinstalling the operating system.

Server Core is designed to provide an environment that reduces:

  • Servicing requirements
  • Management requirements
  • Attack surface
  • Disk space usage
  • Memory (RAM) usage

Server Core supports the following server roles on Windows Server 2012 and Windows Server 2012 R2:

  • Active Directory Domain Services (AD DS)
  • Active Directory Lightweight Directory Services (AD LDS)
  • DHCP Server
  • DNS Server
  • File Services
  • Hyper-V
  • Print Services
  • Streaming Media Services
  • Web Server (IIS)
  • Active Directory Certificate Services
  • Windows Server Update Server (WSUS)
  • Active Directory Rights Management Server
  • Routing and Remote Access Server, including the following sub-roles:
    • Remote Desktop Services Connection Broker
    • Licensing
    • Virtualization

Configuration Level: configuration levels in order of increasing complexity for windows server installations.


Note: In newer Windows versions there are more granularity in configuration levels.

Posted in Database Administration, Sql Server New Feature | Leave a comment

SQL Server 2012 Features:4: Contained Database

In continuation of previous blog:

Contained Databases:

A contained database includes all database settings and the metadata within itself which results no configuration dependencies on the instance of SQL Server. Therefore a contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.

The containment setting of a database can be NONE, PARTIAL or FULL. But only NONE and PARTIAL are supported on SQL Server 2012.

Containment Settings:

We need to make changes on the server level followed by database level.

Server Level Settings:

Go to SSMS–> Right click on Server name–> Properties–>Advanced–> Containment–>choose True


Database level Settings:

Now to change the settings on the database which you want to be a contained database, we can do it for existing as well as with new database for the required server edition.

When a database is created the “Containment type” should be set to “partial” to make the database a contained database, as shown below.


Here we need to choose the partial option for containment type.

After that we have to  create a user under the contained database and check the login for that user as below. Here user is created with name Mirza and we are checking by connecting it.



Here we have to mention the database which is contained and then connect it by using user.

Note: Initially I promise to provide only brief summary however few features are interesting and they can’t be bound in one line definition. This blog is such an example.

Looking forward your likes and response!

Posted in Database Administration, Sql Server New Feature | Leave a comment

SQL Server 2012 Features:3: Pagination

In continuation of previous blog:

  1. Pagination: This feature focuses on the result set of T-sql. It divides the result set into different parts to view easily. We can do pagination by using ‘OFFSET’ and ‘FETCH’ commands. We can pull only the required number of records instead of all.


There is an emp table and we are applying pagination on the same.

Here I have applied pagination for 0 to 5 records.

Few points to ponder:

  1. ORDER BY is mandatory to use OFFSET and FETCH clause
  2. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.



Posted in Database Administration, Sql Server New Feature, T-SQL | Leave a comment

SQL Server 2012 Features:2:Sequencing

In continuation of previous blog..


A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. This sequence is generated in an order of ascending or descending at a defined interval. Sequence is not attached to a table.


A. Creating a sequence that increases by 1

create sequence dbo.empby1

start with 5

increment by 1


How to check sequence through t-sql:

select next value for dbo.empby1

Sequence part1


Sequence part2 ssms

B.  Creating a sequence that decreases by 1

create sequence dbo.empneg1

start with 0

Increment by -1;


C. Creating a sequence using default values

create sequence dbo.mirzaseq ;

Posted in Database Administration, Sql Server New Feature | Leave a comment

SQL Server 2012 Features:1:Column store Indexes

As promised to provide little glimpse of each feature from the definition point of view, I am ready to deliver each feature each week for your kind notice.

  1. Column store Indexes:

columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

For high performance and high compression rates, the columnstore index slices the table into groups of rows, called rowgroups, and then compresses each rowgroup in a column-wise manner.

They are the preferred data storage format for data warehousing and analytics workloads.

Use a clustered columnstore index to store fact tables and large dimension tables for data warehousing workloads. This improves query performance and data compression by up to 10x.

Use a nonclustered columnstore index to perform analysis in real-time on an OLTP workload.

How to create index through SSMS:

Column store Index


Posted in Database Administration, Sql Server New Feature | Tagged , | Leave a comment

Interview Question:Important New Features of SQL Server 2012, 2014 & 2016

Questions may be asked from the DBA prospective so I tried to collect few important features for SQL Server 2012,2014 and 2016 . Next blog will be published with explanation for each feature.

SQL Server 2012 Features:-

  1. Column store indexes
  2. Contained Database
  3. Sequencing Objects
  4. Pagination
  5. Error Handling
  6. User defined roles
  7. Windows server core support

SQL Server 2014 Features:-

  1. New In-Memory OLTP Engine
  2. Enhanced Windows Server 2012 Integration
  3. Enhancements to AlwaysOn Availability Groups
  4. Enhancements to Backups
  5. SQL Server Data tools for Business Intelligence
  6. Updateable Column store Indexes

SQL Server 2016 Features:-

  1. Always Encrypted
  2. Stretch Database
  3. Real Time Operational Analytics
  4. Poly Base into SQL Server
  5. Native JSON Support
  6. Enhancements to Always On
  7. Enhanced In-Memory OLTP
  8. Revamped SQL Server Data Tools

Note:  A brief description will be published for each feature in coming post.

Please like or comment if it is  beneficial for you.

Thank You!!!

Posted in Sql Server, Sql Server New Feature | Leave a comment

Microsoft + LinkedIn


REDMOND, Wash., and MOUNTAIN VIEW, Calif. — June 13, 2016 — Microsoft Corp. (Nasdaq: MSFT) and LinkedIn Corporation (NYSE: LNKD)  announced they have entered into a definitive agreement under which Microsoft will acquire LinkedIn for $196 per share in an all-cash transaction valued at $26.2 billion, inclusive of LinkedIn’s net cash. LinkedIn will retain its distinct brand, culture and independence. Jeff Weiner will remain CEO of LinkedIn, reporting to Satya Nadella, CEO of Microsoft.

LinkedIn is the world’s largest and most valuable professional network and continues to build a strong and growing business.

Nadella’s Statement about LinkedIn:

“The LinkedIn team has grown a fantastic business centered on connecting the world’s professionals. Together we can accelerate the growth of LinkedIn, as well as Microsoft Office 365 and Dynamics as we seek to empower every person and organization on the planet.”

Note: Please refer the below link from Microsoft to get the complete news.

Microsoft to acquire LinkedIn

Kindly share if you like it.

Posted in Microsoft News, Uncategorized | Leave a comment

Blocking caused by -1/Negative SPID

I received a request from my development team for a query which is stuck and not giving any result and no error throwing.

I thought for a moment and start troubleshooting by checking the blocking. I caught the issue as blocking was there. The major blocking spid was -1.

I prepared a solution of this problem for everyone by collecting the information from different sources.


What is -1 spid?

SPIDs with a value of -1 are orphaned distributed transactions. It is a distributed transaction for which the transactional state is unknown. A distributed transaction is a database transaction that involves more than one database system (usually) located on different servers.

These negative values may be -1, -2 etc.

The process responsible for coordinating these transactions is the Microsoft Distributed Transaction Coordinator, or MSDTC.

How to Kill Negative SPId:

Open a new query window and execute the script:

select  * from sys.syslockinfo

 You will get the result as below:

UOW (Unit of work)


Here, need to check the UOW (Unit of work), it would be a 24 character long id, Copy the UOW id and then kill it.

Kill ‘CD947343-36F5-4515-B4CB-3A42402C8207’

Thank You!

Posted in Database Administration, Sql Server, Uncategorized | Leave a comment

SQL Server on Linux


A glimpse of this announcement from Microsoft as below:

SQL Server on Linux will provide customers with even more flexibility in their data solution. One with mission-critical performance, industry-leading TCO, best-in-class security, and hybrid cloud innovations – like Stretch Database which lets customers access their data on-premises and in the cloud whenever they want at low cost – all built in.

“SQL Server’s proven enterprise experience and capabilities offer a valuable asset to enterprise Linux customers around the world,” said Paul Cormier, President, Products and Technologies, Red Hat. “We believe our customers will welcome this news and are happy to see Microsoft further increasing its investment in Linux. As we build upon our deep hybrid cloud partnership, spanning not only Linux, but also middleware, and PaaS, we’re excited to now extend that collaboration to SQL Server on Red Hat Enterprise Linux, bringing enterprise customers increased database choice.”


Note: To read the complete news please refers Microsoft blog and news.


Thank You!

Posted in Microsoft News | Leave a comment

SQL SERVER ERROR- Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF

SQL SERVER ERROR- Cannot insert explicit value for identity column in table ‘tablename’ when IDENTITY_INSERT is set to OFF

Yesterday I got this error while monitoring the replication status of a server. The actual error is in snapshot as below:

SQL SERVER ERROR- Cannot insert explicit value for identity column in table 'SchemeToLocation' when IDENTITY_INSERT is set to OFF


As we know that each replication consists of a publisher and subscribers. I executed the below query at the subscriber end and issue resolved.


Go to Subscriber end and execute the below query by changing the table name:

Declare @TableID Int = (

Select  Top 1 sys.sysobjects.id As Table_ID

From    sys.sysobjects

Join    sys.syscolumns

On  syscolumns.id = sysobjects.id

Where   sys.sysobjects.name = ‘Tablename’

And (sys.syscolumns.status & 0x88) = 0x80 — 0x80 = Identity, 0x08 = Not For Replication

Order By 1   )


EXEC    sys.sp_identitycolumnforreplication @TableID, 1

Note: After running this script at the subscriber end, go to publisher and right click on the database which we need to monitor and launch replication monitor and check the subscriber status.

Your likes and comments would be highly appreciated.

Thank You

Posted in Database Administration, Replication, Uncategorized | Leave a comment