Installing CTE on Microsoft SQL AlwaysOn

This section describes how to implement CTE with Microsoft SQL AlwaysOn in a variety of configurations for primary and secondary replica servers, and assumes that you have a basic understanding of Microsoft SQL database.

You may want to keep the primary server decrypted to serve all users, and use the secondary database for running reports or backups.

• If the database is encrypted, then the Volume Shadow copy-related backups will snapshot and backup encrypted protected data.

• Administrators with the apply_key permission can run a query and pull down reports from the secondary database server without affecting the performance of the primary database server.

• The secondary server could be in a remote Data Recovery location. You may want to secure it with encryption.

• LDT is supported with SQL AlwaysOn. See Using LDT with SQL AlwaysOn for more information.

Methods for Initial Encryption

There are multiple methods for performing the initial encryption of the databases. Decide on which of the following methods best fits your environment. For more information on transforming data, see the CTE-Live Data Transformation with Data Security Manager.

• Data Transformation – Encrypt data in place

• Backup and Restore to a GuardPoint

• Copy and paste the data into a GuardPoint

Configuration 1

• Databases on primary server and secondary replica servers require encryption

• Database name and location of secondary replica server are the same as the primary server

To perform the procedure:

1. Perform a full backup of the primary database.

2. Change the primary database to offline mode.

3. Confirm the creation of a data transformation and/or standard policy.

4. Guard the folder containing the primary database files with that policy:

   1. If using 'Encrypt data in place' as the selected method of encryption, execute the data transformation and then apply the standard policy.

   2. If using the 'Copy/Restore ' method of encryption, apply the standard policy on an empty folder/device.

5. On the secondary server, create a new folder to store the replicated database.

   The folder name and the path must be the same as the primary server.

6. Guard the folder with the standard policy.

7. Perform step 4 above for additional secondary server(s).

8. Put the primary database back into online mode.

9. Setup SQL AlwaysOn High Availability group to perform FULL Data Synchronization.

   This copies the primary database and replicates it to secondary replica servers.

10. Verify that the databases in the secondary server are in “Synchronized” mode.

Configuration 2

• Database on the primary server does not require encryption, but the secondary replica database requires it

• Database names and locations for the secondary replica servers are the same as the primary server

To perform the procedure:

1. Perform a full backup of the primary database.

2. Confirm the creation of a data transformation and/or standard policy.

3. On the secondary server, create a new folder to store the replicated database.

   The folder name and the path must be the same as the primary server.

4. Guard the folder with the standard policy.

5. Perform step 3 & 4 above for additional secondary server(s).

6. Setup SQL AlwaysOn High Availability group to perform FULL Data Synchronization.

   This copies the primary database and replicates it to secondary replica servers.

7. Verify that the databases in the secondary server are in “Synchronized” mode.

Configuration 3

• Databases on the primary and secondary replica servers require encryption

• Database name is the same, but the location of the secondary replica server is in a different location from that of the primary server

To perform the procedure:

1. Perform a full backup of the primary database.

2. Change the primary database to offline mode.

3. Confirm the creation of a data transformation and/or standard policy.

4. Guard the folder containing the primary database files with that policy:

   1. If using 'Encrypt data in place' as the selected method of encryption, execute the data transformation and then apply the standard policy.

   2. If using the 'Copy/Restore ' method of encryption, apply the standard policy on an empty folder/device.

5. On the secondary server, create a new folder to store the replicated database.

   The folder name and the path must be the same as the primary server.

6. Guard the folder with the encryption policy.

7. From secondary server, perform the restore to the primary database.

   1. Select the options Restore with norecovery and Relocate all files to folder.

   2. Specify the path of the new folder from step 5.

8. Repeat steps 4 & 5 above for any additional secondary server(s).

9. Setup SQL AlwaysOn High Availability group to perform JOIN ONLY Data Synchronization.

   This joins the secondary database to the SQL Always High Availability Group. It also establishes replication of new data and logs from the primary to the secondary replicated server.

10. Verify that the databases in the secondary server are in Synchronized mode.

Configuration 4

• Database on the primary server does not require encryption, but the secondary replica database requires encryption

• Database name is the same, but the location on the secondary replica server is in a different location than that of the primary server

To perform the procedure:

1. Perform a full backup of the primary database.

2. Confirm the creation of a data transformation and/or standard policy.

3. On the secondary server, create new folder to store the replicated database.

4. Guard the folder with the standard policy.

5. From secondary server, perform restore the primary database:

   1. Select the options Restore with norecovery and Relocate all files to folder.

   2. Specify the path of the new folder from step.

6. Setup SQL AlwaysOn High Availability group to perform JOIN ONLY Data Synchronization.

   Joins the secondary database to the SQL Always HA Group. It also establishes replication of new data and logs from the primary to the secondary replicated server.

7. Verify that the databases in the secondary server are in Synchronized mode.

Configuration 5

Following is an alternative method for protecting data in a MS SQL Server AlwaysON environment.

To perform the procedure:

1. Shut down SQL services completely, on the secondary node.

   It is important to shut down the secondary node first, in order to keep the assignments the same.

2. Shut down SQL services completely on the primary node.

3. Create GuardPoints, using Data Transformation policies, on the directories containing the databases to be encrypted in the primary node.

   Perform encrypt-in-place encryption on each directory.

4. Create GuardPoints, using Data Transformation policies, on the directories containing the databases to be encrypted in the secondary node.

   Perform encrypt-in-place encryption on each directory.

5. Delete the GuardPoints, using Data Transformation policies, from the primary node.

6. Create GuardPoints, using operational policies, on the four directories in the primary node.

7. Delete GuardPoints, using Data Transformation policies, from the secondary node.

8. Create GuardPoints, using operational policies, on the four directories in the secondary node.

9. Activate SQL services on the primary node.

10. Activate SQL services on the secondary node.