May 26, 2009 at 5:30 pm
Hi All
Just wondering how one could setup a reporting database which will mirror an OLTP database, but strictly for reporting.
Any ideas ?
Thanks
May 26, 2009 at 8:29 pm
Sure, there are a couple of options:
1) Database Mirroring and Database Snapshots (requires Enterprise Edition on the report server)
2) Transactional Replication - requires identifying specific tables (articles) to be published
3) Log Shipping and Standby
4) ETL Process to build data warehouse/data marts
Which one you choose really depends upon the business requirements and how much time/money you are willing to spend.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2009 at 3:47 am
I use p2p replication for my reporting server, this also enable me to have a failover server in the event of disaster on my prod server.
Works well but have the dev team on my back as they can't change column's without removing replication (SO).
As in the previous post there are a number of option (depending on the edition you have and your requirements)
Rgds
JL
May 27, 2009 at 9:04 am
Jeffrey Williams (5/26/2009)
Sure, there are a couple of options:1) Database Mirroring and Database Snapshots (requires Enterprise Edition on the report server)
2) Transactional Replication - requires identifying specific tables (articles) to be published
3) Log Shipping and Standby
4) ETL Process to build data warehouse/data marts
Which one you choose really depends upon the business requirements and how much time/money you are willing to spend.
Thanks for the reply.
Just a couple of points:
Database mirroring, this means that the entire schema from the principal db will be mirrored to a reporting db, problem is that the archive/historic data isnt needed by the reporting tier, this means that with mirroring, everything gets copied across ? is this correct. Also, the principals indexes/schema might not be suitable for reporting purposes. Any comments here, also I thought a standby wouldn't be available for use except when its brought into production/main server role in the case of a DR
Transactional replication: works fine, but from experience, locking/deadlocking problems on the part of the subscribers, especially when you have bulk updates and synchronization needs to be continuous.
Log Shipping and Standby: Will this not have same constraints as mirroring, also I thought a standby wouldn't be available for use.
ETL Process to build data warehouse/data marts: No transformation needed on reporting tier, just some of the tables for querying purposes.
May 27, 2009 at 9:55 am
For database mirroring, yes you have a full copy of the database on the other server that is unavailable because it is recovering transactions. However, with Enterprise Edition you have the ability to create database snapshots and you can create a database snapshot from a mirrored database, making the database available for reporting.
If you don't need all the data in the database available for reporting, then you really should be looking at replication (publishing only those tables you need for reporting), or building an ETL process (extract the data you need).
Advantages of using an ETL process is that you can build the reporting schema however you need for reporting.
And finally, the same setup can exist with log shipping as for database mirroring. Additionally, you can put a database in standby mode and make it available for reporting. At that point, you would not be applying any logs and you would have to catch up later.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply