The Open System Interconnection (OSI) model depicts a data communications system as a seven layer stack. In a similar vein, I will try to depict a SQL Server system as a seventeen layer stack. Yes it does border on sacrilege to use only seventeen words to describe this product. After all it is old enough to legally drink anywhere in the western hemisphere (~22 years old) and it has a development team with a higher population than The Vatican City (~1000 people).
Some intentional generalizations, oversimplifications and cover-ups have occurred for the sake of cleanliness. Please leave a comment or contact me if you feel any omissions could lead to misinforming a reader.
Here We Go
16. Client – This is an application that initiates the connection process using a Provider and Credentials for Windows Authentication or SQL Server Authentication.
13. Endpoint – This is a service that listens for requests of the SQL Server Database Engine Instance.
12. Instance – This is a Relational Database Management System (RDBMS) whose primary purpose is to execute Database transactions abiding by the atomicity, consistency, isolation, and durability (ACID) properties. (Instance name is the first identifier of a four-part name)
11. Database – This is a collection of objects (e.g. Principal) defined as a single unit so it is easier to deploy, secure, maintain and recover. (Database name is the second identifier of a four-part name)
10. Principal – This is the Database-level identity counterpart to Credentials. This identity is used for Database-level authentication and authorization, with authorization being granted through a Permission. Note: Principals also exist at the Instance-level but the choice to list them here was based on the potential future of contained databases.
9. Permission – This is a defined ability (e.g. EXECUTE) which a Principal can be authorized to perform against a securable (e.g. SCHEMA). Note: Permissions and securables exist at many levels but the choice to list them here was based on Schema being the most granular securable scope.
8. Schema – This is a collection of objects (e.g. Table) defined as a single unit so it is easier to secure. (Schema name is the third identifier of a four-part name)
7. Table – This is a set of rows (tuples) defined as a single unit (relation) so that modification anomalies can be reduced by defining references (foreign keys) between the sets. The set is organized as either a Heap or Index. (Part 4 of a four-part name)
6. Index – This is the entire set of rows with columns (attributes) in the case of a Clustered Index or a subset of rows or columns in the case of a Nonclustered Index. These Index subsets are generally defined to improvement performance as are Partition subsets.
4. FileGroup – This is a database storage container defined as a single unit so it is easier to maintain and recover. They are visible to the operating system as at least one File but multiple files with isolated paths can also be used to improve performance.
3. File – This is an operating system (OS) storage container defined as a single unit so it is easier to secure, maintain and recover from an OS perspective which should not be confused with a database perspective. It is stored in nonvolatile memory which is usually a Storage Input Output (IO) subsystem.
2. Storage – This is the media on which the Data is actually recorded for later retrieval.
1. Data – This is the meaning of the 1’s and 0’s recorded onto media. It was no coincidence that Data is listed as number one in this stack. It is the nothing short of the reason the database profession exists so let’s give it the respect it is due.
Hopefully you enjoyed this summarization of SQL Server blasphemous or not, but if this slice of cake was not to your liking then please enjoy this consolation slice of Cake.