Manvendra Singh
Check Instant File Initialization using error log file

Understanding Instant File Initialization after enabling TDE (Transparent Data Encryption) on SQL Server databases

August 19, 2021 by

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.

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.

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.

Check Instant File Initialization

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.

Check Instant File Initialization using error log file

Let’s check the database size so that we can analyze its behavior while extending the data file.

Check DB size

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.

Here is the output which says TDE is not enabled for database Test_DB.

Check TDE is enabled for target database or not

Run the below statement to extend the data file size of our target database Test_DB.

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.

Confirm DB size post allocating additional space

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.

trace flag output

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.

Create the Certificate using the above master key to secure it.

Create a database encryption key by running the below statement.

Finally, enable encryption:

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.

Enable Transparent Data Encryption (TDE)

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.

Verify TDE enablement

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.

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.

Zeroing process has been captured in SQL Server error log because IFI is not working on TDE enabled databases

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.

Extend DB file size after enabling TDE

An error log has also captured zeroing operation as per the below image.

Zeroing phase captured in error log

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.

check db size post additional size allocation

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.

Manvendra Singh
Transparent Data Encryption (TDE)

About Manvendra Singh

Manvendra is a database enthusiast, currently working as a Senior Architect at one of the top MNC. 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

862 Views