Hi list,

I have a data migration script which could be run by either our install program or manually from QA. This script will migrate data from old schema to the new schema.

We'd like to make sure people will NOT run this script simultaneously from different places. e.g. one from install program, while the other is running it from QA etc. What do you think the best way to get it done on the db level? I am thinking to create a table data_mig_status to control data migration concurrency, ie. whenever the script get run, insert a new record to the table data_mig_status and lock the table immediately so no other record can be inserted. So if you can't insert a record to the table, then it means somebody else is doing the migration now, so you have to wait that finish first.

How do you think my idea? Any better approach?

Thanks a lot,
Elaine







MS Sql Server LazyDBA home page