How come you got involved in IT?

I always enjoyed working with computers when I was in school and so it seemed a natural area to study at the university. I enjoyed studying database design so once I completed my BSC in 2005, I started looking for a database developer position.

Whilst working as a junior developer, I realised that the code I was writing took a long time to execute (mainly because of all the cursors I was using). I did some research online and found multiple websites dedicated to SQL Server and opportunities to work as a DBA in particular. I read tons of online articles on websites such as SQLServerCentral.com and the more I read, the more interested I became in that area. It was then that I decided to pursue a career as a DBA.

What are the most common and the most challenging issues you've handled recently?

The most challenging issue I have ever had to perform was a task given to me when I was a relatively inexperienced DBA. A production data warehouse had a different collation to the development and staging versions and I was tasked with bringing the production database in-line with the other environments.

Why not change the collation of the databases in the other environments? Well, as I said, I was inexperienced and looking to prove myself, so off to work I went. The collation of a database can be changed quite simply from the Properties menu. The problem is that the setting only changes the collation for all new objects created. Everything else in the database remains on the old collation.

So after what felt like an age…I came up with the following procedure to change the collation of all the current objects with the database:

1. Script out all database objects
2. Drop all functions
3. Drop all views
4. Drop all foreign keys
5. Drop all primary keys
6. Drop all indexes
7. Drop all statistics
8. Change column collation
9. Change database collation
10. Recreate indexes
11. Recreate primary keys
12. Recreate foreign keys
13. Recreate statistics
14. Recreate views (and associated indexes)

I tested and tested, and tested, so when it came to deployment, it all ran smoothly. However, I’ll always think of this project as one of the most challenging I’ve worked on, not just because of the technical side but because of the human aspect of it as well. It taught me a lot about relationships within an SQL Server database but it also taught me that part of a DBA’s job should be to question why something is being done and to provide alternatives where possible. I should have questioned why the need to change the collation of the production database was felt. It would have been simpler (and a lot less risky) to change the development databases’ collation.

The most common issue that I deal with are blocking/deadlocks occurring within production databases. There is monitoring in place which sends out alerts (via email) meaning I can respond quickly to resolve any issues. An SQL Server agent polls active connections to check for blocking and Service Broker utilising Event Notifications has been implemented to generate deadlock alerts. This allows me to capture the offending query and see what action needs to be taken in order to prevent future incidents (more often than not, either rewriting the query or creating an appropriate index).

However, preventative measures are always better than having to react to issues in real time. I work closely with developers to ensure these issues are kept to a minimum by implementing good database design and by writing efficient SQL queries.

Do you use any external tools for database monitoring? Which ones?

I have worked with Nagios, Zabbix, RedGate, Idera and Confio monitoring tools. All are good systems with their own positives and negatives but I strongly feel that any DBA should be able to implement their own monitoring system. I wrote a short blog post detailing my reasons why.

In your opinion, what server status variables should a DBA log and monitor?

I always create my own database in any SQL instances that I administer. This allows me to setup monitoring for the following:

• Blocking
• Deadlocks
• Auto-Growth
• Failed SQL Server Agent jobs
• Backups
• Percentage transaction log used
• Corruption alerts
• Wait statistics
• Index usage statistics
• Index fragmentation
• Error log auditing
• Disk space
• Memory Usage
• Page life expectancy vs. lazy writes
• IO subsystem performance.

What about disaster recovery and prevention? What is your approach?

Every disaster recovery plan should be tailored to each individual system. It is one of the key aspects of a DBA role within the company to work closely with system owners to define Recovery Point Objectives (how much data can be lost it the event of a failure) and Recovery Time Objectives (how long it should take to bring the system back online).

Once the RPO & RTO have been established, a high availability solution can be built and a disaster recovery strategy implemented. High availability covers SQL Server features such as mirroring, clustering and always on. Disaster recovery strategies start with a backup schedule for the databases but can include features such as log shipping or asynchronous mirroring to a DR site.

I attended an SQL Skills training course last year in which Paul Randal talked about how to approach backup strategies. His advice was to think of backup strategies as restore strategies and that DBAs should consider performing many restores to bring the database online in the event of a system failure. This is one of the best pieces of advice I have been given as a DBA. It is all well and good to back up database transaction logs every 15 minutes, but how many of those will need to be restored in the event of a failure? I really do not want to be restoring hundreds of log backups at 3 o’clock in the morning (because this is generally when failures happen for me, for some reason).