Solved

Difference between databases and database instance in MS SQL

Posted on 2014-10-29
4
148 Views
Last Modified: 2014-11-03
Hello,

  Its a bot confusing - whats the difference between a database and database instance in MS SQL?

I have noticed on a single MS SQL server you can restore several databases. What then is an instance of db?

Thanks
0
Comment
Question by:LuckyLucks
[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
4 Comments
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 167 total points
ID: 40412440
"instance of db" appears to be a method of connecting to a SQL database.  A program called 'sqlbrowser.exe' receives connections to 'instances' and converts them into a database connection.  If you turn off the 'sqlbrowser.exe' service, you can't connect to 'instances' but you can connect to the database if you have the IP address and port number.  But then... if you have several SQL servers running on the same machine, IP address and port number won't be enough to tell them apart.  That's where 'named instances' comes into use because the 'sqlbrowser.exe' service can direct your request to the correct 'named instance'.
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 167 total points
ID: 40412471
A database instance can be considered to be a SQL Server installation on a server, more correctly called a SQL Server instance.
When installing SQL Server a default instance is created.
To install a second instance you would have to go through the SQL Server installation process again and give the new instance a name.
As you state you can install a number of databases on a single instance.
0
 

Assisted Solution

by:AddOnsInc
AddOnsInc earned 166 total points
ID: 40412543
To put it simply the database instance is the set of memory processes that controls access to the data.

For SQL Server specifically, it's the copy of SQLSRVR.EXE that runs as an OS service.

When in doubt, refer to Books Online: http://msdn.microsoft.com/en-us/library/hh231298.aspx

A database is the container for data that requires files on disk.

One SQL Server instance can contain more than one database (as opposed to Oracle where - up to 11g anyway - one instance was one database)
0
 
LVL 6

Expert Comment

by:Mandeep Singh
ID: 40412549
Hi,

Instance is like a new SQL Server in a OS, Every instance has different in name and have different set of rules.

In any instance ,it contains many databases.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 extract information from SQL Server on Database, Connection and Server properties

627 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