Help – I’ve inherited yet another database!

by Jul 9, 2020

With many companies trying to do more with less, it’s not uncommon for a database administrator (DBA) to find out they’ve just been assigned yet another database. If they’re lucky, the new database will be of the same kind as others they manage (e.g. an Oracle DBA gets another Oracle database). But with the plethora of databases available today, that’s becoming far less common. It’s more likely they’ll inherit a database from a different vendor (e.g. PostgreSQL). As such, DBAs may find that they need a common management tool for multiple database platforms. One such tool that’s very capable and reasonably priced is IDERA’s Aqua Data Studio. Not only does it run on Microsoft Windows, Linux, and Mac/OS; it also supports all the following database platforms.



So what are the steps a DBA might want to follow in order to become familiar with their new database assignment? Besides obvious information such as the database version, patches applied, host operating system, whether server or virtual machine, and whether on-premise or in the cloud, what else might the DBA want to get acquainted with?


The first thing the DBA might want to do is to create a simple Entity-Relationship (ER) model of the main database objects (e.g. tables). There are several good reasons for this recommendation. First, it provides the DBA with a basic context from which to begin the acclimation process. It’s an easy way to appreciate the nature of developer and end-user questions (and not look totally clueless). Second, can you imagine attempting to tune a problematic SQL statement without the ER model? Imagine driving your car and trying to find somewhere new without the benefit of your cell phone’s GPS display or a paper roadmap. As primarily an Oracle DBA who inherited a SQL Server database containing the Internet Movie Database (IMDB) data, the first thing I did was to create this ER model using Aqua Data Studio, save it as a PDF file, and print some paper copies for me to scribble upon.



The second thing the DBA might want to do is to manually browse around and examine in more detail all the important database objects (e.g. tables) in order to become more familiar with them. I liken this step to zooming in on an online state-level map (e.g. Google) in order to see the main highways and roads for a given city of interest. Aqua Data Studio allows one to do this with ease. I like to visually inspect the key properties and detailed create script to gather my detailed information. This might seem like an overly tedious and monotonous step, but it will pay dividends that far exceed the required effort.



Now that the DBA has a contextual overview and some reasonable amount of detailed object inspection (i.e. “the what”), it’s time to begin looking at the security (i.e. “the who”). So third, the DBA should most definitely manually browse around and examine in more detail the logins, roles or groups, schemas, and any other security mechanism provided by the database. Once again Aqua Data Studio makes this quite simple as shown here where I’m first looking at the SQL Server user information for the IMDB database. Lo and behold, it looks like there might be a table (keyword) missing SELECT privilege. Time to ask more questions.



Note that even though you might get very comfortable with the database browser, there are other more focused and powerful screens under Main Menu -> DBA Tools for such concentrated tasks. For example, Main Menu -> DBA Tools -> MS SQL Server offers the following key choices: Security Manager, Session Manager, and Storage Manager. Here you can see both the Security Manager and Session Manager. You should find these DBA tools much more useful than trying to always work from the database browser.



At this point, the DBA inheriting the new database should feel somewhat comfortable with their new assignment. Plus a tool like Aqua Data Studio makes doing all the above just as easy and straightforward, regardless of the database platform you’re working with. I cannot imagine doing my job without such a powerful tool.