So ... Bob's left the company to move back east, and you're the new lead database developer on the database. Or, the third-party company to which the maintenance has been outsourced is no longer working on it, so it's yours now. One way or another, you need to take over a database system that you had no part in developing. It's not in good shape, and there's not many resources for you to tap.
What do you do?
I've been faced with this situation a few times now, and have developed a list of some of the things that have helped me the most, both in getting productive, and in bringing the database system up to par.
Backups
Make sure that backups are happening. I'm assuming here that you're the database developer, and not the database administrator. However, just as minimum check, make sure that backups are occurring regularly. Ideally you should successfully restore the backup somewhere else.
Research
Look at the database. Go through and get an idea of the table structure, what the largest tables are by size, what the most commonly used stored procedures are, if there are jobs, and what documentation there is. Read through some the stored procedures. You may find it useful to create a quick and dirty database diagram if there isn't one, using the built in diagramming tool in SQL Server. This can also be a good visual aid when you talk to other people.
Talk to the former developers
This may not be an option, but try hard to have a least a few friendly interviews with the former developers. This is not the time to make comments like, "I can't believe you guys did [insert bad development practice here]". You don't know the history- maybe it was that way when they got the system. You'll want to get as much information as they can give you on current issues, items on this list, etc. Keep things friendly - and maybe try to get their cell number in case of questions. A good relationship with former developers can go a long way.
A bug database
Is there a bug database - somewhere that bugs (and sometimes enhancement ideas) are tracked for this system? This is certainly one of the things that you want to set up, if it's not there currently. I've always been lucky enough to work at companies where bug tracking was taken seriously, and there were systems already in place that I could just plug into. If there's no bug database, time to do some research. I wouldn't suggest reinventing the wheel here, since there's a lot of good systems out there -- just use what's available.
Source code control
Is the code in some kind of source code control system, such as VSS or Perforce? If it is -- is everything up to date? I'm going to hazard a guess that it's either not in source code control, or it hasn't been kept up to date. That's been a big task for me when starting work on inherited systems. There's a number of tools with which to tackle this. In the past I've used a custom written Perl tool that used SQL DMO, but I won't go into detail -- that's the topic of another article. If nothing else, you could use the built in tools that SQL Server provides to script out your database objects, and check them in. Once you have everything checked in, try running a database build from the checked in code, and compare it to production. Also -- make sure you have a good system to keep all the code updated!
Talk to the users and/or business owners
Sit down and have some conversations with the users. This is a good opportunity to get to know their problems and concerns, the improvements they would most like to see, and where things are heading in the future. You want to make sure that this database is sticking around, that it's not going to be replaced with a third party product or anything like that. If you're going to put a lot of work into improving the system, you need to know that your efforts are going to pay off for the business. Also-you'll probably be spending lots of time on issues that are important to a well-run database system (a bug database, source code control, etc), but that won't give them any new features. Make sure they understand this.
Establish credibility with the users by fixing a few things or making some enhancements
Even though you'll probably be needing to spend a lot of time on tasks like setting up source code control, bug tracking, etc, you don't want to do this exclusively. From talks with users, hopefully you've identified enhancements or bug fixes that you could get out quickly. Do what you can here. This is a great way to establish credibility with them. Let them know, too, that once you have the systems in place, bug fixes and enhancements will be much easier to roll out.
Create a development environment
If you don't have a development environment, but code still needs to be written, where are the developers going to write and test their code? I hate to tell you, but if they have access, they'll write and test in the production environment. So you may have stored procedures called CampaignEmailExport_TEST hanging around (and never getting deleted). Or -- oops -- you may accidentally overwrite the production version with your new version, and then it runs and causes hundreds of thousands of emails to be sent where they weren't supposed to. Not that I've ever heard of this happening. This kind of problem can go a long way towards convincing users that time and money needs to be spent on working on setting up a good foundation.
For the development environment-you may be able to just get a backup from production, and set it up on another server. If it's too large, you might need to be creative. Whatever you do, don't develop or test in the production environment.
Drop and/or archive obsolete objects
In a system that hasn't been maintained very well, it's likely that there are a lot of database objects out there that aren't being used. They may have suffixes like 'temp' or 'bak' on them. It can be hard to identify all of these, and you may be tempted to just leave them. However, they can cause a number of problems:
- They make it difficult to figure out what the actual working code base is. If you have a lot of duplicate, backup, "working" or "temp" objects, you don't know what your code base is like, and how complex it is.
- Supposed you'd like to drop a tables because it's huge, and looks like it hasn't been updated in a long time, but it turns out that they're being used by stored procedure X. If it turns out that stored procedure X is never used, but you're keeping it around in the database anyway, then you've just lost this opportunity to enhance your code because of an obsolete stored procedure. This kind of issue, multiplied by all the obsolete objects that are in the database, can cause development to be very slow, or even grind to a halt.
As a lot of readers have mentioned, the safest way of doing this is not to delete right away, but rather:
- Rename by prefixing with something like 'zz',
- Archive the objects to be deleted (script and extract data)
- After a while, delete the object in the database.
Finally...
There's potentially months and months of work if you start from scratch on all of the above. It'll require good judgment on what to prioritize, where to start, and how much time to spend on all the tasks that need doing. And perhaps you're not in a position to set all the priorities. But it can be worthwhile and fun to streamline and tune-up a database that just needs a little work to become a well-oiled machine, requiring much less development time.
Thanks for reading! I welcome feedback in the form of comments, and may post an update to this article with the best suggestions and comments.
