Background:
We currently have a Short-term database (STDB), which will only contain the most recent 30 days of data. We are going to create a Long-term database (LTDB), which will hold 6-months to 1 year of data (haven't decided which yet). The data in our databases is used for investigative purposes, so INSERT is the only function that can be done to/with the data. The only exception to that is: since the two databases are going to be limited by the amount of data, we will need to delete records as they reach the 'cut-off'. For example, when the STDB gets 31 days of data, the oldest day has to be deleted, since all we want on it is 30 days. We also need the LTDB to have the same records as the STDB; in other words as a record is inserted into the STDB it needs to be replicated into the LTDB.
Situation:
We are considering using Transactional Replication with the STDB being the publisher and pushing the data to the LTDB (subscriber). This should give us near-real-time inserts into the LTDB. From my reading of the BOLs, transactional replication creates a 'mirror' image of the publisher database. So, if I delete a record from the STDB the same record will be deleted from the LTDB. I don't want this to happen (if it does, our LTDB will end up with only 30 days of data since that's all that is in the STDB).
Questions:
Is Transactional Replication the best way to do it? Remember, I need the LTDB to have the same current 30 days that the STDB has, plus the previous 5 months or more.
How do I 'shut off' UPDATE from being allowed?
How do I stop deletes on the STDB from happening on the LTDB?
I appreciate any advise/comments/etc.
-Bill
We currently have a Short-term database (STDB), which will only contain the most recent 30 days of data. We are going to create a Long-term database (LTDB), which will hold 6-months to 1 year of data (haven't decided which yet). The data in our databases is used for investigative purposes, so INSERT is the only function that can be done to/with the data. The only exception to that is: since the two databases are going to be limited by the amount of data, we will need to delete records as they reach the 'cut-off'. For example, when the STDB gets 31 days of data, the oldest day has to be deleted, since all we want on it is 30 days. We also need the LTDB to have the same records as the STDB; in other words as a record is inserted into the STDB it needs to be replicated into the LTDB.
Situation:
We are considering using Transactional Replication with the STDB being the publisher and pushing the data to the LTDB (subscriber). This should give us near-real-time inserts into the LTDB. From my reading of the BOLs, transactional replication creates a 'mirror' image of the publisher database. So, if I delete a record from the STDB the same record will be deleted from the LTDB. I don't want this to happen (if it does, our LTDB will end up with only 30 days of data since that's all that is in the STDB).
Questions:
Is Transactional Replication the best way to do it? Remember, I need the LTDB to have the same current 30 days that the STDB has, plus the previous 5 months or more.
How do I 'shut off' UPDATE from being allowed?
How do I stop deletes on the STDB from happening on the LTDB?
I appreciate any advise/comments/etc.
-Bill
