Sunday, May 12, 2013

new concepts in SQL since sql server 2008


sp_releaseschemalock
xp_sqlagent_is_starting
sp_getschemalock
sp_enable_sql_debug
xp_loginconfig
sp_xp_cmdshell_proxy_account
sp_control_dbmasterkey_password
sp_droporphans



extended events
rank/row number/dense_rank/ntile
filestream/filetable
try/catch/throw
GUID
common table expression
data collector (management data warehouse)
pivot/unpivot
cross apply/outer apply
output
deterministic/non deterministic functions
trancount
transaction management
configure SSAS/full text indexing
TDE
subnet cluster
audit
xact_abort
service level security
core mode installation
RAID
locks
coalesce
statistics
plan guides
persisted columns
partitioning
query hints

Thursday, April 4, 2013

error, "Transparent Data Encryption is not available in the edition of this SQL"


If  you try to attach a database using “sp_attach_db” or  “sp_attach_single_file_db”, you may face the below error.
The reason is that “mdf” file you are trying to attach is from sql server 2008 R2 enterprise edition and you are trying to restore on a standard/express edition.


File activation failure. The physical file name "F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\xxx.ldf" may be incorrect.
New log file 'l:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\xxx_log.LDF' was created.
Msg 33117, Level 16, State 2, Line 1
Transparent Data Encryption is not available in the edition of this SQL Serverinstance. See books online for more details on feature support in different SQLServer editions.
Msg 1801, Level 16, State 3, Line 1
Database 'xxx already exists. Choose a different database name.

how to restore TDE encrypted databases when you lose the master key and also to restore SSRS when you lose the encryption key


A good way to restore databases when you lose your master key or the server certificate in sql server 2008 R2 is as follows:

1) get the backups from the tapes, get the complete "f:\program files\microsoft sql server" directory with all subfolders and files
2) install sql server 2008 R2 on a new machine preferably with the same service account as the previous installation and also with the same collation settings
3) now backup the "f:\program files\microsoft sql server" directory from the newly installed sql server, including the system database and user database files; you can do this only after stopping sql services.
4) replace the old backup directory from the tape on top of the newly installed location; what this means is that we are rewriting the complete installation directory and taking it back to the old state.
5) restart sql server and you can connect with SA or any other user who have admin access
6) its not so easy but it works as we have successfully done this

A good way to restore SQL Server Reporting services if SSRS is corrupted and you don't have the encryption key to re initialize the setup.
this means if you face this error, "the report server installation is not initialized, (rsReportServerNotActivated) then you can do the below step.

1) perform the same steps as 1), 2), 3) , 4) but this time the folder should be SSRS installation directory, it looks like this: "F:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER"

after replacing the installation directory with the backup one, start the SSRS config manager and do the 'report manager url' and ' service account' settings.
and when you go to "encryption keys" tab, you would automatically see that everything would be in sync.
quickly backup the encryption key and that's it you restored your report services to your original state.