Data Modification

Choosing a method to track data modifications requires understanding what types of information your organization needs to track. You may want to only log information when something has changed. Depending on your business case, you may need to know not only when something changed but also what changed. There are also a couple options on how you can track these changes. You have the option to either use SQL Server to track these changes for you or you can create database objects that will log this information for you.

One of the things to consider when implementing any sort of logging is the performance overhead. This can be even more critical when tracking data modifications. You will want to make sure that you choose a logging method that allows you to both track the necessary changes and have the expected performance impact. As you may expect, the more detailed logging you implement, the greater the performance overhead.

Out of all the options discussed in this chapter, Change Tracking has the least amount of overhead. If you would like to record more detailed information and you are willing to increase resource utilization, then you may want to consider Change Data Capture. Another possible implementation of tracking change involves using database triggers. While the use of database triggers allows you to more finely tune what is logged and how it is logged, it can come at the cost of even higher resource utilization.

An intermediate option may be using SQL Server Audit depending on the type of data you want to log. SQL Server Audit can be used to track server or database-level activity. In the context of logging changes related to applications, you would want to focus on database audits. The database audit actions within SQL Server Audit can track changes related to all types of data activity including accessing or modifying data. SQL Server uses extended events to monitor the audit activity. The data that can be retrieved includes the time the action happened, information about the user that triggered the audit, and the object affected. It may also be possible to record the statement issued when the audit action took place.

To enable Change Tracking on the Menu database, you will need to specify the database name and indicate that you want to turn Change Tracking on. The last line in the preceding T-SQL code is optional. These values indicate how long you would like to retain changes and if the retention history should be cleaned up automatically.

Change Tracking is now enabled on the dbo.Recipe table. Change Tracking can either record that an entire data row has changed or specify that specific columns have changed. When you track column changes, SQL Server will record that a change happened and the specific column or columns that have changed. If you do not specify a value for TRACK_COLUMNS_UPDATED, SQL Server will use the default value of off.

The records in Table 14-1 are from the CHANGETABLE. There is a column for the RecipeID, the primary key on the table, and columns to track information about the changes. Since none of the data records have been modified, the values are all NULL.

These results are recorded from the update issued in Listing 14-4.

The RecipeID affected is listed in the first column of Table 14-2. This matches the same RecipeID updated in Listing 14-4. The ChangeOperation is listed as a U for update. This also matches the DML action in Listing 14-4. The final five columns in Table 14-2 were populated using specific functions to unmask the columns indicated in the Change Tracking tables. The function involved returns a true value if the column was modified and a false value if the data was not modified. In the case of Listing 14-4, the only column updated was the DateModified. This matches the result in Table 14-2. The columns NameChange, DescChanged, IsActiveChanged, and CreatedChanged are all false. These values were not changed. However, the column ModifiedChanged is true. This indicates that the column referenced, DateModified, was changed.

There are some potential issues regarding consistency when using Change Tracking. Storing the data does not affect the consistency of the data. The consistency of the data is affected as part of the data retrieval. You can do everything possible to make sure you are looking at the most recent data in the Change Tracking table. This includes checking for the last synchronized version in Change Tracking and confirming that this version is still available. However, if the version in Change Tracking is older than the retention period, this data may be cleaned up before the data can be retrieved. With Change Tracking, you may also have occasions where a data modification occurs after the last synchronization. This can cause additional versions or modified records to be returned. Any of these scenarios can affect the consistency of the data returned with Change Tracking. The best practice to minimize issues with consistency is to use the snapshot isolation level.

If you need to know more than the row or column that was changed and the number of changes that occurred for that record since implementing Change Tracking, you may want to consider Change Data Capture. Like Change Tracking, there are benefits and drawbacks of using Change Data Capture. The single largest benefit is that Change Data Capture will capture the details related to the data record that was changed.

For inserted data, you will be able to see exactly what was added to the table. Similarly, you will be able to see all the columns for a data record that was deleted. When updating data, you will be able to access both the data before and after the update. A downside is that each change to the table requires that at least one record is written to a tracking table. Another downside is that two SQL Server Agent jobs are created for each table that is tracked. As you increase the number of tables under Change Data Capture, the amount of resources used will increase as well. This can cause the performance hit associated with Change Data Capture to be severe enough to prevent you from implementing Change Data Capture.

At a minimum you will need to specify the table schema, table name, and the database roles that can access this data. If you specify NULL for the role name, the information recorded by Change Data Capture will be accessible to everyone. Once this table has been enabled for Change Data Capture, I will be able to see the new jobs created in SQL Server Agent. You will receive a message once the T-SQL code in Listing 14-7 completes. You can see an example of this message in Figure 14-1.

The process of enabling Change Data Capture also creates several system tables in the cdc schema. You can see these tables in Figure 14-2, the system tables that were created to manage Change Data Capture.

The table that we will use to track changes on dbo.Recipe is the cdc.dbo_Recipe_CT table.

This can work somewhat differently when adding Change Data Capture to an existing table. If you add Change Data Capture to an existing table, you will still be able to track when changes have happened to your data records, but you will only be able to see the number of data modifications that have happened since Change Data Capture was implemented.

It may not look like there is much information being recorded, but the information saved in this table can be quite helpful. The column __$operation indicates the action that occurred on this table. The __$operation will tell you which type of activity was performed. If the record was inserted, you will have one entry in cdc.dbo_Recipe_CT with the values that were inserted. If the record was deleted, you will have one entry in cdc.dbo_Recipe_CT with the values that were deleted. If the record was updated, you will have two entries in cdc.dbo_Recipe_CT table. The first record will have the values of the record before the update. The second record will have the values of the record after the update.

If the table had already existed and I had enabled Change Data Capture, you will not see those data records until they had been updated. Once these records are modified, they will show up in the cdc.dbo_Recipe_CT table. While Change Data Capture can be easy for setup and implementation, you should use caution when adding tables to Change Data Capture. Each table added to Change Data Capture causes two SQL Server Agent jobs to be created. There is also a significant amount of logging that occurs as a result of tracking changes on these tables. Both can cause SQL Server to use additional resources.

You can use Change Tracking, Change Data Capture, or database triggers to track data modifications. All these options have their own strengths and limitations. When choosing the right option for your organization, you will want to consider what type of data you need to record and the performance overhead you are willing to incur tracking these changes. If you need better performance, you will generally have to choose less functionality. The more information you would like to collect or the more you would like to customize logging data modifications will come at a cost of increased hardware utilization and potential performance overhead.

Error Handling

In addition to logging data modifications, you may also find that you would benefit from logging certain types of errors that occur within the database. While there are SQL Server specific errors that can occur, that is not the focus of this section. There are also errors that occur as a result of the application interacting with the database. Many of these errors can be logged outside of SQL Server as part of your application development. However, you may find that some issues need to be accessible from within SQL Server.

This block of code will insert a record if there are no errors. However, if an error is encountered, then the transaction will be rolled back. There are some ways to integrate this with your application code so that the user is aware there was an error with the transaction. The goal of using this method is to prevent the application from crashing or prevent the end user from expecting the transaction to save correctly. If the user is aware the action has failed, they have the option to correct the issue and retry the action.

You may find that your application is able to write or update data in the database without any issues. However, you may have a process that sends data from one system to another. There may be infrastructure issues or inconsistencies with data types that can cause failures when sending data from one database object to another. These are also a type of error that you will want to determine how it should be handled. You will still want a method to handle these failures, but you may also need more instantaneous reporting that these records failed to be sent or received. Business today requires a constant state of uptime and successful interactions. You can control your effectiveness in responding to these issues by how you record failures when trying to process data.

Database tables that store information that need to be moved through a system often store a status type column in the data table. Whatever process updates, the status of the record may either have issues updating the record status or update the record status to a failed state. When there is not a large amount of data in the table, finding these failed records can be easy. This would usually require a search of records where the status has not changed in a specified period or where the records are in a failed or error status.

The records in Table 14-5 indicate recipes that have been started and their various statuses. The first record is for RecipeID of 1 with a status of completed. The second record is for RecipeID of 1 with a status of cancelled. The third record is for RecipeID of 3 with a status of error. The last record displayed is for RecipeID of 4 with a status of started.

When there is an error for any of the records, those records will have a RecipeHistoryStatusID of 4 in the dbo.RecipeHistory table. Initially the amount of data in this table will not be very large, and it will be easy to find the recent error records within this table. However, over time this table will grow to a considerable size. This can cause SQL Server to search through many records in order to find any recent error records. If we plan for the table dbo.RecipeHistory to grow to a size where it will be difficult to search, we may implement error logging differently. There may be other scenarios where you would want to keep all records that have errored in an error status, but you would also want to know what to be able to resolve any issues with recently errored records.

In either of these scenarios, it may be beneficial to create a table specifically for logging recent error records. Before creating this table, you may also want to consider how this table must be managed over time. Unlike the dbo.RecipeHistory table, you would want to make sure that this new table did not get too large. You would also want to only keep recent error records in this table. Keeping a small number of records in the table allows the table to be easily searched. The goal of this new table will only be to alert users of any recent error records. Considering the purpose of this table, you will also want to design a process to purge data from this table regularly.

Once we have a record in the dbo.RecipeHistoryLog table, we can generate an alert based on the existence of a record in this table. One option may be to have a stored procedure that is executed every 15 minutes. The purpose of this stored procedure may be to generate an email if there are any records found in the dbo.RecipeHistoryLog table. If you choose this method to create your alerts, you will want to also make certain that you remove data from this table regularly. In this case, you would also need a stored procedure to regularly remove data from this table.

Regardless of which method you choose to implement logging errors between your applications and SQL Server, you should make sure that these errors are tracked somewhere that can be accessible to multiple parties. One of the more difficult issues to troubleshoot is when there is no logging available. The goal of logging errors related to SQL Server is to allow individuals within your organization to quickly find where issues are occurring so that they can resolve them efficiently. You may choose to implement most of your error handling from the application. However, it may be possible to generate automated reports from within SQL Server for errors that need to be corrected promptly.

As part of your application development, you will want to consider what type of logging is necessary for your organization. In industries where there is the possibility for greater theft, it may be more important to track when a data modification occurs and who modified the data. Depending on the information needed will determine what type of logging you implement for data modifications. You will also want to consider how to manage errors associated with the database. These are not errors that are specific to SQL Server but are errors that occur as a result of the T-SQL code that has been written for use by your applications. After determining how to manage logging for your application, you may want to consider how to design your T-SQL code to be reusable.