April 13, 2010 at 4:26 am
Hello Everyone:
I am trying to figure out how to implement replication under following scenario.
Our operational database server is having issues related to huge amount of data. So we have planned to move certain amount of data based on some criteria to secondary database. Also, the data which will be shifted to secondary database, needs to be deleted from operational database.
For example, we only want to hold last 6 months of data in our operational database and move the rest to secondary database. This movement will occur every night so that the process overhead do not impact the operations of the company.
Please assist me in this regard on how too achieve this and specially which type of replication I need to put in place.
Thanks a ZILLION in advance
WA
April 14, 2010 at 7:08 am
First a warning. This could back fire on you if you delete it completely of your production DB.
But this can be achieved. You can set up transactional replication between the two servers with a condition not to replicate delete commands. Thus you will be able to delete from production and keep the data in the subscriber.
Here are the problem that will arise with this set up. If your replication breaks, you will not be able to reinitialize it. If there is a delete that is valid, that will not get propagated to your subscriber leading to inconsistent data.
Maybe you should look at another scenario. I can give a small example.
Create additional file group and create archive tables in these newly created filegroups. Then have a nightly job that runs an insert into the archived table and then deletes the inserted rows from the main table. This will make your table smaller and much more efficient and you will still have all the data in another table in a different file group.
Brad Mcgehee has an article regarding this. Check it out.
http://www.sql-server-performance.com/tips/filegroups_p1.aspx
Also you can check online for File groups to get more info on this.
-Roy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply