Introduction and Overview
Track Data Changes
The course is part of this learning path
In the Information age data is the new currency and like anything valuable, it needs to be protected. Azure SQL and its environment provide a range of mechanisms for protecting your data from a multitude of hazards. The potential threats range from bad actors trying to steal information to unintentional human error corrupting your data. To cover all eventualities Azure provides pre-emptive protection in the form of network security, several types of data encryption, data classification, and vulnerability assessment services. After the fact protection is available in the form of built-in data change tracking. This course shows not only tells you about what protection is available for your database but also how to implement it.
If you have any feedback relating to this course, feel free to contact us at firstname.lastname@example.org.
- Learn what security components are available within Azure SQL
- Understand how these elements work together to provide a secure environment
- Learn how to implement infrastructure security
- Learn how to secure your data from external and internal hazards
- Learn how to implement data change tracking
- Anyone who wants to learn how to implement secure Azure SQL databases
- Those preparing for Microsoft’s DP-300 exam
To get the most out of this course, you have should a general understanding of the fundamentals of Microsoft Azure. Experience using databases — especially SQL Server — would also be beneficial.
The GitHub repository for this course can be found here: https://github.com/cloudacademy/azure-sql-data-security-dp-300
There are several ways that you can track changes to information in database tables. You could use update and delete triggers to log changes, but Azure SQL, managed instances and SQL Server have a built-in feature called system versioned and temporal tables. That’s temporal as in time not to be confused with temporary. Essentially this means that you associate a history table with the table you want to track data changes in. Not only do these tables need to have the same structure, but they need to have specific date-time columns and they must have an integer primary key. You have 3 options for creating the history table. You can create the history table automatically when creating the data table, or you could create the history table automatically for an existing data table or associate an existing history table with a data table. However you choose to implement the history table, once it’s in place it will automatically log historical versions of table records when they are updated or deleted. Let’s have a look at how you might implement a system versioned temporal table and it will become clear to you how it works.
I have a table called customer and I want to track data changes using a system versioned temporal table. As I said there are several ways that we can do this. I’m going to copy the customer data into a temporary table, and then recreate the customer table with the required datetime2 columns and set system versioning to on, specifying both the history table and how long to retain the history for. The first thing I want you to take note of are the two datetime2 columns SysStartTime and SysEndTime. The column names aren’t important, but their type and attributes are. One needs to be generated always as row start, and the other generated always as row end. Whatever you call your datetime2 columns, they must be used as the parameters in period for system_time. The next aspect of setting up system versioning is much more intuitive. We create the table with system versioning equals on, specifying the name of the history table. The history table must belong to the same schema as the data table. The history table has the potential to grow rapidly in size, so there is the option of specifying how long you want to retain history for. I’m going to set the history retention period to one month. Now let’s go through the process. I’ll create the temporary table, then insert my customer data into it and drop the original customer table. Next, create the new customer table with system versioning set to on. When we refresh the tables in the object explorer, we can see the newly created customer history table nested inside the system versioned customer table, complete with a special little clock icon. Finally, I’ll insert customer data into the new customer table. Doing a select from custom history shows nothing, as nothing has changed yet. I’ll update the name of customer 2 and look at the customer history table again. Now we can see the old name of customer 2.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.