Contained Database–A new feature in SQL SERVER 2012 TIP #81

While working on a project sometimes it happened that we need to shift the database from one server to another due to various reason (sometimes it is only money ).

We are aware that this easy step take backup and restore another machine but glitch is what about the users which have access to the database ?

Now , the next step of us to create each individual user on newly database server which is very tedious job sometimes.

In SQL SERVER 2012 a new feature introduced by Microsoft which is Contained Database.  This feature allows us to move meta data as well with database backup.

Let me explain it in more detail what I mean to say here. A contained database is a feature which isolate database from other database and from SQL SERVER host for authentication which means now the authentication can be done on database level.

For example you want to move a in house Employee management system which has around 100 users which access this database so if the database in contained enabled then in that case we do not have to create new users on another sever.

Now let me explain here how to enable this feature

Step 1:- Enable this feature on server level by right clicking on server and from property TAB

We can enable this feature by following command as well.

EXEC sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO
GO

Step 2:- Now right click and go to property TAB of database on which we have to enable Containment Type feature

We have to enable this feature and set the value Partial

We can enable this feature via SQL command

ALTER DATABASE IndiandotnetDB SET CONTAINMENT = PARTIAL

Once we have enable above this feature we are good to go.

Now when you create user on database you can easily move this database with the META DATA which means user.

Warning :- If you see the image below you will find Replication, change data Capture,Change Tracking are not supported contained databases.

so if you are using Replication, change data capture, change tracking please avoid enabling this feature and use traditional way of creating user.

I hope this feature might help you somewhere.

Thanks

RJ

How to insert value in Identity column ? TIP #80

 

Happy new year 2015 .

Suppose you have a source table and one destination table (Which is exact replica of source table) and you want to copy all the rows of source table into destination table. Now the challenging part here is that there is an identity column and in source & destination table and you want to insert same value of identity column in destination tables column.

Lets understand this by an example.

Suppose you have tblStudentSource table which have StudentId as primary key and with Identity feature

Now you have another table which is tblStudentDest with same column structure which tblStudentSource has

now you want to insert all the value of tblStudentSource into tblStudentDest with keeping the identity value.

In this case you have to write following command.

SET IDENTITY_INSERT  tblStudentDest ON;
INSERT INTO tblStudentDest (StudentId,FirstName,LastName,Course,detail) SELECT * FROM tblStudentSource
SET IDENTITY_INSERT  tblStudentDest OFF;

I hope this might help you somewhere.

Thanks

RJ

Happy new Year 2015.