Change Tracking Internals (SQL2008R2) Part 4

Change Tracking Overhead

When change tracking is enabled for a table, some administration operations are affected. The following table lists the operations and the effects you should consider.

Operation	When change tracking is enabled
DROP TABLE	All change tracking information for the dropped table is removed.
ALTER TABLE DROP CONSTRAINT	An attempt to drop the PRIMARY KEY constraint will fail. Change tracking must be disabled before a PRIMARY KEY constraint can be dropped.
ALTER TABLE DROP COLUMN	If a column that is being dropped is part of the primary key, dropping the column is not allowed, regardless of change tracking. If the column that is being dropped is not part of the primary key, dropping the column succeeds. However, the effect on any application that is synchronizing this data should be understood first. If column change tracking is enabled for the table, the dropped column might still be returned as part of the change tracking information. It is the responsibility of the application to handle the dropped column.
ALTER TABLE ADD COLUMN	If a new column is added to the change tracked table, the addition of the column is not tracked. Only the updates and changes that are made to the new column are tracked.
ALTER TABLE ALTER COLUMN	Data type changes of non-primary key columns are not tracked.
ALTER TABLE SWITCH	Switching a partition fails if one or both of the tables has change tracking enabled.
DROP INDEX, or ALTER INDEX DISABLE	The index that enforces the primary key cannot be dropped or disabled.
TRUNCATE TABLE	Truncating a table can be performed on a table that has change tracking enabled. However, the rows that are deleted by the operation are not tracked, and the minimum valid version is updated. When an application checks its version, the check indicates that the version is too old and a re-initialization is required. This is the same as change tracking being disabled, and then re-enabled for the table.

Effects on DML

Change tracking has been optimized to minimize the performance overhead on DML operations. The incremental performance overhead that is associated with using change tracking on a table is similar to the overhead incurred when an index is created for a table and needs to be maintained.

For each row that is changed by a DML operation, a row is added to the internal change tracking table. The effect of this relative to the DML operation depends on various factors, such as the following:

• The number of primary key columns
  
• The amount of data that is being changed in the user table row
  
• The number of operations that are being performed in a transaction
  

Snapshot isolation, if used, also has an effect on performance for all DML operations, whether change tracking is enabled or not.

 

 

 

Change Tracking Internals (SQL2008R2) Part 3

Storage with Change Tracking

Here we will see how Data Storage happens with Change Tracking and what effect does it carry. For Each User Table enabled for Change Tacking there is one internal change table and Internal Transactional Table.

There is one internal transaction table for the database.These internal tables affect storage requirements:

 

 

Internal change table for each table enabled for Change Tracking and Internal Transactional Table.

 

 

 

 

 

I have enabled a Table for CT; let’s see what the space is used for by internals tables. Initially Internal change Table will be of Zero Size.

 

 

Now have some transactions done on the table (Delete or Insert) and check the space used by internal tables.

 

 

 

For each change to each row in the user table, a row is added to the internal change table. This row has a small fixed overhead plus a variable overhead equal to the size of the

Primary key columns. The row can contain optional context information set by an application. And, if column tracking is enabled, each changed column requires 4 bytes in the tracking table.

For each committed transaction, a row is added to an internal transaction table.

 

sys.syscommittab :

Displays one row for each transaction that is committed for a table that is tracked by SQL Server change tracking. The sys.dm_tran_commit_table management view, which is provided for supportability purposes and exposes the transaction-related information that change tracking stores in the sys.syscommittab system table. The sys.syscommittab table provides an efficient persistent mapping from a database-specific transaction ID to the transaction’s commit log sequence number (LSN) and commit timestamp. The data that is stored in the sys.syscommittab table and exposed in this management view is subject to cleanup according to the retention period specified when change tracking was configured.

Catalog Views for Change Tracking (SQL2008R2) Part 2

To determine which tables and databases have change tracking enabled, you can use the following catalog views:

sys.change_tracking_databases: The view list the database’s enabled for Change Tracking.

sys.change_tracking_tables        : The view list the table’s information enabled for Change tracking.

sys.dm_tran_commit_table       : Displays one row for each transaction that is committed for a table that is tracked by SQL Server change tracking. The sys.dm_tran_commit_table management view, which is provided for supportability purposes and exposes the transaction-related information that change tracking stores in the sys.syscommittab system table. The sys.syscommittab table provides an efficient persistent mapping from a database-specific transaction ID to the transaction’s commit log sequence number (LSN) and commit timestamp. The data that is stored in the sys.syscommittab table and exposed in this management view is subject to cleanup according to the retention period specified when change tracking was configured.

Also, the sys.internal_tables catalog view lists the internal tables that are created when change tracking is enabled for a user table

Enable Change Tracking (SQL2008R2) Part 1

 

Change tracking in SQL Server 2008 enables applications to obtain only changes that have been made to the user tables, along with the information about those changes. With change tracking integrated into SQL Server, complicated custom change tracking solutions no longer have to be developed.

Change tracking is an important building block for applications that synchronize and replicate data in scenarios in which end-to-end replication solutions do not work and a custom solution is required.

For example, a scenario that requires synchronizing data with data stores that are not SQL Server databases or in which the store schemas are very different.`

 

Enable Change tracking at Database Level.

 

 

Change Tracking

Use to enable or disable change tracking for the database.

To enable change tracking, you must have permission to modify the database.

Setting the value to True sets a database option that allows change tracking to be enabled on individual tables.

You can also configure change tracking by using ALTER DATABASE.

Retention Period

Specifies the minimum period for keeping change track information in the database. Data is removed only if the Auto Clean-Up value is true.

The default value is 2.

Retention Period Units

Specifies the units for the Retention Period value. You can select Days, Hours, or Minutes. The default value is Days.

The minimum retention period is 1 minute. There is no maximum retention period.

Auto Clean-Up

Indicates whether change tracking information is automatically removed after the specified retention period.

Enabling Auto Clean-Up resets any previous custom retention period to the default retention period of 2 days.

 

ALTER DATABASE [BigMarket] SET CHANGE_TRACKING = ON;

 

 

 

Enable Change tracking at Object Level.

 

 

Change Tracking

Indicates whether change tracking is enabled for the table. The default value is False.

This option is available only when change tracking is enabled for the database. For more information, see Database Properties (ChangeTracking Page).

To enable change tracking, the table must have a primary key, and you must have permission to modify the table.

You can also configure change tracking by using ALTER TABLE.

Track Columns Updated

Indicates whether the SQL Server Database Engine tracks which columns were updated.

 

 

Will cover about internals of Change Tracking with important catalog views in next post.

 

 

 

 

 

 

 

 

 

Database Mirroring: Transaction Hardening behavior when Mirror Fails (Synchronous mode)

 

If we configure Database mirroring for Synchronous mode where principal waits for an acknowledgement from the mirror server before settling a commit or rollback, what would happen if the mirror server fails?

 

When transaction safety is set to FULL, the database mirroring session runs in high-safety mode and operates synchronously after an initial synchronizing phase.

The mirror server synchronizes the mirror database with the principal database to achieve synchronous mirror operation session. When the session begins, the principal server begins sending its active log to the mirror server. The mirror server writes all of the incoming log records to disk as quickly as possible. As soon as all of the received log records have been written to disk, the databases are synchronized. As long as the partners remain in communication, the databases remain synchronized.

After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction’s log to disk. Note the wait for this message increases the latency of the transaction.

The time required for synchronization depends essentially on how far the mirror database was behind the principal database at the start of the session (measured by the number of log records initially received from the principal server), the work load on the principal database, and the speed of the mirror system. After a session is synchronized, the hardened log that has yet to be redone on the mirror database remains in the redo queue.

As soon as the mirror database becomes synchronized, the state of both the copies of the database changes to SYNCHRONIZED.

Synchronous operation is maintained in the following manner:

1. On receiving a transaction from a client, the principal server writes the log for the transaction to the transaction log.
   
2. The principal server writes the transaction to the database and, concurrently, sends the log record to the mirror server. The principal server waits for an acknowledgement from the mirror server before confirming either of the following to the client: a transaction commit or a rollback.
   
3. The mirror server hardens the log to disk and returns an acknowledgement to the principal server.
   
4. On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client.
   

High-safety mode protects your data by requiring the data to be synchronized between two places. All the committed transactions are guaranteed to be written to disk on the mirror server.

High-Safety Mode without Automatic Failover

The following figure shows the configuration of high-safety mode without automatic failover. The configuration consists of only the two partners.

When the partners are connected and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data)( When mirror fails, it switches to Asynchronous mode until mirror comes back online and catches up, Once Principal and Mirror synchronized Mirroring mode switch back to Synchronous mode.). If the principal server is lost, the mirror is suspended, but service can be forced to the mirror server (with possible data loss).

High-Safety Mode with Automatic Failover

Automatic failover provides high availability by ensuring that the database is still served after the loss of one server. Automatic failover requires that the session possess a third server instance, the witness, which ideally resides on a third computer. The following figure shows the configuration of a high-safety mode session that supports automatic failover.

Unlike the two partners, the witness does not serve the database. The witness simply supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server.

When a witness is set, the session requires quorum—a relationship between at least two server instances that allows the database to be made available.

Automatic failover requires the following conditions:

• The database is already synchronized.
  
• The failure occurs while all three server instances are connected, and the witness and mirror server remain connected.
  

The loss of a partner has the following effect:

• If the principal server becomes unavailable under the above conditions, automatic failover occurs. The mirror server switches to the role of principal, and it offers its database as the principal database.
  
• If the principal server becomes unavailable when those conditions are not met, forcing service (with possible data loss) might be possible. For more information, see Forced Service (with Possible Data Loss).
  
• If the only mirror server becomes unavailable, the principal and witness continue.
  

If the session loses its witness, quorum requires both partners. If either partner loses quorum, both partners lose quorum, and the database becomes unavailable until quorum is re-established. This quorum requirement makes sure that in the absence of a witness the database never runs exposed, that is without being mirrored.

 

 
 

 

 

    

Perform full database backup without breaking the backup chain

In today’s time every organization retains a daily full database backup copy on tape. A DBA also make sure that he has full database backup and transactional log backup files to perform a point in time / Full recovery. When there is a major Change Deployment happens on the database a full database backup is mostly done. While all this is in progress DBA forgets what happens If the full backup done in middle of transactional log backup’s and the same is lost thinking it is of no use ,as Transactional backup job is running and performing the Log Backups.

When it comes on restoring the database backup and T-Logs DBA get surprised to see the below error

“System.Data.SqlClient.SqlError: The log in this backup set terminates at LSNXXXXXXXXXXXXXXXXXXXXX, which is too early to apply to the database. A more recent log backup that includes LSN XXXXXXXXXXXXXX can be restored. (Microsoft.SqlServer.Smo)”

On checking the backup tables DBA get more surprised that a full backup of the database is done and the same doesn’t exists to the location. Now only question is; how we will do point in time/Full recovery of the database .Other DBA will answer to this; we have used DB Mirroring rather doing Full, Diff and transactional Log Backup.

What will a DBA do in case an Update statement is executed as” Human Error” on a table which holds the most important data for the organization? How will this be recovered by Database Mirroring? Well it will not.

I will suggest DBA’s the following Strategies which I mostly follow.

1. Revisit the Organization Backup Policy and check how and when the backup is scheduled and who hold the responsibility.
   1. If the Primary Owner for the Backup is Tape Backup Admin (Datacenter Backup Admin), check how the full backup is done.
      1. File Level Backup (Data File and Log File) —-Cannot Restore the Backup in No Recovery Mode; Point in time / Full recovery is not possible.
      2. SQL Native Backup (SQL Server Format)
      3. LiteSpeed Format
      4. DPM (Data Protection Manager)
      5. Alerts on Failure and Success of Backup
      6. Action Plan on Failure.
   2. If the Primary owner is DBA
      1. What is the backup strategy (Once Full à5 Min Log Backup àEvery 5 hour’s Differential Backupà5 min Log Backup) .Strategy is defined as per Organization business and on Database importance.
      2. Where is the backup done (Disk /Tape)
      3. What is the format of the backup (Compressed /LiteSpeed format)
      4. Alerts on Failure and Success of Backup
      5. Action Plan on Failure.

If you follow any of the above 2 steps always the secondary owner to perform Full Database backup if needed using COPY_ONLY HINT .

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. For this purpose, copy-only backups were introduced SQL Server 2005. The types of copy-only backups are as follows:

• Copy-only full backups (all recovery models)A copy-only full backup cannot serve as a differential base or differential backup and does not affect the differential base.
• Copy-only log backups (full recovery model and bulk-logged recovery model only)A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create another routine, current log backup (using WITH NORECOVERY), and then use that backup together with all other previous log backups that are required for the restore sequence. However, a copy-only log backup can be created for performing an online restore

 Example:

In the below example i have done a Database Full Backup as part of backup strategy along with Transactional Log Backup and in the middle done a Database Full Backup again using COPY_ONLY HINT to provide the same to develpment team.

 

Restore the Database Full backup and TLog Backup part of Backup strategy.

Database (Full, Diff & Transaction Log) Backup List

I thought of writing about the subject after visiting a customer. It is really important to have the list of fullback and transaction log back sequentially performed. When it comes to Recovery I have seen most of the DBA running queries on msdb database and struggling to get the list of Backups and sequential Transaction Log Backup.

I have written T-SQL Code and designed a Report which helped me and other DBA to quickly check the backup and transaction log backups performed on a specific Database. This report was designed for one of our mission critical Database Only. Users are allowed to change the code according to their environment.

Snapshot of the Report is as below.

 

Script

SELECT bs.backup_set_id,bs.first_lsn,bs.last_lsn,bs.checkpoint_lsn,             bs.database_creation_date,bs.backup_start_date,bs.backup_finish_date,                 type= CASE bs.type WHEN ‘D’ THEN ‘FULL Backup’ WHEN ‘I’ THEN ‘DIFF Backup’ WHEN ‘L’ THEN ‘LOG Backup’ END ,bs.database_name,bmf.media_family_id,bmf.physical_device_name                     FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf ON                 bs.media_set_id=bmf.media_set_id             and bs.database_name=‘DatabaseName’ and bs.backup_finish_date between convert(nvarchar(30),GETDATE(),101) and GETDATE() ORDER BY bs.backup_set_id asc

 

 

 

 

 

How to capture Delete and Truncate T-SQL using Database Audit Specification

It really becomes evident to capture Delete and Truncate T-SQL in an environment which is supported by a Heterogeneous Environment. It really helped me to understand the scenario and pattern when DBA/ Jobs/SSIS Packages deletes or truncate Data from a Table based on Application / Business requirement.

Here we will see how to configure Database Audit Specification to capture delete and Truncate T-SQL .Later we will see how we can view the audit report and send the same in an Email.

Connect to the SQL Server Instance and navigate to SecurityàAuditàNew Audit

Provide the Audit Name (Filename) Audit Destination (File) and File Path (x:\).

Audit Configuration first created is Disable (Enable the same)

Select the Database on which delete and Truncate T-SQL to be captured.

DatabaseàSecurityàDatabase Audit Specification

Type a Name for the Database Audit and select the Audit name which was configured to capture the T-SQL Commands.

In “Audit Action Type” Select the Delete and “Object Class” as Database and Object Name will be the Database Name. Select the Principal name as Schema Name by which objects where created.

Truncate Commands are translated as Delete in the Audit file. Enable the Database Audit Speciation once configuration is complete.

Now execute delete and truncate commands against the database on which Audit is configured. Once commands are executed and completed check the commands are captured and written in an Audit File.

SELECT event_time,succeeded,object_id,object_name,session_server_principal_name,server_principal_name,server_instance_name,database_name,Statement,file_name,audit_file_offset from fn_get_audit_file (‘D:\*–SM*’,null,null) order by event_time desc

 

To simplify this and schedule the same in a report I have created Stored Procedure which gives the output as UseràCommand Execution DateàNo of Commands Executed

The stored procedure has two parameters

1. @ReportforDays: Provide number of days for which audit report will be pulled. (Report for last 2 days or 20 days)
   
2. @AuditFile: File Location and filename (Ex: D:\*abc*)
   

Store Procedure Code:

USE [MASTER] GO CREATE PROCEDURE dba_audit_varify (@ReportforDays VARCHAR(2),@AuditFile VARCHAR(MAX)) AS BEGIN SET NOCOUNT ON DECLARE @ReportDays VARCHAR(2) DECLARE @StartLoop INT DECLARE @CountLoop INT DECLARE @StStartLoop INT DECLARE @StCountLoop INT DECLARE @Exe_User     VARCHAR(255) DECLARE @ex_dt                 DATETIME DECLARE @Statements VARCHAR(MAX) DECLARE @AuditFilename         VARCHAR(MAX) DECLARE @Executor             TABLE (ex_id INT IDENTITY (1,1),ex_user VARCHAR(255) ) IF EXISTS (SELECT TOP 1 * FROM tempdb.sys.sysobjects where name like ‘%#Executor_statement%’) DROP TABLE #Executor_statement CREATE TABLE #Executor_statement (ex_id INT IDENTITY (1,1),ex_user VARCHAR(255),Statements VARCHAR(MAX),execution_dt DATETIME ) DECLARE @session_server_principal_name_text varchar(MAX) SET @ReportDays=@ReportforDays SET @AuditFilename=@AuditFile                 SET @session_server_principal_name_text=‘SELECT DISTINCT session_server_principal_name FROM fn_get_audit_file                 (‘+””+@AuditFilename+”’,null,null) WHERE session_server_principal_name !=””’+‘ and event_time between GETDATE() ‘+ ‘- ‘ +@ReportDays +‘ and GETDATE()’                 Print space(5) + ‘[-] User Audit Report for Database ‘                           INSERT INTO @Executor (ex_user)         EXEC (@session_server_principal_name_text)         SET @StartLoop=@@ROWCOUNT         SET @CountLoop=1             While @CountLoop <= @StartLoop                     BEGIN                     SELECT @Exe_User= ex_user from @Executor WHERE ex_id=@CountLoop                     –PRINT space(11)+’|’                     PRINT space(5)+‘[-]‘+ ‘ User :[' + @Exe_User + '] ‘                                          ——————-Statement—————————-                         INSERT INTO #Executor_statement (execution_dt,ex_user,Statements)                         SELECT event_time,session_server_principal_name,Statement from fn_get_audit_file    (@AuditFilename,null,null)                         WHERE session_server_principal_name !=” and event_time between GETDATE() -convert(int,@ReportDays) and GETDATE() and session_server_principal_name=@Exe_User                         ORDER BY event_time desc                             SET @StStartLoop=@@ROWCOUNT                             SET @StCountLoop=1                             –Print @StCountLoop                             While @StCountLoop <= @StStartLoop                                 BEGIN                                                     SELECT @ex_dt=execution_dt,@Statements=Statements from #Executor_statement WHERE ex_id=@StCountLoop                                     PRINT space(6)+‘|’+space(5)+‘[|-]‘+‘Execution_Date :[' + convert(varchar(20),@ex_dt) + ']‘                                     PRINT space(6)+‘|’+space(5)+‘[|-]‘+‘Query :’+@Statements                                     SET @StCountLoop=@StCountLoop+1                                 END                     ———————————————————                     SET @CountLoop=@CountLoop+1                     END SET NOCOUNT ON END ————————————————————————————————————-

 

 

 

How to manually generate cluster Log on Win 2008

So far I have seen cluster logging is not enabled by default and it really makes difficult to troubleshoot cluster issues; to troubleshoot the issue I have to manually generate the log for command prompt.

Here we will learn how to generate the cluster log from command prompt.

Steps

• Open the command with administrator privileges. (Better to open the command prompt with “Run as Administrator”.)
  
• Type “Cluster /Cluster: ClusterName log /gen /copy “C:\temp”. You should get output as follows
  

 

 

Running Ad Hoc Queries on Remote SQL Server without Linked Server

How to run Ad Hoc T-SQL Queries between SQL Server Instances (on Local or Remote Location) without Linked Server.

 

To execute Ad Hoc T-SQL you need to enable ‘Ad Hoc Distributed Queries’
server option from sp_configure.

 

sp_configure
‘Ad Hoc Distributed Queries’,1

reconfigure
with
override

 

 

TO access remote data from an OLE DB data source
you can use OPENROWSET to Run

 

SELECT a.*

FROM
OPENROWSET(‘SQLNCLI’, ‘Server=ServerName\InstanceName;Trusted_Connection=yes;’,

‘SELECT name from master.sys.databases’)
AS a;