Solved

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

Posted on 2014-04-13
11
17,675 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 34

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 23

Assisted Solution

by:Patrick Bogers
Patrick Bogers 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 34

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EF5 How do I stop pre-compiled views? 8 52
Powershell error using sql agent job 24 37
Report 8 27
*** Windows Server 2012 Websites Set Up *** 17 28
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

732 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