When you start a job at a new company, you are often handed a collection of databases on one or more instances of SQL Server. Maybe you are just taking over the administration of a set of databases at your current employer. Either way you need to take over a set of databases on a system that you had no part in designing, implementing or configuring. It may seem like a gift or a curse, depending on the work you put into taking over that new responsibility.
What do you do when you inherit a database?
Create a List
Create a list of the systems, including the server names, locations (physical and network), versions of operating systems, versions of SQL Server, applied patches, system owners and users, etc. Think of this task like an investigative reporter, trying to collect all the facts and writing everything down.
First thing you want to do is protect the data as it exists today. Inventory the existing backup jobs and locations. Make sure that backups are happening and document the current schedule. Verify that backups are scheduled at the correct time and are running as expected. Talk to the person who ships the backup tapes or manages the network location that stores the backup files to verify the jobs are running smoothly from their perspective. Make sure you understand the type and schedule of the backup jobs and you should attempt at least one restore job to verify the process. Don’t change anything until you know for sure the possible impact of that change and document everything.
Start with one database on one server and look at that database. Start going through the details of the database and get an idea of the table structure, what the largest tables are by size, what are the most commonly used stored procedures, if there are scheduled jobs, and what existing documentation exists. Read through some the stored procedures and functions to see the process and flow of the custom objects.. 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. Work your way through all the databases on all the servers until you have documented everything. Depending on the size of what you have inherited, this could take months or even years, but start soon and make updating your documentation a requirement.
If this is possible, interview the developers to see what they know about the databases. They will often have insight on the purpose of specific database objects and can often explain what specific design choices were made and why. They should have documentation and testing evidence that will help you understand how things are supposed to work and even how all the parts and pieces fit together. You’ll want to get as much information as they can give you on current issues, items on their database development list, etc. Keep things friendly – and maybe try to get their cell number in case of questions or emergencies. A good relationship with developers can go a long way.
Is there a somewhere that bugs (and sometimes enhancement ideas) are tracked for this system? A bug database (or even a bug spreadsheet) is one of the things that you want to create, if it doesn’t already exist. If there’s no bug database, do some research and either create a new process and procedure around tracking issues, or select a solution from the internet. Don’t start reinventing the wheel, you can just use something that is available.
A bug database will be a place to track everything that is wrong, and allow you to pull a constant list of things to work on as you get more familiar with the inherited databases. If someone comes to you with an issue that you can’t tackle immediately, store it in the bug database for future reference.
Source Code Control
Is the database code in some kind of source code control system, such as VSS or other commercial product? Has the system been recently updated with the latest changes? Even if a system isn’t in place or can’t be implemented, you should probably run an export script for the entire database each week as a minimum precaution. You don’t want to make changes (or have other people making changes) that break the system and you can’t get back to the original version of the object.
You can also look at implementing formal change control processes and policies that manually drive change requirements, including approval signatures and testing evidence to better protect your databases. This is were those positive relationships with the developers will pay off.
Schedule a conversation with the database users and system owners. This is a good opportunity to get to know their problems and concerns, the improvements they would most like to see, and where they see the system heading in the future. You want to make sure that this database is staying in production, verify the systems that use the database are stable, and get a feel for production support requirements and expectations. Make sure the users understand the status of the system, why you need their help, and make sure they grasp the complexity of the database. Don’t assume they understand what you have been asked to do and how long it might take before you will feel comfortable in making any changes.
As maintenance and support progress, consider having annual meeting to discuss upcoming events and what maintenance events or performance issues to keep owners knowledgeable about the system and provide them an opportunity to ask questions or offer advice for improvements.
Schedule a Change or Enhancement
From your conversation with the users and system owners you might have identified a change, enhancement, or bug fix that you could get done quickly. Doing what you can to quickly resolve an outstanding issue will be a great way to establish credibility and demonstrate your value. Users should see you as a solution to their issues, not a roadblock to performance improvements or system enhancement.
Always keep the owners and users of they systems informed about changes, downtime, or maintenance so they know you are working on continuous improvements and how any changes might impact their use of the system.
One of the compliance requirements for most environments is the requirement developers don’t have access to production environments. The idea is that all development and testing happens on a serrate development environment, and production data only exists on the production systems. 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? You may have stored procedures called MyEmailExport_TEST hanging around and never getting deleted in your production environment. To create the development environment you may be able to just get a backup from production and restore the database on another server. If the database is too large, you might need to find a creative way to perform this needed task. Whatever you do, don’t develop or test in the production environment.
Drop Obsolete Objects
In a system that hasn’t been maintained very well, it’s likely that there are a lot of database objects that aren’t being used. They may have suffixes like “_delete”, “_temp”, “_test”, or “_bak” to identify them, even if they are used in production. It can be hard to identify all of these objects and you may be tempted to just leave them. However, they can cause a number of problems. If you have a lot of duplicate, backup, “working” or “temp” objects, you don’t know what your code base is or how complex it really is right now. The safest way of doing this is not to delete the suspect objects right away, but rather label them to change the name and “break” anything that might call them, then drop them in a few weeks after you know it is safe. Make sure you have a backup (that you keep for the duration of the renaming and deletion cycles) before the name change and another backup (that you keep for a few weeks) before you delete the objects.
Work with the development team to resolve this issue and to also make sure it stops happening.
While you have just been handled the potential for months and months of work, this new responsibility will require good judgment on what to prioritize, how much time to spend on all the tasks that need doing, and patience to handle the new tasks. Maybe you’re not in a position to set all the priorities, but you should have the confidence to make your suggestions and recommendations known to your supervisor. Communication is the key to managing your new responsibility.