Thursday, January 5, 2012

AX 2012 - Run Reports without hitting the Production DB

Hi there!

My fellow Sunriser, Brandon George,  has written an amazing article about running reports without hitting the Production Database.

Form the article:

"Since the default .Net Business Connector configuration for SSRS is pointing back to the same AX instance, when the SSRS instance is executing the reporting and firing the Data Extensions to get to the correct Query object and therefore data, then in this standard approach, we are hitting the production database for your instance of AX..."

"The critical keys that enable this concept are, (1) having the production instance of your AOS and Reporting Server setup, pointing to your SSRS server role within AX. (2) Changing what AOS the SSRS server hits for processing data, by deploying a custom configuration file. (3) Log Shipping is enabled at on the AX Production database, so that the AX Reporting Database is kept up to date with live data."

"...That is code promotion, or change management. Why does this matter? Well simply put, it's because the model store that represents the application now lives as a part of the production database. This then gets replicated when any changes take place."

Brandon George is a Senior Technical Architect at Sunrise Technologies and I personally believe he is one of the few people in the world that has truly master AX. I strongly suggest to follow his blog as it contains valuable, innovative, and critical information about AX.

You can access his blog (and the article) from here.

Take care!

6 comments:

  1. Hi.
    I fail to see the problem with running reports off your production/live system. Can you shed some insight as to what the problem is?

    ReplyDelete
  2. Hi Steve,

    There is no really a problem when you do this. What the post is trying to say is that you have a choice (although not tested a 100%) to run the reports from a DB other than the production one. This is helpful when you have limited memory resources regarding processing power.

    I guess the only real drawback is licensing, but if you have the licenses and a low processing power, this option can be useful to divert processing from the Prod DB to an SSRS DB that will replicate some data.

    I hope this is clear ... ;)

    ReplyDelete
  3. Hi,

    I see a problem here, if you enable log shipping the 2nd database will marked as read only database and for getting the reports executed you need to have BC setup for which you need another instance of AOS.

    You cannot start AOS unless you have the database in non restricted mode because when you start AOS it inserts records in the tables.



    The other option you can do is by using 3 databases
    1. your production database
    2. your log shipping database
    3. restored copy of your log shipping database which is not restricted.



    Now connect your 2nd AOS instance to your 3rd database and point your SSRS to the 3rd database.

    ReplyDelete
  4. Jagjeet,

    This is an interesting approach to consider. Thanks for the feedback.

    ReplyDelete
  5. If you just need a reporting database, consider transactional replication.

    Mirroring is a DR solution or Log shipping , not intended for creation of a reporting DB. Snapshots on mirroring are kinda a way to make a failover server useful until it's needed for failover.

    Regards,
    Xavier Fernandes

    ReplyDelete
  6. There are many ways to accomplish the task either using Log shipping,mirroring,transactional replication.I think it needs to be tested before come to conclusion.For example transactional replication we need all the tables to have primary key ,without this it is impossible to achieve.

    ReplyDelete

Thank you for your thoughts. Your comment will appear in my blog shortly after review.

Have a great day!