Did you ever want to know how often an entry was updated, which entries were deleted or which tables were changed? Database tracking can give you answers to all these questions and more.

Use cases

There may be many reasons to use database tracking. Here are some of them:

  • Analyze behavior of your business. What entries are updated more than others, which are deleted and so fourth.
  • Comparing between two databases. This comes handy when the databases are very big, and a full comparison takes long time and lots of resources. Also, with database tracking your can choose which commands to track and which tables. This can refine your databases compare.
  • Tracking can make your version upgrade a zero downtime procedure, and you can avoid using a log file (Look for an example of a version upgrade using tracking later in this post).

How does tracking work?

Tracking a database is done by storing the keys of the changed entries in a table that is created for that purpose. Once starting to track, all keys of changed entries are stored in the table. This is done based on the user’s decision, which commands and which tables to store. For example, one can choose to track Update commands only, in a specific table only.

Tracking does not affect your data, it keeps your database untouched.

How to use tracking?

The following example creates a database TestDB, and three tables, and track by key is applied to it.

In order to run the example below you need to download PdbLogic  and attach it to your database, and there you go.

Create database and tables

The following piece of code creates a database TestDB, and three tables, Departments, Roles and Users, and fill them out with dummy data.

Start tracking

Calling the following command starts the tracking:

Exec PdbLogic..PdbsetKeyTrackDatabase SourceDatabaseName='TestDB' ,@IsResetTrack=1;

We can see that a new database is created TestDB_track for the purpose of storing all the changes that occurred in TestDB. The database contains tables that correspond to the original tables. Currently the tables are empty, since no change is performed on TestDB yet.

Have changes in the database

Now it is time to make changes in TestDB, so the changes will be tracked and stored in the tracking table:

The tracking table has content now. We will look at the changes made in ‘Departments’ table in TestDB_track database.

select * from [TestDB_track].[dbo].[Departments]

Stop tracking

When you wish to stop the tracking, call the following command:

Exec PdbLogic..PdbunsetKeyTrackDatabase @SourceDatabaseName='TestDB';

Once calling PdbunsetKeyTrackDatabase that tracking is stopped. You can always restart it.

‘Departments’ table that resides on the tracking database (TestDB_track) includes two columns ‘Extract Time’ and ‘Load Time’ that are currently NULL for all entries. Our tracking tool also gives an optional syncing mechanism, to apply the changes to a differnt database. These columns are used by the sync command. If you wish to dig in to a more advanced use case using the track option, keep reading how to use tracking when upgrading a version.

We will use an example that takes us through the whole process of creating two databases, source and destination, then backup and restore them while using the track Add-in.

Create database and tables

As before, the following piece of code creates a database TestDB, and three tables, Departments, Roles and Users, and fill them out:

Upgrade a version using tracking

Version upgrades tend to take long time, to cause downtime, and to put your database’s consistency in jeopardy. Using tracking while running backup and restore will simplify the process and keep zero downtime. The following example also shows how to use the sync track command, to match two databases.

Steps for upgrading

A typical upgrade of a database follows these steps:

  • Backup the source database
  • Restore the backup at the destination database
  • Perform changes on the new database
  • Shut down the source database
  • Switch connections to the destination database
  • Turn on the system

This is a lengthy process, and once it is finished, the source database is already different again, so a new sync should take place.

If you choose to use tracking for your upgrade, you will follow these steps:

  • Run setTrack (from PartitionDB)
  • Backup and restore the source database
  • Perform changes on the backup database
  • Sync track (from PartitionDB)
  • Switch to the destination database

We will use an example that takes us through the whole process of creating two databases, source and destination, then backup and restore them while using the track Add-in.

Start tracking

First thing to do is to set the tracking to start. We would rather start early than late. In case we capture entries that are already updated, it will resolved itself at the end of the process.

Exec PdbLogic..PdbsetKeyTrackDatabase @SourceDatabasename='TestDB',@IsResetTrack = 1;

Backup and restore

Once the tracking is working, we start backup and restore. In this example we use the PartitionDB cloning function. This will take care of creating a destination database, backup the source database and restore at the destination. However, you can use your favorite way to backup and restore your database.

Exec PdbLogic..PdbdropDatabase @TargetDatabaseName='TestDBClone'; -- drop TestDBClone if exists
Exec PdbLogic..PdbcloneDatabase @SourceDatabaseName='TestDB', @TargetDatabaseName='TestDBClone';-- creating clone database for TestDB

At this point all operations that are performed on the source database will be tracked while backup and restore are going on. When ready, all changes will move to the destination database.

Source database is evovling

Backup and restore take time. During this time the source database TestDB is changing. Here are some changes:

Unset tracking at the destination database

You probably remember that we turned on the tracking event in TestDB before starting the clone. Therefore, we need to turn off the event in the destination database, TestDBClone:

Exec PdbLogic..PdbunsetKeyTrackDatabase @SourceDatabaseName='TestDBClone';
go

Compare the two databases

Backup and restore are completed. The two databases are now differ by the manipulation that was done on the source database, TestDB while backup and restore took place. To see the differences we use here PartitionDB compare Add-in.

Exec PdbLogic..PdbcompareDataDatabase @TargetDatabaseName='TestDBClone',@SourceDatabaseName='TestDB';

We can see that there are many differences between the two databases. In order to match the two databases we will call PartitionDB sync track function. It will use the tracking database to update the destination database.
Then we will compare the databases again.

Sync databases

Our example deals with uploading a new version to a database, so it is fair to assume that changes will be made at the destination database TestDBClone while the live system is still working (as we said, there is no down time).
You can add or remove tables, functions and more in TestDBClone. Once you are ready to upgrade you just need to synchronize your source and destination database, testDB and TestDBClone:
Exec PdbLogic..PdbsyncKeyTrackDatabase 
@TargetDatabaseName='TestDBClone', @SourceDatabaseName='TestDB',@IsClearTrack=0;

Sync again

It may have been long time since we restored the destination database. During this time the live database (testDB) was still working, and we might have lots of changes done on it. If this is the case, its recommended to sync the databases again.
Exec PdbLogic..PdbsyncKeyTrackDatabase 
@TargetDatabaseName='TestDBClone',@SourceDatabaseName='TestDB',@IsClearTrack=0;

Update and restore are completed, and the two databases are matching.

Note that if we look into the table in the tracking database after syncing we see that the ExtractTime and LoadTime are not NULL anymore. They note the time in which the last sync was performed, so the next sync will start from then:

Switch to the new database

At this point its up to you to decide how to continue. You may decide to set TestDBClone to be live, or you can switch the names of the two databases. You can do it using your own way or by using PartitionDB switch Add-in:

Exec PdbLogic..PdbswitchDatabase @SourceDatabaseName='TestDBClone', @TargetDatabaseName='TestDB';
go

Unset tracking

You are done tracking, so it is time to turn tracking off. Set @IsDropTrackDatabase  to drop the track database TestDB_track that was created for the tracking.

Exec PdbLogic..PdbunsetKeyTrackDatabase @SourceDatabaseName='TestDB',@IsDropTrack=1;

Wrapping up

Database tracking has many uses. This post showed two of them. The first example showed how to track changes in a database for a later analysis. The second example showed how tracking and syncing can assist us to backup and restore databases and keep zero downtime.

For your reference here is the list of parameters to pass to PdbsetKeyTrackDatabase:

Parameter Description Mandatory
SourceDatabaseName Name of source database to track Yes
IsTrackInsert Whether to track Insert command. Default is true No
IsTrackUpdate Whether to track Update command. Default is true No
IsTrackDelete Whether to track Delete command. Default is true No
IncludeTables Names of tables to include in tracking. If not specified, track all No
ExcludeTables Names of tables to exclude from tracking. If not specified, don’t exclude any No
SourceLinkedServerName Linked server name No
TriggerPrefix Prefix to add to triggers names. Default is Pdb No
TrackDatabaseName Name of database that is tracking the changes. Default is database name plus _track No
IsResetTrack Should the database and tables be erased in case exist, before starting the new track. Default is not to erase No
IsOutputScript Decide if print the command or run it No

List of parameters of PdbunsetKeyTrackDatabase:

Parameter Description Mandatory
SourceDatabaseName Name of the database to track Yes
IncludeTables Names of tables to stop tracking. If not specified, stop all No
ExcludeTables Names of tables to exclude from stop tracking. If not specified, don’t exclude any No
SourceLinkedServerName LInked server name No
TriggerPrefix Prefix to add to triggers names. Default is Pdb No
TrackDatabaseName Name of database that is tracking the changes. Default is database name plus _track No
IsDropTrack Should the track database be dropped at the end of the tracking. Default is not to drop No
IsOutputScript Decide if print the command or run it No

List of parameters to pass to PdbsyncKeyTrackDatabase:

Parameter Description Mandatory
TargetDatabaseName Name of destination database Yes
SourceDatabaseName Name of source database Yes
TargetLinkedServerName Destination linked server name No
SourceLinkedServerName Source linked server name No
IncludeTables Names of tables to sync. If not specified, sync all No
ExcludeTables Names of tables to exclude from syncing. If not specified, sync all No
TriggerPrefix Prefix to add to triggers names. Default is Pdb No
TrackDatabaseName Name of database that is tracking the changes. Default is database name plus _track No
IsClearTrack Should the track database be cleared at the end of the sync. Default is not to clear No
IsIgnoreRowVersion  Should row version get synced or ignored No
IsDisableConstraints  Should constraints be disabled prior to the sync No
IsEnableConstraints  Should constraints be enabled after the sync No
IsDisableTriggers  Should triggers be disabled prior to the sync No
IsEnableTriggers  Should triggers be enabled after the sync No
IsRebuildIndexes  Should indexes be rebuild after the sync No
IsForceConstraints  Should constraints be enable after the sync in case they were disabled prior sync. This is used in case of error duging the sync process. No
IsOutputScript Decide if print the command or run it No

To use tracking and syncing and other PartitionDB utilities, download our PdbLogic and attach to your database. .
To learn more about PartitionDB capabilities check out our blog.