User, group, or role 'dbo' already exists in the current database

1) Check the owner of the database
     If owner is sa and you are trying to update user as dbo while mapping database you may receive this  
    error.
2) Make sure owner name and assigned permissions

To know the last update time of a table on a database

--- To know the last update time of a table on a database ---

SELECT OBJECT_NAME(OBJECT_ID) AS Db_Name, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'KALYANDB')
AND OBJECT_ID=OBJECT_ID('AllLog2')

Database Mirroring Questions

1. Is it possible to setup database mirroring between standard and enterprise edition
No, It is not possible.

2. How can we setup mirroring for MSDB database ?
No, It is not possible to setup mirroring for system databases (master, model, msdb, tempdb), it is possible only for user databases

3. Is it possible to rename Mirroring Database ?
No, It is not possible to rename a database when mirroring is enabled on it.

4. What are different modes available in database mirroring ?
 a) High Performance -- It is also known as asynchronous mode, in this mode principal server sends transaction log to mirror server without waiting for any acknowledgement. Transactions are in the principal server commits without waiting mirror server to commit. (Transaction Safety Off)

 b) High Safety -- It is also called as synchronous mode, every transaction in the principal will be commited after getting acknowledgement from the mirror server. The transaction on the principal server will be released only after commited at mirror server. The high safety mode protects data. (Transaction Safety -- Full)

5. Can we enable mirroring and logshipping on same database ?
Yes, we can setup mirroring and logshipping on primary database with one difference, incase of logshipping we can map one primary with multiple secondary servers, it is not possible in mirroring. It is one to one.

6. Is it possible to query mirroring database ?
Yes it is possible only using database snapshots, otherwise it is not possible.

Find All Table Names Using Column Name

select name, object_name(id) from syscolumns where name='column_name'

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I encountered the message while removing subscription in replication

1) We need to verify the owner of the database
2) If owner is null then assign 'sa', otherwise assign any domain user which has admin permission on the database.
3) Delete the subscription

What is MSDTC

MSDTC - Microsoft distributed transaction coordinator.

SQL Server uses MSDTC when it wants to execute any transaction on more than one server. Distributed transaction is also a normal transaction but it spans across multiple servers. In distributed transaction some part of transaction is executed on server1 and then it connected to server2 to execute remaining part of the transaction. In simple words when a transaction updates data on more than one server. MSDTC helps to ensure the distributed transaction successfully completed. This DTC uses two phase commit protocol, in the phase one the transaction manager request to prepare commit for each component involved in the transaction and in the phase two, if all the components in the transaction is successfully prepare then it announce the commit decision.

a) Application manager calls the transaction manager to begin a transaction

b) When application has completed its changes then it asks transaction manager to commit the transaction. If all components are prepared then it broadcasts commit decision, if any of the components are not prepared then it broadcasts abort decision.

c) If a transaction manager fails when the transaction is prepared for commit or rollback then that transaction will become in-doubt transaction. The reconciliation will happen when the transaction manager re-connects again.

To enable & Configure MSDTC in windows 2003 ServerControl Panel – Add remove programs – Application Server – Details
Enable network DTC access – OK
Component Services – My computer – properties -- MSDTC Tab -- Configure from here.

To enable & Configure MSDTC in windows 2008 Server
Server Manager – Roles – Add Role – Select Application Server – Select Inbound and Outbound Transactions – Install – After completion
Administrative Tools – Component Services – My Computer – Distributed Transaction Coordinator - Local DTC –Configure from here

Auditing in SQL Server

Basically Auditing helps to monitoring any action on a database, using this feature you can audit any actions of server level as well as database level.

Purpose of Auditing
a) To ensure accountability of user actions
b) Prevent users to perform inappropriate actions
c) Monitoring any unusal actions
d) Log the unauthorized user actions.
e) Collect data about specific database activities.
f) Ensure complaince such as SOX (sarbnes-oxley act), HIPPA (Health Insurance Portability and Accountability Act.

Using this feature we can audit server actions, database actions such as DML and DDL operations, also we can log the output into a file, windows security event log or windows application event log.

Auditing is not new in SQL Server 2008, prior to this version auditing can be done using several ways like SQL Server Traces or Login Auditing and C2 Audit mode etc.,

When compare with prior versions SQL Server 2008 has various benefits.

a) Ability to audit events at database level or instance level
b) Ability to audit many different activities like DDL operations, DML operations.
c) Ability to capture and review audit results
d) Fast and Light weight – Because SQL Server 2008 uses extended events to caputre audit data. This is very minimal overhead when compared to SQL Server Traces.
e) Easy to setup

SQL Server Audit Components

SQL Server Audit – This object helps to monitor single sql server instance or database-level actions to monitor. We can setup multiple audit events for each SQL instance.

Server Audit Specification – The server audit specification belongs to SQL Server audit. This object is used to specify audit events at Instance level. This collects many server-level action groups raised by the Extended Events feature.

Database Audit Specification – This database server audit specification belongs to SQL Server audit. This object is used to specify audit events at database level. We need to create one database audit specification per database.

Target – The results of audit are sent to target. This target can be a file, windows system security log, windows application event log.

Steps to setup Auditing
1) Create New Audit object and assign name and target
2) Either you can setup Server Audit Specification (to monitor instance level actions) or you can setup Database Audit Specification (to monitor database-level actions)
3) By default Audit is in disable state, we need to enable audit specification
4) Later we can view the audit result in the target.

Using SSMS

1. Open SSMS – Security – Audit – Right Click – Select New Audit
2. Specify Audit name and target location of the audit  -- Click OK to create an audit
3. After setup SQL Server Audit, we need to specify Server Audit Specification
4. Open SSMS – Security – Server Audit Specification – New Server Audit Specification / New Database Audit Specification
5. Select required audit actions need to monitored and then Click on OK Button

Note – Initially Audits and Audit specifications are in disabled mode, first we need to enable it to start capturing the events. Eventhough you specified text file as target, we can’t directly read the text file using a text editior, we need to rightclick on Audit – select view audit logs, and from there you can export into a text file. Similarly if you select target as application log or security log then all these events will be logged into respective sections in the event viewer

To verify Kerberos Authentication for a current session

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)

Some times we may get the below error while updating the login from dbo to some other login from user mapping window.

1. I have database which is mapped with dbo login and dbo schema.
2. I tried to update the database with app_user login, then it displays that error message.

To fix the issue we need to change the default owner to 'sa' and then tried to update the login.

Use database_name
sp_changedbowner 'sa'

It may cause because dbo is mapped with some other login, then we need to change the dbo login name to 'sa', it is because the sa login is reffered as dbo and it will exists in all the databases. So we should not modify the dbo login to any other login except 'sa'.

login already has an account under a different user name. (Error: 15063)

We have migrated few databases from old server to server server, later the application is failing to login at one particular database. I verified the database security and confirmed that user is missing then i started adding the user into that database and i encountered the below error

The login already has an account under a different user name. (Microsoft SQL Server, Error: 15063)

There is a login called app_user, I tried  to add that user like below
Databases -- Security -- New User -- Name - app_user and Login name = app_user but it is giving the above error. It is because the dbo is mapped with app_user for that reason it is not allowing us to map the new user with same login.

To resolve this
Run the below command on the problematic database
Use database_name
sp_changedbowner 'sa'

After executing the above coammdn then it will allow you to add a new user then we can map the user with specified to login. We can't directly do this from wizard, we need to run the T-sql command to modify it. And also it doesn't allow to drop the dbo user from the database.

Filetable in SQL Server 2012

File Table is a new feature introducted in SQL Server 2012 Denali to manage files on a table. This is mainly used to maintain unstructured data. Filetable combine’s filestream with windows file system API to deliver enhanced BLOB capabilities in sql server. File table is a combination of table and file system. File table built on filestream.

Like all other tables in SQL Server file table is also an ordinary table with one difference ie. It has fixed schema, we can’t alter the filetable schema.

There are few limitations with the FileStream a) the files which are stored in the filestream column cann’t be read by the user directly. b) No extensions will show for those files if we view from the windows explorer, c) there is no method to create subdirectories in filestream folder d) by seeing the file it is not possible to know the path of the file, e) it doesn’t uses the win 32 API there are specific APIs for IO.

As a first step we need to enable filestream access

1) Open SQL Server Configuration Manager
2) Select SQL Server Service – properties
3) Click on FileStreamTab
4) Check Enable FileStream for Transact-SQL Access
5) Enable Filestream for file I/O Access
6) Windows Share Name – Type whichever name you want
7) Check Allow remote client access to FileStream Data

CREATE DATABASE FileTeableDemo

ON

PRIMARY ( NAME = FileTeableDemo_Data,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTeableDemo.mdf'),

FILEGROUP FSFileTeableDemoGroup CONTAINS FILESTREAM(NAME = FSFileTeableDemo, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FSFileTeableDemo')

LOG ON ( NAME = FileTeableDemo_Log,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileTeableDemo_Log.ldf')

WITH FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'KalyanDocuments')

GO

SELECT * FROM sys.database_filestream_options WHERE database_id=DB_ID('FileTeableDemo')

USE FileTeableDemo

GO

CREATE TABLE MyDocuments AS FileTable

CREATE TABLE MyImages AS FileTable

If you run the above two create table statements then it will create two folders inside the shared folder which we specified at step 6 in the above. While creating any filetable we need not to specify any columns in the definition, it has its own standard.

We can directly explore the folder like other folders.

Select * from MyDocuments

Select * from MyImages

To explore file table
SSMS -- Databases -- Database -- Tables -- FileTables -- Righ click on object name and select explore file table directory.

We can directly drag and drop the files into those folders using normal windows explorer, then automatically SQL Server will insert records into the tables. It stores the file with all its attributes and file name with extension in the sql server. If you rename file in sql server automatically it get affected in the folder and vice versa.

Syntax to rename any file name

UPDATE MyImages set name='c3.jpg' where stream_id='E6FA8E76-3ACB-E111-A317-000FFEE7BCF9' 

Like other backups we can do a normal backup and restore this into other server. After restore you need to have access permission for that share path to explore the filetable contents.

Note - The base folder can't be deleted becoz it is referred by SQL Server, but the contents (files) inside the folders can be alerted / modified / deleted with appropriate permissions.

Tabular Data Stream (TDS) SQL Server

Tabular Data Stream -- It is a application layer protocol specific to sql server for communication between sql server and client applications. SQL Server Network Interface (SNI) is a protocol layer used by SQL Server instance to communicate with SQL Native client using a generic protocol like tcp/ip. When a client wants to communicate with SQL Server, then SNI packages the message into TDS and send over the network. On the server side there are different end points listening for TDS packets.

What is endpoint ?
A SQL Server end point is the place of entry into SQL Server. SQL Server communicates with network using endpoints, there are various types of endpoints which supports specific type of communication.

To view the endpoints of sql server
select * from sys.endpoints  -- Displays list of end points exists in the SQL Server.

There is one specific endpoint for DAC (Dedicated Administrator Connection) and other endpoints for specific to each protocol. There are two types of endpoints a) system endpoints b) user endpoints
By default SQL Server creates endpoints in the sql server instance one for each protocol. If you want to create any user endpoints we can create using create endpoint, alter endpoint, drop endpoint etc., commands.

Locking, Blocking and Deadlock

Locks
Locking is a part of SQL Server operations. When a row is read by a query a lock will be placed on that row to prevent any modifications which reading. If we issue a update statement on a single row a lock will be placed on that row to prevent reading the data which updating. Different types of locks issued by SQL Server DB engine to determine how the resources can be accessed by concurrent transactions.

Types of Locks

Shared Lock-- SQL Server applied shared lock on all read operations. Any number of shared locks can be held on a particular resource on the same time. As soon as the select statment completes reading the shared lock will be released from the resource.

Update Lock -- Update Lock, In other words it is a combination of share and exclusive locks. It is to prevent the deadlock scenario. An update lock will be held when user trying to modify the data. If an update command is issued first thing what it will do is, to update the row first it needs to find out the row which needs to be updated, till that time it places a shared lock initially after that it will convert that into exclusive lock to prevent other users needs to modify the record at same time. These update locks are not only used for update operations, DB engine uses update locks all time when a search operation needs to perform prior update or delete commands.

Exclusive Lock -- This lock is applied when any insert / update / delete operations are performed and to ensure no multiple updates will happen on the same resource at the same time. Exclusive locks are not compatible with any other locks. If an exclusive lock is placed on a resource the other transactions needs to wait until the current transaction is completes.

Intent Lock -- Intent locks are used to setup lock hierarchy. It protect placing a shared (S) or exclusive (X) lock on a resource lower in the lock hierachy. There are three type of Intent locks Intent Shared (IS), Intent Exclusive (IX) and shared intent exclusive (SIX).

Schema Lock -- Schema locks are applied while performing DDL operations on tables, views and such as adding a column to a table, or dropping a table etc., There are two types of schema locks
a) Schema Modification Lock (Sch-M) -- While performing any DDL operation like altering a table it wont allow other transactions to be performed until the lock is released to prevent concurrent access to table structure.
b) Schema Stability Locks -- The DB engine applies schema stability locks when compiling or executing queries, and it doesn't block any transaction locks like (shared / exclusive or any other) locks. If it is refer an index at that time it places a schema lock because to ensure some other process should not delete that one.

Bulk Update Locks -- The DB Engine uses bulk update locks (BU) when bulk copying data into tables using bcp utility. This will be applied only when TABLOCK hint is specified at bcp utility. It allows other users to perform bulk copy data into same table in parallel and preventing accessing data by users that are not performing bulk load operation.

Key Range Locks -- It protects a range of rows read by a query while using a serializable isolation level.
And also ensures to prevent other transactions to insert rows (phantom inserts and phantom deletes).


Blocking

If a process say Transaction1 placed a lock on a table (ie., on a complete table or a set of rows) in the same time Transaction2 is trying to modify the data which is hold by Transaction1 then blocking occurs. Because the Transaction2 needs to wait until Transaction1 completes it work and release the lock. Blocking often happens on heavy OLTP systems.


Deadlocking
When two processes say transaction1 and transaction2 have locks on seperate objects, for example transaction1 is locked objectA and transaction2 is locked objectB and transaction2 is trying to aquire lock on objectA and transaction1 is trying to acquire lock on ObjectB then deadlock occurs.
SQL Server will automatically detect and resolves deadlocks by using an internal process called LOCK_MONITOR, when a deadlock occurs then sql server will choose a process which will take less amount to time to abort or rollback then it aborts that process, like this deadlock will be resolved. There are seveal ways to identify using profiler deadlock graph, dmv like sys.dm_tran_locks etc.,

Will SQL Server always Perform Table Scan to find the result of SELECT COUNT (*) FROM mytable

The answer is NO. The Query processor will use the index with smaller number of pages. Ie., with the least I/O cost.

Lets verify this with the below example:

Create the below table with all default values and loop it for 10000 records with out any indexes

Create table mytable (Col1 int Identity, Col2 Bigint Default 1, Col3 Char(100) Default 'SQL')

GO

SET NOCOUNT ON

GO

Insert into mytable Default values;

GO 10000

Execute the below command

select COUNT(*) from mytable

The query processor has no choice it will do a table scan because no index is attached to the table.

Now we will create an index on Col2 as below and execute select count(*) command

create nonclustered index Col2_Index on mytable(Col2)

select COUNT(*) from mytable

If you observe the below output table scan will become index scan and cost comes down from 96% to 85%

Now we will create an index on Col1 which is lower in size in terms of storage because it has int column and we will execute the select count(*) command

create nonclustered index Col1_Index on mytable(Col1)

select COUNT(*) from mytable

If you observe the below output the cost again come down from 85 to 82%.

This is how select count(*) will works. Lets see the page count of each index

select INDEX_ID, page_count,id.name

from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('mytable'),NULL,NULL,'LIMITED') dmd, sysindexes id

where dmd.index_id = id.indid and object_id('mytable') =id.id

xp_servicecontrol To Monitor Services Status From SSMS

To check the SQL Server services or any other service status using SSMS you can execute the extended stored procedure xp_servicecontrol. This is undocumented extended stored procedure.

Syntax
EXEC xp_servicecontrol N'Querystat|Start|Stop’,N'Service Name'

QueryState
Use this command to check the Services status whether service is running or stopped

Start
Use this command to start the service
Stop
use this command to stop the service

EXEC xp_servicecontrol N'querystate',N'MSSQLServer'   -- Checks the status of SQL Server Service
EXEC xp_servicecontrol N'querystate',N'SQLServerAgent'
EXEC xp_servicecontrol N'querystate',N'Spooler'

EXEC xp_servicecontrol N'start',N'SQLServerAgent'  -- Start the SQL Server Agent
EXEC xp_servicecontrol N'stop',N'SQLServerAgent'  -- Stop the SQL Server Agent

Script to identify users having sysadmin role

SELECT sp.name, sp.type_desc, sp.create_date FROM sys.server_principals sp, sys.server_role_members srm
WHERE sp.principal_id = srm.member_principal_id AND SRM.role_principal_id = 3