0

I have a production SQL instance and database using SQL Server 2012 Express. I need to archive (on demand) from this database to a different database on a different SQL Server 2012 instance (on a different machine). The archiving process requires that the original data is deleted after successful archiving.

I need to execute in real time and for it to be meaningfully quick to the user. Not much to ask really! The archiving is performed by a user from a browser screen calling a .Net service (in C#).

I am limited to Javascript client (Sencha ExtJS), C# on the back end server and SQLExpress2012 on both database servers.

I have been using a stored procedure on the source database which is called from a .Net service (C#).

The source data to be archived resides on a parent table and 1 or more child tables. The destination database contains tables of the same format. I am currently using Linked Servers to point from source server to destination server. I am using a stored procedure on the source database to execute a distributed transaction between the two databases but the transaction can take 30 seconds to 90 seconds to transfer 5000 parent rows and 10000 to 15000 child rows.

Is there a better and faster way of doing this? I have considered using table parameters on both servers but I have read that I cannot write to a table parameter within a stored procedure and I that cannot pass them between SQL instances.

theduck
  • 2,589
  • 13
  • 17
  • 23
user3049876
  • 23
  • 6
  • You need to first locate the bottle-neck, since there are several components involved. If you have a slow network, for example, changing the code would probably not speed up the process. – Abra Oct 26 '19 at 15:51
  • Are you expecting this to happen real-time? I would be doing this in a stored proc in such a way that the originating user flags the rows for archival, and the archiving is actually done asynchronously. It doesn't sound like the sort of thing a user should have to wait for. – TomC Oct 28 '19 at 01:21

0 Answers0