Replication or Update via Trigger ?

Hi SQL Gurus,
I have an application where the end user insist that there should be nine(9)
databases, same server within the system.
There are 'shared tables' on one of the database where any
insert/update/delete on those 'shared tables' must appear immediately on
other databases.
In this case, should I use replication or doing updates via Trigger ?
Thanks for your comments,
KristI am not sure why he/she is insisting for identical databases? Perhaps,
instead of creating tables in all these databases, you can opt to create
views which simply SELECT from a table in a single a database. You don't
have to worry about the data being in sync either.
Replication is not a solution for addressing such requirements. You can opt
for a trigger, but with the information from your post, I rather would
conclude it is an overkill to support redundant data for no apparent
reasons.
--
- Anith
( Please reply to newsgroups only )|||Hi Anith,
I used to think of View.
But I need to create FK from transaction table to these 'shared tables'
e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
and Area are 'shared tables'
How can I do this with view ?
Thanks,
Krist
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I am not sure why he/she is insisting for identical databases? Perhaps,
> instead of creating tables in all these databases, you can opt to create
> views which simply SELECT from a table in a single a database. You don't
> have to worry about the data being in sync either.
> Replication is not a solution for addressing such requirements. You can
opt
> for a trigger, but with the information from your post, I rather would
> conclude it is an overkill to support redundant data for no apparent
> reasons.
> --
> - Anith
> ( Please reply to newsgroups only )
>|||Krist, first find out the reason that your user needs 9 databases.
1. If is for security then perhaps create 9 different users or roles instead
and assign permissions accordingly.
2. If it's a limitation in the front end application then perhaps you'll
need to stick with several databases
3. If the user later wants to distribute the 9 databases to 9 different
servers then cross-database views become more tricky.
It's a rather strange requirement, and the underlying reason for it probably
constrains your options even further.
"tristant" <krislioe@.cbn.net.id> wrote in message
news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> Hi Anith,
> I used to think of View.
> But I need to create FK from transaction table to these 'shared tables'
> e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
> and Area are 'shared tables'
> How can I do this with view ?
> Thanks,
> Krist
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I am not sure why he/she is insisting for identical databases? Perhaps,
> > instead of creating tables in all these databases, you can opt to create
> > views which simply SELECT from a table in a single a database. You don't
> > have to worry about the data being in sync either.
> >
> > Replication is not a solution for addressing such requirements. You can
> opt
> > for a trigger, but with the information from your post, I rather would
> > conclude it is an overkill to support redundant data for no apparent
> > reasons.
> >
> > --
> > - Anith
> > ( Please reply to newsgroups only )
> >
> >
>|||Hi Anthony,
You are absolutely right : the reason the number (3)
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
So, that is my reason, what options do I have now ?
Thanks,
Krist
"Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> Krist, first find out the reason that your user needs 9 databases.
> 1. If is for security then perhaps create 9 different users or roles
instead
> and assign permissions accordingly.
> 2. If it's a limitation in the front end application then perhaps you'll
> need to stick with several databases
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
> It's a rather strange requirement, and the underlying reason for it
probably
> constrains your options even further.
> "tristant" <krislioe@.cbn.net.id> wrote in message
> news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > Hi Anith,
> > I used to think of View.
> > But I need to create FK from transaction table to these 'shared tables'
> > e.g : SalesOrder must have FK to Salesman and Area table, where
Salesman
> > and Area are 'shared tables'
> >
> > How can I do this with view ?
> >
> > Thanks,
> > Krist
> >
> > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > I am not sure why he/she is insisting for identical databases?
Perhaps,
> > > instead of creating tables in all these databases, you can opt to
create
> > > views which simply SELECT from a table in a single a database. You
don't
> > > have to worry about the data being in sync either.
> > >
> > > Replication is not a solution for addressing such requirements. You
can
> > opt
> > > for a trigger, but with the information from your post, I rather would
> > > conclude it is an overkill to support redundant data for no apparent
> > > reasons.
> > >
> > > --
> > > - Anith
> > > ( Please reply to newsgroups only )
> > >
> > >
> >
> >
>|||Your options:
1. Partitioned views
2. Replication
3. Log shipping (backup & restore)
"tristant" <krislioe@.cbn.net.id> wrote in message
news:OQxaWDgxDHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hi Anthony,
> You are absolutely right : the reason the number (3)
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> So, that is my reason, what options do I have now ?
> Thanks,
> Krist
> "Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
> news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> > Krist, first find out the reason that your user needs 9 databases.
> > 1. If is for security then perhaps create 9 different users or roles
> instead
> > and assign permissions accordingly.
> > 2. If it's a limitation in the front end application then perhaps you'll
> > need to stick with several databases
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> >
> > It's a rather strange requirement, and the underlying reason for it
> probably
> > constrains your options even further.
> >
> > "tristant" <krislioe@.cbn.net.id> wrote in message
> > news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > > Hi Anith,
> > > I used to think of View.
> > > But I need to create FK from transaction table to these 'shared
tables'
> > > e.g : SalesOrder must have FK to Salesman and Area table, where
> Salesman
> > > and Area are 'shared tables'
> > >
> > > How can I do this with view ?
> > >
> > > Thanks,
> > > Krist
> > >
> > > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > > I am not sure why he/she is insisting for identical databases?
> Perhaps,
> > > > instead of creating tables in all these databases, you can opt to
> create
> > > > views which simply SELECT from a table in a single a database. You
> don't
> > > > have to worry about the data being in sync either.
> > > >
> > > > Replication is not a solution for addressing such requirements. You
> can
> > > opt
> > > > for a trigger, but with the information from your post, I rather
would
> > > > conclude it is an overkill to support redundant data for no apparent
> > > > reasons.
> > > >
> > > > --
> > > > - Anith
> > > > ( Please reply to newsgroups only )
> > > >
> > > >
> > >
> > >
> >
> >
>