Background:
We have a very old legacy database that uses flat files, this database is our primary customer repository. We export text files every half hour (at the top and bottom of the hour) from this database and then SQL runs a DTS package every half hour (at the 1/4 and 3/4 hour) that imports this data.
Our SQL DB currently consists of two sets of tables, temp and production. The DTS purges the temp tables and imports the text files into the temp tables, it then runs a stored procedure that does a data compare and adds/modifies/deletes records in the production tables so the data is current (matches the temp tables). We have two issues that need to be resolved, 1) locking during the import/update and 2) several triggers need to be added to the production tables for automation purposes.
What I am going to do is setup a new DB and then replicate the production tables in the import database production tables to the new DB, and then put the triggers on the tables in the new DB.
This will solve:
1. Locking, because the locks will be in the new DB and replication will handle those issues without crashing the import processes.
2. Triggers will fire on a per update vs. trying to fire on bulk inserts (from the import, currently to create some of the triggers we are needing to use complex cursors to accomplish the tasks).
My Question: Is there a better way to accomplish this? Am I making things to difficult/complex?
Thanks in advance for any input, guidence, help.
Persistence....Nothing in the world can take the place of persistence. Talent will not; nothing is more common than unsuccessful men with talent. Genius will not; unrewarded genius is almost a proverb. Education will not; the world is full of educated derelicts. Persistence and determination alone are omnipotent. -Calvin Coolidge
We have a very old legacy database that uses flat files, this database is our primary customer repository. We export text files every half hour (at the top and bottom of the hour) from this database and then SQL runs a DTS package every half hour (at the 1/4 and 3/4 hour) that imports this data.
Our SQL DB currently consists of two sets of tables, temp and production. The DTS purges the temp tables and imports the text files into the temp tables, it then runs a stored procedure that does a data compare and adds/modifies/deletes records in the production tables so the data is current (matches the temp tables). We have two issues that need to be resolved, 1) locking during the import/update and 2) several triggers need to be added to the production tables for automation purposes.
What I am going to do is setup a new DB and then replicate the production tables in the import database production tables to the new DB, and then put the triggers on the tables in the new DB.
This will solve:
1. Locking, because the locks will be in the new DB and replication will handle those issues without crashing the import processes.
2. Triggers will fire on a per update vs. trying to fire on bulk inserts (from the import, currently to create some of the triggers we are needing to use complex cursors to accomplish the tasks).
My Question: Is there a better way to accomplish this? Am I making things to difficult/complex?
Thanks in advance for any input, guidence, help.
Persistence....Nothing in the world can take the place of persistence. Talent will not; nothing is more common than unsuccessful men with talent. Genius will not; unrewarded genius is almost a proverb. Education will not; the world is full of educated derelicts. Persistence and determination alone are omnipotent. -Calvin Coolidge