One user database serving multiple application databases

Question

I manage a fairly large database that has grown in complexity and design from one application database. Now there is a plan to add a fifth application, which carries with it its own scheme and specific data. I am looking for SSO solutions, but actually this is not what I need. My goal is to have one point of client registration, logins and authorization.

Ideally, each application requests authentication and gets permission for several applications, where the applications will then connect to the appropriate database for operations. I have no first-class experience with this degree of separation, since one database has clung perfectly for years. Any best practice articles will be appreciated :)

I would introduce a basic database that supported shared data - Customer / Company / Products

• Primary tables and primary keys . To maintain referential integrity, I must have a smaller replicated table in each application database. What are some ways to exchange keys between different databases and provide referential integrity?

• Replication . Two subscribers are currently retrieving data from the production database, where the data is subsequently transferred to the reporting DW solution. Am I following a road that could lead to disappointment?

• Data integrity . As I can see, for example: DATABASE_X.PREFERENCES.USER_ID = always refers to a = CORE_DATABASE.USERS.USER_ID

• . / ?

• White Papers. - ?

Answer 1

URL- . , , , .

http://blogs.msdn.com/b/sqlcat/archive/2008/06/12/sql-server-scale-out.aspx

2005 , http://msdn.microsoft.com/en-us/library/aa479364.aspx#scaloutsql_topic4

... http://msdn.microsoft.com/en-us/library/ms345584.aspx

:) http://sqlcat.com/whitepapers/archive/2010/06/08/scale-out-querying-for-analysis-services-with-read-only-databases.aspx

Answer 2

, Master . .

Answer 3

RAC? , . . .

Answer 4

Don't give up on the idea of ​​having separate applications and linking in / out functions with webservice (esque) queries. I have seen that billing / user registration systems are separated in this way. Although on an extremely large scale, this may not be a good idea.