| Author |
Topic |
|
jholovacs
Posting Yak Master
163 Posts |
 Posted - 2007-09-11 : 10:31:33
|
| In an ongoing effort to offload non-production work from our production system, I implemented log shipping to a reporting server, where the databases copied over were in standby and people could query them to their heart's content. The goal here was to have near real-time data in a read-only state that people could query for fairly resource-intensive reports without impacting production performance (which is hurting).I probably should have known better. Pretty soon I got complaints about their connections being broken in the middle of long-running queries. Sigh, ok, I disabled option to force users to log off during the restore.Then I started getting complaints about the databases not refreshing often enough. Sure enough, people are logged in to these databases 24-7, and not transaction logs are getting restored, and my "near real-time" databases are anything but.So in my search for the right solution, I came across Database Snapshots (I'm running 2005) and I thought I may have discovered the Holy Grail (Insert choir of angels music here) of my reporting needs... until I looked into it a bit further.Snapshots look like they'd be perfect for my reporting needs, but the big problem is that they have different database names. It's fairly straightforward to create a "rolling snapshot" where I have a new snapshot available every 15 minutes, and clean up the old ones after a certain amount of time, but all my current ad hoc queries, functions, and procs won't use them, and honestly I'm stumped as to how to dynamically reference the current snapshots of the production databases in a simple way.Dynamic SQL for all my queries is not a viable option. I'm fairly new to 2005, and I'm hoping I've overlooked some built-in functionality that will allow me to reference the current snapshots in the SQL with minimal changes to the infrastructure.Can I get some suggestions?Thanks,Jeremy Holovacs |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-11 : 12:15:03
|
| I find it amusing really that users need realtime data when in fact they only need to query like a day's back at utmostSure it sounds great to hear we have a reporting server that has realtime data... Yes! But if you look into the requirements, the maximum users look into the data is 1 hour or at the end of business hours...But to be constructive, how many of the tables do you really use to generate the reports? If it's just a couple of them, try to look into replication (scheduled transactional)--------------------keeping it simple... |
 |
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2007-09-11 : 12:23:42
|
| Eh... the problem with transactional replication is that 1) it's a larger load on the production machine, and 2) it replicates to a read/write database. The first one is probably something we could live with (although 100+ GB of data replicating is no small deal), the second, not a chance. If the data gets changed by a developer "trying something out" (and don't get me started on change control) our reports are potentially skewed, and that puts us in violation of our contractual obligations, which the business owners classify as a "Bad Thing".Long story short, my requirements are as follows:-minimal load to production system-read-only db's on the reporting server-near real-time currency on reporting server-24-7 availability on reporting server-automated (no way I'm going to babysit this thing)If someone has a suggestion that meets this criteria, I may name my first born child after you. |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2007-09-11 : 13:28:11
|
| Thinking about this, what I'd really like is a way to alias databases for a connection, a la object-orientation:set mydatabase = master.dbo.func_get_database(<Query to select most current snapshot name>)--this would (in my world) reference the specified database as [mydatabase] for the rest of the queryselect t.valuefrom mydatabase.dbo.table1 twhere t.id=5I think Oracle can do something like that. It seems that SQL Server should be able to.___________________________Geek At Large |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-11 : 15:48:00
|
| "I find it amusing really that users need realtime data when in fact they only need to query like a day's back at utmost"Indeed. We used to have a "Enquiry database" to take the load off the Transaction Server which was freshened up overnight with "yesterday's data".Managers used to complain ... I would ask them how they would know that querying the "live server" was more up to date - pointing to their Secretary's intray overloaded with the day's post etc.Eventually I capitulated and then the did actually realise that the changing, inconsistent, view during the day was fa worse than the known-stead-state copy of yesterday's data taken at a time when everyone had gone home, and was thus not changing-under-foot!"-minimal load to production system-near real-time currency on reporting server"inverse squares law (or somesuch!) on those two. The more near-current you want it the more hardware you will have to throw at the problem to facilitate the "every transaction replicated" instead of "once a day snapshot" (to name the two extremes)"-read-only db's on the reporting server"Can you achieve that (or a read-WRITE database) by only giving the users SELECT permissions?Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-11 : 15:59:34
|
i'm a stickler for correction so bear with me...1. have you actually measured this? or tried it even in your test environment? knowing sometimes is different from actual  , and like i mentioned if there are only a couple of tables used for reporting, i recommend replication, sometime the other tables are just 'system tables' which are not updated or rarely updated like country codes2. yes it replicates to a read/write db but direct changes on the articles should be forbidden unless it's replication...else you get replication error if it hits the right spot. That's the beauty of read/write, you can create more stored procedures for the reports--that's why you have security in placeyour dev has access to production and tries something out? good luck!answer: db security--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-11 : 16:07:46
|
| "your dev has access to production and tries something out? good luck!"That results in a Dead-Dev here!Oh I do like a good public flogging! |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2007-09-11 : 16:34:35
|
| All would be well if I had started with this, but me and the other DBA we have inherited this particular... problem. Screaming bloody murder is helping to reduce the developers mucking about in prod, but it doesn't get them out completely. A good part of my time is spent cleaning up various parts.Unfortunately I really don't have input regarding the requirements for the reporting server, I just have to make them happen. Some of them showed up only after we'd started down this path; I'm sure everyone knows what that is like. Security is not a realistic solution; everybody and their brother has write access to the server, and I wil not be able to change that.Replication is being discussed, but the writability is considered (and actually is) a serious problem. Snapshots could be the answer, if the technology were a bit more mature, and we could reference the snapshots by alias. It seems to me that would be a logical destination of snapshot technology, but apparently I am wrong. Sigh...___________________________Geek At Large |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-12 : 02:00:28
|
"Replication is being discussed, but the writability is considered (and actually is) a serious problem"So this is currently a Read-only database.If you introduce replication why can't you ONLY give SELECT permissions to it? Same difference isn't it?Now please don't tell me that everyone has SA permissions on the whole server?  Kristen |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2007-09-12 : 07:47:09
|
| of course they do... we DBA's are trying to put some sanity back into that, but it's a vanguard action at best... every step forward is a half-step back. We might get there eventually, but right now it's not ready for us to implement true db security.___________________________Geek At Large |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-12 : 14:13:38
|
| If everyone has SA access then I can't see any way to sensibly be of any help.That needs sorting out first.Kristen |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2007-09-12 : 14:43:10
|
| Perhaps if we could go back to my original question: Does anyone know of a way to implement usage of a rolling database snapshot?___________________________Geek At Large |
|
|
|
|
This may be a hopeless cause...