Configuring a Database with Transparent Database Encryption & Availability Groups

From what I’ve seen, Transparent Data Encryption (TDE) for SQL Server isn’t as heavily used a feature as many of the HA/DR options, but if your company has a requirement for encryption at rest, it’s a great feature to be able to access out of the box. On the MySQL side, I had done extensive testing with Gazzang, this was managed at the OS level, versus TDE at the database level.

Some things to note with TDE in SQL Server:

  • Requires SQL Server Enterprise Edition
  • There is some overhead, approx 3-5% (I need to run performance tests to validate this, but this is what I’ve read)
  • Any server where the database will be restored (either as a replica, or a standalone copy), will require the certificate restore & the Enterprise version of SQL Server
  • If the certificate for the database is lost, the database is not recoverable
  • Backup compression is not supported with TDE databases
  • Your tempdb database will also automatically be encrypted if any databases on an instance are encrypted.
  • You cannot add a TDE database to an availability group via the GUI, it must be added via T-SQL

Continue reading

SQL Server Replication – Recovering from a Network Outage

In continuing my on-going issues with SQL Server replication the past couple of weeks, we had a data center outage on Monday, which resulted in a forced failover to our DR data center.  Our MySQL database applications successfully failed over and required no followup.  We did run into 1 peer-to-peer row conflict, which I was able to address, similar to the issue late last week I documented here.

Things looked to be  running along fine, and I could see that my DR database was up to date with my production database.  My monitoring system was showing some undistributed transactions, but nothing that seemed too outrageous.  But I was continuing to get a few Network error notifications, which lead me to look at the SQL Server Replication Monitor.  Everything was green, but my undistributed transactions were showing well over an hour.

Continue reading

Becoming a “DevOps” DBA

I went to my first SQL Saturday this past weekend up in Maine, the first session I attended was on Best Practices for database deployments, it got me thinking about transitioning from more of a Production Support DBA in an IT organization to the DevOps DBA in an Engineering role I’ve take up over the past few years, though I don’t think the differences are quite as big as some may think.  For one, the production support aspect never goes away, though a lot of that may be from working with small to mid-size companies.  I’m still on call, things still need to be up & running.

The biggest change for me seems more around the size of the changes coming in, being in an Agile environment drives much of this, but also being part of an engineering environment versus implementing off the shelf applications from outside vendors.  There is still a review on what changes are coming in, but in the IT organization, these were managed as projects, and some applications may go years between vendor updates.  Depending on the application there can be a massive amount of schema/data changes, often times this was a negotiated downtime event, coordinating the app & database updates.  Depends on the application.

In this agile setup, many of the database changes are fairly small & straightforward to review, sometimes a simple as adding a single column, index etc.  Since it’s an internal development, we can mandate that all changes are backwards compatible, so that database changes can be deployed independently of the application change, giving us time to “bake in” the change, make sure there are no negative impacts.  These database changes happen up to a few times a week, depending on the component being deployed.  They are uptime changes for the most part, often during business hours – +1 for minimizing weekend work.

Database tuning is a necessity on both sides, though larger vendors may have configuration setting recommendations required.  When SQL tuning, often the only option I had was to add indexes (outside of additional tuning on the instance configurations).  With an internal development team, there is some more flexibility (To a degree – when devs use ORMs like Hibernate or Active Record, adding indexes is still more straightforward for SQL tuning – but SQL can be rewritten in a better manner than created by the tools, but you lose some of the benefit of using the tool for managing your code in the first place).  But there is more of an opportunity to address sql rewrites for performance tuning in house, versus working with an external vendor to address their code.