That's a very useful link. Thanks, Wayne.
We acquired a little over a dozen companies some time ago. Each billing system was in a separate SQL database. I'm transferring the databases into our main billing database. The main transfer scripts I use are during off hours and haven't been an issue. I've got 4 databases left to transfer.
We began offering a new service last month that has to be in our main database. A number of our existing customers have signed up for this service. As a result, Sales/Cust Svc have had to manually transfer customers, thus losing histories & 'misleading' some financial reports. I created a transfer button in the interface that calls the 'single-customer transfer' proc. I've been wrapping the procedure in a transaction to be sure if anything goes through, it all goes through, and if there is an error, I can roll it back (There is a ton of bad data in some of these other databases.)
For a single transfer, it did appear it would lock up the database during a single tranfer for 10-20 seconds. Not very user friendly, but I'd hear no complaints as it saved employees the hassle of manually transferring, and saved those of us fixing the improperly transferred accounts a few headaches. Plus, these existing transfers are down to 5-10 a day.
The single transfer proc was written so concurrency wasn't much of an issue, but I've been playing with the code to speed it up, and can cut the time to about 1/5th of the original transfer time. But the code changes would need to have no other transactions processed during the transaction.
I expect to have all of the legacy databases transferred withing the next three weeks, so this won't be an issue shortly. If wrapping the proc in a transaction will work, I think I'll stick with that, as even the optimized code is over a thousand lines.
Thanks for your time, Wayne. Much appreciated.
Dan
