SQL Server Performance Forum – Threads Archive
Database Archiving…..
Hi,<br /><br />I have 2 Servers running MS SQL7 SP4, Standard Edition.<br />Merge Replication is setup, Server 1 as Publisher and Server 2 as its subscriber.<br />The subscription is setup push from the Server1 to Server2, and it work either way, One is active and other is its backup.<br /><br />The replicated database which has nearly 80 tables, modifying/updating data on daily basis. I want to establish a new Server having hardware RAD-1, for archiving of the data.<br />So that different reporting can be done on it<br /><br />Analysis is that <br /><br />1. 60 tables only got data changes + addition.<br />2. 20 tables are those which only append data, there is no way to do changes in them.<br /><br />I want to replicate 60 tables by making a SP which makes a duplicate copy every night on the Archiving Server.<br /><br />+<br /><br />I want to transfer all data from the 20 tables which verify the condition (GetDate() – 3),to the Archive Server.<br /><br />Please guide….., Need to have your ideas<br />Thanks..[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
Since writing DTS script code to determine the changed (inserts and updates) data for the 60 tables is going to be a nightmare, I would suggest implementing transactional replication. For the 20 tables, transactional replication with a row filter on your date column will do the trick. You may also want to use stored procedure replication – the Distribution Agent will executes the same stored procedures that you used for your data modifications at the subscriber with the same parameter values. Also consider setting up the Distributor on Server2 (currently being used for the backup copy)
Nathan H.O.
Thanks for an early reply. Would it be ok to implement transactional replication on the two severs, as Merge replication is already there. It think it will degrade the performance of the two Server. Infact at present two Servers, 1 & 2 are setup along with Merge replication, At an INSTANCE any one of them is online and the others act as backup of it. They can be switched backup and forth to distribute the load. Like Server 1 is online for a week/month
And then Server 2 is online. http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=490 I need to implement a new Server for Archiving purpose to minimize the database load on the current 2 servers. There is no operation from midnight to 6.00 am morning, so there should not be problem for running a SP at late night. I seek to do things at the New archive Server and does not touch much the Current Servers. Need ideas…
Thanks.
With one of the servers acting as a backup (or inactive) server, merge replication was not a wise choice initially. But I think you did this because of the periodic changeover issue for which you might be "forgiven". Once again, although writing a stored procedure to determine the changes in the 60 tables and filter on the 20 tables is possible and may even work within your 6 hour window it will not be as "in-built" and efficient as transactional replication +/- row filters. If you decide to take the stored procedure option, think about:
- SELECT query performance from the 80 tables.
- INSERT performance on the target tables as they grow daily; file growth, index fill factor issues etc.
- INDEX issues on the target database – you said it will be used for DSS/OLAP/reporting.
- The extra OVERHEAD of tracking the daily changes in the 60 tables – it will definitely add to the load on the server. I am thinking about the complex triggers and unnessary tables required for this.
- The perfomance issue of running distributed queries – linked servers, rowset functions etc.
]]>