[Blog] Help Yourself to a Slice of SQL Server Seventeen Layer Cake

Seventeen Layers
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


17. Credentials – This is either a Windows principal token or login and password of a user or service that are supplied to a Client.

16. Client – This is an application that initiates the connection process using a Provider and Credentials for Windows Authentication or SQL Server Authentication.

15. Provider – This is the Application Programming Interface (API) that translates all commands into Transact-SQL and communicates with Tabular Data Stream (TDS) packets using a network protocol.

14. Transact-SQL – This is the programming language used for writing statements to be delivered to an Endpoint.

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.

5. Partition – This is a slice of an Index defined implicitly or explicitly by a Partition Function that is then placed into at least one FileGroup.

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.


That’s It

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.


About Robert Matthew Cook

Hello, my name is Robert Matthew Cook. This blog is autoposted to from my main blog at www.sqlmashup.com. For more profile information or to leave a comment, please visit me there.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s