Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Difference between databases and database instance in MS SQL

Posted on 2014-10-29
4
Medium Priority
?
157 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
4 Comments
 
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 668 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 668 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 664 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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.
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

824 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