3

I need your help DBAs, I need to know the pros and cons of my question.

We have this new "DBA", who doesn't know about DBA really, he got promoted to DBA temporarily to become an IT supervisor, He does not know any sql and no scripting skills.

He just took out all our developers' access to sql server to connect locally. I want to justify to our director that whatever this guy is doing is not comfortable for us..accessing the server and running the ssms.

Btw, he just changes access without even sending any courtesy emails. I need your advise on whether I'm wrong. I'm a developer for this company and for my past 10yrs, I've never worked in a company where you actually run your SSMS on the actual server.

Here's what I gather and my opinion.

  1. Risk to accidentally rebooting the SQL Server.
  2. Data security on the server.

I wish to add more so I can present this to my Director, I'm just not comfortable doing non-server stuff on the server period.

  • 1
    Some Windows boxes have a Max Number of RDP Connections set to 2, so only 2 people will be able to connect to the server. SSMS on the server will take up RAM and CPU away from SQL Server itself. Sounds like your DBA prefers to disable all incoming TCP connections, instead of setting up the correct SQL access for each user/group. – Oreo Mar 27 '18 at 14:04
  • 1
    running ssms locally uses resources like cpu and memory... resources best left for the database engine. logging in remotely using rdp also does the same. In my opinion, people should only be logging on to the sql server box in "special" circumstances. – DimUser Mar 27 '18 at 14:05
  • both of your answers are the exact same thing we tried to explain. additionally, he want's us to connect using our domain account, which is fine because the way we access now is thru tunneller, since we are under a different domain and the SQL server is on another domain. again thank you for confirming that I was right. – CyberNinja Mar 27 '18 at 14:18
  • Slightly unrelated: you can set Windows Server to automatically log off disconnected sessions after x number of minutes. This is great if you can't keep people off RDP sessions but want to minimize the effects on resource consumption. Believe it or not, many people will just close (disconnect) an RDP client without logging out. – Daniel Hutmacher Mar 27 '18 at 15:25
  • If your workstation is on a different domain than the server, you'll probably want to talk to your AD admin to set up trusts between the domains. – Daniel Hutmacher Mar 27 '18 at 15:26
3

As a general practice we never even install SSMS on the server hosting SQL Server.

You got the first two already

1.Risk to accidentally rebooting the SQL Server.

2.Data security on the server.

However there are more:

First of all each session that goes to the server will take up resources (Memory/Cpu/Diskspace,DiskIO) needed by the SQL Server engine, this means a server might get under pressure by allowing people to directly connect on the Server. Which will slow down performance and might even cause timeouts to your applications.

When accessing the SQL Server Server, you can copy and duplicate data on the disks if there is not a good security structure, meaning you can easily fill up the diskspace where your sql server database files are located. Meaning your files won't be able to grow any more

If you have not set up the security rules well, you are able to install other software on the servers also taking away precious memory from the SQL Server server.

0

I do agreed with what the others have explained and posted above. And same as you, I do feel uncomfortable for developers to connect the SQL server locally, even despite that their access will be limited to any of the changes on the server.

Out of point (sorry, my reputation is not enough to comment on other's post). but I would like to add on 1 point (probably there's more but I couldn't think of it at the moment) to your part of "...I've never worked in a company where you actually run your SSMS on the actual server". This actually do matters or help for DBA instead.

This will be needed when DBAs are supporting SQL servers with different regions. So for example, you are located in Asia, but one of the SQL server is located at U.S. Accessing remotely will be super slow and sometimes, might even hang your SSMS. And I seen cases (not many though) that this could potentially cause impact due to a query running remotely by a DBA where he/she might be doing some fine-tuning or DB changes...

  • yes, you have a point about running the ssms but again, the purpose of having ssms on the server and running it is not for daily and long term use like for developers but only for special cases just like what you said. – CyberNinja Mar 29 '18 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.