Sep 21, 2009

Red Gate SQL Backup 5.4 vs SQL Server 2008 Backup


RedGate 5.4 Backup Vs SQL 2008 Backup Vs SQL 2008 Backup with Encryption

RedGate 5.4 Encrypted Compressed Backup

How it works?

1. Backup the database,
2. Compress the Database,
3. Encrypt the Database with a Password,
4. Password needs to be supplied while restoring/backing up the database, or password needs to be place in script in job.

Database size for testing

7 GB

Backup Time

                134 Secs

Backup Size

                715 MB

Restore Time

                247 Seconds.

Encryption

                Yes, 256 Bit.

Compression Level

                3 - Full (Available options, 1, 2, 3)

Advantages

1. Encrypts the Backup file, not the database.
2. Flexible Compression levels can be used.
3. Compression Ratio is large than SQL Compression.
4. Backup can be split into multiple files.

Limitations

                1. Requires to buy another License.
2. Password is displayed on backup/restore script, anyone can see the password, who has access to view the jobs.

Tested Script

DECLARE @exitcode int
DECLARE @sqlerrorcode int
exec master..sqlbackup N'-SQL "BACKUP DATABASES [myDB]  TO DISK = ''C:\SQL Data\backup\'' WITH NAME = '''', DESCRIPTION = '''', ERASEFILES = 5, COMPRESSION = 1, THREADS = 1,MAILTO = ''DBATeam@Domain.com''"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
  RAISERROR ('SQL Backup job failed with exitcode: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode
END

SQL 2008 Ent Full Backup Compressed

How it works?

1. Backup the Database,
2. Compress the Database,
3. Password Protection is available, however without encryption. Open URL below for more information - http://www.mssqltips.com/tip.asp?tip=1108

Database size for testing

7 GB

Backup Time

120 Seconds

Backup Size

865 MB

Restore Time

246 Seconds

Encryption

No.

Compression Level

Full, Only Option

Advantages

1. No Need to purchase extra License, this is part of SQL 2008 License.

2. Password protection is available to backups.

3. Supported by Microsoft as part of Microsoft Product.

4. Compresses the Database.

Limitations

1. Does not support Encryption.

Tested Script

BACKUP DATABASE MyDB TO DISK= 'C:\SQL Data\backup\myDB.bak'
WITH COMPRESSION
GO

RESTORE DATABASE  MyDB FROM DISK = 'C:\SQL Data\backup\myDB.bak'
WITH RECOVERY,
MOVe 'DATA1' TO 'C:\SQL Data\Test\DATA1.mdf',
Move 'LOG1' TO 'C:\SQL Data\Test\LOG1.ldf',
Move 'INDEX1' To 'C:\SQL Data\Test\INDEX1.mdf'
GO

SQL 2008 Ent Full Backup Encrypted Compressed

How it works?

1. SQL 2008 supports Encryption at database level and encryption has to be enabled first time using the following steps.

     a. A database certificate has

          to be created followed by a Master Key.

     b. Using certificate, Database Encryption Key

          can be created to make database encrypted.

     c. Encryption can be enabled on database at

         this point (CPU Intensive task).

2. Backup the Encrypted Database.

3. Compresses the database.

Database size for testing

7 GB

Backup Time

218 Seconds

Backup Size

                4.73 GB

Restore Time

                337 Seconds

Encryption

                Yes, 256 bit.

Compression Level

                Full, Only Option

Advantages

1. No need to display the password on restore command or backup command. SQL Server uses the inbuilt database certificate for security.

2. No Need to purchase extra License, this is part of SQL 2008 License.

Limitations

1. CPU Intensive Task.

2. Backup Compression ratio least of other two option.

3. In case certificate is lost, we can lose the Data.

Tested Script


--1. CREATE MASTER ENCRYPTION KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*****'


--2. CREATE DATABASE CERTIFICATE
CREATE CERTIFICATE dbCERT WITH SUBJECT = 'Database Certificate TDE'

--3. BACKUP CERTIFICATE
BACKUP CERTIFICATE dbCERT TO FILE = 'C:\SQL Data\DB CERTIFICATE\db_bkupcert_1.cert'
WITH PRIVATE KEY(
 FILE = 'C:\SQL Data\DB CERTIFICATE\db_bkupcert_1.cert', ENCRYPTION BY PASSWORD = '*****');

--4. CREATE DEK -- DATABASE ENCRYPTION KEY
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE dbCERT


--5. ENABLE ENCRYPTION ON SELECTED DATABASE
ALTER DATABASE myDB SET ENCRYPTION ON

--6. ABOVE COMMAND WILL START A BACKUP PROCESS OF ENCRYPTING DATABASE, MONITOR THE STATUS OF ENCRYPTION PROCESS USING
--FOLLOWING COMMAND.

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

No comments:

Post a Comment

Optimizing Indexes with Execution Plans

Contact Me

Name

Email *

Message *