CDC FEATURE – SQL SERVER 2008

This feature introduced in SQL Server 2008. With this feature we can capture the changes that are being done to any specific table. The changes made are stored in new tables that will get created automatically after enabling this feature. Till before SQL Server 2008 we can capture the changes made to tables with the help of AFTER TRIGGERS.

In order to use this feature first we have to enable this at database level and afterwards we can enable it for respective tables. Once we enable CDC, 2 new jobs gets created (explained later) these jobs serves as monitors of CDC process. They keep monitoring the data changes of respective tables and will update the repository tables which gets created automatically. The repository tables will have information of all DML operations performed on particular tables for which CDC has been enabled along with old data.

In order to use this feature first we need to enable this feature at database level and then we need to enable for specific table\tables for which we want to capture the data changes.

To enable this feature we need to run below command:

EXEC sys.sp_cdc_enable_db

This procedure must be executed for a database before any tables can be enabled for change data capture in that database. Change data capture records all insert, update, and delete (DML)activity

applied to enabled tables, making the details of the changes available in an easily consumed relational format. Change data capture feature is available only in SQL Server 2008 Enterprise, Developer and Evaluation editions.

Note: Change data capture cannot be enabled on system databases or distribution databases.

sys.sp_cdc_enable_db creates the change data capture objects that have database wide scope, including meta data tables and DDL triggers. It also creates the cdc schema and cdc database user and sets the is_cdc_enabled column for the database entry in the sys.databases catalog view to 1.

sys.sp_cdc_disable_db  this SP disables change data capture for the current database. This SP disables change data capture for all tables in the database currently enabled. All system objects related to change data capture, such as change tables, jobs, stored procedures and functions are dropped. The is_cdc_enabled column for the database entry in the sys.databases catalog view is set to 0.

 

We can use below query to confirm whether CDC is enabled or not:

    USE master
    GO
    SELECT [name], database_id, is_cdc_enabled
    FROM sys.databases

sys.sp_cdc_enable_table this SP Enables change data capture for the specified source table in the current database. When a table is enabled for change data capture, a record of each data manipulation language (DML) operation applied to the table is written to the transaction log. The change data capture process retrieves information from the log and writes it to change tables that are accessed by using a set of functions.

To enable CDC for specific table we have to use below command:

EXEC sys.sp_cdc_enable_table
@source_schema = N'Administrators',
@source_name = N'Shift',
@role_name = NULL
GO

ü  Administrators         –  Is the schema to which the table belongs.

ü  Shift                   –  Is the table name.

ü  @role_name                 –  Is the database role used to gate access to change data. The purpose of the named role is to control access to the change data. If explicitly set to NULL, no gating role is used to limit access to the change data.

When change data capture is enabled for a table, a change table and one or two query functions are generated. The change table serves as a repository for the source table changes extracted from the transaction log by the capture process. The query functions are used to extract data from the change table. The names of these functions are derived from the capture_instance parameter in the following ways:

 
All changes function: cdc.fn_cdc_get_all_changes_<capture_instance>
Net changes function: cdc.fn_cdc_get_net_changes_<capture_instance>

cdc.fn_cdc_get_all_changes_<capture_instance> Returns one row for each change applied to the source table within the specified log sequence number (LSN) range. If a source row had multiple changes during the interval, each change is represented in the returned result set.

cdc.fn_cdc_get_net_changes_<capture_instance>  Returns one net change row for each source row changed within the specified LSN range.

sys.sp_cdc_enable_table also creates the capture and cleanup jobs for the database if the source table is the first table in the database to be enabled for CDC.

To verify for which tables CDC is enabled we can run below query:

SELECT [name], is_tracked_by_cdc
FROM sys.tables 

sys.sp_cdc_disable_table drops the change data capture change table and system functions associated with the specified source table. It deletes any rows associated with the specified capture instance from the change data capture system tables. Metadata that describes the configuration details of the capture instance is retained in the change data capture metadata tables cdc.change_tables, cdc.index_columns, and cdc.captured_columns.

EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name = N'Shift',
@role_name = NULL

Note: If change data capture is enabled on a table with an existing primary key, and the @index_name parameter is not used to identify an alternative unique index, the change data capture feature will use the primary key. Subsequent changes to the primary key will not be allowed without first disabling change data capture for the table.

After enabling CDC we will notice new table for the schema ‘cdc’ got created automatically. Name resembles like cdc.HumanResources_Shift_CT.

This table contains all the data changes along with the old values. This table will have 5 additional columns:

ü      $start_lsn

ü      $end_lsn

ü      $seqval

ü      $operation

ü      $update_mask

 

Each of these new columns will have information like

$start_lsn – This will have the starting of log sequence number.

$end_lsn  -- This is the ending log sequence number.

$seqval  -- This will have sequence of values to order the row changes within the transaction.

$operation – This contains value as per the DML operation performed.

Like 1 – for Delete statement

        2 – for Insert statement

        3 – value before Update statement

        4 – value after Update statement

$update_mask -- is a bit mask where every column that was changed is set to 1.

Below 2 DMV’s can be used to monitor the CDC process.

sys.dm_cdc_log_scan_sessions:

The sys.dm_cdc_log_scan_sessions management view contains one row for each log scan session in the current database. The last row represents the current session. The view also contains a row with a session ID of 0, which has aggregate information about all the sessions since the instance of SQL Server was last started.

sys.dm_cdc_errors:

The sys.dm_cdc_errors management view contains one row for each error that is encountered during the change data capture log scan session.

Note:

•       Computed Columns are not tracked in CDC. The column will appear in the change table with the appropriate type, but will have a value of NULL.

      •       Changes to individual XML elements are not tracked.
      •       Timestamp data type in the change table is converted to binary.

Example:

Enabling CDC for the database:

USE TestDB

GO
EXEC sys.sp_cdc_enable_db

Verifying:

USE master

GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases

Enabling CDC for table:

USE TestDB

GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'HotelDetail',
@role_name = NULL

Verifying:

USE TestDB

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

Querying DMV’s:

USE TestDB

GO
select * from sys.dm_cdc_log_scan_sessions
Go
select * from sys.dm_cdc_errors

Disabling CDC for a table:

EXEC sys.sp_cdc_disable_table

@source_schema = N'dbo',
@source_name = N'HotelDetail',
@capture_instance = N'dbo_HotelDetail‘

Disabling CDC for a database:

EXEC sys.sp_cdc_disable_db

 

Thanks!!!

How to send notifications automatically in case any new database is created.

Today I faced a new quest that is to raise an alarm incase if any new database is created in the server.

I have scouted in Google as a first step J but didn’t find any script meeting my requirements so wrote a script of my own.

So the best way I thought of achieving this is through server level DDL trigger which gets fired automatically when anyone creates a new database. I used msdb.dbo.sp_send_dbmail inside the trigger to send mail to respective recepients. In the mail being sent I need information like when the database was created, what is the recovery model of it, who is the owner of the database, collation. Based on requirement we can get other information as well.

IF EXISTS (SELECT * FROM sys.server_triggers

    WHERE name = 'Alert_Database_Creation')

DROP TRIGGER Alert_Database_Creation

ON ALL SERVER;

GO

CREATE TRIGGER Alert_Database_Creation

ON ALL SERVER

FOR CREATE_DATABASE

AS

Declare @sub varchar(2000),@srv varchar(2000),@dbname varchar(2000),@bod varchar(2000)
Declare @recoverymodel varchar(2000),@collation varchar(2000),@datecreated varchar(2000),@owner varchar(255)

select @srv= @@SERVERNAME

select @dbname = name,@owner = SUSER_SNAME(owner_sid), @collation = collation_name,@datecreated =
(SELECT CONVERT(VARCHAR(20), create_date, 100))
,@recoverymodel= recovery_model_desc from sys.databases
where (DATEPART(DD,create_date) = DATEPART(DD,GETDATE())) AND (DATEPART(YYYY,create_date) = DATEPART(YYYY,GETDATE()))
AND (DATEPART(MM,create_date) = DATEPART(MM,GETDATE()))
 

    select @sub = 'New Database Has Been Created in server '+ @srv

    Select @bod = 'Database Name    : '+ UPPER(@dbname) + Char(13)+'Database Owner  : '+@owner+ CHAR(13)+'Recovery Model  : '+@recoverymodel

    +CHAR(13)+'Collation                 : '+@collation+CHAR(13)+'Created Date        : '+@datecreated

    EXEC msdb.dbo.sp_send_dbmail

      @profile_name = 'profile name',

      @recipients = 'email@address’,

      @body = @bod,

      @subject =@sub

Thanks!!

Property Owner is not available for Database '[databasename]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

This error sometimes appear while opening the properties of the database in most cases reason would be due to missing of database owner. Reason could be the current owner of the database is no longer valid it would have been removed from the server or would have been disabled or other reasons. So as the owner is not available SQL server will not allow us to see the properties of the database and will throw below error:

 

Property Owner is not available for Database '[databasename]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

 

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Owner&LinkId=20476

 

Once we change the owner of the database this issue will get fixed automatically. As we can’t access the property of the database through GUI we need to use the below command to change the owner of the database:

 

Sp_changedbowner ‘username’

 

The above command will change the owner of the current database. After changing the owner we will be able to see all the properties of the database.

User '' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

After successfully installing report services and configuring all options in ‘Reporting Services Configuration Manager’ while trying to access the URL under ‘Reporting Manager URL’ section of configuration manager its asking to provide credentials which is weird as it should directly connect. To avoid this we need to add the respective reporting URL to ‘Trusted Site’ zone in iexplorer Tools - > Internet Options -> Security -> Trusted Sites -> Sites.

Now after adding the reporting URL to trusted sites zone it’s not asking for credentials but after connecting throwing below error:

User '' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

This happens when the user does not have the permission to the reporting url, even if you have windows admin privileges, access to reports URL should be given explicitly. This happens mostly during fresh installations, in such case you need to open iexplorer or browser with administrator credentials like shown in screenshot below:

Right click on iexplorer and select the option ‘Run as administrator’, once browser will open, open the report url and in url on right hand top corner you will find option ‘ Site Settings’ shown in screen shot below

 

 

Click on Site Settings and in it click on ‘Security’ tab

 

 

Under security tab click on ‘New Role Assignment’ and add the user to whom you want to give the access to SSRS URL and click on OK. In our case you can add the user with which you logged into machine and give him ‘System Administrator’ and ‘System User’ roles. After giving permission you should not receive this error any more.

Thanks!!

How to upgrade Service Pack of a SQL Server 2008 Cluster?

Here I will try to explain how to upgrade SQL Server 2008 to SP3(Service Pack 3) on clustered environment.

The build number for SP3 is 5,500 and the version is 10.00.5500.

So what we are having is an Active/Active clustered environment the steps to follow for Active\Passive cluster also almost same steps.

As known in Active\Active cluster both the nodes involved in clustering will have some resources running in them. We have nodes named Node1 and Node2. SQL instance names are SQL1 and SQL2, SQL1 is running on Node1 and SQL2 instance is running on Node2. The resources are grouped as Primary group and Secondary group.

Steps to be done before starting the installation.

1.    As a DBA’s first job would be to take FULL backups of all the databases(including system and user databases) of both the instances.

2.    If needed you can make note of all server configurations, user permissions and SQL configurations. (This is an optional and won’t require unless in worst case scenario).

3.    Download the SP3 from this link.

As we have latest backup now we are good to proceed with installation. If we are going to perform this in a production environment it’s obvious to update all the users about the downtime. If the installation goes fine SP3 upgrade will not take more than 30mins for a clustered environment.

Steps to follow for installing SQL Server 2008 SP3:

1.    Login to the Node1 with administrator credentials.

2.    Open ‘Failover Cluster Manager’(FCM) from Start-> Administrative Tools -> Failover Cluster Manager. (This is for Windows 2008 OS).

3.    Once FCM opens, under the ‘Services And Applications’ you can find what all groups are created/resources running in your cluster environment. In my case I have 2 groups ‘Primary group’ and ‘Secondary Group’. As name says primary is running on Node 1 and secondary is running on Node 2. We can check the ‘Current User’ (explained in Step 6 below) to determine which groups/resources running on which node. In my case the current owner for ‘Primary Group’ is Node 1.

4.    After right clicking on the ‘Primary Group’ which is under services and applications section you will find one option ‘Move this service or application to another node’, once you place your cursor on this you can see ‘Move to Node 2’. Once you click on this option all the services and applications will be failed over to the Node 2 or the another node available in your clustered environment.

5.    Failover will not take even minutes it should complete in seconds as because that if why we use cluster environment in order to high data availability.

6.    Once all resources failed over to another node you can re-confirm by clicking on the Primary Group. After clicking on the group, on right side if you check you will find one option like ‘Current Owner’, in our case the current owner will be Node 2 as we failed all resources from node 1 to node 2.

7.    After confirming we can start the installation of SP3 by double clicking on the ‘SQLServer2008SP3-KB2546951-x64-ENU.exe’ file we downloaded already. As mine is 64bit windows I have downloaded exe with x64 and for 32bit you need ‘SQLServer2008SP3-KB2546951-x86-ENU.exe’.

8.    There is nothing much you need to select during installation just proceed by clicking ‘Next’. While checking the files in use it will show a file named ‘fdhost.exe’ is in use, you can actually ignore this and proceed by clicking next or kill this exe from task manager, it doesn’t harm anything even if you leave it running also during installation.

Note: The fdhost.exe belongs to SQL server Fulltext service and if you stop this service you can skip the reboot after installation of SP3.

9.    The next steps are pretty much same where in you need to accept the license agreement, then features that you want upgrade and finally click on ‘Install’. After clicking on install it will start the installation.

10. Once installation is done, we have to reboot the machine.

11. Once server comes back online you can open the FCM as mentioned above in step 2 and click on the Primary Group and you can find current owner of all resources is Node 2 as we failed over earlier, now following the same procedure as mentioned in Step 4 fail over the resources of primary group from Node 2 to Node 1.

12. Here thing to notice is, as mine is an Active\Active cluster we have resources running on Node 2 also. So we have to failover the resources or services and applications that are running on Node 2 to Node 1 same as mentioned in Step 4.

13. After failover of all resources to Node 1, we can start installing the SP3 as mentioned in steps 7,8 and 9.

14. Once installation is done, we need to reboot this node as well and once machine come back online you can failback the resources.

15. Once all the resources are back in place as prior to installation you can open the management studio on both nodes and confirm whether the SP3 installation was successful or not. SQL Server 2008 SP3 version number will be ‘SQL Server 10.0.5500’. To verify you can right click on server name and select properties or you can run the ‘select @@VERSION’ command which will give detailed information.

Let me know if any corrections. Thanks!!

How to FAILOVER databases in MIRRORING?

When our requirement is high data availability & Automatic failover is required in case of any disaster then we can opt for Mirroring of databases. In mirroring we need to maintain a separate WITNESS server if we want to AUTO-FAILOVER databases in case of any disaster. Without a WITNESS server also mirroring can be configured but in such case mirrored databases will not auto failover during disaster, DBA’s need to manually fail over the databases.

Below picture shows mirroring configured between Server A & Server B, Server C is configured as WITNESS server. So this witness server continuously monitors the health/availability of the Server A (i.e.., Main Server) and whenever the database of Server A becomes un-available the witness server will make the database of server B (secondary server) as the principal server.

In mirroring the database of main server is called PRINCIPAL Database & the secondary server database is called Mirrored database. This mirrored database will be in restoring state until principal database is available. Whatever the changes made to the Principal database will automatically move to the mirror database.

If we configured mirroring without a WITNESS server when PRINCIPAL database becomes un-available then in order to FAILOVER we can user below command:

ALTER DATABASE <Database Name>

SET PARTNER FAILOVER;

Or

we can failover through GUI by clicking on ‘Failover’ tab which is in properties of database -> click on Mirroring -> click on Failover.

The below tables are important for getting details of Mirroring.

select * from sys.database_mirroring_witnesses;

Go

select * from sys.database_mirroring_endpoints;

Go

select * from sys.database_mirroring;

‘mirroring_role_desc’ column in sys.database_mirroring table will give the type of the server either Witness, Mirror or Principal.

‘mirroring_state_desc’ column of sys.database_mirroring table will have details weather Mirroring is synchronized or disconnected.

Note: While considering any high data availability features we need to take into consideration many things like space requirements, network traffic, cost of maintenance & so on. Based on our server set up we need to select the best suitable one.

Thanks!!!

How to bring the secondary database online in Log shipping?

In Log shipping by default the secondary database will not come online automatically if the primary database becomes un-available.During log shipping, the secondary database will be in Standby or No Recovery mode that means it will be available for Read-Only.

In order to bring the secondary database involved in log shipping to online we have to use any of below commands. These command are to be used in case the primary database becomes un-available either because of database corruption or database going offline or any other damage to the primary database.

Restore Database <Database Name> WITH Recovery

(or)

Restore Log <Database Name> WITH Recovery

Thanks!!!

Unable to start execution of step 1 (reason: Error authenticating proxy ‘windowsNT\Vinay’, system error: The security database on the server does not have a computer account for this workstation trust relationship.). The step failed.

We are having few SQL job which runs SSIS packages. They were running fine from many days and all of a sudden today they started failing with below error:

Unable to start execution of step 3 (reason: Error authenticating proxy windowsNT\Vinay, system error: The security database on the server does not have a computer account for this workstation trust relationship.).  The step failed.

The windows login ‘windowsNT\Vinay’ is having access to SQL server and having DBO permission given on all the required databases. When verified the windows login is active in Active Directory also. Verified all the System, SQL logs and didn’t found any critical errors. The situation became much critical that all the jobs which are running SSIS failed with same error and they are the must to be run jobs.

After verification only thing noticed is SQL services have been re-started a day prior but no errors reported before or after the reboot. But the error says some where the login is missing the authentication keeping this in mind I have verified the SQL services and noticed they are running under ‘NT Authority\Network Service’ which is not normal, the services were supposed to be running under ‘windowsNT\Vinay’ account. After a quick confirmation I have changed the SQL services to run under ‘windowsNT\Vinay’ account. After restarting the SQL services, I have re-started the jobs which were failing earlier with the error. Now all the jobs started running fine without any error and completed successfully.

If you are receiving this error while logging into server itself then many of the forums have suggested to rejoin the domain. But in my case I was able to login to server without any issue but only issue is my SQL jobs were failing.

Thanks!!

Partitioning of Tables

Partitioning means dividing. The concept of dividing a huge table into small pieces and storing the pieces in different location is called partitioning.

As a matter of fact it takes less time to search for a particular row in a table having 1000 rows than searching for a row in table having 10 lakh rows. When a table is really huge with millions of records and table is used frequently then we can make use of sql feature called PARTITIONing. It increases the performance of the queries on huge tables. A RANGE PARTITION will helps in splitting the table or making the table in small parts based on range of values.

For example we have Employee table as shown in below picture and it has millions of records.

 In order to increase the performance of queries that are being run against the Employee table we can partition it as shown in below diagram:

We can partition a table based on values of any one column. If we look at the above picture you can notice the employee table has been partitioned into three partitions P1, P2 and P3 based on the values of ‘Empno’ column. The first partition will have employee details with EmpNo form 1 to 2500, second partition will have employee details with empno’s from 2501 to 5000 and the 3^rd partition will have employee details whose empno is more than 5000. So now if we query details of employee whose  empno is 2566, SQL will directly look for it in partition 2 and returns the required details instead of searching entire table. It is the same Employee table but after partitioning acts as 3 different tables. This obviously saves a lot of time searching small tables instead of one huge table.

Note: We can partition an already existing table or we can specify partitioning while creation of new table.

In order to partition any table we first need to create a partition FUNCTION. In partition function we specify the ranges for our partitions. Means while creation of partition function we specify what sort of values should fall under which partition.

Syntax:

Create Partition Function <Function Name> (DataType)

AS RANGE LEFT FOR

Values (value);

Funcation Name : User defined.

DataType         : This data type value depends on the type of column we are selecting for partitioning the table.

RANGE            : It will be either LEFT or RIGHT

                            LEFT – The first value is the maximum value of the first partition.
                            RIGHT – The first value is the minimum value of the second partition.

Value              : Is the limit of values.

 Example:

Create Partition Function Emp_Part1 (INT)

AS RANGE LEFT FOR

VALUES (2500);

With above command I am creating a function named ‘Emp_Part1’. As I am partitioning the Employee table based on values of Empno column values so I  specified the datatype as ‘INT’ as Empno will hold employee numbers, value specified is ‘2500’ with RANGE – LEFT this mean the values from 1 to 2500 will be in first partition. As LEFT is specified, the first value that is 2500 here is the maximum value of first partition.

Next step in partitioning is we need to create SCHEME for the partition function. This SCHEME will help in specifying which partition to be stored in which file group.

Syntax for SCHEME creation:

Create Partition Scheme <Scheme Name>

AS Partition <Partition Name>

TO (Filegroup1,Filegroup2,..);

Example:

Create Partition Scheme Part1_Scheme

AS Partition Emp_Part1

TO ([PRIMARY]);

Here I created SCHEME with name ‘Part1_Scheme’ for the partition ‘Emp_Part1’ (which we created earlier). Here I specified ‘([PRIMARY])’ for the file groups this indicates my both partitions to be stored in Primary file group itself. But after executing above command we will receive below error:

Msg 7707, Level 16, State 1, Line 1

The associated partition function 'Emp_Part1' generates more partitions than there are file groups mentioned in the scheme 'Part1_Scheme'.

This is because we are planning to partition the table into 2 i.e.., a partition will have values from 1-2500 and the other one will have values above 2500. So we need to explicitly specify where the 2 partitions to be stored that is in which file group. so the correct command will be like below

Create Partition Scheme Part1_Scheme

AS Partition Emp_Part1

TO ([PRIMARY],[PRIMARY]);

As here I want to store both my partitions in Primary file group I specified PRIMARY for both, in case we need to store in another file group we need to specify the file group name. Also thing to remember here is how many partitions we have for each of the partition we need to specify the file group explicitly.

Now as I already have Employee table I will try to partition an already existing ‘Employee’ table. In order to partition an existing table we need to re-create CLUSTERED index with command below:

Create CLUSTERED INDEX Emp_Clu

ON dbo.Employee(EmpNo) ON Part1_Scheme (EmpNo);

Here I am creating a clustered index named ‘Emp_Clu’ for ‘Employee’ table on ‘Empno’ column. And at same time I am specifying to partition the ‘Empno’ column based on ‘Part1_Scheme’ value ranges which we created earlier.

I have inserted some rows and wanted to verify how rows are stored in partitioned table. In order to find how many rows are there in each partition we can use below query:

SELECT $PARTITION.Emp_Part1(Empno) AS Partition,

COUNT(*) AS [COUNT] FROM dbo.Employee

GROUP BY $PARTITION.Emp_Part1(Empno)

ORDER BY Partition ;

One of the query I like is what if suppose I want to see the data of first partition? The below query helps in viewing the data of any particular partition:

 SELECT * FROM dbo.Employee

WHERE $PARTITION.Emp_Part1(Empno) = 1;

Here ‘1’ specifies to retrieve data of first partition. Likewise if we need data of fifth partition then it will be ‘5’.

Now another important query is what if I want to check my partition value ranges. Below query will give value ranges of all partitions of a table:

SELECT * FROM SYS.PARTITION_RANGE_VALUES;

MS Link for $partition function.

Partitioning is nothing but splitting of huge tables into smaller pieces for easier access that’s it. Partitions are not new tables but subsets of main table.

Thanks!!

If any mistakes please let me know it helps me to learn.