Solved

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

Posted on 2014-04-13
11
14,867 Views
1 Endorsement
Last Modified: 2014-05-12
whats the difference between a database name and instance name in SQL?
1
Comment
Question by:Ikky786
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 39997219
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
 
LVL 19

Assisted Solution

by:Patricksr1972
Patricksr1972 earned 72 total points
ID: 39997220
Database is database, instance is your server name.

To check your instance name run query:  select @@ServerName
0
 
LVL 33

Expert Comment

by:ste5an
ID: 39997234
See @@SERVERNAME (Transact-SQL) and @@SERVICENAME (Transact-SQL).

@@SERVICENAME returns the instance name. @@SERVERNAME is the 'full qualified name'.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 7

Assisted Solution

by:tankergoblin
tankergoblin earned 72 total points
ID: 39997278
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 214 total points
ID: 39997699
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 142 total points
ID: 39999613
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
 

Author Comment

by:Ikky786
ID: 40002947
Can I have multiple instances running inside a SINGLE copy of SQL running on a server?
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 214 total points
ID: 40003061
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 142 total points
ID: 40004077
>> 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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 214 total points
ID: 40004203
Hence "there are license considerations that must be obeyed / complied with."
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 57
VB.net and sql server 4 33
SQL Error - Query 6 24
performance query 4 20
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question