We want to allow our customer base to be able to access their account
information online. I want to setup a second SQL server so the customers can
use this for looks up. The front end to access this info is web based.
What replication method is the best one to use to update the database say
every 24 hours at night? Thanks!!
if your database is not too large, a snapshot replication maybe best for
you.
else somekind of logshipping will be good too, see the other thread on
simple log shipping.
justin
Scopus69 wrote:
> We want to allow our customer base to be able to access their account
> information online. I want to setup a second SQL server so the customers can
> use this for looks up. The front end to access this info is web based.
> What replication method is the best one to use to update the database say
> every 24 hours at night? Thanks!!
|||I think transactional replication would work for this. However this will
require each table you are replicating to have a primary key.
I am a little confused by the data flow. Are you saying data moves from the
web server SQL Server database to another SQL Server? Or is it moving
internally to the SQL Server supporting the web site.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:3D5CC7EA-4702-417E-AE2D-9985B1E4B781@.microsoft.com...
> We want to allow our customer base to be able to access their account
> information online. I want to setup a second SQL server so the customers
> can
> use this for looks up. The front end to access this info is web based.
> What replication method is the best one to use to update the database say
> every 24 hours at night? Thanks!!
|||Sorry for the confusion. The GUI interface to the data is a web interface
that connects to the backend SQL server. What I would like to do is setup
another web & SQL server for our cutomers so they can use it for lookups. I
really don't want them in our prduction DB.
I was wondering what is the best way to get the data off the production SQL
server to the customer SQL server on a nightly basis? I don't think log
shipping will work because it will put the shipped DB in "read only"
So what method would be the best to use? Thanks!
"Hilary Cotter" wrote:
> I think transactional replication would work for this. However this will
> require each table you are replicating to have a primary key.
> I am a little confused by the data flow. Are you saying data moves from the
> web server SQL Server database to another SQL Server? Or is it moving
> internally to the SQL Server supporting the web site.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:3D5CC7EA-4702-417E-AE2D-9985B1E4B781@.microsoft.com...
>
>
|||I think transactional is your best bet.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:76F950CE-B1EA-493F-8092-5ED6AA73EC75@.microsoft.com...[vbcol=seagreen]
> Sorry for the confusion. The GUI interface to the data is a web interface
> that connects to the backend SQL server. What I would like to do is
> setup
> another web & SQL server for our cutomers so they can use it for lookups.
> I
> really don't want them in our prduction DB.
> I was wondering what is the best way to get the data off the production
> SQL
> server to the customer SQL server on a nightly basis? I don't think log
> shipping will work because it will put the shipped DB in "read only"
> So what method would be the best to use? Thanks!
> "Hilary Cotter" wrote:
|||I also like Transactional Replication if the data is dynamic at the source
and the users who will be talking to your target server need updated
information as well for their lookups. If current data is not an issue, that
is they don't mind the data being static, then may be snapshot will work.
But then again it depends on how large the data is. For me one way
Transactional seems to fit the bill here.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:enpFYKCDGHA.1028@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I think transactional is your best bet.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:76F950CE-B1EA-493F-8092-5ED6AA73EC75@.microsoft.com...
interface[vbcol=seagreen]
lookups.[vbcol=seagreen]
will[vbcol=seagreen]
based.
>
sql
Showing posts with label customers. Show all posts
Showing posts with label customers. Show all posts
Sunday, March 25, 2012
Friday, February 10, 2012
before delete triggers in MSSQL
Hi
I've got several tables that have foreign key relationships with a 'users'
table - for example tasks (assigned to user) and customers (liason of
customer). When I delete a user, I would like to set all the foreignkeyed
rows to have null as a user, rather than doing a cascading delete. This
could be done in a stored procedure, but the problem is that the application
has multiple modules that can be added and removed, so I don't know at
execution time what tables there are. The only way to do this I could think
of was with triggers. I know this is supposed to be a big no no, but
couldn't think of anything else. Not that it matters, because triggers won't
work here as the trigger is fired after the delete is done and hence bombs
out due to violated constraint checking. I can't use an 'INSTEAD OF' trigger
unfortunately as I need the facility to have multiple triggers. I see that
Oracle has a BEFORE trigger, imagining that this would solve the problem. Is
there similar functionality in SQL, or another way to do this?
I was hoping that this is a common task and that there is an easy way to do
it, but no luck so far with searches
Thanks
JoeOn Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:
> The only way to do this I could think
>of was with triggers. I know this is supposed to be a big no no, but
>couldn't think of anything else.
Hi Joe,
Why do you think triggers are a big no no? Of course, they shouldn't be
your first option and you should prefer DRI over triggers where possible,
but there are situations where triggers are an invaluable instrument.
> Not that it matters, because triggers won't
>work here as the trigger is fired after the delete is done and hence bombs
>out due to violated constraint checking.
That's correct. You either have to remove the foreign key constraint and
do the checking in the trigger as well, or you have to use INSTEAD OF
triggers.
> I can't use an 'INSTEAD OF' trigger
>unfortunately as I need the facility to have multiple triggers.
Maybe I'm missing something, but why don't you just combine the actions of
those various triggers into one trigger?
> I see that
>Oracle has a BEFORE trigger, imagining that this would solve the problem. I
s
>there similar functionality in SQL,
The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
has to offer.
> or another way to do this?
As I already indicated, you could move the constraint checking to the
trigger as well. But that's a bad idea, since that would force you to
write and maintain more trigger code, it would slow things down and it
would deny the query optimizer the knowledge of this constraint, so that
it can't use this knowledge to optimize query execution.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have to agree 100% with Hugo. Use instead of triggers, or drop your
relationships and implement them in triggers (which will be just as good,
but will be pretty painful to implement consiering you can just do it in the
instead of trigger.) You can have as many actions in the trigger as you
want.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:cva111hh4p6m600c28jagd6m348r6g2jii@.
4ax.com...
> On Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:
>
> Hi Joe,
> Why do you think triggers are a big no no? Of course, they shouldn't be
> your first option and you should prefer DRI over triggers where possible,
> but there are situations where triggers are an invaluable instrument.
>
> That's correct. You either have to remove the foreign key constraint and
> do the checking in the trigger as well, or you have to use INSTEAD OF
> triggers.
>
> Maybe I'm missing something, but why don't you just combine the actions of
> those various triggers into one trigger?
>
> The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
> has to offer.
>
> As I already indicated, you could move the constraint checking to the
> trigger as well. But that's a bad idea, since that would force you to
> write and maintain more trigger code, it would slow things down and it
> would deny the query optimizer the knowledge of this constraint, so that
> it can't use this knowledge to optimize query execution.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
I've got several tables that have foreign key relationships with a 'users'
table - for example tasks (assigned to user) and customers (liason of
customer). When I delete a user, I would like to set all the foreignkeyed
rows to have null as a user, rather than doing a cascading delete. This
could be done in a stored procedure, but the problem is that the application
has multiple modules that can be added and removed, so I don't know at
execution time what tables there are. The only way to do this I could think
of was with triggers. I know this is supposed to be a big no no, but
couldn't think of anything else. Not that it matters, because triggers won't
work here as the trigger is fired after the delete is done and hence bombs
out due to violated constraint checking. I can't use an 'INSTEAD OF' trigger
unfortunately as I need the facility to have multiple triggers. I see that
Oracle has a BEFORE trigger, imagining that this would solve the problem. Is
there similar functionality in SQL, or another way to do this?
I was hoping that this is a common task and that there is an easy way to do
it, but no luck so far with searches
Thanks
JoeOn Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:
> The only way to do this I could think
>of was with triggers. I know this is supposed to be a big no no, but
>couldn't think of anything else.
Hi Joe,
Why do you think triggers are a big no no? Of course, they shouldn't be
your first option and you should prefer DRI over triggers where possible,
but there are situations where triggers are an invaluable instrument.
> Not that it matters, because triggers won't
>work here as the trigger is fired after the delete is done and hence bombs
>out due to violated constraint checking.
That's correct. You either have to remove the foreign key constraint and
do the checking in the trigger as well, or you have to use INSTEAD OF
triggers.
> I can't use an 'INSTEAD OF' trigger
>unfortunately as I need the facility to have multiple triggers.
Maybe I'm missing something, but why don't you just combine the actions of
those various triggers into one trigger?
> I see that
>Oracle has a BEFORE trigger, imagining that this would solve the problem. I
s
>there similar functionality in SQL,
The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
has to offer.
> or another way to do this?
As I already indicated, you could move the constraint checking to the
trigger as well. But that's a bad idea, since that would force you to
write and maintain more trigger code, it would slow things down and it
would deny the query optimizer the knowledge of this constraint, so that
it can't use this knowledge to optimize query execution.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have to agree 100% with Hugo. Use instead of triggers, or drop your
relationships and implement them in triggers (which will be just as good,
but will be pretty painful to implement consiering you can just do it in the
instead of trigger.) You can have as many actions in the trigger as you
want.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:cva111hh4p6m600c28jagd6m348r6g2jii@.
4ax.com...
> On Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:
>
> Hi Joe,
> Why do you think triggers are a big no no? Of course, they shouldn't be
> your first option and you should prefer DRI over triggers where possible,
> but there are situations where triggers are an invaluable instrument.
>
> That's correct. You either have to remove the foreign key constraint and
> do the checking in the trigger as well, or you have to use INSTEAD OF
> triggers.
>
> Maybe I'm missing something, but why don't you just combine the actions of
> those various triggers into one trigger?
>
> The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
> has to offer.
>
> As I already indicated, you could move the constraint checking to the
> trigger as well. But that's a bad idea, since that would force you to
> write and maintain more trigger code, it would slow things down and it
> would deny the query optimizer the knowledge of this constraint, so that
> it can't use this knowledge to optimize query execution.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Comments (Atom)