Databases vs. Instances

As a long-time Oracle developer and administrator, one of the most interesting differences that I've found as a newbie SQL Server developer and administrator is in the simple basic term, database. 

In simple terms, a database is a set of database files in permanent storage that share a data dictionary, logs and control files.  An instance is a set of running processes that maintain a database and its control files and logs.  While there are in-memory databases where the database is in the server's memory, rather than maintained in permanent storage while its associated instance is running, eventually even this kind of database does get written to permanent storage.

Until Oracle 12c with its multi-tenant option, a database and an instance in Oracle were pretty close to the same thing.  With Oracle Real Application Clusters (RAC), you might have several instances on separate servers accessing a single database, but you would not have several databases under the same instance.  It would not be unusual to run several Oracle databases on the same server, but each database would be maintained by its own instance (or instances in the case of RAC).  Instances might share a network listener for client connections, but the client would indicate the instance to which it wanted to connect, and therefore which database.

But in SQL Server, and a few other relational databases, for example, PostgreSQL, a single instance can run several databases.  There are also some required databases managed by an instance, such as the database that contains the shared data dictionary.  Each database can have its own schemas, users, tables and other database objects.  None of these need to be unique except within a database.  A full unique name for a database object would be databaseName.schemaName.objectName.

This came in handy recently when I needed to create a new test database identical to our existing development database with three separate schemas.  My Oracle-trained brain initially was thinking that I needed a new SQL Server instance, since I didn't have an extra server or database license.  This is possible, but not very common, and it requires the original SQL Server installation media.  But I was reminded that I could just create a new database in the existing instance.  It could have the same schemas and database objects, but a different database name.  And it was just as easy to configure the test version of the application to connect to a different database in the same instance as to a database with the same name in a different instance.

I had two choices available for creating my test database.  It is very easy to use SQL Server Management Studio to back up my development database, and then restore it into a new test database.  But that would also copy the development data instead of creating the tables empty.  Instead, this proved to be a good opportunity to test my DDL scripts.  I have one script for each object to be created, and a master script to call each in the correct order.  This also executes scripts that load data into reference and lookup tables - remember that I consider data for these tables to be part of the source code for the database.

So, chalk this one up as an advantage for SQL Server.  Multi-tenant Option in Oracle 12c (and its successor, Oracle 18) has similar advantages - and may even be a little better if you need to move tenant databases around.  But it is an extra cost option, while multiple databases is a standard part of SQL Server.