April 27, 2009 at 5:37 pm
Auditors had access to another system using MS Access. We've replaced that system with another and they would like their access to production data back via MS Access. I don't want to give them this type of access to run adhoc and MSAccess is a consistent poor performer against our server.
The database is 2GB and growing.
The only option I can come up with is to make a copy of the database on the same server.
We don't have another server to move it to. I considered our development environment, but it would be a licensing issue.
The database is too large for access.
Any ideas?
April 27, 2009 at 8:48 pm
Since you are worried about licensing, but you correctly are trying to avoid Access... This is hardly and optimal solution, but what about another instance on the same server. You can put limits on it to try to keep it masked off from your production instance. Limit it's memory and keep it down to a single processor. That'll also allow you a bit of leeway in terms of security. It's not perfect, but I think it'll work out better than putting it on the same instance as your production data.
Move to 2008 then you can put the Resource Governor to work along with some policy based management.
"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
April 28, 2009 at 12:42 am
Tell the auditors that they will have to supply their own server(s) because Access will cause problems on the production server
They can restore the prod DB onto their server and run all the queries they want.
April 28, 2009 at 1:30 am
Why can't you try installing an Express Edition and then restore your DB .
Since your DB is less than 4Gb you can use SQL Server Express Edition and Express Edition is free.
April 28, 2009 at 8:05 am
My solution for this, in the many cases I have run into in the past, is to restore the nightly backup of said DB to another DB on the same server, called [DB]Reports and tell the people to use that one. Typically it doesn't tax the server hardware enough to matter, and it causes no locks or waits on the production data, so everyone is happy. As part of the automated restore process I run the necessary scripts to grant or limit access for the various users.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
April 28, 2009 at 9:39 am
Ratheesh.K.Nair (4/28/2009)
Why can't you try installing an Express Edition and then restore your DB .Since your DB is less than 4Gb you can use SQL Server Express Edition and Express Edition is free.
Not a bad idea, but it will be over 4GB in one year.
April 28, 2009 at 9:40 am
Stamey (4/28/2009)
My solution for this, in the many cases I have run into in the past, is to restore the nightly backup of said DB to another DB on the same server, called [DB]Reports and tell the people to use that one. Typically it doesn't tax the server hardware enough to matter, and it causes no locks or waits on the production data, so everyone is happy. As part of the automated restore process I run the necessary scripts to grant or limit access for the various users.Chris
This will probably be what we do.
April 28, 2009 at 9:43 am
Grant Fritchey (4/27/2009)
Move to 2008 then you can put the Resource Governor to work along with some policy based management.
We're just finishing up our migration of 170 dbs to 2005. I'll get started on that ASAP boss!:-D
April 28, 2009 at 9:54 am
As others have said, don't let them run ad-hoc queries against the production system for their reports, it will all end in tears. People with no appreciation for SQL won't think twice about running SELECT *, exporting the results into Excel then using Excel to find the 1 record they actually wanted in the first place.
I would keep them off the production box altogether. It's been my experience that once a system is in place, however temporary you may have intended it to be, it suddenly becomes the "business critical" can't-be-changed-ever way going forward. And if the production db is slow it's your fault (as the DBA) not their fault because they are running "essential reports for senior managers".
I think you could make a good business case for them having their own server with a copy of the db. Cost would be minimal if you use SQL Express and, dare I say it, a desktop PC with 4Gb RAM. I'd probably still attempt to persuade them that they need a server with SQL Standard but be prepared to drop back to hideous desktop PC solution later. It will make the cost seem suddenly uber cheap and then they feel they've "saved" the company money by digging their heels in and overcoming the nasty DBA.
When (not if) the PC hard drive decides to throw in the towel you can smugly point out they should have gone for a server in the first place (with RAID 1 on the disk).
April 28, 2009 at 10:08 am
sam (4/28/2009)
Grant Fritchey (4/27/2009)
Move to 2008 then you can put the Resource Governor to work along with some policy based management.We're just finishing up our migration of 170 dbs to 2005. I'll get started on that ASAP boss!:-D
Excellent! I mean, you don't have to get it done today. Just do it before you go home. 😛
"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
April 28, 2009 at 10:11 am
Thinking about it further, would this be construed as a test system? Then you don't need to buy a license for it.
Either way, if you really have to let them in, don't put them on a seperate database. Create a seperate instance. This will reduce memory & cpu as much as if they were on your main production instance, but they're queries won't interfere with your procedure cache. They're SELECT * queries won't muck up your data cache.
If you really can't get them on another system, this is the least bad choice.
"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
April 28, 2009 at 10:53 am
Is the overhead of another instance limited?
April 28, 2009 at 11:00 am
Yes. You can apply controls to it directly so that it only uses a single CPU and limited memory. It means that the auditors will actuall make less impact on your system. Again, it's not the RIGHT solution, which is to get another server for these guys, but it'll work as a compromise.
"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
April 28, 2009 at 11:22 am
- another solution to offload the msaccess :sick: stuff may be db mirroring with a read only copy on the mirror site.
But since licensing is an issue ... you need an extra license because you will use the mirror actively.
- If you want to avoid prod db locking, you may want to use snapshot database copies if you are on Ent Edtn. The queries will still influence your server cpu/ram/io, but at least the users will not be able to block your production apps, because they work on the read-only copy !
Also, depending on how you licensed your Ent Edtn you may be entitled to install more than one instance on that same OS. If you do so, be sure you install Windows System Resource Manager, so you control over the cpu priorities for the instances.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 28, 2009 at 6:28 pm
So if we have the extra instance and set that two use 2 processors of 8 using the affinity mask, can the other instance still use all processors?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply