Reporting using Replication | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Reporting using Replication

We have about 200 databases on approximately 20 servers and possibly doubling
in the near future. These databases have identical schemas. We are running
SQL 2005, SP1, on Windows 2003. We are looking for a reporting solution, to move our reporting off production
databases, and have our reporting go against a secondary data set, where the
data used for reporting is near to real time as possible, where near to real
time would be less than 15 minutes, hopefully closer to seconds. The reports and queries run against this secondary data set would be strictly
read only. I have read up on log shipping and mirroring as reporting solutions, but in my mind, log shipping and database mirroring have too many drawbacks, and to me, are not a good fit for our reporting needs. I have read here and there that transactional replication is also an optional reporting solution, yet in speaking to a 3rd party vendor that provides transactional replication in their product, said that transactional replication is not a good fit for near to real time reporting since you have to pause the replication, then mount the database before it can be queried/reported against, take a snapshot of the replicated data, run the report, then after the report is run dismount the database, and then sync up the transactional data that has been held in a queue by restarting replication. If that is the case, then transactional replication would certainly not meet our reporting needs! How does one set up transactional replication so one does not have to jump through all these hoops to simply query or report on near, to real time data? Is it just as simple as setting up transactional replication and set your reports to go against the database that contains the replicated data?
Transactional replication: Yes, setup transactional replication and use the replicated db for reporting purpose but if your db is too big don’t do the snapshot… restore the db on destination server and when configuring subscription choose the option "Subscriber has schema and data"… Mirroing: Is also a good solution but do not use syncronus mode use asyncronus mirroring… Logshipping: is not the right solution because every time you restore the log you have to kill all the users from the db…. Mohammed U.
]]>