SQL Server replicating data to an archive

Question

I have the following scenario:

Multiple SQL Servers where production machines log about 80GB of data (mostly images) daily in a database (each machine has a server with one database). Since these servers have about 4TB of space the amount of days they can store is limited. So the data on these servers is overridden frequently. But we want to make reports, that date back to up to 2 years.

I could code stored procedures to fetch all new data entries and copy them to a backup SQL Server which has more space every 2 hours or so. But I am talking about a database with approximately 25 tables and I think there might be a better way.

Do you think transactional replication or log shipping might be better options in this scenario?

If the SQL Server on the production site roles over the data (I have not yet had the opportunity to check how this actually works on the systems), I don't want the backup server to replicate this. The Backup server should keep all records and only add new ones.

Any advice on this would be appreciated.

Answer 1

if i understand correctly log shipping and transactional replication is not an option because they basically update on the other side whatever you do on the primary server. you will get an updated database and if the info is overridden so is the secondary database. i would maybe try create a planned DWH database that i would copy the fresh data every period of time in some way.. maybe SSIS or bulk insert.

looks like it'll take some work like understanding when is data being overridden: every 5 minutes? at random times when they update it in the app? and then decide accordingly on what to do(maybe even use a trigger on insert or update to bulk insert it to a DWH table in s different server). I hope this helps. keep in mind if the updates and insert happen often this triggers may cause performance issues in some cases if done constantly.

Answer 2

Apart from developing ETL process using SSIS or Store Procedure etc, if you want to use Transaction Replication there is option to ignore DELETEs in replication. Provided the archival on primary server is being performed using DELETE statements.

When you create a publication and add articles, go to Article Properties and in Statement Delivery section choose "do not replicate DELETE statements" against Delete delivery format.

Answer 3

Think Short Term/Long Term

Long Term

For the sake of argument, let's just say "what if" replication "might" be the better option than a straight copy of the data using stored procedures or some other T-SQL. You would need to create a test/dev environment to replicate your data and try to include load testing to see how it might affect production. And that's a lot of work, possibly new hardware and licensing as well. Here I'd do something like this article and not replicate the deletes:

https://www.mssqltips.com/sqlservertip/2520/options-to-not-replicate-sql-server-delete-commands/

But, to me, replication is more of a long term solution--if it's a solution at all.

Short term

I think how can we copy all this data asynchronously in SQL Server--all at once--to make the copying more efficient. SQL doesn't have any built in way to do this, so I've traditionally used the Windows start command to call sqlcmd.exe for all the procs or T-SQL I need to call at once/asynchronously.

And, everyone seems to have a different creative preference for making async SQL calls. I happen to like what the author did in by making an async stored procedure that uses the SQL job engine under the hood:

https://www.motobit.com/tips/detpg_async-execute-sql/

At the end of the day, if you can stack 25 copies of your data all at once (followed by the deletes), and it doesn't adversely affect performance, I'd say that's fairly efficient at getting the job done.

We've been following something similar for copying our hospital's audit data to a reporting database for over a decade now. The original audit database stays lean and performant while the reporting database continues to grow and grow exponentially.