Replication - what type do i need?

[SQLScholar]

Hey all,

I am looking into replication of data between 2 SQL 2000 servers - but its a little unusual set up (well according to what i have read).

What i effectively have is a master SQL server which has all our records on. However there is a SQL server used for our website. This server will get a limited amounts of inserts (currently only inserts) which need to be replicated to the master server.

So what i thought was that this looks like one way transactional - but looking online it seems to sugguest that one way replication should only have the data changed on the publisher - not a subscriber.

Secondly is it possible to limit the websites SQL server to only allow to replicate inserts (so that it cant delete\update data)?

Can anyone help me with any sugguestions?

Hope this makes sense, and TIA

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[mrdenny]

Correct, transactional replication is one way only.

What you need to look into is merge replication. This will allow you to make changes to both the publisher and subscriber.

Within the replication UI it self, no there is no way to limit replication to only move the inserts. However if you edit the stored procedures which the replication setup creates you can tell it to ignore the updates and deletes by simply removing the update and delete commands from within the procedures. SQL will still think that it's replicating these changes, but the changes won't actually be written anywhere.

Do keep in mind that if you do this, any time you rebuild the replication you will need to put this change back in place.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[SQLScholar]

Sorry - just to confuse....

What i want is one way - but once the changes have been moved (the inserts) all updates should then be done on the publisher not the subscriber....

Sorry for not being clear the first time.

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[mrdenny]

Same idea then. Setup the transactional replication from the publisher to the subscriber. Edit the stored procedures on the subscriber so that when updates and deletes are received they are not processed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog