TDE-(Transparent Data Encryption)

Creating a TDE protected database
Create a Master Key
A master key is a symmetric key that is used to create certificates and asymmetric keys.
Execute the following script to create a master key:

USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass@word1';
GO

Note that the password should be a strong one (i.e. use alpha, numeric, upper, lower, and special characters) and you have to backup (use BACKUP MASTER KEY) and store it in a secure location

Create a Certificate
Certificates can be used to create symmetric keys for data encryption or to encrypt the data
directly. Execute the following script to create a certificate:

USE master;
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate'
GO

Note that certificates also need to be backed up (use BACKUP CERTIFICATE) and stored in a
secure location

1.Create a backup of the server certificate in the master database. If the certificate that is used
to protect the database encryption key is lost, you will not be able to access the data in a TDE
protected database. The following example stores the backup of the certificate and the private key
file, in the default data location for this instance of SQL Server (C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA).

You must retain copies of both the certificate file and the private key file in order to recover the certificate. The password for the private key, does not have to be the same as the database master key password.

Use Master
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\Testing_TDECert.cer'
WITH PRIVATE KEY
(FILE = 'C:\Cer_Private_Key.pvk',
ENCRYPTION BY PASSWORD ='Pass@word11')

Create a Database Encryption Key

A database encryption key is required for TDE. Execute the following script to create a new
database and a database encryption key for it:

USE Testing

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert

Enable TDE

The final step required to implement TDE is to execute the following script:

ALTER DATABASE Testing
SET ENCRYPTION ON
GO
SELECT [name], is_encrypted FROM sys.databases
GO

You can query the is_encrypted column in sys.databases to determine whether TDE is
enabled for a particular database

Moving TDE Protected DB to another sql server instance

1.Create a database master key on the destination instance of SQL Server. The password does not
have to be the same as the source server.

USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass@word123';

2.Recreate the server certificate by using the original server certificate backup file. The password must be the same as the password that was used when the backup was created.

Use Master
create CERTIFICATE TDECert111111
from FILE = 'C:\Testing_TDECert.cer'
WITH PRIVATE KEY
(FILE = 'C:\Cer_Private_Key.pvk',
DECRYPTION BY PASSWORD ='Pass@word11')

Backup the master database
•Installing the certificate and attaching the database have changed information in the master database.
You should create a new backup of the master database.

Disable TDE

USE MASTER
GO
ALTER DATABASE Testing
SET ENCRYPTION OFF
GO
USE Testing
GO
DROP DATABASE ENCRYPTION KEY
GO
DROP CERTIFICATE TDECert
DROP MASTER KEY

For more learning About TDE Click Here

Read the rest of this entry »

Move User Database Files using Alter Database Command

For moving User database files using alter command , You need to follow these simple steps in same sequence.
1. Put DB in single User Mode using below query

Alter database Dbname
set single_user

If there is some connection openned with DB then use below query to check connections

select DB_Name(database_id) as DBName,* from sys.dm_exec_requests
where session_id>=51 and database_id=DB_Id('DBName')

If you find any open connection with DB then you can kill the process
2. Take User DB offline

alter database Dbname
set offline

3. Now copy mdf and ldf to new location and move DB files location in master DB using Alter Database Command
4.To view logical name of DB files, run below query

select * from sys.master_files

Run below query to in update location of DB files master DB

Alter Database DBname
modify file(Name='Testing', filename='D:\DB_Backup\DBName_DB\Testing.mdf')
Alter Database DBname 
Modify
file(Name='Testing_log',filename='D:\DB_Backup\DBName_DB\Testing_log.mdf')

5. Take User DB online

Alter database Dbname
set online

6. Now check DB files location

Use DBName
Select * from sysfiles

7. Now Set DB in multi-User mode

Alter database Dbname
set multi_user

Read the rest of this entry »

Shrink log file size of Database

There are fixed boundaries from which a transaction log file can be shrunk. The size of the virtual log files within the log determines the possible reductions in size. Therefore, the log file can never be shrunk to a size less than the virtual log file. Also, the log file is reduced in increments equal to the size of the virtual log file size. For example, a transaction log file of 1 GB can consist of five virtual log files of 200 MB each. Shrinking the transaction log file deletes unused virtual log files, but leaves at least two virtual log files. Because each virtual log file in this example is 200 MB, the transaction log can be reduced only to a minimum of 400 MB and only in increments of 200 MB. To be able to reduce a transaction log file to a smaller size, create a small transaction log and let it grow automatically, instead of creating a large transaction log file all at once.

A DBCC SHRINKDATABASE or DBCC SHRINKFILE operation immediately tries to reduce a transaction log file to the requested size (subject to rounding). You should backup the log file before shrinking the file to reduce the size of the logical log and mark as inactive the virtual logs that do not hold any part of the logical log. For more information, see Shrinking the Transaction Log.

Best Practices
Consider the following information when you plan to shrink a database or file:
A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
Most databases require some free space for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.
Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.
These are the steps to create log.
Run below query to check log size of all databases whose log Size more than 1 GB

select DB_Name(A.database_id) as DBName, 
A.name,A.type_desc,(A.size*8/1024) as size_in_MB,
(select primary_database  from msdb.dbo.log_shipping_Primary_databases
where primary_database=DB_Name(A.database_id)) as LogShipping_DB,
'Mirror DB'= case
WHEN B.mirroring_guid is not null then 'Mirroring DB'
else ''
end
from sys.master_files as A, sys.database_mirroring as B
where A.database_id=B.database_id
and A.type_desc='LOG' and (A.size*8/1024)>1024

Note – In above query result, you will also get Databases who are involved in mirroring session. So ignore mirror Database.

Shrink log size using log truncation .

If you are using Sql Server 2005 then run below query –

• First Take log backup
  

  Backup Log Database_Name
  To Disk='L:\Tlog_Shrink\DBName_Log.trn'

• Now truncate the log of DB using below T-Sql Command -
  

  Backup log  Database_Name
  To Disk='D:\DB_Backup\DBNAME_log.trn'
  With truncate_only
  

  Note - Above command will truncate the log but will not create any log backup file on disk. After running this command, you can not take log backup becoz log truncation break the the LSN of log. Now if you want to take log backup of DB, first you need to take Full or Differential backup then log backup will start again. If you will not take full or Differential backup after truncation of log of DB then you will not able to recover data to point-of-failure.

• Now shrink log file size using below T-Sql Command -
  

  USE   Database_Name              
  DBCC Shrinkfile('logical name of Log file',Size in MB)

• Now check the log size of DB using below T-Sql Command -
  

  DBCC SQLPERF(logspace)
  


• Take full Backup – After log truncation you need to take full backup to start log backup again or to get data to point-of-failure.
  

  Backup Database Database_Name
  To Disk='L:\Tlog_Shrink\DBName_Full.Bak'
  

If you are using Sql Server 2008 then run below query –

• First Take log backup
  

  Backup Log 
  To Disk='L:\Tlog_Shrink\DBName_Log.trn'
  

• Truncate the log by changing the database recovery model to SIMPLE
  

  USE Database_Name
  ALTER DATABASE Database_Name
  SET RECOVERY SIMPLE;
  GO

• Shrink the truncated log file to 1 MB.
  

  DBCC SHRINKFILE ('Log file Name', 1);
  GO

• Reset the database recovery model to FULL
  

  ALTER DATABASE Database_Name
  SET RECOVERY FULL;
  GO
  

• Now check the log size of DB using below T-Sql Command -
  

  DBCC SQLPERF(logspace)
  

• Take full Backup
  

  Backup Database Database_Name
  To Disk='L:\Tlog_Shrink\DBName_Full.Bak'

Read the rest of this entry »

Check DB Mirroring Status

Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Database mirroring is supported in SQL Server Standard and Enterprise.

Database mirroring offers a substantial improvement in availability over the level previously possible using Microsoft SQL Server and provides an easy-to-manage alternative or supplement to failover clustering or log shipping. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover with no loss of data from committed transactions. During a typical mirroring session, after a production server fails, client applications can recover quickly by reconnecting to the standby server.

Note
You cannot mirror the master, msdb, temp, or model databases.

select DB_Name(database_id) as DBName,
mirroring_role_desc,mirroring_state_desc,
mirroring_safety_level_desc,mirroring_connection_timeout
from sys.database_mirroring
where mirroring_guid is not null

Read the rest of this entry »

Check DB Owner Name

We have successfully setup the mirroring between our primary site SQ1 and SQ02 servers. We have also configured the log shipping from our primary site to our secondary/DR site. It is working fine and we equal no of DBs available on DR site SQL server. These DBs are read only as per the log shipping configuration.

Now, at primary site user “A” is the owner of DBs but at DR site user “B” is the DB owner. seems this attribute is not getting replicated to DR site and we are not able to change the owner manually as DB is read only.

Is there any way to change the DB owner name at DR site without stopping the log shipping ?Yes,As shown in this query you can get owner name.

SELECT SUSER_SNAME(owner_sid) as Owner,* FROM sys.databases

Read the rest of this entry »