TDE IN AN ALWAYSON AVAILABILITY GROUP

ADDING A TDE ENCRYPTED DB TO A SQL SERVER ALWAYSON AVAILABILITY GROUP


I’ve recommended against the use of Transparent Data Encryption (TDE) database container encryption (SQL Server & Oracle) in the past. Reference my blog or blog. TDE flat out provides no protection while the database is running, and we all strive for 24×365 uptime.  Given the option, any bad guy is going to steal your data through the front door rather than shut down your database and raise the ‘something is wrong’ alert. Of course, follow normal procedures for encrypting database backups that will be moved off-site, and destroying or wiping abandoned storage.

Adding a database to an existing availability group is another one of the hassles that TDE causes for the very little (or no) benefit it provides. Specifically with SQL Server 2014 and prior, one can not use the standard SQL Server Management Studio (SSMS) wizard to add a TDE encrypted database to an AlwaysOn Availability Group (AAG).

In my situation, I was lucky. We choose to encrypt the second database with the same key. The first database was in the AAG, and the AAG was functioning perfectly. The problem is the wizard had the second database greyed out… as if it was not a candidate for AAGs.

So, here’s what I did to add the TDE encrypted database. (Remember to test your interpretation of these instructions on a non-production AAG.)

  1. Insure that your first database is TDE encrypted, the AlwaysOn Availability Group is functioning (synchronizing) properly. This means that TDE keys have been placed on all nodes (ie replica/instances) of the AAG. 
  2. Insure that the current ‘primary’ node has been set to the same node as the node with the second database. (There is a ‘no data loss’ method of manually failing an AAG.)
  3. Insure the second database is TDE encrypted with the same key as the first database. As the first database is functioning in the AAG, the keys that exist on all the AAG nodes are the same keys that are needed for the second database.
  4. On primary node of the AAG…
    1. Insure the second database is using FULL recovery.
    2. Use the backup wizard to take a full backup of the second database
    3. Use the backup wizard to take a tlog backup of the second database
    4. Add the second database to the AAG with the following TSQL command… ALTER AVAILABILITY GROUP aag_name ADD DATABASE database_name;
  5. On each of the secondary nodes of the AAG…
    1. Use the restore wizard to restore the full backup taken of the second database. INSURE ‘NORECOVERY’ is specified.
    2. Use the restore tlog wizard to restore the tlog backup taken of the second database. INSURE ‘NORECOVERY’ is specified.
    3. Add/Join the second database to the AAG with the following TSQL command… ALTER DATABASE database_name SET HADR AVAILABILITY GROUP = aag_name; 
  6. Insure that the secondary nodes are synchronizing (after ‘refreshing’ in SSMS). Test a manual failover.

It’s far simpler to not have to deal with TDE in a AAG… It’s especially frustrating given that TDE offers no real security. (yea, the ‘dead horse’ thing!).

Keywords: Always On Availability Group, AlwaysOn Availability Group

Troy Frericks.
blog 01-Jun-2016
=
Copyright 2015-2016 by Troy Frericks, http://dba.frericks.us/.
#

SAN vs NAS

SAN VS NAS

I recently built and implemented a VTL (Virtual Tape Library). Database backup files were placed on a disk drive. Those files were continuously copied to tape, retained on-site until the next weekly tape pickup, and they were then moved off-site. Another team dealt with the tape copies, off-site moves, etc. That left me to deal with the disk portion, and that’s all my software addressed. It would make full backups, incremental/differential backups, and transaction log backups to disk based on a schedule. Retention of each type of backup is configurable for each server. Restores are always from disk. If the disk backups have aged past the retention period, or were otherwise corrupted, tapes would need to be recalled on-site and restored to the VTL directory… in essence staging those files for the database restore. It’s a very reliable and inexpensive backup method… but what does all that have to do with SAN VS NAS?

I was recently at the Percona Live 2015 conference talking about the process. The subject turned toward the type of disk storage used. Looking at the pros & cons of each type of shared storage was pretty easy for this specific applications. As we were discussing pros and cons of each, one guy asked what the difference was between SAN and SAS. Everyone had their own opinion that went above and beyond the basic difference. Things like SAN is more expensive than NAS. NAS is slower. Both of those are not necessarily true. So, what is the difference between SAN and NAS?

SAN stands for Storage Area Network. NAS stands for Network Attached Storage. That really does not help much; I added it for completeness. The core difference between SAN and NAS is SAN uses block level protocols to access the data, whereas NAS uses file level protocols to access the data. One can think of it in a MS Windows(tm) desktop environment as Shares are similar to NAS and Drives are similar to SAN (although the physical storage is external to the desktop). Also note, the storage can be a hybrid. For example, the open source product: openfiler This device will function with many file and/or block level protocols. Try to deduce the difference between SAN and NAS protocols from their Products page (tab) 🙂 SAN seems to be talked about explicitly, but NAS information is mostly implied.

So, which makes more sense for the VTL: SAN or NAS? I vote for NAS as the only storage operations are file-save/write, file-sequential-read, and file-delete. If we were talking a storage container for a database engine, I’d suggest that SAN would be more appropriate as the file is read & written at the block level.

Troy Frericks.
blog 12-May-2016
=
Copyright 2015-2016 by Troy Frericks, http://dba.frericks.us/.
#