Salesforce, Python, SQL, & other ways to put your data where you need it

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

Everything I learned in DBA school

05 Mar 2022 🔖 sql databases
💬 EN

Table of Contents

I’ve treated my syllabus, slides, homework, and notes from “DBA class” like gold for years now, but I’ve never re-read them. Iime to TL;DR the principles I might want to “remember how to Google” and throw them out.

Syllabus

  • Class 1: a DBA does…
  • Class 1: Access, security, & user management (Homework 1)
    • Database creation
    • User creation
    • Setting access rights
    • Passing access rights to others
    • Loading the database (not sure what that means)
  • Classes 2 & 3: PL/SQL & T-SQL scripting languages (Homework 2)
  • Class 4: Working w/ SQL Server – environment, DBA tools, & some more scripts (Homework 3)
  • Class 5: Working w/ Oracle – environment, DBA tools, & some more scripts
  • Class 6:
    • Database integrity
    • Database design issues (incl. normalization)
    • Database alteration
  • Classes 7 & 8: Performance management (Homework 4 & 5)
    • System performance
    • Database performance
    • Indexing
    • Application performance
    • Query optimization
  • Class 9: (Homework 6)
    • Storage technologies (RAID & SAN)
    • Data availability
    • Database & storage management
    • Database durability (incl. fault tolerance, database recovery)
    • Disaster planning
  • Class 10:
    • Oracle & SQL Server misc.
    • Performance & tune-up misc.

System administration

Often times, a DBA is also responsible for maintaining the virtual machine a DBMS is running in, its operating system, and keeping the DBMS software up-to-date.

Personality

“You’re it!” when things go wrong, so be a good communicator w/ managemnt, designers, developers, & end users.

You want info in the database to be stored correctly, readily available, adaptable to changes, & secure.

DBA responsibilities

Although you get to maybe have a little fun data modeling or creating a database from someone else’s logical data model, your job is mostly ongoing performance & operation of the database.

  • Backup & recovery
  • Integrity (it’s always less work to catch problems earlier, so constantly watch for problems)
  • Buffer/cache (memory)
  • SQL optimization
  • Security
  • Logging

Security

  • Think about the computers from which end users will be accessing the database with their very own keyboard & mouse. How’s the security there? You can’t do much about this, but work with a CISO and such to promote security.
  • Securing logging into the server on which the DBMS runs as various usernames.
  • Securing logging into the DBMS as various usernames.
    • Collapsed w/ next on Oracle – just my database username kodes.
    • On SQL Server, though, I might have a DBMS “login” of kodes but…
  • Securing logging into the databases inside a DBMS as various usernames.
    • Collapsed w/ previous on Oracle – just my username kodes.
    • …On SQL Server, though, the “login” of kodes might then use a “user” of kt for one database and a “user” of katie for another.
  • Securing operations against the objects inside a database (grant, revoke, roles).

Roles

Interestingly, at the office, the work of creating roles (e.g. create role the_role) and granting them certain authorities (e.g. grant select on employee to the_role) might be done by a DBA, but the work of adding & removing individual users or user groups to/from these roles might be done by a different person (e.g. grant the_role to username_1) – say, someone working in a Security team.

DBMSes always come with some predefined/built-in/system-defined DBMS-wide (possibly referred to as “server-level” or “server-wide”) roles and database-level roles.

SQL server comes with a special login sa that’s a member of the RDBMS-wide role sysadmin, which can do anything and everything. Another RDBMS-wide role it comes with is serveradmin, which only configures RDBMS-wide settings. Each database you create in it comes with a role of db_owner (slang dbo) that’s kind of like sysadmin, but per-database … can do anything within a database, db_datareader, which can select all data in all user tables in the database, and db_datawriter, which can insert/update/delete all data in all user tables in the database. It looks like when hosted on Windows, it might also come with a BUILTIN\administrators login (local Windows OS administrators – passwordless). And maybe it also comes with another administrator called Sys, but maybe that’s something that was set up for us in class.

Oracle comes with a special user system, a special user sys, and a special user internal. Each database you create in it comes with predefined roles of CONNECT, RESOURCE, DBA (which has WITH ADMIN OPTION, so it can pass along its DBA privileges to other accounts), and Public (a role that every database user is a member of by default).

I think “groups” in SQL Server might be the same idea as Oracle roles?

Users

CREATE USER my_username IDENTIFIED BY my_password isn’t the only way of creating an Oracle database user – just a very common one, known as a “database authenticated” user. But you can also, in Oracle, create database users that are connected to users on the operating system running the DBMS, known as an “externally authenticated” user. And you can create database users who are “globally authenticated” – e.g. via LDAP.

In SQL Server, all the fancy variations on with which technology an end user authenticates to the DBMS is set at the “login” level. Any given “login” only gets to be affiliated with 1 database “user” at a time, and no two logins may point at the same database “user”, so this works out and logins just sort of log right into databases w/o too much trouble once authenticated at DBMS level, if a database is specified (e.g. with Use db_1 at the top of a T-SQL script).

That said, for backwards compatibility, SQL Server does also offer “database authenticated” (users? logins?) and DBMS-host-OS “externally authenticated” (users? logins?).

Creating databases / VM notes

I believe SQL Server DBMSes often run on Windows servers/VMs. And you need Windows OS admin rights to create databases. (Hence in class, we didn’t actually create any SQL Server databases – they all came pre-created by the person who set up our VMs.)

In class, we also had our Oracle databases running on Windows VMs. That made it really easy for us novices to get into them remotely and really feel like DBAs who were working from an actual DBMS’s host OS, while also having a handy GUI.

Tooling

SQL Server Management Studio is the most common Windows software for interacting with SQL Server databases. If you’re already logged into the VM hosting the DBMS, you might be able to just set up logging in with Windows Authentication. But you’ll probably just do that once or so to enable TCP/IP so that you can also log into it from other computers, e.g. via a username & password over SQL Server Authentication.

Here’s a great first query to make sure you’re up & running in SSMS:

SELECT name
FROM sysobjects
WHERE name LIKE 'sys%'
ORDER BY name

CLI tooling

Oracle uses sqlplus. SQL Server uses sqlcmd since version 2012.

Don’t forget that OS-level scripts can be used to help make passing parameters to these CLIs a little less tedious when you’re doing a lot of repetitive work.

Oracle system topography

An “instance” is all memory stuctures & background processes allocated to support running Oracle databases.

One “instance” can support more than 1 databases. Each is identified by an “Oracle Service Identifier” or “SID”.

Each SID is supported by a set of OS-level files:

  1. A “parameter file” that contains most of the configuration parameters for an “Oracle database instance” – e.g. Init.ora or various other control files for SIDs that are cross-referenced from Init.ora, e.g. F:\oracle\admin\SID\pfile\Init.ora.832009143549 for an SID created at 832009143549, a.k.a. 8/3/2009, 2:35:49 PM.
  2. The “database files” containing the data associated with a particular database. Often have a .dbf extension. Often stored in C:\oracle\oradata\the_SID\.

(Note: in SQL server, deleting a database file from within the DBMS deletes it on the OS as well. In Oracle, it stays on the OS and you can later reclaim it.)

  • First, Init.ora (the parameter file) gets loaded into memory.
  • Then the control files (ending in .ctl?) for each SID do.
    • I think these are binary, so you edit them through ALTER SESSION database commands, not directly in the OS.
    • Trivia: often in triplicate copies for reliability.
    • Often stored in C:\oracle\oradata\the_SID\.
  • Then the handles to the database files get loaded into memory.
  • Then other memory gets allocated and various other system info gets loaded into memory.

SQL server has a structure that allows for less granular control, but also is faster.

In addition to control files and database files, each Oracle database also has redo log files (often stored in C:\oracle\oradata\SID\ with a .log extension – like control files, don’t work with them directly through the OS, do it through the DB) and various other files.

Oracle 11g tends to end up in C:\oracle\product\11.1.0\Db_1 and the installer will try to set you up an OS environment variable of ORACLE_HOME pointing to that path.

If you run out of OS filesytem space for your log files, your database can’t run.

Logging works in a circular data structure pattern called a “rollback segment.” If you come around to the beginning and see old transactions that still haven’t been committed, the DBMS errors out because you’re “out of log space.”

Inside a given Oracle database, it’s divided into one or more tablespaces (e.g. “SYSTEM”, “USER”, “index”, “other”, “RBS”, “TEMP”, “Auxiliary”), each of which in turn is divided into one or more segments (each logical object like a table or index gets its own segment, so the number of logical objects in your database is the number of segments there will be) (segments not part of SQL Server architecture), which is divided into one or more extents that get to use continuous space on disk and are not necessarily uniform in size on Oracle (but uniform in size on SQL Server), which is divided into many pages (a.k.a. data blocks, a.k.a. blocks) of one data file. Pages are not necessarily uniform in size.

Note the implication that a logical object – a segment – might span multiple OS files.

Also note the implication that a logical object is contrained by a tablespace’s size, since an object cannot span more than one tablespace.

Extents cannot span data files, but segments can.

The DBMS creates one or more OS datafiles to hold a tablespace’s data.

OK, that’s up to p. 17 in the papers I left off in. TODO: come back to this.

--- ---