Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

is it possible to have Single instance(name or SID) on multiple database? Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 5:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 2:48 AM
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...!!!
Post #1469415
Posted Tuesday, July 2, 2013 6:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1469439
Posted Tuesday, July 2, 2013 6:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 2,237, Visits: 2,684
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’! **
Post #1469446
Posted Wednesday, July 3, 2013 8:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1470094
Posted Wednesday, July 3, 2013 9:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 33,088, Visits: 15,197
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
Post #1470120
Posted Wednesday, July 3, 2013 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 2:48 AM
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......:)

Post #1470126
Posted Wednesday, July 3, 2013 9:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1470135
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse