Tuesday, August 27, 2013

[SQL 2012] TDE database Encryption_state stuck on 2

[SQL 2012] TDE database Encryption_state stuck on 2

TDE database Encryption_state stuck on 2

Posted: 27 Aug 2013 02:01 AM PDT

Hello all,I am testing TDE in a lab environment for a proof of concept for roll out. I have 3 servers, a primary, a disaster recovery using AlwaysOn Availability Groups, and a report server subscriber using transaction replication from the primary. I set up the primary first using the scripts below:USE MASTERGO-- Create a Master KeyCREATE MASTER KEY ENCRYPTION BY Password = 'Password1';-- Backup the Master KeyBACKUP MASTER KEY TO FILE = 'Server_MasterKey' ENCRYPTION BY Password = 'Password2';-- Create Certificate Protected by Master KeyCREATE Certificate SQLCertTDEMaster WITH Subject = 'Certificate to protect TDE key';-- Backup the CertificateBACKUP Certificate SQLCertTDEMaster TO FILE = 'SQLCertTDEMaster_cer' WITH Private KEY ( FILE = 'SQLCertTDEMaster_key', ENCRYPTION BY Password = 'Password3' );Then I encrypted the Primary DB with the following scripts:USE TestDBGO-- Create a Database Encryption KeyCREATE DATABASE ENCRYPTION KEY WITH Algorithm = AES_128 ENCRYPTION BY Server Certificate SQLCertTDEMaster; -- Enable the Database for Encryption by TDEALTER DATABASE TestDB SET ENCRYPTION ON; When I run the following select statement (select * from sys.dm_database_encryption_keys ). It lists the database in an encryption_state = 2, with 0 percent_complete. It has been that way for 24 hours and the database is only 2GB.Any suggestions?Thanks,DK

Deadlocks trace flag

Posted: 26 Aug 2013 04:17 AM PDT

Hi,We were experiencing some deadlocks on our database so I activated the deadlocks trace flag with DBCC TRACEON(1204, -1) to log the deadlock information.Now that we've found the problem my question is if there's any problem, overhead, slower performance, .. on SQL Server if the flag is ON, since by default the flag if OFF..Thanks,Pedro

aggregation rules ( Max or Join )

Posted: 26 Aug 2013 09:06 AM PDT

Hi,I have a table like below, and I need to break it by period of time and split amt by respective period, so I'll get let say 3 row out from one input.I also want to keep rest of fields as is , how better achieve this?I can use just Max(col1) or include all Col1,2,3,4 on Group BY or ... something elsewhat guru do in this caseTxMario[code="other"]id timeIN timeOut Duration col1 col2 col3 col4---------------------------------------------------------1 6/2/13 8/15/13 64 days info1 info2 info3 info4 || \/ 1 NULL 6/30/13 28 days info1 info2 info3 info4 1 NULL 7/31/13 31 days info1 info2 info3 info4 1 NULL 8/15/13 15 days info1 info2 info3 info4[/code]

SSDT Problems for VS 2012

Posted: 26 Aug 2013 07:55 PM PDT

Hello guys I need urgent help here.I've installed ssdt sp 1 and it worked for about few days. Yesterday when I was about to create a new project, reporting services and integration services went missing. I tried repairing but didn't work.I decided to uninstall ssdt then installing it again, but no luck because the ssdt feature shows as installed . what can I do to solve this because when I click next it shows an error saying the features are already installed. What are the features that are under ssdt that I haven't uninstalled.Please I really need help, need to finish a project.Regards

Questions on Policy Based Management

Posted: 26 Aug 2013 05:38 AM PDT

Hello, I am a beginner DBA wanting to learn as much as I can from the DBA world. Please pardon me if my questions sound stupid. Can two different conditions be combined to be used on one policy? I am trying to check for all servers with versions 2008 R2 and above(Server Facet), and to verify that their Backup Compression is enabled(Server Configuration Facet). If the compression is disabled, I'd like to enable them. I haven't been able to find anything regards to combining two or more different conditions onto one policy. Is this even possible?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog