I saw a note recently where someone had put up a new server in their environment. They build some ETL processes and replication in a short time to move data from the primary server to this new reporting instance. This was done to accommodate a few power users that wanted to write their own ad hoc queries, which had previously been causing performance issues on the main server.
Is this a good fix? Does it make sense to spent the $$ and time for another instance for a few users?
I saw a comment on the post where someone had noted that some performance tuning might have been a much better investment. That or limiting the access from the power users to prevent them from causing issues with large queries that might not be efficiently run.
Like most everything, I think It Depends. I've used this in cases where the ad hoc queries help the business. Either someone is running what-if type scenarios that can be used to feedback information to management, or they have the political power to make someone think they are. In either case, spending money on a server and a little time makes sense for me.
However not every power user, or ad hoc query individual should have an instance. Keeping more than one or two instances in close sync can be an issue. Users that aren't familiar with how to query data, or even use a tool like Tableau, can be a huge drain on resources. There is also the management of additional instances. If you double your number of production instances, you might end up spending more time patching, monitoring backups, etc., than you want to.
I think this can be a cheap fix in limited use. It's a tool that I'd recommend DBAs keep in mind for heavy users, but not one that you should reach for quickly. Make sure it's a good fit, and it really implements a fix. After all, some performance tuning on your regular server might do the job, and it benefits all users.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.
You can also follow Steve Jones on Twitter:
or now on iTunes!
- Windows Media Podcast - MB WMV
- iPod Video Podcast - 18.4MB MP4
- MP3 Audio Podcast - 4.2MB MP3
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
