Solved

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

Posted on 2014-04-13
11
18,645 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
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 
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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 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