SQL Server Performance

Options for duplicating a live database for offline reporting purposes

Discussion in 'Getting Started' started by tesuser, May 21, 2010.

  1. tesuser New Member

    We implement SQL based ERP systems and use SSRS as a reporting platform reading directly from the live database. The scope of our SSRS reporting has now grown to such a degree I feel it is appropriate to start reporting offline as I suspect the performance issues some of our larger clients are experiencing might be due this dual use of the live database.
    I aware of terms such as "replication" and "transaction log shipping" but I have no practical experience of these and any other methods of duplicating a sql database and the relative pros and cons of each. I would suspect the parameters of the duplication will influence the best option so in our case these would be duplicating databases of between 10-100 Gig on an overnight basis in both 2005 and 2008 standard edition installs.
    Any advice in this regard gratefully recieved.
  2. jpenniman New Member

    You're assumptions are correct. Using a live OLTP database for complex reporting has a significant performance impact on the OLTP application... especially in SQL Server.
    Before I get into reporting db options, another critical area to look at is tuning the queries for the reports. If you haven't already, you should start there before introducing a more complex and costly environment. Just because a query is fast on a 10GB DB doesn't mean it will be on a 100GB DB.
    Assuming you've fine-tuned the queries and database...
    I've used several approaches in the past, each with great success. Like anything else, there are different solutions that fit different scenarios. I list a few here, but instead of breaking out pro's vs con's, which are subjective, I'll simply list the caveats to using each.
    There are several things to consider when choosing a reporting database solution:
    • Cost: Initial and TCO
    • Hardware requirements
    • How up-to-date the reporting data needs to be... does it have to be real-time?
    • Are there transformations that need to happen?
    • Load/Resource usage on the existing OLTP without reporting
    • Load/Resource usage introduced by reporting
    The four broad "solutions" are replication, log shipping, database mirroring, and SSIS Jobs. There are many different combinations and setups for each of the solutions, each with their own pro's, con's, and caveats.
    • Realtime (as close to it a possible)
    • DB can be used to load-balance reads (not writes unless you use merge replication) from the OLTP is some cases.
    • Requires another server, some additional networking hardware(ideally), and an additional SQL Server license. Though, if you are in a virtual environment, this can be "bent" a little... Microsoft only requires license per physical socket... you can have as many VM's on a single machine with the same license.
    • More DB hours: you've just increased the complexity of the sql server environment.
    Log Shipping
    • Dependent on transaction log backups.
    • Not realtime, but can be near realtime (Microsoft recommends log backups every 2-5 minutes, but beware of the performance implications of this).
    • More DB hours: you've just increased the complexity of the sql server environment.
    • It should be on it's own server
    SSIS Jobs
    • Doesn't require another server. (though, your situation may need separate hardware altogether).
    • Not realtime
    • Depending on the complexity of the scripts, you may need a C# developer to create and maintain them.
    Database Mirroring is similar to log-shipping except that it's a little closer to real-time and has automatic failover should you also want your copy to double as a standby server (could help in justifying the need for additional hardware and licenses).
    If you choose to do any of it on the same physical server, make sure the database files are on different physical disks; if they share the same spindles in any way, you've not only defeated the purpose, but made matters worse. If you do consider the same server to reduce costs, understand first the CPU and memory utilization of the OLTP app and the reporting. CPU contention, just as disk contention, will not yield you the results you are looking for.
    What I typically do is setup replication. I configure the replicated copy as read-only and use it for all real-time reporting. I then setup SSIS jobs that read from the copy and denormalize and transform the data into a data warehouse for more complex decision reporting. These jobs, depending on need, are schedule anywhere from every two hours to once a day, week, or month.
    This offers the greatest flexibility and performance across the OLTP and reporting systems.
    As I said, there's no one solution, and you may find that each of your customers require a slightly different approach depending on their needs.
    If you need any assistance taking a look at a particular environment or script, I'd be happy to help.
    Good luck, and feel free to contact me if you have any additional questions.
    Jason M Penniman
    Milestone Technology Group, LLC
  3. satya Moderator

    An excellent & detailed explanation by Jason here, as they have covered all the aspects of information you are looking I would like to add about database snapshots facility too (if enterprise edition is used), which comes handy for such reporting based requirements to reduce the load on live server.

Share This Page