Monday, January 21, 2008

A Reporting System Architecture - SQL Server Central

An interesting use of Database Mirroring to create a low-impact reporting solution.

These seemingly simple requirements added more complexity to the project than one might think. We mulled over numerous ideas and varying solutions to satisfy the business needs. Our final solution involved the use of a staging database setup on the reporting server (a seperate box) that is populated via SQL Server mirroring. ETL Scripts are then written in SSIS that utilize a snapshot of the mirror database to denormalize the data and populate two reporting databases (details below). The solution turns out to be elegant, to perform well and has basically zero negative impact on the current OLTP system.

A Reporting System Architecture - SQL Server Central

1 comment:

Unknown said...

the system architecture is too complex