INFRA Database Design – System Administrator View

I’ve only been an assistant System Administrator, but I know what SAs want to see. They want to see the servers, their network addresses and what is running on each one.

Servers

There are two kinds of servers, physical servers and virtual servers. Physical servers are actual computers, pieces of hardware. Thus, we include attributes for the characteristics of the hardware like manufacturer, model number, and serial number. We also include the location of the hardware.  They will either run operating systems that will directly host services, or operating systems like IBM’s venerable VM/CP (I mention this, because I once used it) or VMWare that host virtual machines. Sometimes an operating system that can run services will also run a virtual machine host as a service, like Oracle’s VirtualBox or Microsoft’s Windows Virtual PC. We also use servers that are hosted in the cloud, like AWS or Oracle’s platform as a service. I would not include containers such as Docker or Kubernetes as servers, though there are some parallels. I have a self referencing relationship to show that some servers host other servers. Please note the importance of distinguishing the physical servers from the virtual ones. We often want to know that certain servers are on separate physical machines. For instance, as a DBA, I want to be sure that if the physical device hosting my database fails, the backup data needed to recover it is on another device that survived the crash.

Aliases

Servers have name, but a server may have several names. In fact, even when I list an IP address for a server, it is the primary IP address only. You see, network gateways may in fact have an internal address for a server and an outside address. As far as names go, a Web Server may be configured to show a home page for one application when accessed by one name and for a different one when accessed by another name. The user may never know that they are really the same server.

Services

A Service is a sort of nebulous concept – it can mean all kinds of running software that provide things an application system needs. A Database Instance, which was already explained, is just one specific type of service. Others might include LDAP Directories like OpenLDAP, that provide identity services, Web Servers like Apache that show and get resources using the HTTP protocol, or Application Servers like Glassfish which do similar work but tend to have additional capabilities more suited to running application systems. There are also services that your application system might use that are not operated by your organization, like Bing Maps or Yahoo Finance. I included Application Service as a target for application system deployments for the Deployment Manager view but the two specific types of services in my design are NOT the only types available. This is where I’d put references to Docker or Kubernetes containers – which are useful ways of hosting your services.

Access Ports

One thing that services often do is open certain access ports on your server. SAs want to know which ports are open as each open port is a security concern. And of course others on the development and maintenance teams need to know how to access the services that their tools and application systems are using. Each port is associated with a particular service on a particular server and two services cannot use the same port on the same server. Therefore if Apache Web Server is using ports 80 and 443 on dev.mycompany.com, and Glassfish is also running on that server, Glassfish must use some other ports, like 8080 and 4443.

The full address of a service will include an alias or IP address, a port number, and maybe a resource name as well. Usually, an access port will also be associated with a particular standard protocol used to access the service. Protocols include those such as HTTP and HTTPS for unencrypted and encrypted web services, LDAP for accessing directories, or SMTP for mail servers. There are some protocols that are proprietary as well, such as SQL*Net for Oracle RDBMS. Some ports are standard for certain protocols, such as 80 for HTTP, while others are customary but not required.

Entity Relationship Diagram for System Administrator View