Thought to share a specific conversation of one of the interviews did last month on a DBA role. The interview was done over the Skype call as the Interviewee was at a different place. We were having a conversation on past experiences of Interviewee and I learnt that he has worked for a company that maintains many databases for many clients and of course, he was explaining the work he had to do.
Here is the Brain Basher of the week based on the conversation I had. What is the best way of adding common objects to new databases when they are created?
Let me explain this by adding the conversation we had on it (Note that I have obtained permissions from the client and Interviewee for adding this small part of the interview to my blog, this is not the exact conversation had, have slightly adjusted it for writing purposes). let's call Interviewee as Jack.
Me: Okay Jack, since you have worked with a company that maintain large number of databases for clients, I am sure that you have many common objects like Tables, Stored Procedures in all databases.
Jack: Yes Dinesh. There are set of Tables and Procedures that we need to create when a new database is created to a new client.
Me: How do you do it? Are you adding them manually? Or have you done some automation on it?
Jack: I had automated it, yes, up to some extent. I have a script for creating a database and that script not only had the database creation part, I had creation code of all other required objects. So, all I had to do is, just run this script with few modification for setting up a new database.
Me: Great, do you know any other technique or method to make sure that all required common objects are available in the newly created database without adding them through the script?
Jack: There may be but, since I was comfortable with what I had, I did not look for something else. I had no issue with the ready-made code.
Me: Agree, but what if I need to make sure even I can create a database in your environment with all common objects, without your script? Or assume that one of your colleagues need to create a new database for a new client, and you are absent and he cannot find your script.
Jack: Yes, we experienced it several times and I accept that I did not have a document that explains how to do it :). But it was not a major issue at that time.
Me: Yes, it is always great to maintain a run-book (read more on Run Book: SQL Server Brain Basher of the Week #021 - Documentation). Anyway, we can use some components of the SQL Server for handling that, any idea how to use existing SQL Server components to automate some of those without having a script?
Jack: Like SQL Server Agent, using a job?
Me: How SQL Server starts the database creation when we ask to create one for us?
Jack: I am not sure about the internal operation but it requests the space from the OS based on the way we have configured the files and do some other operations, must be, and complete it.
Me: It actually takes a copy from a template and use it for creating the database. Do you know where is the template store?
Jack: Must be in one of the system folders. Sorry Dinesh, not sure where it is actually located.
Me: No problems. It is actually one of the system databases. It is Model Database.
Jack: Yes, I have heard about it but did not check much on that.
Me: You can actually add all your common objects to the Model Database and keep. Then, when anyone create a database, since it takes a copy of Model Database, the newly created database will automatically contain all required components. So, we do not need to add them manually or via a script.
Jack: Yes, I have missed that part...........
Conversation continued...........
Remember this, Model is the template and it is used by SQL Server to create databases when we ask.
No comments:
Post a Comment