Prev: restricting access to sp_table_statistics2_rowset
Next: exec usp_MyProc cast(@MyParam as int)
From: Tibor Karaszi on 26 Feb 2010 10:37
> I always threat MSSQL databases like a synonym to Oracle schemas - and
> evidently the analogy isn't completely correct.

I have a feeling that a better comparison to schema in Oracle would be
schema in SQL Server (what used to be object owner). And perhaps the
instance concept in Oracle is better compared to a combination of database
and instance in SQL Server. Disclaimer: I have never worked with Oracle.

I feel your pain, Martin. There are no set answer to your situation...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Martin Plechsmid" <Send(a)No.Mail> wrote in message
news:OUkjNdvtKHA.4332(a)TK2MSFTNGP05.phx.gbl...
> Thank you!
>
>> First, let me say that the "can" should have been "can't".
>
> I was sure you can, but I tested that - and you're right. I always threat
> MSSQL databases like a synonym to Oracle schemas - and evidently the
> analogy isn't completely correct.
>
>> There's no answer to this. Since restoring either database to an earlier
>> point in time is a disaster scenario possibility, you can always have
>> inconsistent data. This is one of the facts of spreading the data over
>> several databases! You can use log backups and transaction log markers to
>> allow restore of several databases' log backups to such a log marker, but
>> that is pretty burden of the DBA.
>
> One of my personal alternatives was to replicate the data from one db to
> the other. But that wouldn't solve anything. When you restore one
> database, you have inconsistencies again.
>
>> If you decide to go this route (if you decide to have related tables in
>> different databases in the first place)
>
> That's not up to my decision. Customer's database is controled by one
> application, Order's db by another, so it's natural that the databases are
> separate. But every application needs to work with customers somehow (e.g.
> by attaching orders to customers that sent them). And we want to have a
> central evidency of customers, not separate evidency for each application.
> Thus we need either to link records in one db to customers in the central
> evidency, or replicate customers from the central evidency to individual
> application databases.
>
> Martin.
>
>
From: Michael Coles on 26 Feb 2010 23:34
"Martin Plechsmid" <Send(a)No.Mail> wrote in message
news:%23$iP$jttKHA.6140(a)TK2MSFTNGP05.phx.gbl...
> Hello Tibor,
>
> thank you for your answer.
>
>> Short answer is that you can have an FK across databases.
>
> Yes, I know that. But then I would have to link the database tables
> directly, which would result in necessity of modification the Order's
> database data model whenever the Customer's database data model changes.
> To prevent this I suggested to introduce the adapter. But you cannot
> create a foreign key from table to a view!
>
>> What if you restore one of the databases to an earlier point in time!
>
> Good point. So what would you suggest? Please note that the dependency
> between the two databases is in one direction only - the Order's db
> depends on the Customer's db, but not the other way round.

You don't have many options for this. You can use triggers like Tibor
suggests, but it's not fullproof and it's not the most efficient set up, but
it's workable. Is it possible to move the Order DB tables and Customer DB
tables to different schemas of the same database? Do you have to enforce
your RI across databases in real-time?

--
Thanks

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

From: Martin Plechsmid on 1 Mar 2010 08:07
Thank you, Michael.

> Is it possible to move the Order DB tables and Customer DB tables to
> different schemas of the same database?

Looking at the program codes, I doubt so. Because the program references the
"dbo" schema at many places explicitly. Perhaps I'll be able to modify the
code...

> Do you have to enforce your RI across databases in real-time?

No, I do not have to enforce RI at all. But it's allways better to have it,
if it's natural.

Martin.


From: Michael Coles on 4 Mar 2010 01:09
"Martin Plechsmid" <Send(a)No.Mail> wrote in message
news:%23sXzbAUuKHA.3928(a)TK2MSFTNGP02.phx.gbl...
>> Do you have to enforce your RI across databases in real-time?
>
> No, I do not have to enforce RI at all. But it's allways better to have
> it, if it's natural.

The reason I ask is because it's common to combine data from disparate
sources in data warehouses and datamarts for reporting purposes, often in
batches during off-peak hours. I'm not sure what your ultimate purpose for
enforcing the RI across databases is, but if it's for cross-database
reporting and you don't have to enforce the RI in real-time you might
consider some sort of data warehouse with a batch loading process.

From: Martin Plechsmid on 4 Mar 2010 06:55
>>> Do you have to enforce your RI across databases in real-time?
>>
>> No, I do not have to enforce RI at all. But it's allways better to have
>> it, if it's natural.
>
> The reason I ask is because it's common to combine data from disparate
> sources in data warehouses and datamarts for reporting purposes, often in
> batches during off-peak hours. I'm not sure what your ultimate purpose
> for enforcing the RI across databases is, but if it's for cross-database
> reporting and you don't have to enforce the RI in real-time you might
> consider some sort of data warehouse with a batch loading process.

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.

Martin.


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