SQL Server Mirroring with TDE

Question

SQL Server Mirroring with TDE

toolman21

SSC-Addicted

Points: 428
More actions
March 15, 2023 at 1:17 am

#4160802
I am trying to mirror a TDE Encrypted Database. (SQL 2019). When I try to start mirroring with the GUI it says "Database not configured for mirroring". Same if I try to use ALTER SET PARTNER code.
I created a TestDB not encrypted and the mirror starts fine using the same steps. (restore with norecovery etc).
I restored the master key, certificate on the second server, they show as there when queried.
I tested to see if the DB would restore to the second server as normal WITH RECOVERY and it goes as expected and I can see data.
I am out of ideas.
- This topic was modified 1 year, 8 months ago by toolman21.
March 15, 2023 at 11:54 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 1

toolman21 wrote:

I am trying to mirror a TDE Encrypted Database. (SQL 2019). When I try to start mirroring with the GUI it says "Database not configured for mirroring". Same if I try to use ALTER SET PARTNER code.
I created a TestDB not encrypted and the mirror starts fine using the same steps. (restore with norecovery etc).
I restored the master key, certificate on the second server, they show as there when queried.
I tested to see if the DB would restore to the second server as normal WITH RECOVERY and it goes as expected and I can see data.
I am out of ideas.

Are you getting error while trying to configure Mirror for TDE enabled database?

toolman21 SSC-Addicted Points: 428 More actions · Answer 2

Yes. The database is restored to Recovering status. At that point you are supposed to be able to start the mirror, but the process currently errors out with "Database cannot be opened it is in the middle of a restore" using the GUI.

If I use the alter set partner is gives me the Database is not configured for mirroring.

(Before they were both giving me the not configured message)

I can setup a normal NON TDE mirror and it works. I can restore the encrypted DB to the second server normally and see data so my assumption is that they keys/certs are in the right place.

I feel like I missed some step, but for the life of me I haven't figured out what it is yet.

rvsc48 SSCertifiable Points: 7685 More actions · Answer 3

A couple of things you could check:

Make sure you are on the Primary server when configuring mirroring
Make sure you are restoring the latest Sql Server Transaction log of the database you are mirroring to the Secondary
Finally, run the mirroring configuration commands from a query window instead of using the Mirroring Wizard. See this link - https://howardsimpson.blogspot.com/2020/07/database-cannot-be-opened-it-is-in-the-middle-of-a-restore-microsoft-sql-server-error-927.html

toolman21 SSC-Addicted Points: 428 More actions · Answer 4

I got it working. I think you have to run the set partner first on the secondary DB, then the primary. And I had to run them manually the GUI failed.

alter database Database01 set Partner = 'TCP://Server02:5022'

Thanks for the suggestions.