Home / server / Questions / 396911
In Process
scottm
Asked: 2012-06-09 05:30:55 +0800 CST

How do I data warehouse a set of tables and delete the source?

  • 772

I have a large table in MS SQL that I am having issues reading from while a third party application writes to it. My plan to resolve the problem is to move all of the data from one database and delete the data in the source database periodically. The problem arises when the table has too much data ( hundreds of millions of rows), but I need to be able to read all of the data for a rows which could top a billion rows. I don't have the ability to modify the source database because it is third party.

I'm curious what some options might be to help facilitate this. Is it possible to replicate a table in a way that when I delete the data in the source table, it's not deleted in the destination table?

sql-server replication

3 Answers

  1. There are a number of potential solutions to this problem. Picking the right one is dependent on your environment and your requirements, which aren't flushed out in much detail here.

    Based on your stated need to query the entirety of the multimillion (or billion) row table, I expect that you need to do some reporting on from the vendors' database. Here are some potential solutions that I would consider to meet that goal:

    • READ UNCOMMITTED: Execute your queries as READ UNCOMMITTED (e.g. WITH NOLOCK). This will ensure that the queries and the OLTP activities are not interfering with one another, though your results may get a little dirty if you're querying up-to-the-second data.
    • Snapshots: Create database snapshots for read-only queries. Refresh them on a regular schedule, or prior to running the reports (if they are ad hoc). If reporting performance is the only reason you want to clear the data out of the table, this might be your best solution.
    • ETL: Create an _Archive database on the same server containing the required table(s). Populate them from the prod DB with a daily/weekly/monthly archiving job that moves data into it from the prod DB, then deletes from prod after the insert is successful.

    A little more information might help us to better assist you.

    • 1

  2. THe standard approahc is to use an ETL script of some kind to make a copy of the data prior to deletion.

    • 0
    • Insert triggers that copy every new entry on creation
    • Scripts run at regular intervals or before deletion that insert non-duplicate rows.

    There's no reason that your data warehouse tables need to even be on the same server, much less tied to the live database with constraints. Deletion shouldn't affect the warehouse. Just have commands that add data.

    • 0