sql server replication

I want some way of doing replication in which only inserted and updated data gets replicated not the deleted one.

The idea is to build archiving database from the live db. but we want live db to be small.

1- Move changed data from live db to archive db
2- delete data from live db of older than today

we have to implement this in sql server. any other solution than replication.
LVL 1
EpurchaseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lojk.Net and Infrastructure ConsultantCommented:
Your best option is to use triggers added to your tables.

http://msdn.microsoft.com/en-us/library/ms189799.aspx

also

http://msdn.microsoft.com/en-us/magazine/cc164047.aspx

After each 'Insert/Update' trigger you coudl write to another table in another database (if required) based on the values that were changed from the current row (that the trigger provides to you)  and then flush transactions from the bottom of your current table as required (or overnight using an SQL Agent Scheduled Task stored procedure).

The topic of triggers is too complicated to go into here - you will just need to go and read up about it but be warned you will almost certainly need to learn about stored procedures too (if you are not already familiar with them) as without them things get very complicated quite quickly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EpurchaseAuthor Commented:
Is there any way we can achieve the same using replication? I know the trigger option but wanted to do somehow using replication.
0
lojk.Net and Infrastructure ConsultantCommented:
Well, your original question ends..

"have to implement this in sql server. any other solution than replication."

Other than replication and using triggers, what else is there? clr doesn't supply any option that tsql doesn't already provide either.

I understand what you are trying to achieve I have done similar things too - using triggers, you could probably do something using a bunch of half baked client side code but it won't work (as well)

Have fun with this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.