by Bill Wunder

Finding a balance between a Production SQL Server's well being and the never ending and always changing needs for internal  support and project development folks to easily get at the data is difficult.

In an insipient application environment you might find a frightening number of folks that have direct access to tables even though you've consciously and carefully built the application to use only stored procedure access. The extreme example here is when the "sa" password is common knowledge within the shop or when a high percentage of the affected internal users are members of the sysadmin server role or have membership in a database role such as db_owner or db_datareader or db_datawriter resulting in exposure of the dataset to any imaginable - and even some unimaginable - queries.

Ideally what should happen is that the queries folks need to monitor and manage the application and support the customer are created as a normal and planned front loaded aspect of the development process. In the real world, the time constraints on delivering the project on the committed date or the urgency of identifying and correcting a spurious problem don't lend themselves to a disciplined and full featured development process. As an extension of those realities, it is not uncommon to experience well meaning and dedicated people wrecking havoc on production systems over and over as they reuse poorly formed queries to slog through volumes of data with little or no regard to the consequences of their searches and manipulations on production systems. In fact, in far to many cases the person writing such queries has inadequate training and experience to even realize that they might be affecting the production systems.

As the application or the shop in general if it's a shop with ongoing new product development matures the natural trend is toward reducing the number of users with relatively unfettered access to the production system. Typically, the onus is placed on those folks with the best matched skills and experience to construct and execute queries for others in the shop. If those experts happen to be the same folks responsible for writing the production database queries and fulfilling the more formalized line of business reporting requirements the results in a hidden demand on the already inadequate time resources available to the expert staff. They will be tasked with requirements gathering, development, testing and supporting a layer of ad hoc reporting that is not budgeted in the planning process for their time. To make it worse, the urgency of the ad hoc needs is invariably such that what ever work is in-hand must be interrupted to service the one off needs. As we all know, an interruption that requires as little as 5 minutes can easily result in a far greater loss of productive time on planned tasks.

One thing that is helpful is to define these queries in stored procedures and deploy them to an intranet site. This allows the expert to construct and test good queries that won't clobber the production systems and at the same time empower the interested user to execute the query on demand without the need to interrupt the query developer with each request to re-run the query. What is not helpful about the approach is the extra time and resources it takes to actually get the query deployed to the intranet. From practical experience, it is frequently easier in the moment for the query developer to avoid deployment and in so doing set them self up for additional interruptions in the future. Somewhat pennywise to be sure, but in rapid application environments, who's got time to care?

If any of this sounds familiar, SQL Server 2000 has actually delivered something that can take you light years ahead in your ability to support one off and ad hoc reporting and data manipulation. It's true! Once a developer has a satisfactory query or stored procedure they can deploy the query to the intranet with an adequately formatted output in about 30 seconds by using Reporting Services. No need to modify an asp page or worry that someone might modify the query by giving it to them to run in Query Analyzer. You still want to stay with the model of using only stored procures to help anyone resist the temptation of  changing the reports rdl commandtext or sneaking into a Query Analyzer somewhere to exploit those select permissions and grant only execute permissions and only to domain groups or database roles having the interested internal users as members. If you stick with these simple rules, administration of the reports using the browser based Report Manager can be safely delegated to someone other than the database expert. This will empower those internal users to decide who should and should not have the ability to access particular reports, SQL Servers, and database.

The possibilities for using Reporting Services in on off reporting are vast and the advantages are large. Reporting Services can help you minimize the pain points for one off and ad hoc queries. Using the Report designer can be the first step in getting your T-SQL experts more familiar with Visual Studio .Net and there further your organizations preparation for SQL Server 2005. As your people gain exposure to what Reporting Services can do: the presentation formats possible from html data grids and charts to XML and .pdfs files, the role based security and securable virtual folder hierarchies, the scheduling flexibility from on demand re-query to scheduled caching to static snapshots, even the ability to schedule and deliver email subscriptions or easily place a result set on a file share I predict you will see an explosion of interest in Reporting Services for your "real" reporting needs, so be prepared. Perhaps best of all, from installation to design to deployment to administration, Reporting Services is easy and now that SP1 is at the door there should be nothing to hold you back from letting this awesome tool go to work for you. There just seems to be nothing but upside to using Reporting Services in the One Off and ad hoc niche.

Now I don't want you to think I'm saying Reporting Services will handle all your ad hoc query needs. There will still be that genre of queries that only need to be executed once or twice on a server. I can see no reason to deploy a query to the intranet reporting server if it will not be reused. In most other cases Reporting Services can simplify one off data retrieval and presentation while allowing you to maintain a stable production environment, even for queries where usage is light. The convenience will simplify the lives of those busy people around you. If you can help your co-workers simplify life a little they'll all be a little happier and so will you. How can you go wrong?

Bill