Prev: restricting access to sp_table_statistics2_rowset
Next: exec usp_MyProc cast(@MyParam as int)
From: Michael Coles on 4 Mar 2010 14:21
"Martin Plechsmid" <Send(a)No.Mail> wrote in message
news:%23UtVZG5uKHA.732(a)TK2MSFTNGP06.phx.gbl...
> The ultimate purpose of all this is to have a single central evidency of
> custommers. This evidency will be shared by other applications that can
> read it, update it, attach their own information to the customers (for
> instance their orders, correspondency) etc.
>
> We could have local database of custommers for each individual
> application, and synchronize them via replication, but then we would have
> to solve problems like replication collisions etc. I'd rather prefer to
> have a single shared database and access it from the various applications,
> either by web services - or directly, if we can afford it.
>
> So I don't think a data-warehouse would be a solution. Data-warehouses are
> meant to be essentially "static" (usually updated once a day), while we
> need an evidency that is "alive".
> My questions referred to the "shared" solution - on how records in the
> central database could be referred from the application databases.


OK, now that the purpose is clear, it sounds like you need a centralized
database that can be updated in real-time. Not something a once-a-day batch
process will handle well. You can refer to tables in remote databases by
setting up linked servers, for instance, but that doesn't address your
initial problem of foreign-key constraints across databases. Your idea of
abstracting the physical databases away behind a services layer (web
services, etc.) is a good idea, but it doesn't necessarily address the issue
of ensuring data integrity at the physical storage level.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

From: Martin Plechsmid on 5 Mar 2010 03:44
> You can refer to tables in remote databases by setting up linked servers

Perfect, I didn't know that something like that exists! I must think out
whether this solution is suitable, but it's good to know that something like
that is possible.

> but that doesn't address your initial problem of foreign-key constraints
> across databases. Your idea of abstracting the physical databases away
> behind a services layer (web services, etc.) is a good idea, but it
> doesn't necessarily address the issue of ensuring data integrity at the
> physical storage level.

No, that addresses other issues like e.g. possibility of dead-locks when
several different applications access the same database. On the other hand,
the disadvantage of web services is lack of "transactionality". I think we
have no other option than to choose what we need more - distributed
transactions, or prevention of dead-locks. These two features seem to go
against each other, so not even linked servers will help with this.

Martin.



First  |  Prev  | 
Pages: 1 2 3
Prev: restricting access to sp_table_statistics2_rowset
Next: exec usp_MyProc cast(@MyParam as int)