August 2011 - SQL Freelancer Blog

Backup Database on Primary Server:

Restore Database on Secondary Server: (RESTORE WITH STANDBY)

(Picture of Object Explorer once restore is complete)

Create a shared folder on the primary server (SQL Server Agent service account must have read/write permissions):

Create a shared folder on the secondary server (SQL Server Agent service account must have read/write permissions):

Enable Log Shipping at the Publisher:

Click “Enable this as a primary database in a log shipping configuration”, then click Backup Settings:

Enter Network Share on Primary Server
Enter Local Folder path on Primary Server
Enter the number of Minutes, Hours, or Days to keep the deleted files and to Alert if no backup occurs
Name the SQL Agent Job and determine a schedule to backup the log files

Click OK

Click Add to add a secondary server:

Click Connect to connect to the secondary server:

Select No, since we initialized the database in the beginning by restoring in Standby Mode, otherwise you can choose one of the other options. Hit OK.

Go to next tab, Copy Files.

Enter Local Folder Path on Secondary server
Enter the number of Minutes, Hours, or Days to delete copied files
Name the SQL Agent Job and determine a schedule to copy the log files

Go to next tab, Restore Transaction Log.

Since we put the database in Standby/Read Only mode select Standby Mode
If you would like to delay restoring the transaction log you can enter a value in the “Delay restoring backups at least” otherwise leave at 0 minutes.
Enter the number of Minutes, Hours, or Days to Alert if no restore occurs
Name the SQL Agent Job and determine a schedule to restore the log files

Hit OK

**OPTIONAL** To create a monitor server click “Use monitor server instance” and then click Settings

Click Connect to connect to a monitor server instance
Select the login method you would like to use to connect to the monitor server.
Enter the number of Minutes, Hours, or Days to delete log file data
Name the SQL Agent Job and determine a schedule to for the alert task

Hit OK twice.

In this post I’ll show you how to setup Transparent Data Encryption (TDE). TDE is new in SQL Server 2008 and serves as an encryption method that uses a database encryption key (DEK) to protect SQL Server’s data and log files. The DEK is a key secured by a certificate stored in the master database.

To setup TDE we’ll need to run a few scripts: (My test database is named TDE)

The following script will create the master key with a specified password ElephantRhin0:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ElephantRhin0';
GO

Next, we’ll create a certificate named TDECert that will be protected by the master key:

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

After creating the certificate we’ll backup the certificate to a specified source:

USE master;
GO
BACKUP CERTIFICATE TDECert TO FILE = 'C:\TDECert_backup' WITH 
PRIVATE KEY ( FILE = 'C:\TDECert_key' ,ENCRYPTION BY PASSWORD = 'ElephantRhin0' )
GO

Once the certificate is backed up we will create the DEK using the AES algorithm and protect it by the certificate:

USE TDE;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO

The final step is to set our database to use encryption:

ALTER DATABASE TDE
SET ENCRYPTION ON;
GO

If everything completed successfully then we have officially encrypted our database with TDE, but don’t take my word for it, run the following query to confirm:

SELECT name, is_encrypted
FROM sys.databases
WHERE name = 'TDE'

Click here to view the rest of this post.

Month: August 2011

SQL Server Log Shipping

Securing and protecting SQL Server data, log and backup files with TDE