SQL SERVER RULES

SQL SERVER RULES

Wednesday, September 12, 2012

Index Usage Monitoring

Database consists of two types of pages Index pages and Data pages.By running up of some sample queries we can come to conclusion that what data in the table and what type of data is being populated on to the table on other hand it is little bit hard to find out which index is being mostly using for DML operation and which index is being used for Scans and Seeks.

Earlier Version's of sql server don't have an opportunity to gain the performance of Dynamic Management Views.One function and View can give a lot of in depth data about the indexes.

                                        Function:  sys.dm_db_index_operational_stats

                                                   lets see the usage of this function



Dynamic View: sys.dm_db_index_usage_stats

Now let see the usage of this view









Tuesday, September 11, 2012

Backup Using Transparent Data Encryption

Transparent Data encryption is one of the newly introduced feature in sql server 2008. Transferring the data between two networks/machines must be enriched with security limits to get rid of malicious  attacks.It ensures the best possibility of encryption for data and log files.Sql server engine perform the certain operation in order get transparent of data.Certainly encrypts the data before it written into the disk and Decryption the data before reads from the disk.

Usually backups perform on disk or tapes contains sensitive information that has to protect.Configuration of TDE is not as complex as clustering,Mirroring etc, just using the standard backup command after the encryption option is truned on. The backup files with TDE is highly protected,without the access with key is completely failure.

As we all know  that there are various algorithms have already been exposed in cryptography world..In our case AES algorithm is best for to make the backup file encrypt.lets bit concentrate more on encryption and decryption.

Normal definition as follows

Encryption--Plane text to cipher text
Decryption--cipher text to plane text.

basically there are two types of keys symmentric  key and asymmentric key,by using of these keys in order to storage of encrption data is effective.


USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD 'mY_P@$$w0rd'

Then, we create the server-based certificate which will be used to encrypt the database

CREATE CERTIFICATE AdventureWorksCert
WITH SUBJECT 'My DEK Certificate for AdventureWorks database'

We will then set the encryption for the AdventureWorks database by creating a database encryption key and password using the certificate we just created.

USE Adventureworks
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM AES_128
ENCRYPTION BY SERVER CERTIFICATE AdventureWorksCert
GO

The encryption algorithm choice is yours, since a few different options exist.  For guidance on selecting an encryption algorithm on SQL Server, check out this Microsoft TechNet article.  After creating the database encryption key, we'll enable encryption on the database level.

ALTER DATABASE AdventrueWorks 
SET ENCRYPTION ON

Let's backup the encrypted AdventureWorks database this time but using a different filename.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\Adventureworks_encrypted.bak'
WITH INITSTATS 10

Thats all Done!