Hi All,

I implemented TDE on one of my SQL Server instance. When I query the
database state with the following query it returns 2 to one of my
databases which is about 110GB. Its been 2 weeks and its still says
2.

SELECT db_name(database_id), encryption_state,*
FROM sys.dm_database_encryption_keys

The same instance has other Databases which are less than 50GBs are
all set to show 3 on the Encryption_State column.

These databases are also set to mirror and I have applied the
Certificates to the DR and the mirroring has been active and
synchronized state. I see logs were restored as part of mirroring. I
also have been running Transactional DB backups. I have no complains
from the application side and the database is fully accessible.

One thing I am happy to note that my backup sizes are big to match the
data file size after the encryption as I am told the concept of
compression doesnt come into play after a DB is encrypted.

I would greatly appreciate if anyone can shred some light on this and
confirm that the encryption is complete. How do I confirm that the
encryption is complete?

Thanks in advance..
- BK.

Query Builder (querybuilder (AT) gmail (DOT) com) writes:
Well, if it says 2, that would indicate that it is not complete. What does
the other columns in this DMV report for this database? Particularly, what
does percent_complete report?

I found
http://www.simple-talk.com/sql/datab...nsparent-data-
encryption/
which suggests some alternate ways to verify completion.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

I really appreciate your response...

The percent_complete says 0 for all the databases including the one
which is still encrypting..
is_encrypted = 1 for all Databases
encryption_state = 2 for only this DB but everything else is 3.

The mirroring to DR is also synchronized. I do see the backup size has
grown to be about 81GB where it used to be about 25GB with
compression. (The db size is actually 115GB).

I am worried as its been almost 2 weeks. Any help would be much
appreciated..

Thanks again..

Regards,
BK.




On Jul 26, 8:38*am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Query Builder (querybuilder (AT) gmail (DOT) com) writes:
Since I don't work much with TDE, I consulted my MVP mates. One them
brought up this:

Sounds like a long-running transaction is preventing log truncation and
the log wrapping. IIRC the encryption_state won't switch to 2 until the
log wraps.

That would also account for the increasing backup size - as more and
more log needs to be included in the backup because of the long-running
transaction.

All from memory - so could be incorrect about the TDE thing. Ask him to
check log_reuse_wait_desc and DBCC OPENTRAN.

Is this applicable to your situation?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Hi Erland,
Thanks again for all your help on this matter.. I really appreciate
your support..

I do not have any long running transactions. DBCC OpenTran did not
return any transactions. I do have mirroring enabled and i see that
the databases are synchronized and no pending logs to be applied on
the DR.

I checked the log_reuse_wait for this database and its set to 2
(log_Backup).

I am worried in the event of a DR, will I be able to fail over and
make sure the DB will not get corrupt?

I look forward to your response..

Thanks again..

Regards,
BK

On Jul 28, 5:11*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Query Builder (querybuilder (AT) gmail (DOT) com) writes:
And you are not using replication?

Paul's response indicated that the log has to wrap. Could it be that the
transaction log is very big in relation to the update activity in the
database?

I don't know, but what do I know, is that you need to test anyhow,
no matter the state is 2 or 3. That is, you need to know that you
understand the procedure in case of a failover, and that the databases
are still accessible after a disaster. It seems like a good idea to
first test with one of the other databases.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx