Tuesday, July 24, 2007

OLTP database (on SQL Server 2005) accessed by an online processing system and a reporting application as shown in the diagram.

1) The online processing system needs to access only subset of records (recent records).2) The reporting application needs complete set of records and should be able to access them in almost real time.3) For better performance, security and logical segregation – we need to have online processing system and Reporting application access different instances of the database.

Solution:To satisfy Requirement 1:a) Partition transaction table.b) Switch old partitions out when they are no longer needed. (Switching partitions out for getting rid of old records is much efficient than executing DELETE statement.)c) Online Processing System to access partition tables.

To satisfy Requirement 2:a) Create insert / update trigger on the Partitioned tableb) Insert / Update on Full Table for every Insert / Update on Partitioned table.c) Triggers aren’t invoked during partition switching of Partitioned Table – hence all records will remain in Full Table.

To satisfy Requirement 3:a) Setup Transaction Replication between 2 SQL Instancesb) Replicate the Full Table.c) Reporting application to access the replicated Full Table.

Questions / Issues in this approach:a) Is it advisable to use triggers in production systems. Are there any known drawbacks.b) Inserts / Updates to Partitioned Table have slowed down because of insert / update triggers associated with Partitioned Table.c) When SQL Replication is enabled – the performance of the system still goes down – Tested by having the distributor with Publisher and with Subscriber. Having the Distributor with Subscriber is found to be performing little better than having the Distributor with Publisher.

Your comments / opinions on this approach is welcome.

Are there any better approaches for satisfying the above listed requirements without hampering the performance of Online Processing System.