is it possible to have Single instance(name or SID) on multiple database?

Question

is it possible to have Single instance(name or SID) on multiple database?

nikunj_lodhi

SSC Rookie

Points: 41
More actions
July 2, 2013 at 5:43 am

#275714

Hi Folks,
I wanted to know that can we have a single instance on two different databases?
I am till now aware that it is a one to one relation(correct me if i'm wrong)
If yes,then how?
Thanks in advance...!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

nikunj_lodhi (7/2/2013)
Hi Folks,
I wanted to know that can we have a single instance on two different databases?
I am till now aware that it is a one to one relation(correct me if i'm wrong)
If yes,then how?
Thanks in advance...!!!

your question is not exactly clear, because "instance" has a special meaning to SQl server, whcih referneces a complete , independant installation potentiallycontaining one or more user databases.

By the title of the post, i think you mean" is it possible to have one specific user in two different databases?

If that is the quesiton, then the answer is yes, because each user points to a specific login, and that login is unique.

Code Examples would be something like this, where I'm assuming you have two databases named "SandBox" and "Development"

USE master;

create login ClarkKent with password = 'NotTheRealPassword';

USE [SandBox]

Create USER [ClarkKent] FOR LOGIN [ClarkKent]

USE [Development]

Create USER [ClarkKent] FOR LOGIN [ClarkKent]

with that, the user ClarkKent would be able to connect to the Server, and use either of those two databases, but as it sits, they could only see a few rows in sys.objects;

i didn't grant access to anything specific, other than what the public role can see in any database by default.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

HanShi SSC-Dedicated Points: 33506 More actions · Answer 2

nikunj_lodhi (7/2/2013)
Hi Folks,
I wanted to know that can we have a single instance on two different databases?
I am till now aware that it is a one to one relation(correct me if i'm wrong)
If yes,then how?
Thanks in advance...!!!

If I read your post I think you are mixing up Oracle and Microsoft SQL Server. I have almost no knowledge of Oracle DBMS but AFAIK a database can relate to multiple Oracle instances (see http://docs.oracle.com/cd/E11882_01/server.112/e10713/startup.htm). That is contrary to Microsoft SQL Server where there is one instance that can contain one or more user-databases.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

nikunj_lodhi (7/2/2013)
I wanted to know that can we have a single instance on two different databases?

Since this was posted on the Oracle section of the forum, please allow me to answer from the Oracle point of view.

An Oracle Instance and an Oracle Database are one and the same. It refers to the set of code - either Unix daemos or Windows services - plus data files, control files, etc.

Hope this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 4

In SQL Server, you have an instance that contains control information, scheduling, logins, etc. and then you can have multiple databases inside the instance. The instance is at a higher level, and acts as a container for the databases.

You can have multiple instances on a Windows host, but each of those has it's own databases.

nikunj_lodhi SSC Rookie Points: 41 More actions · Answer 5

Hi All,

Thanks for the reply.

@steve-2, that was what i was looking for.....can we do this with Oracle too......???

@paul-2: I want to use the same instance number for ten different servers so dats y i was looking for it.....Still thanks......:)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

nikunj_lodhi (7/3/2013)
@Paul: I want to use the same instance number for ten different servers so dats y i was looking for it

Oracle instances are identified by the SID defined by ORACLE_SID

Answering your question, yes... you can have the same SID in multiple servers, they are independent and do not care about each other.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.