SQL Server Transparent Data Encryption (TDE) enables encryption on database files to secure its databases. Enabling TDE might have some adverse effects on your database system or on some database features. We must understand our database environment and plan accordingly before opting and deploying any new feature to production systems.
SQL Server offers various features to make our database systems more stable, scalable, and faster to serve business requirements. These features must be planned and implemented carefully to avoid any unexpected behavior or issues. You might get another pain rather than getting some relief by deploying such features if you are deploying it without careful planning.
I am going to demonstrate one of such scenarios of adverse impact on Instant File Initialization after enabling TDE on your environment. This feature is very crucial if you have very large databases with highly transactional workloads like SAP or mission-critical applications. We enable Instant File Initialization to speed up the process of extending or increasing the data file sizes. If we enable TDE (Transparent Data Encryption) on any user database, then instance File Initialization will stop working for that database and you might end up having severe performance issues if you do not have appropriate settings for the database files growth.
- You can look at the below articles to understand Instant File Initialization and TDE:
Here, I will show you how enabling TDE will disable instant File Initialization functionality for that database and how can you plan to deal with such a scenario if you have IFI (Instant File Initialization) enabled and want TDE to be configured on that database.
Prepare the Environment
I have a SQL Server Instance which is enabled to use IFI (Instant File Initialization) functionality. Now the business has decided to enable TDE on one of the transactional databases hosted on that SQL Server instance. Let’s check whether IFI is enabled or not on our target SQL Server instance by executing the below T-SQL statements.
|
1 2 3 |
--Check IFI Enablement SELECT servicename, instant_file_initialization_enabled FROM sys.dm_server_services |
Above DMV sys.dm_server_services will display all services installed on my system and their respective settings and configurations. Here is the output where we can see IFI is enabled for this SQL Server instance.
We can also check IFI is enabled or not by looking into the SQL Server error log file. SQL Server captures this information in an error log file while starting the database engine. Have a look at the below image.
Let’s check the database size so that we can analyze its behavior while extending the data file.
Now, we will extend the data file of the above database along with enabling trace flags 3004 and 3605 to track whether Instant File Initialization is working or not before and after enabling TDE. Both trace flags will help us determining whether IFI is working or not.
Next, we will see the impact of TDE on IFI in the below section. We will get to our conclusion by comparing the outcome of the below two steps.
- Track IFI (Instant File Initialization) functionality is working or not before enabling TDE
- Track IFI (Instant File Initialization) functionality is working or not post enabling TDE
Track IFI (Instant File Initialization) before enabling TDE (Transparent Data Encryption)
Validate TDE is enabled for data Test_DB or not by running the below T-SQL statement.
|
1 2 3 4 |
--Check TDE Enablement SELECT name, is_encrypted FROM sys.databases FROM name=’Test_DB’ |
Here is the output which says TDE is not enabled for database Test_DB.
Run the below statement to extend the data file size of our target database Test_DB.
|
1 2 3 4 5 6 7 8 9 10 11 |
--Extend DB data file size before enabling TDE USE master; GO ALTER DATABASE Test_DB MODIFY FILE ( NAME = Test_DB, SIZE = 5000MB ); GO |
Command will be executed within seconds and a specified file size has been allocated to the primary data file.
Verify allocated data file size in the below image.
Trace flag 3605 has not tracked anything while extending data file size because IFI has skipped the zeroing phase of space allocation. Neither we can see anything in the error log except logging an entry that trace flags 3605 and 3004 are enabled for session id 57 under which I was running the above command. Here is the output. Note, I have not enabled the trace flag globally on this instance rather I have used it for my working session id only.
Enable TDE (Transparent Data Encryption)
Now, we will enable TDE on database Test_DB in this section so that we can compare the outcome of before and after effect. I would recommend you going through this article, configure Transparent Data Encryption (TDE) in SQL Server to understand and learn the basics of TDE and how to enable it for your database.
I have first created a master key by running the below T-SQL statement.
|
1 2 3 4 5 |
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD='$ql$h@ck@12'; GO |
Create the Certificate using the above master key to secure it.
|
1 2 3 4 |
CREATE CERTIFICATE Test_DB_TDECert WITH SUBJECT='Test_DB_Encryption'; GO |
Create a database encryption key by running the below statement.
|
1 2 3 4 5 6 |
USE Test_DB GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE Test_DB_TDECert; GO |
Finally, enable encryption:
|
1 2 3 |
ALTER DATABASE Test_DB SET ENCRYPTION ON; GO |
Check the status of the Transparent Data Encryption enablement process using DMV sys.dm_database_encryption_keys or in SQL Server error log file and finally validate whether TDE is enabled or not. Remember, the bigger the size of your database longer it will take while enabling the Transparent Data Encryption.
As the screen suggest, make sure to take a backup of the certificate and encryption key as suggested by SQL Server to ensure you could restore them if they go missing or corrupted. You should read above attached article to backup and restore these certificates.
Track IFI (Instant File Initialization) after enabling Transparent Data Encryption
We have enabled TDE for database Test_DB in the above section. Let’s validate it again to ensure we are doing the right thing.
TDE has been enabled on the database Test_DB as per the above image. Now, we will again extend the size of the data file while enabling the trace flag 3004 and 3605 to analyze the result of whether IFI is working or not. I have reset the data file size of database test_DB back to its original size approx. 102MB that is shown in the 4th image. Now I will run the below statement again to see the difference between its execution since it ran before enabling the TDE.
|
1 2 3 4 5 6 7 8 9 10 |
--Extend DB data file size after enabling TDE DBCC TRACEON (3004,3605) GO ALTER DATABASE Test_DB MODIFY FILE ( NAME = Test_DB, SIZE = 5000MB ); GO |
Once you execute the above statement, you will observe it is taking longer than it has executed before enabling Transparent Data Encryption. In between, I have checked the error log file and this time I can see that zeroing process has been captured and logged in to the error log file. As I have executed the above T-SQL statement with enabling trace flag 3004 and 3605 so zeroing process will be logged in the SQL Server error log file. The below image is showing it.
We can see the very same operation which was completed within seconds before enabling TDE is now taking approx. a minute. Have a look at the below image, this file size allocation has taken approx. 51 seconds in allocating 5000MB of space.
An error log has also captured zeroing operation as per the below image.
I have again checked the data file and IFI configuration for this instance. The data file size is set to the desired value. We can also see IFI is still enabled for this instance, but it is not working for this database Test_DB on which we have enabled Transparent Data Encryption.
We have validated and proved that IFI does not work during data file space allocation post enabling TDE despite its configuration enabled for SQL Server instance by looking at the SQL Server error log.
Data file allocation can be in form of increasing data file space of the database or creating a new data file for identified database or you are restoring the database itself. If you are planning to enable TDE then you should consider allocating appropriate size to its databases otherwise it will take more time and severely impact your IO operations.
Points to consider while Enabling TDE
We have seen one of the adverse effects of enabling Transparent Data Encryption on Instant File Initialization. Think if you have a critical large database system and you have not considered this fact before enabling TDE. It is always recommended to plan and all aspects of impacts before enabling TDE. If you must enable TDE in your production system you can consider the below points to avoid any unwanted zeroing operation during production hours.
- Database Auto growth size must be appropriately set to number value
- Calculate database future growth and set its size accordingly to avoid unnecessary data file space allocation
- Back up your databases before enabling TDE. Make sure to back up the certificate and keys after enabling TDE
- Always evaluate database growth and if needed allocate size during off-hours or maintenance windows
- Avoid removing TDE and recreating it for data file space allocation
Conclusion
This article intended to help understand the behavior of Instant File Initialization after enabling Transparent Data Encryption (TDE) on SQL Server Databases. I hope this article was helpful, in case of any questions, please feel free to ask in the comments section below.
He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects.
You can also find him on LinkedIn
View all posts by Manvendra Singh
- Difference between SQL SELECT UNIQUE and SELECT DISTINCT - January 16, 2024
- How to do a group by clause - April 12, 2023
- PostgreSQL vs MySQL: Understanding their differences - March 13, 2023