I can't seem to find what's the essence regarding the difference in remote and consolidated user. When to use what??
From the documentation:
It's hard to really understand the difference based on this description.
Take this scenario where I have a main database (A) and two satelite databases (B,C) which have their own subscription to the same publication. I would use two remote users in the main database and let dbremote do the replication. In what scenario do I need a consolidated user?
Publishers of B and C need to GRANT CONSOLIDATE to publisher of A.
Publisher A needs to grant REMOTE to publishers of B and C.
In a single-tiered hierarchy, there is one consolidated database with one or more remote databases underneath. In such a hierarchy, the consolidated database grants REMOTE permission to the publishers of the remote databases. Each remote database grants CONSOLIDATE permission to the consolidated database publisher.
For example, there is a consolidated database identified by its publisher, HeadOffice, and a remote database identified by its publisher, RegionalOffice.
On the consolidated database, HeadOffice, you:
On the remote database, RegionalOffice, you:
Conceptually, a SQL Remote system is a hierarchy of databases.
In a single-tiered hierarchy there is a single, consolidated database at the top and underneath are one or more remote databases.
In a multi-tiered hierarchy, again there are remote databases at the bottom. However, in the tier immediately above the remote databases are databases that are both consolidated for the remote databases in the tier below, and also remote for the database immediately above them in the hierarchy, and so on, until you reach the top. At the top is, again, a single consolidated database.
Both the consolidated database and the remote databases need to identify which database(s) they can send messages to and receive messages from. Each database also has a userid associated with it. The user associated with that userid is called the publisher of the database.
For remote databases,you must GRANT CONSOLIDATE to the publisher (identified by the publisher’s userid) of the consolidated database they will be communicating with (the database above it in the hierarchy). The remote database can only communicate with one consolidated database (a one-to-one relationship).
For consolidated databases, you must GRANT REMOTE to the userid of the publisher of each remote database (in the tier below the consolidated) it will be communicating with (a potentially one-to-many relationship).
In a multi-tiered hierarchy there will be users who are granted both REMOTE and CONSOLIDATE (publishers of the databases in the middle tiers).
Here are some links to the SQL Remote documentation to assist you further:
Single-tiered hierarchy: http://dcx.sybase.com/index.html#1201/en/sqlremote/sra-design-s-4573934.html
Multi-tiered hierarchy: http://dcx.sybase.com/index.html#1201/en/sqlremote/sr-admin-sectc-5175782.html
Permissions overview (PUBLISH, REMOTE, CONSOLIDATE): http://dcx.sybase.com/index.html#1201/en/sqlremote/sr-managing-perms.html
CONSOLIDATE permission: http://dcx.sybase.com/index.html#1201/en/sqlremote/wrmpse.html
GRANT CONSOLIDATE statement: http://dcx.sybase.com/index.html#1201/en/dbreference/grant-consolidate-statement.html
REMOTE permission: http://dcx.sybase.com/index.html#1201/en/sqlremote/sra-design-s-5806195.html
GRANT REMOTE statement: http://dcx.sybase.com/index.html#1201/en/dbreference/grant-remote-statement.html
If you have any further questions, please feel free to follow up.
Best Regards, Andy
answered 05 Dec '12, 15:58
The Consolidated user is the user higher up in the hierarchy the remotes are the users lower in the hierarchy. This is why you can have multiple remote users but only one consolidated user.
answered 05 Dec '12, 12:42