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 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
**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:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ElephantRhin0';
Next, we’ll create a certificate named TDECert that will be protected by the master key:
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
After creating the certificate we’ll backup the certificate to a specified source:
BACKUP CERTIFICATE TDECert TO FILE = 'C:\TDECert_backup' WITH
PRIVATE KEY ( FILE = 'C:\TDECert_key' ,ENCRYPTION BY PASSWORD = 'ElephantRhin0' )
Once the certificate is backed up we will create the DEK using the AES algorithm and protect it by the certificate:
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;
The final step is to set our database to use encryption:
ALTER DATABASE TDE
SET ENCRYPTION ON;
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
WHERE name = 'TDE'
Click here to view the rest of this post.