1

I've been using transactional replication to mirror a table from one database to another and lately it's been a pain to keep running.

My goal is to simply keep an exact copy of a table in another database.

Table inserts are about 100 a day from the ASP page from which records submitted, up to a maximum of 400 per day on peak days.

The total records in the table wind up to be about 20,000 by the end of a period, when we flush the table.

The table has a few text fields, but isn't huge.

What's the easiest way to write inserted rows and updates to those rows to another database table on the same server with the same structure?

I think maybe replication is overkill, and it's a huge pain.

5
  • Kinda depends on what your goals are. I mean, replication is often used to keep another database up to date so if the primary DB fails, you can switch over to the other one, but that would be kinda pointless if they existed on the same server. So, what are you trying to accomplish?
    – user55703
    Oct 25, 2012 at 2:28
  • That particular table is used for other processes that pretty much lock it up, so I make a copy and work from that; that copy is never altered in the other DB, so it's pretty much a one-way push all the time. Replication seemed like overkill for one table, but it worked - until a lot of little things made it stop working, rebooting, etc. Had to reinitialize the sub a lot. For one table..
    – Caveatrob
    Oct 25, 2012 at 4:59
  • If you're really having serious lock contention problems on a table of that size/update frequency, I suspect you've got bigger problems and should just look into fixing that instead. However: you could add an insert trigger to the table that just also inserts on your "mirror" table.
    – user55703
    Oct 25, 2012 at 5:15
  • Alternatively, if you don't need the data to be to-the-second up to date, you could do an incremental backup of the table and restore it to another schema on a schedule.
    – user55703
    Oct 25, 2012 at 5:18
  • Honestly, for some reason I thought copying and moving the table to a different database on the same server would make a difference in processing speed and availability, but maybe I'm wrong. I read the hell out of the table and create thousands of records on it based on parsing text from it. But I don't know now if I've just been silly because they are on the same server. Is there a huge penalty for joining/processing files across databases? I keep the large files I generate from the table in question on one DB so I can keep the production system light for remote backup.
    – Caveatrob
    Oct 25, 2012 at 18:56

0

You must log in to answer this question.

Browse other questions tagged .