I have a couple of questions regarding a framework that I may want to change regarding the moving of data between databases on the same SQL Server.
I will provide some background first, then describe my current method and then maybe you can help me figure out a better way to achieve this goal
I have a multi-user win32 application that uses SQL Server extensively. What happens is that through user interaction, system processing, etc... rows are inserted, updated and deleted pretty much constantly in the database.
As time goes by the database grows larger and larger. This is mainly because I need to retain audit data for tracing purposes regarding transactions, inventories etc... So I need to be able to say who did what and at what time. This is available through reports that are embedded in the application so they use the same ADO datasource as the main application.
Users complain after a while that some queries take a long time to run, despite regular index and database maintenance, simply because the database grows larger and larger. This also has some impact when they need to send me a database backup to investigate problems, find bugs etc... So in order to prevent this problem, I have designed an archiving functionality outside the application.
There is another database for archiving purposes. It's an exact copy of the application database in terms of schema except for identity inserts which are OFF (since I want ID's to match).
What we designed is a simple stored procedure that will look into the live database's schema, find tables that have certain timestamp fields that tell us that the record has been deleted. If we find one of these fields, then the table is eligible for archiving. For each of these tables, we will go day by day and build 2 dynamic SQL statements (using EXEC): one to INSERT INTO Archiving(SELECT * FROM LIVETABLE WHERE FIELD <= @MaxDate) and one to DELETE FROM LIVETABLE WHERE FIELD <= @MaxDate. Basically we want to archive all tables, but one day at a time (keeping at most X days of live data in the live database for reporting purposes, which is configurable by the users), and waiting 2 seconds between each table so that we can give some leeway and reduce locks on live tables for the application which has to keep running during that time.
This stored procedure can be programmed using a simple Job with 1 step at a user's will.
As you can imagine, this works great when this is set up when the live database is first created. However, most of the people I try to make implement this in their environment have waited about 2/3 years on average to implement this method. This means that the first time they want to run it, it takes days to run. Some tables have about 4,000,000 records by then and only 1,000,000 are to be kept so for each day, we need to move X records and this takes time just for locating the records to move
So my question is this: how can you implement a solution that can work on multiple client sites to effectively move data from one database to another (on the same server), not just copy, but move, ie add to the archiving environment one and remove from the live environment without impacting performance of the live application too much? And it has to work on 2000 and 2005.
Thanks a lot and don't hesitate to ask more questions regarding how it's currently designed if it is unclear.