We are looking for implementing Transaction Replication for creating a separate reporting database for our large SQL Server 2008 database. The source database has estimated size of 100TB for 1 year - 60% of the data is "insert once" and "reference only (no update)". There are about 8-10 very large tables estimated to have few terabytes of data (We plan to implement partitioning and filegroups creation of I/O balancing). The tables have average rowsize ranging between 80 bytes to 3Kb and on an average the maximum no. of rows inserted per day could be 100 transactions X 4 million users in a single table.
Will transaction replication suite this kind of database for reporting database creation - (where Transaction rate is not very high but data storage needs are high)?
We are also thinking of geocluster solution for database HA/failover. However, not sure if geo cluster will provide read only access to replicated database in windows cluster - which may be used for reporting purpose.
Any pointers in this area are highly appreciated. Thank you.