![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture11_10.png\")
<\/p>\n<\/p>\n
Introduction<\/strong><\/p>\n “This blog demonstrates the detailed steps needed to set up Transparent Data Encryption with AlwaysOn Availability Group.”<\/em><\/p>\n The SQL Server provides Transparent Data Encryption (TDE) for encrypting the physical files to protect customer sensitive data. It was introduced with SQL Server 2008 as an Enterprise Edition feature.<\/p>\n TDE is available with the following SQL Server Editions:<\/strong><\/p>\n Let\u2019s explore how to Configure TDE with AlwaysOn Availability Group in the following scenarios.<\/strong> Scenario: Adding TDE encrypted database to AG group.<\/strong><\/p>\n We are using a two-node AG to set up the TDE and the following process explains the steps in detail. Follow the secondary steps on each of your secondary replicas (if you have more than 1 secondary)<\/p>\n Tip:<\/em> It is always recommended to run DBCC CHECKDB to ensure that the database is error-free and take the latest full backup of the database before implementing TDE. <\/p>\n Step 1: Primary Instance – Create a Master Key<\/strong><\/p>\n If you are encrypting the TDE for the first time, then there should be no master key and you can use the following SQL that will give no result set.<\/p>\n Now create a master key in the master database using strong passwords.<\/p>\n Validate the master key:<\/p>\n Back up the Master Key to a secure location as a best practice. The password for the backup can be different from the Master Key password.<\/p>\n Step 2: Primary Instance – Create a Certificate<\/strong><\/p>\n Create a certificate to secure the database encryption keys. The default expiry date of the certificate is 1 Year. Validate using the below TSQL to confirm that the certificate has been created.<\/p>\n Step 3: Primary Instance – Creation of Database Encryption Key (DEK)<\/strong><\/p>\n Create the DEK which is a symmetric key to encrypt the actual database content and you can create using available AES algorithms.<\/p>\n Step 4: Primary Instance – Backup the Certificate <\/strong><\/p>\n Back up the certificate and the private key as a good practice. With this, you can restore the database backup files or attach the database data files to another SQL Server instance.<\/p>\n Step 5: Secondary Instance – Create a Master Key <\/strong><\/p>\n You should create a database master key on all secondary replicas if it does not exist, this is like step 1 in the primary instance. The master key was already created on both the instances in step 1.<\/p>\n Step 6: Secondary Instance – Create Secondary Certificate<\/strong><\/p>\n Copy the certificate from the primary replica to all secondary replicas and create a certificate on the secondary replica using the primary replica certificate.<\/p>\n You need to specify the decryption password that was used earlier to encrypt the backup on the primary replica.<\/p>\n Step 7: Primary Instance – Enabling TDE Encryption<\/strong><\/p>\n Query the following command as a final step to enable the TDE in the required database.<\/p>\n Now, let’s monitor the progress of the encryption process and make sure the state is 3 which describes the encryption is completed.<\/p>\n The following query lists the databases with TDE enabled on the databases.<\/p>\n The above result shows that the TDE is enabled on the TDE_DB database, and the encryption state 3 means that the database is completely Encrypted. By default, the tempdb will be encrypted automatically when we encrypt using TDE on any user database. <\/p>\n Step 8: Adding Database to the Availability Group <\/strong><\/p>\n Let\u2019s add the encrypted database to the AG group.<\/p>\n \nNote:<\/strong><\/em> Adding TDE encrypted database to an Availability Group does not support GUI options in SSMS.<\/p>\n You need to use TSQL to add the database to the AG group. On the Primary Replica, take a full backup, transaction log backup for the database TDE_Test database, and copy it. You need to then restore it with NORECOVERY<\/em> on secondary. <\/p>\n \nOnce the backup and restore is complete, run the following commands to add the database to the Availability Group.<\/p>\n Step 9: AG health Validation <\/strong><\/p>\n Now validate the AG health check status through the dashboard and a manual failover test to perform to make sure that our database, with TDE enabled on it is working fine.<\/p>\n Scenario: Configure TDE to the database which is already existing in the AG group. <\/strong><\/p>\n Follow the steps (which I had had discussed in our first scenario) to enable TDE when the database is already added to the AG group. <\/p>\n Step 1: Primary Instance – Create a Master Key<\/p>\n Step 2: Primary Instance – Create a Certificate<\/p>\n Step 3: Primary Instance – Creation of Database Encryption Key (DEK)<\/p>\n Step 4: Primary Instance – Backup the Certificate<\/p>\n Step 5: Secondary Instance – Create a Master Key<\/p>\n Step 6: Secondary Instance – Create Secondary Certificate<\/p>\n Step 7: Primary Instance – Enabling TDE Encryption<\/p>\n Step 9: AG health Validation<\/p>\n \nAs I had already created the master key and certificate on both replicas in our previous scenarios, we can skip steps 1,2,4,5,6,7. You just need to create DEK and enable the TDE from steps 3 and 7.<\/p>\n \u2022 Primary replica: node1<\/p>\n \u2022 Secondary replica: node2<\/p>\n \u2022 AG Group:TDE_AG<\/p>\n \u2022 AG database : Test_tde<\/p>\n \u2022 TDE Certificate : TDE_AG2021<\/p>\n Step 3 and 7 \u2013 On Primary Instance<\/strong><\/p>\n Monitor the progress of the encryption process and make sure the state is 3 which describes the encryption is completed.<\/p>\n Check TDE enabled database with the following query.<\/p>\n Validate the AG health check and do a failover test to make sure everything is working fine. <\/p>\n Scenario: Rotating Expired Certificate<\/strong><\/p>\n When you notice that the TDE certificate is expiring soon, you need to rotate the certificate as a best practice even though the expired certificate will not cause any issues on Database regular operations. <\/p>\n You can check the expiring date for our TDE certificates and follow the steps described to rotate the SQL TDE certificates<\/p>\n Step 1: Primary Instance – Create a New Certificate <\/em><\/p>\n Step 2: Primary Instance – Backup the Certificate <\/em><\/p>\n \nStep 3: Secondary Instance – Create Secondary Certificate<\/em> <\/p>\n Step 4: Primary Instance – Rotate the SQL TDE certificate<\/em><\/p>\n Validate the Expiry date for the Test_tde database:<\/p>\n Step 5: AG health Validation<\/em><\/p>\n Save the expired TDE certificates for a while to restore any older backups. The new certificate will only be used when you are restoring the databases that were backed up since the key rotation.<\/p>\n Conclusion<\/strong><\/p>\n The SQL Server provides Transparent Data Encryption (TDE) for encrypting the physical files to protect customer\u2019s confidential information. In this blog, I have described various scenarios to configure TDE for the AlwaysOn availability group databases.<\/p>\n","protected":false},"excerpt":{"rendered":" Configuring TDE with AlwaysOn Availability Group<\/p>\n","protected":false},"author":2,"featured_media":7160,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26286","content_type":"blog","created_by":"10130","authored_on":"2023-11-23T13:58:42+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Rachamallu Jayaprakash Reddy, SQL Server Database Administrator, Rackspace Technology","seo_description":"Discover the steps to establish Transparent Data Encryption with an AlwaysOn Availability Group, ensuring secure and encrypted SQL Server physical files.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26286-insights_image-database-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Microsoft SQL","product":"","publisheddate":"2021-11-09T13:58:42","rxt_solutions":"Databases, Data Management","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Implementing TDE with SQL Server AlwaysOn Availability Group | Rackspace Technology","field_topics":"","field_slug":"configuring-tde-with-always-on-availability-group","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7158","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/comments?post=7158"}],"version-history":[{"count":2,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7158\/revisions"}],"predecessor-version":[{"id":9070,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7158\/revisions\/9070"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media\/7160"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media?parent=7158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/categories?post=7158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/tags?post=7158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n
\n1. Adding TDE encrypted database to AG group.
\n2. Configure TDE to the database that already exists in the AG group.
\n3. Rotating Expired Certificate<\/p>\n\n
![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture1_31.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture2_27.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture3_25.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture4_22.png\")
<\/p>\n
\nTip<\/em>: It\u2019s a best practice to set the expiry date for 5 years as it is not good to get this expires in one year<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture5_18.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture6_11.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture7_11.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture8_10.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture9_10.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture10_9.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture11_9.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture11_11.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture12_8.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture13_7.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture14_7.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture16_3.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture17_5.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture18_3.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture19_1.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture20_2.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture21_2.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture22_0.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture23_0.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture24_1.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture25_0.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture26_0.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture27.png\")
<\/p>\n![\"tde](\"\/wp-content\/uploads\/2026\/blog_article_images\/26286-body-Picture28.png\")
<\/p>\n