Solved

Difference between databases and database instance in MS SQL

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

832 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