Solved

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

Posted on 2014-04-13
11
12,781 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 32

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 32

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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher 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:ScottPletcher
ScottPletcher 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.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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 …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now