February 27, 2014 at 7:10 am
Ok... without going into lots of detail, here is what I have.
i. Users log into our web reporting system and view reports, each of which "slice and dice" data in different ways.
- The reports all use SQL queries to extract data, much like SSRS and Crystal Reports, etc.
ii. We now have a request to extract a "generic" view of the data, regardless of the report being viewed. To do that, I need to capture the results of the report and make the records available to query a "generic" table.
- There can be anywhere from a count of 5 records to hundreds of thousands of records, depending on the report and date range.
iii. Due to security, people logging in to view report(s) should only be able to see their data and not see anyone else's data. This should not be an issue, as the report users do not log directly into SQL server, nor can they log into SQL Server via their web logins.
My current new process is as follows:
- Convert the existing reports, wrap the results into a "temp" table and put it into TEMPDB (tables portion of TEMPDB, not true "temporary tables"). I figured, in case SQL Server goes now, we will "wipe out" any existing TEMPDB tables and start from scratch. Saving existing data from a previous report in case of a server outage is not an issue.
- After a certain period of time (TBD, perhaps 2 hours?), any "older" tables will be deleted.
- Upon SQL Server startup, a SQL Agent job will run to assign various accounts full DB_OWNER permissions in TEMPDB.
The biggest challenge was to make the report data/specific records available to query the "generic" table. The generic table query/output will probably only be done 10% or less of the time reports are run.
My question is this:
- Does using TEMPDB in this fashion make sense?
- Or would it make more sense to create a "generic" database to do this?
- Can anyone think of any consequences of using TEMPDB this way? [i]Note: We do not anticipate that this will add too much of an extra load on TEMPDB and the way it is configured on a separate HD[/i].
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
February 27, 2014 at 8:26 am
Since tempdb is a total dumping ground, I'd be seriously hesitant to use it in this fashion. I'd look at some other mechanism for caching this common data, possibly not even using a database, but putting it in memory on a server that would allow people to pull from it there. But, if I had to keep it as tables, I think I'd put them into a separate schema within the database I'm querying rather than a completely different database. That would make management a little more clear. But, if that's an issue, then I'd go for the separate database. But I'm pretty sure I'd shy away from tempdb.
Oh, and I'd look at tuning up your tempdb. Having just one additional disk for it might not be adequate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply