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 Data Capture (CDC)–An easy way to track data changes of a database–TIP #79

 

In last TIP #78  we have discussed Change tracker (CT) which was introduced in SQL SERVER 2008. CT feature only tracks which row is changes means on which row Insert/update/delete operation is performed but it does not track what exact value is changed.

If we want an audit of database means whatever changes occurred in database we want to capture those changes and later on see what are changes made then before CDC feature we have to right triggers but now in this case we can use Change Data capture (CDC) feature.

By the name it is clear that it capture the data which is changed.

Lets understand here step by step how to enable this feature

Step 1:- The first important step is to enable CDC on selected database. To enable CDC we have to write following command

EXEC sys.sp_cdc_enable_db

Note:- sometime you may get error while doing this so just cross check your database owner.

We can cross check whether CDC enable or not on database by following command

SELECT [name], database_id, is_cdc_enabled  FROM sys.databases  WHERE is_cdc_enabled = 1

Step 2:- Once we have enabled CDC setting on Database then we need to enable same setting for table which we need to capture.

For this we have to write following command

EXEC sys.sp_cdc_enable_table
    @source_schema ='dbo',
    @source_name ='tblStudentCDC',
    @role_name ='StudentCDCRole',
    @supports_net_changes = 1

Now When we run above command, we will find following items added in our database. We will find new tables created automatically in our database under CDC schema as shown in below figure

Whenever we enable CDC for a table a new table is created in System tables (if already not created) under a new schema which is “CDC” which denote change data capture.

a) CDC.captured_Columns:- This table contains all the captured columns of a CDC enabled tables.

As shown in below figure:

SELECT * FROM [cdc].[Captured_Columns]

b) CDC.Change_tables:- By the name it is clear that this will table will contain list of all the tables on which we have enabled CDC feature.

SELECT * FROM [cdc].[change_tables]

c)CDC.Index_Columns:-  This table keeps information of all the Indexes of a table

SELECT * FROM [CDC].[Index_Columns]

d) [cdc].[ddl_history]:-  This table contains all the information of schema changes of a CDC enabled table.

SELECT * FROM [CDC].[ddl_History]

e)[CDC]. [lsn_time_mapping]:-  This table keeps all the LSN ( Log Sequence Number)related information. The base of this table is whatever the transaction done on the CDC enabled table that will be capture in this table.

SELECT * FROM [CDC].[lsn_time_mapping]

On the same time few jobs also created which you can find in SQL SERVER agent as shown in below figure

Step 3:-We can also cross check all CDC enabled tables by using following command

SELECT [name], is_tracked_by_cdc  FROM sys.tables  WHERE is_tracked_by_cdc = 1

Step 4:-  Every captured table has suffix _CT and  the name is same as enabled CDC table. In current scenario we have enabled CDC feature on tblStudentCDC so the capture table is tblStudentCDC_CT.

Now we can find all the data change reference of a CDC enabled table by following command.

SELECT * FROM [cdc].[dbo_tblStudentCDC_CT]

Sometimes it is also called mirror table which keeps all the track.We will find following extra columns

_$Start_lsn,_$end_lst_$sequal,_$Opeartion,_$updatemask

_$Operation column track all the operations on the table and the different values denotes  different operations status as mention below
Like if value is 1 then it is a Delete Statement (means record deleted from main table)
if value is 2 then it is Insert Statement (means record inserted in main table)
if value is 3 then it denotes Value before Update Statement
if value is 4 then it denotes Value after Update Statement

So, with this easy steps we can enable CDC on database and tables and can enjoy this feature.

We can easily disable this feature which we will discuss in the next post.

Till than enjoy CDC.

Enjoy !!!

RJ !!!