CODEWITHSUNDEEP
sql-serverinstance
user1449307
user1449307

Reputation: 27

One or more users per database instance?

This is a very basic/fundamental question, so basic perhaps, that I can't find the answer.

In SQL Server 2012, one can make several instances of the database on the host server. But can several users use the same instance, or is a instance assigned to a specific user?

Put another way: would one make a "marketing" instance, such that all people from marketing would share that instance? (same log-on)

Or, is it one instance pr. user?

Thx in advance

Upvotes: 1

Views: 1829

Answers (3)

StuartLC
StuartLC

Reputation: 107237

A physical (or virtual) server 'machine' can have many instances of SQL Server.

Each instance of SQL Server can have many databases.

Each database can be used simultaneously by many (really many) users.

Edit

Re - your marketing department scenario

As duffymo states, although a SQL server instance does allow you to directly add Active Directory users (logins) and assign them privileges to one or more databases (as users), assigning roles and permissions on the way, this would typically only be for 2 or 2.5 tier software architectures (i.e. User runs an app on his / her desktop, each desktop connects to the SQL database). Nowadays, the app would be centralized (e.g. Web, or SOA), and only the app server(s) would connect to the database, handling the user security / access control concerns.

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135789

An "instance" is a fully independent install of the SQL engine on a server. Each instance can support multiple databases and each database can have many users simultaneously connected.

Upvotes: 1

duffymo
duffymo

Reputation: 308743

One instance per user sounds ridiculously inefficient to me. No one can share common data, and you have to create/delete an instance every time you add/remove a user.

If you use that design, how do you deal with archiving everyone's data?

I would go with a single instance, multiple users. I'd have authentication/authorization in place to ensure that people only saw the data they were entitled to.

Upvotes: 2

Related Questions