whats the difference between a database name and instance name in SQL?

whats the difference between a database name and instance name in SQL?
Ikky786Asked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
A database contains Tables where the actual data is stored.
An instance contains Databases.
A Server (hardware) can contain multiple Instances.

Each Instance is a SQL Server install (or data base engine programs, tools, and associated programmes). Each User Database (and tables) is created (by writing code, using tools, copying / restoring existing databases).

You can have different Instances installed on a machine by naming them when you install the SQL Server programmes. For example you can have SQL2005, SQL2008, SQL2012 as separate named instances of SQL Server. You can also have the same version installed as different Instances such as SQL2005DW (for datawarehouse), SQL2005_PAY (for payroll), SQL2005_ERP etc etc.

You can have one (and only one) instance known as the DEFAULT instance, Every other instance on the same machine must have a name (16 characters).

The programmes (SQL Server database engine) compete with each other for machine resources so you must understand and manage how each instance will compete otherwise they can grind a machine to a halt.

It is probably more common to find multiple databases within an instance. Each instance has "system" databases (MASTER, MODEL, RESOURCES, MSDB, TEMPDB) and then you can create your own user databases within that instance.

In terms of managing server resources, you can manage individual instances with regard to how much memory and CPU each one will use. You cannot do that for databases within an instance.

You can apply security and access constraints at both the Instance or Database levels. Although sometimes, the business may prefer highly sensitive / secure data as a separate instance (such as a Payroll database) to avoid any risk.

When you connect to a database you have to provide both the Instance Name and the Database Name.

Hope that helps...
1
 
ste5anSenior DeveloperCommented:
The database name is the name of the database. sic. An instance is the Windows Service (sqlservr.exe) running serving one or more databases. There could be more then one instance running in parallel.
See also Database Engine Instances.
0
 
Patrick BogersConnect With a Mentor Datacenter platform engineer LindowsCommented:
Database is database, instance is your server name.

To check your instance name run query:  select @@ServerName
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
ste5anSenior DeveloperCommented:
See @@SERVERNAME (Transact-SQL) and @@SERVICENAME (Transact-SQL).

@@SERVICENAME returns the instance name. @@SERVERNAME is the 'full qualified name'.
0
 
tankergoblinConnect With a Mentor Commented:
DB_NAME
db_name  is the name of the database that is stored in form of OS level datafiles.


INSTANCE NAME
SID or instance name is the gateway in order to access the database which is stored in the form of datafiles on disk .
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
There is a natural hierarchy when dealing with SQL Server.

The SQL Server software itself can be considered the top level.  When you install the SQL Server database software, you create an "instance".  Individual data within that instance is stored in "databases".  The system itself has at least 4 databases that will exist in all instances -- master, model, msdb and tempdb.

You create your own "database"(s) to hold your data, giving them any valid name you want.

Within those tables are tables and other objects.

Instance
....Databases
........Tables

You may install multiple instances on the same server and, of course, across servers.  But all instances must have unique names from each other within the network.  Otherwise, SQL wouldn't know which requests went to which instance.
0
 
Ikky786Author Commented:
Can I have multiple instances running inside a SINGLE copy of SQL running on a server?
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
no.

each instance is a single copy (as installed) of sql server

you can have multiple databases within a single copy (instance) of sql server.

can you install a single copy of SQL server a number of times ? Not really - there are license considerations that must be obeyed / complied with.

you need to consider that sql server when installed becomes a (single) instance
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> can you install a single copy of SQL server a number of times ? Not really - there are license considerations that must be obeyed / complied with. <<

It depends on the licensing.  If you have full CPU licenses, you can install as many instances of SQL on those CPUs if you want.  This is often overlooked as a way to cut costs by using multiple instances of Standard vs. one Enterprise license; at least the last time I looked at licensing costs, which I am certainly no expert on -- it's so complicated now you almost need a licensing specialist :-) .
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Hence "there are license considerations that must be obeyed / complied with."
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.