Question on Reporting/Standby Strategies

  • We are in the process of building an new application that will call for reporting.

    What is the best method with SQL 2005 standard to maintain a real time reporting server?

    Would it be mirroring or using SSIS with the reporting server?

    What are the pro's/con's of each?

    Thanks

    Susan

  • That's a big question and it will really depend on the needs of your application.

    If the reporting needs to be 100% real-time and it will not conflict, running the reports against the OLTP system may be the least maintenance and work just fine.

    If your reporting will be a heavy load conflicting with your OLTP activity, having some kind of a standby server with a copy may be a good solution.

    If you need to have a separate database just for reporting, a warm standby may not be a good solution if you need different indexing or want to have different stored procedures in it.

    I would suggest you carefully lay out the specifics of what you are looking for before you try to choose a method of implementing it.

    Also, you mention real-time, but make sure that is what your users really want. I have found that many users like the sound of real-time reporting but for some reason freak out when two of them run the same report 10 minutes apart and do not get the same results.

    I have found that a good reporting strategy generally consists of real-time OLTP "production" reporting for maintaining daily work, and a data warehouse of some sort providing strategic reporting and financial reporting. You may want to spend the time to break your reporting into these two categories and come up with a strategy for each group.

  • Thanks, that was very helpful...

    Sounds like I need to pin down the actual requirements first.

    Thanks

    Susan

  • In SQL Server 2005 Standard edition you cannot create database snapshots and hence you cannot use it for reporting purposes as the mirror database will be in restoring state. You can create database snapshot only in Enterprise edition of SQL Server 2005. However you can use log shipping in SQL Server 2005 where the secondary db will be in read-only mode and can be used for reporting purposes..Refer these links for more info,

    http://sql-articles.com/index.php?page=articles/lship/lship.htm

    http://sql-articles.com/index.php?page=articles/dbmrr.htm

    [font="Verdana"]- Deepak[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply