In my company we are going from desktop application to online application. For that reason, we are migrating our data from individual desktop databases to online SQL server database. The schema for online and desktop databases are different. That's why we have to write some SQL procedures to migrate data from desktop db to online. There are about 10 stored procedures to transfer data. Tables are around 25. 3-4 tables have around 800K to 900K data for each desktop databases. We have about 2000 individual databases to transfer.
Our process is as follows:
- all the .bak files will be in a folder
- there will be a windows service
- which takes one database at a time, restore that database and transfer data from that database to online using those 10 stored procedures.
- then delete the desktop database
- thus the process goes on for all databases.
Now comes the issue. While doing this process, we are getting timeout for any of the random stored procedures. The timeout is happening absolutely random. May be it migrated data for 5 databases and timeout for one of the procedures while loading data for the 6th. Another time may be it is for 10th or 12th database.
To overcome this problem, we have increased SQL server memory, clear the DBCC cache after each database migration. But it still happens, although the timeout frequency decreases.
Also note that, some procedures do some calculation on migrated data and loads another table.
In this circumstances, I am really looking for some help. I have tried best to describe my situation. Is there any way, we can avoid this timeout issue?
Thanks in advance.