When development is getting done on production SQL server....
-
I was looking for a reason why application that is working fine most of the time all of a sudden showing this odd timeout errors and discovered that people are actually getting on to the production SQL Server box and building SSRS and Power BI reports. PowerBI is the main culprit chewing up all the CPU ( hitting 100% ) and very large amount of memory. Only reason they don't want to do dev work somewhere else is they don't want to spend money for new server or upgrade current one with more resource. It is speced for SQL server and not for development. They are happy to pay for support while we investigate and re run same steps as application from backend but don't want to spend on upgrade. I am worried some day someone will kill that box...
Zen and the art of software maintenance : rm -rf * Maths is like love : a simple idea but it can get complicated.
-
I was looking for a reason why application that is working fine most of the time all of a sudden showing this odd timeout errors and discovered that people are actually getting on to the production SQL Server box and building SSRS and Power BI reports. PowerBI is the main culprit chewing up all the CPU ( hitting 100% ) and very large amount of memory. Only reason they don't want to do dev work somewhere else is they don't want to spend money for new server or upgrade current one with more resource. It is speced for SQL server and not for development. They are happy to pay for support while we investigate and re run same steps as application from backend but don't want to spend on upgrade. I am worried some day someone will kill that box...
Zen and the art of software maintenance : rm -rf * Maths is like love : a simple idea but it can get complicated.
Sounds to me like the access and permissions to that production server need to be restricted to read only access and a new instance spun up for the devs. Although even that is being generous, if you restore production instance to the dev instance each night the devs should never need to go anywhere near the production server. Given your current setup, it's a given that someone is going to commit some change to production that will require a full restore. It's just a case of when.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Sounds to me like the access and permissions to that production server need to be restricted to read only access and a new instance spun up for the devs. Although even that is being generous, if you restore production instance to the dev instance each night the devs should never need to go anywhere near the production server. Given your current setup, it's a given that someone is going to commit some change to production that will require a full restore. It's just a case of when.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
GuyThiebaut wrote:
if you restore production instance to the dev instance each night the devs should never need to go anywhere near the production server.
That's a nice theory. The problem with the backup/restore idea is that you can't say "just backup the tables". You get the entire database, and that will overwrite views, functions, and stored procs that the devs may be working on. And that's assuming they're not working on tables (adding/removing columns, keys, and/or constraints). The only recourse is to be able to re-sync the code from a database source project after a restore happens, but that also has dangers because devs are forced to check-in code that possibly doesn't work, unless there's a "work" database on the server where transient procs are developed and then moved to the actual database project when they are finished.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
GuyThiebaut wrote:
if you restore production instance to the dev instance each night the devs should never need to go anywhere near the production server.
That's a nice theory. The problem with the backup/restore idea is that you can't say "just backup the tables". You get the entire database, and that will overwrite views, functions, and stored procs that the devs may be working on. And that's assuming they're not working on tables (adding/removing columns, keys, and/or constraints). The only recourse is to be able to re-sync the code from a database source project after a restore happens, but that also has dangers because devs are forced to check-in code that possibly doesn't work, unless there's a "work" database on the server where transient procs are developed and then moved to the actual database project when they are finished.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013Unfortunately it's not just a theory it's a reality of how I and all the other devs I work with develop. In terms of losing work it just means that we have to be very organised to save our work outside of the database in the form of scripts. We also have local databases which persist and are very cut-down versions with regards to data that we can work on but much of the time the data in the local databases is not sufficient for proper testing.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I was looking for a reason why application that is working fine most of the time all of a sudden showing this odd timeout errors and discovered that people are actually getting on to the production SQL Server box and building SSRS and Power BI reports. PowerBI is the main culprit chewing up all the CPU ( hitting 100% ) and very large amount of memory. Only reason they don't want to do dev work somewhere else is they don't want to spend money for new server or upgrade current one with more resource. It is speced for SQL server and not for development. They are happy to pay for support while we investigate and re run same steps as application from backend but don't want to spend on upgrade. I am worried some day someone will kill that box...
Zen and the art of software maintenance : rm -rf * Maths is like love : a simple idea but it can get complicated.
virang_21 wrote:
I am worried some day someone will kill that box...
Raspberry Pi's can host SQL Server these days. Under $75, per user, each getting their own dedicated server. I could help, but need to be on location for that. :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Sounds to me like the access and permissions to that production server need to be restricted to read only access and a new instance spun up for the devs. Although even that is being generous, if you restore production instance to the dev instance each night the devs should never need to go anywhere near the production server. Given your current setup, it's a given that someone is going to commit some change to production that will require a full restore. It's just a case of when.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
Their rational for not doing it is because there is some government agency data in that application and putting that on a different server means getting few approvals from suits. I still think its worth the pain to get approval once but company thinks otherwise and gave dev direct access to that server.
Zen and the art of software maintenance : rm -rf * Maths is like love : a simple idea but it can get complicated.
-
I was looking for a reason why application that is working fine most of the time all of a sudden showing this odd timeout errors and discovered that people are actually getting on to the production SQL Server box and building SSRS and Power BI reports. PowerBI is the main culprit chewing up all the CPU ( hitting 100% ) and very large amount of memory. Only reason they don't want to do dev work somewhere else is they don't want to spend money for new server or upgrade current one with more resource. It is speced for SQL server and not for development. They are happy to pay for support while we investigate and re run same steps as application from backend but don't want to spend on upgrade. I am worried some day someone will kill that box...
Zen and the art of software maintenance : rm -rf * Maths is like love : a simple idea but it can get complicated.
Why the hell do devs need real time data, oh wait this is a hardware issue, you are missing a box. No amount of tweaking, backups, schema setups is going to help you if your company is too tight to get a dev box. Prepare your I told you so email now.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
Their rational for not doing it is because there is some government agency data in that application and putting that on a different server means getting few approvals from suits. I still think its worth the pain to get approval once but company thinks otherwise and gave dev direct access to that server.
Zen and the art of software maintenance : rm -rf * Maths is like love : a simple idea but it can get complicated.
So they are more concerned with the effort involved in getting approval than with the security risks of devs having access to live data :omg: ?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I was looking for a reason why application that is working fine most of the time all of a sudden showing this odd timeout errors and discovered that people are actually getting on to the production SQL Server box and building SSRS and Power BI reports. PowerBI is the main culprit chewing up all the CPU ( hitting 100% ) and very large amount of memory. Only reason they don't want to do dev work somewhere else is they don't want to spend money for new server or upgrade current one with more resource. It is speced for SQL server and not for development. They are happy to pay for support while we investigate and re run same steps as application from backend but don't want to spend on upgrade. I am worried some day someone will kill that box...
Zen and the art of software maintenance : rm -rf * Maths is like love : a simple idea but it can get complicated.
