SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
nikunj_lodhi
nikunj_lodhi
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
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...!!!
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28086 Visits: 39927
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
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4378 Visits: 3668
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
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5339 Visits: 4639
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
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62134 Visits: 19102
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
nikunj_lodhi
nikunj_lodhi
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
Hi All,

Thanks for the reply.

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

@Paul: I want to use the same instance number for ten different servers so dats y i was looking for it.....Still thanks......Smile
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5339 Visits: 4639
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search