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

Change Tracking–an easy way to track your changes TIP#78

 

I am pretty much sure by the title of this post you had idea of the post content. Although I am late to post this feature but anyways Change Tracking (CT) is a feature came in SQL SERVER 2008.

As the name mention it track the changes like DELETE, INSERT , UPDATE type DML operations which we can synch to another database. (Microsoft Synch Framework).

In simple word what ever we change in table will be track by Change Tracking feature.

The main constraint which I would like say here is there should be a primary key in table for which we have enabled this CT (Change tracking) feature.

Now your next question is how to use this feature so not to worry below is step by step way to use this feature.

Step 1:- First step is enable Change Tracking property of database. you can enable this property by right clicking database property as shown in below figure.

Or you can write following query to enable CT (Change tracking) feature on database

ALTER DATABASE IndiandotnetDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

Now let me explain here what is CHANGE_RETENTION & AUTO_CLEANUP property here.

So change retention is feature which tell how many days we have to keep the change history. In above statement we have chosen for 3 days.

Now next feature is AUTO clean_up with this help of this feature we can clean garbage data from temporary storage where SQL SERVER keeping the data.

Step 2:- Now we have to enable same feature on SQL table which is tblStudentSource on which we have to track database.

If we see there are two property which is basically mapping  property for which we have enabled this property.

We can run following command as well

ALTER TABLE tblStudentSource
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

For tables which we want track we have to enable this feature. Currently I chosen only single table which is tblStudentSource.

Step 3:- We can cross check Whether feature is enabled or not on database and table by running following command

SELECT DB_Name(database_id), * FROM sys.change_tracking_databases
SELECT object_Name(object_Id), * FROM sys.change_tracking_tables

Step 4:- if we see result of step3 command we will find our CT (Change Tracking) feature is enabled on database & particular table.

Now if we make any changes in table tblStudentSource (or table on which we have enabled this feature).

Now suppose We have inserted,  deleted some records in tblStudentSource table

Before enable Change Tracking feature  we have following table data

Now once we have enabled this we have inserted a record and deleted a record from tblStudentSource

Now to track our changes we have to run following command

SELECT CTTable.StudentId ,
       ssource.FirstName , ssource.LastName,
       ssource.StudentId ,
       ssource.detail,
       ssource.Course,
       CTTable.SYS_CHANGE_OPERATION, CTTable.SYS_CHANGE_VERSION, CTTable.SYS_CHANGE_COLUMNS, CTTable.SYS_CHANGE_CONTEXT
FROM CHANGETABLE (CHANGES tblStudentSource, 1) AS CTTable
LEFT OUTER JOIN tblStudentSource AS ssource
ON ssource.StudentId = CTTable.studentId

 

Now if we see above snap we will find the all the changed row whether it is inserted or deleted is tracked by SQL SERVER and we can find it easily.

Here we would like to see some more important points.

So Whatever changes track by ChangeTracking feature of SQL Server is stored in a internal table which we can find with following command.

SELECT * FROM sys.internal_tables WHERE parent_object_id = OBJECT_ID('tblStudentSource')

If you see above query statement which we wrote to determine data which we have changed there is a function which we have used “CHANGETABLE”

The first parameter of this function is tablename with changes prefix and second parameter is Change tracking version number. Here Change tracking version number is nothing but a tracking of operation held on that particular version.

We can find the  current version by following command

SELECT  CHANGE_TRACKING_CURRENT_VERSION()

This version will helpful in synchronization.

I hope this might helpful to you somewhere in your project.

It is a good to know feature.

Thanks

Rj