DBA Skills For Developers

Introduction

In many smaller companies or departments, there is no dedicated DBA. In these cases, it falls on *someone* in the office to come to grips with the blackbox that is the database server, keep it running, and dive in when the things go wrong. The objective of this article is to give non data-focused developers a quick roundup of useful information and knowledge that will enable them to both solve some routine issues that come up and also help prepare for some in advance. This is not intended to be a very in-depth article, rather it's a quick read over coffee break that should give the reader some useful starting points. I would be delighted if any more experienced DBAs out there would like to comment and help expand the list. This article focuses specifically on MS SQL Server but the concepts should be useful for most relational database systems. I have also attached some scripts as a download that I use to gather the information on a system that you may find useful.



Core concerns

A lot of projects start small, and over time, the complexity increases. What was a good decision to start with in the original design of a database or system may now be sub-optimal. Rather than putting solid fixes in place, for the sake of speed of development and 'getting things out the door', it is not uncommon to code around issues without digging-deep to find the problem and solve it. The following few items will give the new developer DBA a head start in getting and keeping on top of things.

The three core concerns of the developer DBA should be System stability, Data Integrity/quality, and Speed. With this in mind, we will look at some of the options available to assist in these areas.

Getting to know the database

One of the key things you need to know about your database is its purpose and architecture. Hopefully, there is someone on the team who can give you a guided tour, if not (and regardless!), there are numerous tools, both, commercial and free, you can use (out of the box, I normally have both Visual Studio and MS SQL management studio in my toolkit at least). To start with, here are some ways to get a quick handle on things:

1. Confirm a good backup and restore system is in place
   clearly, with no database, there is no DBA job to do! ... the most important first step I always take is ensure there is a reliable backup system in place and that a restore of the database has been properly tested.
   
   
2. Identify where the data is / which tables have the most data / how fast it churns
   I normally run a series of scripts that give me information about the state of the database, and I use this to both inform myself on how things are and to indicate any areas that I need to investigate further or enquire about. Finding out which tables have the most data, which tables change data most often, which tables have potential index issues / where indexes take up more space than the actual data, etc.
   
   
3. Find out the relationships between the tables
   Examining the links between data tables can give insight to the design of the database, the importance of certain tables, and dependencies.
   
   
4. Find out how data comes in / goes out (data acquisition/reporting).
   Data can come in in various ways, identify those routes and look for potential points of failure, brittleness and anything that could put an undue strain on the system over time.
   
   
5. Investigate how/why data is transformed/changed from its original state.
   Some systems take data in and simply drop it in place into a series of tables and models, others manipulate the data out of the way. Find out where these transformation areas are and what they do. Frequently they can hold clues down the line when things go wrong. One place that transforms and 'creative happenings' can take place is buried inside triggers. A trigger placed against the Insert of data in one table can result in the delete or update perhaps from another seemingly unrelated table except for some long forgotten business rule. If you are trying to track down what has caused a change, then the SQL-Search tool I mention later in the article will assist. If you are specifically hunting for triggers then check out the solutions here.
   
   
6. Start to document from day one.
   If you have inherited an undocumented or sparsely documented system, then make it your job to document your findings as you go along. It will make life easier not only for you in the long run, but also anyone coming behind you.
   
   
7. Use inbuilt reports.
   MS SQL Management studio comes with a number of ready-to-go system reports that will help you to both get to know what you are dealing with, and help you to monitor changes over time. Access the reports by selecting the database, right-clicking, and running the report you need.
   
   
   

Make it robust

It is critical when working with the data to write robust code. Some things you should be aware of are transactions, error trapping and logs. Once I have a handle on the structure and architecture of a database, my next stop is generally the stored procedures. Stored procedures as you most likely know are bundles of pre-written database scripts that can be called on demand. When writing code for the server in C# or the FrontEnd in JavaScript we are careful to code defensively, have appropriate error handling in place, and log appropriately when things go wrong. The database stored procedure is no different, and as we generally have less interaction with users than say a front-end of a website or the error dialog in a WinForms application, I would argue that we need to be extra careful in our SQL code to ensure we handle things robustly and gracefully. There are three things I look for in a stored procedure:

1. If appropriate, is the script enclosed with a transaction block? ... I generally use transaction blocks where the data process I am doing is an all or nothing situation - that is, it must all work perfectly, or else we need to roll-back (literally) to the state we were in before we ran the script.
   
   This screenshot shows a selection of data, a change, and then a rollback with the original data unaffected.
   
   
   
   More information about transactions here.
   
   
2. Where there is a danger of a failure of the script through data transformation errors, data mis-match or availability etc, then we need to know what happened. In these cases I use a Try/Catch block in a similar manner we would in a procedural language such as C#. The following example forces an error by attempting an insert of a primary key that already exists.
   
   1. BEGIN TRANSACTION; BEGIN TRY -- Generate a constraint violation error. DELETE FROM Production.Product WHERE ProductID = 980; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; -- log the error! IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION; GO
   An example error output (for an insert problem) is as follows,
   
   Severity: 16 LineNum: 2 ErrorMessage: Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF.
   
   More information about try/catch in tsql here. The catch is useful as it can tell you the line number of your script it has a problem with, deliver an error message, and also give you an indication of the level of severity of the problem. More information about tsql error severity codes here.
   
   
3. When things go wrong, we need to record them. You can use the inbuilt MS SQL error logging system (raise error with log), but that requries permissions you may not always have (eg: hosted servers), so mostly I have an error log table I insert into myself.
   
   Finally, you can of course (and I mostly do), combine all of the above to give yourself a better chance of not getting a phonecall at 3am! (yes, I've been there...)
   
   This example from msdn illustrates:
   
   1. BEGIN TRANSACTION; BEGIN TRY -- Generate a constraint violation error. DELETE FROM Production.Product WHERE ProductID = 980; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; -- log the error! IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION; GO
   nb: as with most safety harnesses, there is a cost to using error trapping, transactions etc - in most cases it's fine, but always better to read up on it and know the implications before you implement.