Solved

Difference between databases and database instance in MS SQL

Posted on 2014-10-29
4
143 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

710 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