CMDB Data Design - OMIS 675
Group project to design and build the database, used in a CMDB (Configuration Management DB) 25 pts/person Due by end of class 3/9
Lab Objective: Working in your groups, you will design a database for a CMDB system, and then create it. I expect at least 6 tables with Primary Keys that are all linked together by Foreign Key constraints. Your deliverable will be a two -page document, emailed to me by the end of class:
1. A cover page listing all team members, their Z-IDs, and your group name
2. A page created using SQL Database Diagram showing all your DB Tables, with column names and data types visible.
One group member will email firstname.lastname@example.org the document by end of class
Your boss was awoken at 4AM by a call from the helpdesk. The helpdesk was reporting to her that a Windows server, supported by your team, was down. First they called your server admin; no answer. The manager is next on the list; your cell phone was downstairs. Protocol states wait 30 minutes then call the director. If nobody answers that call, then the CIO gets called. Fortunately for you, your boss, the director, answered. When you awoke at 6 and looked at your cell phone, you noticed two attempted calls from your helpdesk, and a two from your boss. S#@T! You check your emails and see a help desk ticket was created at 3AM stating Server1776 was down. Whew, just a development server – nobody uses that. What a relief. You call the helpdesk and let them know to stop worrying, then your boss. The helpdesk is relieved to hear the status. Your boss, on the other hand, takes little comfort in knowing that the server was an unused development server. She asks, “What if it was a production server? I don’t know what do about it, why are they calling me?” Then she says, “ They would have called Bruce (our CIO) next!” You say “I know, I know, it’s just that the helpdesk didn’t know it was a dev server and that with the new monitoring system we didn’t have the call lists all laid out yet.” In your head you’re thinking that the helpdesk should have known better than waking up a director in the morning for a server of unknown importance. You’re upset at them, but you realize you’re the one on the hook for this. You’re the manager after all. You tell her the only thing that will satisfy her “I’ll get it fixed”.
You decide that this is going to take something greater than just reprimanding your server admin for not answering his phone. After all, you didn’t answer yours either. What really needs to happen is that the helpdesk needs to know what servers actually require off-hours support if they go down. One of the complications is that your helpdesk staff turns over frequently, so keeping them informed is sometimes a futile effort. On top of that you have several hundred servers. To complicate things more, you average about 5 new servers a month and remove about 1 a month from production status. You know that you need a system to manage this information.
After some basic research you learn that the kind of system that could help your situation is called a Configuration Management Database, or a CMDB. CMDB systems are used to store information about the components (called Configuration Items) of an information system. Most notably a CMDB is used to store relationships between configuration items. CMDBs will let you know things like which software depends on which hardware, or more importantly for your situation what applications are impacted by a server being down. CMDBs are also used to store ownership information. The helpdesk will know who should be called when that server goes down. CMDBs are used in most large organizations where the information is complex and dispersed. Because they are used by large organizations they seem to be pretty expensive. The open source versions look too complicated and unfriendly to use. That’s it; you decide to roll your own. But before you do, you need to start where every good application starts – with requirements.
Fortunately, since you support the Windows servers, you have a pretty good idea of what runs where. Linux and Unix servers are used to support many production applications as well, you have less of an idea of what runs where on those servers. You know that you need to create an interface for other teams to enter information about their servers and applications, so if you can create a model that works on a few different applications it should work for all. Here are the applications that you decide to work on.
Your mission is to relate applications to servers. Here are your servers:
o Servers1-5 used for running Email
o Servers6-10 used for running HR
o Servers11-15 used for running HD
o Servers16-20 used for running VOIP
o Servers21-25 used for running ProjectTracker
Not every server has the same impact on an application. For instance, if a development or test server goes down, the application won’t be affected at all. But if a DB server goes down, the application is unavailable. The types of servers you support are:
· DB Server
· Test Server
· Dev Server
· Application Server
The urgency of resolving a server down event is determined by its impact on the application. Your (Service Level Agreement) SLA expresses the following impact types and their resolution timeframes.
Impact and resolution times
· Critical – 1 hour
· High – 4 hour
· Medium – 8 hour
· Low – 24 hour
The following server roles have these impacts
Server types and impact
· DB Server – Critical
· Application Server – High
· 2 Application Servers – Critical
· Dev Server – Medium
· Test Server – Low
A Warning could be that the server is still up, but possibly has a bad power supply or fan. It’s still running, so it can’t ever be a critical event.
With this information, the helpdesk can at least tell what applications are using which servers, and which servers have what type of impact on the application. Now if a server goes down, they know the impact and when to call – but they need to know who to call. The application support teams are as follows
Application Support Groups
· EmailSupport – Supports Email and VOIP
· HRSupport – Supports HR
· HDSupport – Supports HD and ProjectTracker
Server Support Groups
· LinuxServerSupport – Supports HR and Email Servers. Everything except DB servers
· WindowsServerSupport – Supports HD, ProjectTracker, and VOIP servers. Everything except DB Servers.
· UnixServerSupport – Supports all DB Servers, for all applications
The members of support groups can change, so a method for changing the members is necessary. Another requirement is that the groups should accommodate a call order, so if the first person on the list isn’t available, the helpdesk will know whom to call second, or third. A call order also helps accommodate planned time-off. When the person typically first in line to take a call goes on vacation, they can lower themselves to third on the call order list in the CMDB. Currently the call orders for servers are:
· LinuxServerSupport Call Order
1. Bo, 815-901-9000, Bo@work.com
2. Jessica, 815-901-8000, Jessica@work.com
3. Alex, 815-901-7000, Alex@work.com
· WindowsServerSupport Call Order
1. Deb, 815-901-1000, Deb@work.com
2. Carlos, 815-901-2000, Carlos@work.com
3. Ali, 815-901-3000, Ali@work.com
· UnixServerSupport Call Order
1. Siva, 815-901-4000, Siva@work.com
2. Laxman, 815-901-5000, Laxman@work.com
3. Tisha, 815-901-6000, Tish@work.com
That’s it, that’s enough for you to sketch out what the database will look like, then create the tables, then use SQL Database Diagram to create all the Foreign Key constraints. If you can do this part, you can partially populate the tables with information. You know that at the heart of a good application is the database – and at the heart of a good database are normalized tables. You also know that some of the logic in your requirements will need to be enforced in the application, not in the database, so you’re just focused on getting the tables built and related.