Database access for SQL Server business and reporting users

SQL Server business users and reporting users have a common goal: a successful business. Their roles, however, are different as they compete to capture data. To successfully report against transactional databases know your hardware resources, CPU scenarios to consider and options to take advantage of such as SSIS. This tip outlines recommendations to create the most efficient environment for all users.

SQL Server business users and reporting users share a common goal: a successful business. Business users capture

the line of business data critical to place orders, assist customers or schedule a service. Once that data is captured and the product or service is delivered, the role of reporting becomes of equal importance. Typically, reporting users focus on another portion of the business to determine business trends based on the data collected. Their reports reveal potential opportunities to capitalize on new business.

Although it's for different purposes, the line of business users and reporting users need to access the same data. In many small and mid-sized organizations they contend for the same resource: the database. A worst case I've observed is that in some cases the reporting users are able to stop the line of business users dead in their tracks when processing a single report. Ultimately this leads to frustration between the groups while working to achieve their individual goals.

In this tip we will outline recommendations to report against transactional databases for the lines of both business users and reporting users to operate in an efficient manner. We'll cover the following topics:

 

  • Understand requirements, educate users and determine options
  • Hardware resources for a reporting server
  • SQL Server upgrades
  • Single server CPU configuration
  • Pre-aggregate data
  • Push data
  • Scheduled SSIS extraction
  • Nightly detailed reporting
  • Analysis Services cubes on relational databases

Recommendations

 

  1. Understand requirements, educate users and determine options
    Work with the users to find out the reporting requirements. What you will probably find is that some reports are more time sensitive than others. Also understand who will be using the reports and find out how they capture the data today. Then try to group the reports into categories and outline for the users the options available with the associated time and capital expenditures. Understanding the user requirements and having the users understand the options will hopefully lead to a successful outcome.

     

  2. Hardware resources for a reporting server
    First and foremost if a separate server can be setup to support the reporting needs then by all means setup a dedicated reporting server. The relational model can still be used on the reporting server and data can be migrated from the production system to the reporting system based on restoring full backups on a daily basis.

    Another alternative is to use log shipping which can be setup to issue transaction logs on a 15, 30 or 60 minute basis on the line of business system then restored to the reporting server. From a technical perspective, one stumbling block is going to be stopping the reporting users when a transaction log is restored, so you may want to coordinate the restore process to occur hourly or greater. For both options, be sure to properly license the reporting server and consider this server as an optional backup server in a pinch.

     

  3. SQL Server Upgrades
    As you upgrade your SQL Servers to 2005 consider rebuilding your old SQL Server as the new SQL Server 2005 reporting server if it is reliable enough to support the reporting users. If you can upgrade the CPU, memory or disk subsystem at a reasonable cost, consider that as an alternative as well to expedite the reporting process.

    Another upgrade approach would be to take the time to perform capacity planning. Determine if it makes more sense to purchase two small servers rather than one large server. As an example, buy a 2 CPU server with 2 GB of memory and the needed storage for the line of business application and then buy a separate 2 CPU server with 2 GB of memory and the needed storage for the reporting users. From a SQL Server per CPU licensing perspective the costs should be the same. Therefore, explore the hardware and Windows 2003 licensing cost difference with your vendor and see if it makes sense.

    As a final consideration, in SQL Server 2005 consider the Workgroup edition to determine if it meets the line of business and reporting requirements when only the relational engine is needed as opposed to the Reporting Services or advanced BI needs.

     

  4. Single Server CPU Configuration
    By default the max degree of parallelism (MAXDOP) is configured to use all CPUs for parallel processing. On a 4 CPU machine one option would be to configure the MAXDOP to 2 in order for 2 CPUs to handle reports. The remaining CPUs would operate independently to address the line of business needs. The only obstacle here is determining the query cost when the 2 CPUs are used for parallel processing. So review your reporting query plan to determine the correct cost.

    Although this is a means to fine tune how SQL Server uses the CPUs, this may not completely resolve the issue. We may need to use the currently presented options along with this next set of options.

     

  5. Pre-aggregate data
    If the users do not need real time data, one option is to pre-aggregate the data on a regular basis (i.e. every 15, 30 or 60 minutes) and write the summary data to a table. Then the users could query the aggregate table to fulfill their reporting needs

     

  6. Push data
    A similar option to pre-aggregating the data is to execute the needed queries once every hour and push the data to users either in an Excel format email or a static web page. This gives the users regular access to the data and saves the time of running the reports because they are delivered directly.

    To help expedite this process, review your current indexes to determine if any can be added to the relational model to improve data access without negatively impacting the line of business application.

     

  7. Scheduled SSIS extraction
    Another available option in SQL Server 2005 is using SQL Server Integration Services to extract data to a separate database and perform ad-hoc reporting. Based on data volume and the frequency of the extraction, this may be a viable option. Depending on data size, the amount of necessary historical data and restraints on the licensing budget, another option to consider is the SQL Server 2005 Express Edition. Data can be extracted to an Express Edition and then reported on as needed. In considering hardware and database limitations imposed with the Express Edition, this may be a good alternative to retain a day's or week's worth of data for reporting purposes.

     

  8. Nightly detailed reporting
    If reporting is intensive and a day's delay is acceptable with the users then consider running all of the reports at night after the nightly processing and backups. One option here is to identify indexes necessary to support the reports. Decide whether the creation and drop of indexes will save time and not negatively impact the line of business users.

     

  9. Analysis Services cubes on relational databases
    Keep in mind that if detailed analysis is needed, SQL Server 2005 Analysis Services cubes can be built from a relational data model. To help performance, the cube can be scheduled to be built during off hours on the relational database and stored on a separate server. Although this is feasible, I would highly recommend using a separate Analysis Services server to support a large number of users, a large amount of data and to process complex logic.

Balancing the line of business and reporting needs via a single database is a requirement for most organizations. It is just a matter of understanding the user requirements, available options and the time\budget constraints. A final solution will typically require a combination of options discussed in this tip in order to resolve the business needs. Good luck!

About the author: Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.

More on SearchSQLServer.com
 

This was first published in September 2006

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close