It is an all too common scenario.
You’ve just started as a DBA at a new organisation. Your first task (after locating important
things as coffee, toilets and vending machines), will be to go through the SQL Estate
familiarising yourself with all the new instances that you’ve inherited.
Hopefully someone will have taken the time to carefully put together documents ensuring that you have all the right information that you require to get up to speed quickly.
Hopefully someone will have taken the time to carefully put together documents ensuring that you have all the right information that you require to get up to speed quickly.
But of course this won't be the case!
If you’re lucky you’ll get a handwritten list of SQL servers and now you need to go off and gather all the important pieces of
information that you need to administer these instances correctly. Now just to add, I’m not talking maintenance
here. It's very common that the first thing we do is login and check that all the
relevant tasks like backups and consistency checks are up to date, and that is good, but we’re at a much more important step before that.
The very first thing you need to do is to find out what each server is, what it is for, what it is doing, who owns it (and its databases) and what its
recovery objectives are.
It is imperative that you get this information because all of those things will determine many of the technical processes that you will be either implementing, or certainly looking after. Take DR/HA for example; technical terms yes but the business rules determine the strategy for both.
And you cannot simply script this from SQL or get a 3rd
party tool to retrieve the information. It is absolutely impossible to do this in an automated manner. No, you're going to have to speak to people to find out what you need. You're even going to have to ask people who the right people are to ask!
It can be an extremely long winded and painful task. New SQL instances will
seemingly be popping up from nowhere over the next few days, weeks even! With each new discovery, the whole information gathering process starts again.
Now here is the most frustration thing. It could have all been done before you even stepped
through the door. But it wasn't, and you're now paying for these gifts you've inherited. Inheritance Tax.
So please take the time to do this. Do it now if you haven't already done so. It isn’t just going to save your bacon (and it will) but it is
going to save the next person an awful lot of pain, and you can bet they’ll thank you for
it.
We use One-Note. It is flexible enough to take original documentation and also handle multiple edits, with all changes marked by who did what and when. Also, a good discovery script, what I used to call 'gap analysis' comes in handy. If you have access to the SQL server, you are more than half-way there. Gaining 'sa' privileges might take a little work, but if you are the DBA you are going to need it.
ReplyDeleteBut some commands are universal, so if you save them to a script, they will work regardless of whether you are inheriting an old SQL2000 server or a newer model. For example, xp_msver is going to get you SQL version, and OS / Memory/ CPU info. @@SERVERNAME will get you the machine name and @@SERVICENAME will get you the instance name. Then there are commands for finding the drives, the databases, and files. You can use xp_regread to comb the registry for service account names and backup locations. msdb contains a wealth of information regarding jobs, replication/log shipping/mirroring, as well as maintenance plans. The master database can give you all the logins and hashed passwords for documentation, backup history,
A data dictionary, consisting of databases, objects, purpose, business need and triage information is important for that 'smoking hole in the ground' scenario where you need to go to your DR site.
Oh, and we always save a copy of the mssqlsystemresource.mdf and ldf, just in case.
Excellent points and great shout about One-Note! I haven't used it before for anything beyond personal notes/links really but I can see it being extremely useful!
DeleteI have to admit I do have a Recovery folder for every server on a file share or in the cloud where I keep login scripts, service info, one off backups of system databases, a screenshot of volumes, linked server scripts among other bits and pieces...just in case I need any of them!
Many thanks
D