MS SQL 2005: update "history" database with new/updated info from other database

[MikHo]

Hello experts!

I have a a pretty complex question for you (if you can solve it )

Background:
SQL Server 2005 standard edition is used.

I have one database used for updating information about printers and their respective printouts done by different users.

There is also a log table where ALOT of information is stored ( we are talking about 300 rows / 15 seconds ).

The customer of mine is now requesting another database server to be setup to run their reports from, so the production database can focus on the users.

Since the ticket_log table is updated with so much information this table is truncated regulary where the oldest data is deleted. (happens once every 2 months)

The "history" server should have all information from the full year or so ( cleanup intervall is not decided yet ).

So what I would like to accomplish is updating the "history" server once every night with new data from the production server. (im thinking in the lines of an SSIS package)

Edited information (example: number of printed pages / printer) should UPDATE the information on the history server.

New information (example: new rows in the ticket_log) should be ADDED to the "history" server and stay there even after the ticket_log table on the production server is truncated.


I considered log shipping at first but that will update the whole database with all changes on the production server.


Hope you understand what I'm looking for but if you are interested in helping me with this and need more information I am more then happy to provide this.

Hope you can help.

thank you in advance.

[rmiao]

You may need copy rows via linked server in scheduled job.

[MikHo]

Thanks for replying,

I have also thought of linked servers and that it should be the best way to go but I have also thought of log shipping the production database to the second server and then copy rows from there.

The thing that I havent worked out yet ist the T-SQL or what to use to do the copying.