I’ve decided to put together this blog post as a beginner’s guide to dealing with an inherited SQL server environment.
You should read this if you’re going to be put in charge of the SQL servers in your company, for example if you’ve just been hired as the new DBA or if you’re a consultant or maybe a Windows admin tasked with the job of being a DBA as well.
If you’ve inherited a mess, where do you start? What do you do when the only guy who knows the infrastructure leaves?
When you inherit a SQL Server environment, your duty is to make sure that one bad config on that one key component doesn’t bankrupt the entire company you work for.
When you inherit a nightmare environment here’s a few things you must do ASAP:
1. Write down as much info as you can find
Try to find out as much information as you can and start taking notes. Consult your colleagues who have been working in the DBA department. Make yourself familiar with the environment and infrastructure as soon as possible. Your observations and notes should be helpful when combined with the documentation.
No matter how tempted you might be, don’t make any spur-of-the-moment decisions and start changing things.
2. Find the Documentation
This is usually the step most people get stuck on when investigating the current infrastructure.
Ask if the previous DBA had documented the SQL servers you’re now supposed to administer.
Let me be honest here: You’ll be disappointed by what you find. Or the lack thereof.
What happens is that since most corporate environments are sloppy you usually end up inheriting some nightmare from someone with little or no work ethic, so most likely you won’t find any documentation.
Gather any relevant information and documentation you can dig up.
3. Document the Entire Environment
Whether you’ve found the SQL server documentation or not, it’s always a good idea to document the entire environment you’re administering before you start making any changes. Document all the servers, SQL instances, IPs, databases, permissions, and everything else you can think of.
If anything happens, you’re responsible for those servers. Remember that.
Understand the environment and its needs; you have to get up to speed on what’s going on and what’s what. If you can identify he complete SQL server inventory, you’ll know what you’re dealing with.
If you don’t know EXACTLY what you’re doing, you could make an even greater mess.
I can recommend a great tool for you, SQLDocKit.
It can document all your SQL server instances running in your environment and create an SQL server inventory. Additionally, you can print out the documentation and show the management how unimaginable the company’s current SQL server environment is.
The documentation you create with SQLDocKit is your paper trail when and if something goes wrong.
4. Check the backups
Check whether the previous DBA made any backups, and how and where the data is backed up. There have been cases where there’s not a single mention of backups, in which case you have a problem.
Also, you want to check the recovery model settings.
What about backup policies?
5. Check free space and autogrowth policies
You need to review your current disk space usage, and how much of the allocated size is related to your databases. Check the size of all SQL server data (primary and secondary) and log files.
Also, check the autogrowth settings of each database. It’s a best practice for autogrowth to be enabled for safety reasons, but for now don’t make any changes if you find your environment is different. Later, when you see how your SQL servers are performing and how the data is growing, you can set the autogrowth settings to fit your needs best.
6. Make a detailed action plan
Once you have the newly created documentation, you can decide what to do next. Write down everything that needs to be changed. You’re going to need this when you sit down with management to discuss your concerns.
The most important advice I can give you is to have a disaster recovery and backup plan carefully defined.
Then, whatever you do, DO NOT test your contingency plans when there’s an outage.
7. Document BEFORE and AFTER
Needless to say, it’s a smart thing to have the before settings for any change in your environment.
Also, remember that using the default SQL server settings isn’t a best practice. If you want to make sure your environment is secure and runs at its optimal performance, you will surely have to tweak a thing or two.
The tool I mentioned, SQLDocKit, will allow you to compare the before and after settings of your environment, but also it can do an automatic comparison. For example, if any changes are made to your servers every week you will be able to see them. Check out the SQLDocKit Best Practices Library to match your SQL Server settings according to the latest community best practices.
BTW, ping me for an SQLDocKit license that is completely free for a month, so you can try it in your environment and create the documentation you need. Click on the top menu to contact me. I’d be happy to help if you’re having trouble administering a server environment.
Feel free to share your nightmare experiences when inheriting an SQL server environment in the comments below.