November 9, 2016 at 8:39 am
Hello --
One of my colleagues wanted to know if there is a major advantage to utilizing a local installation of SQL Studio to connect to a remote database server, rather than
connecting to the server using Remote Desktop Protocol, and running the Studio application in that manner?
November 9, 2016 at 8:57 am
In most environments I work with SSMS that is installed on my workstation. Advantages:
1)I can register servers and use groups when I want to run some scripts on few servers instead of connection to each one separately and run it on that server
2)I can configure and maintain keyboard shortcuts for SSMS at one place and not on each SSMS on each server
3)I have one script library that I can use for all servers.
4)On my work station I can also install tools such as plan explorer, SQL Search, etc'. On servers I won't be able to install those tools
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 9, 2016 at 8:58 am
You're much less likely to shut down the server by mistake :-D.
November 9, 2016 at 9:08 am
SSMS application by itself takes memory. I would rather use that memory on my local workstation than the actual server itself. SQL Server is a memory Pig. So.....
November 9, 2016 at 9:19 am
Other reasons not to allow users to RDP on to the server:
(1) If users don't have access to log directly on to the server, you have a more secure environment (principle of least privilege)
(2) You only get two remote connections to the server. If they're being used by users querying databases, it's more difficult for those with a genuine need to administer the server
(3) It's an unnecessary use of resources. Not just memory, but CPU and possibly network as well. And if someone happens to save a massive script or result set to their My Documents folder on the C drive, you could, in the worst cases, find your server falling over unexpectedly
John
November 9, 2016 at 9:39 am
John Mitchell-245523 (11/9/2016)
Other reasons not to allow users to RDP on to the server:(1) If users don't have access to log directly on to the server, you have a more secure environment (principle of least privilege)
(2) You only get two remote connections to the server. If they're being used by users querying databases, it's more difficult for those with a genuine need to administer the server
(3) It's an unnecessary use of resources. Not just memory, but CPU and possibly network as well. And if someone happens to save a massive script or result set to their My Documents folder on the C drive, you could, in the worst cases, find your server falling over unexpectedly
John
+100
😎
There is absolutely nothing when working on an SQL Server that requires RDP to the server, prefer to have my servers headless if possible.
November 9, 2016 at 9:57 am
To a degree this is just piling on, but this is an important question.
You are much better off not using SSMS on the server for a number of reasons. Most of them for me are wrapped around performance. Dealing with query results and a GUI locally on your database server is not a proper use of those resources. Then you add in the fact that you're effectively exposing the server to security issues that are absolutely unnecessary makes using SSMS even worse.
Frankly, you should probably using SQL Server Core installations on most servers anyway. Having a GUI on the server of any kind is a waste of resources that are better served being elsewhere.
"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
November 9, 2016 at 9:59 am
There is absolutely nothing when working on an SQL Server that requires RDP to the server
Not quite true- if you work in a bank or other high security environment you may find that you have to use a 3rd party tool to log on to every instance, and this forces you to RDP to the server (and it "films" every single thing you do). There's no connecting via SSMS in those environments. I hate it.
November 9, 2016 at 10:17 am
Beatrix Kiddo (11/9/2016)
There is absolutely nothing when working on an SQL Server that requires RDP to the server
Not quite true- if you work in a bank or other high security environment you may find that you have to use a 3rd party tool to log on to every instance, and this forces you to RDP to the server (and it "films" every single thing you do). There's no connecting via SSMS in those environments. I hate it.
I get what you are saying but in my opinion that would not be the right solution, rather secure a transit server to which one can RDP on to and work on SSMS from there, point being that the risk of RDP'ing on to the database servers is a risk then mitigated.
😎
This is relatively straight forward, transit being most commonly a VM with multiple NICs plus just a little bit of network tinkering. It is my preferred way of setting up sandbox environments which are i.e whole multiple node systems in total isolation.
November 9, 2016 at 10:30 am
Beatrix Kiddo (11/9/2016)
There is absolutely nothing when working on an SQL Server that requires RDP to the server
Not quite true- if you work in a bank or other high security environment you may find that you have to use a 3rd party tool to log on to every instance, and this forces you to RDP to the server (and it "films" every single thing you do). There's no connecting via SSMS in those environments. I hate it.
One solution that I've seen to this issue is to have a common server to which everyone connects through RDP. That server then connects to the production servers through standard means, not RDP. That way you don't have to have a GUI on the working server, just on the access servers. This approach has passed auditing. It may depend on the auditor.
"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
November 9, 2016 at 10:34 am
Grant Fritchey (11/9/2016)
Beatrix Kiddo (11/9/2016)
There is absolutely nothing when working on an SQL Server that requires RDP to the server
Not quite true- if you work in a bank or other high security environment you may find that you have to use a 3rd party tool to log on to every instance, and this forces you to RDP to the server (and it "films" every single thing you do). There's no connecting via SSMS in those environments. I hate it.
One solution that I've seen to this issue is to have a common server to which everyone connects through RDP. That server then connects to the production servers through standard means, not RDP. That way you don't have to have a GUI on the working server, just on the access servers. This approach has passed auditing. It may depend on the auditor.
Grant, are you tired of typing "I agree with Eirikur"? Because you just did:-D
😎
November 9, 2016 at 11:02 am
Eirikur Eiriksson (11/9/2016)
Grant Fritchey (11/9/2016)
Beatrix Kiddo (11/9/2016)
There is absolutely nothing when working on an SQL Server that requires RDP to the server
Not quite true- if you work in a bank or other high security environment you may find that you have to use a 3rd party tool to log on to every instance, and this forces you to RDP to the server (and it "films" every single thing you do). There's no connecting via SSMS in those environments. I hate it.
One solution that I've seen to this issue is to have a common server to which everyone connects through RDP. That server then connects to the production servers through standard means, not RDP. That way you don't have to have a GUI on the working server, just on the access servers. This approach has passed auditing. It may depend on the auditor.
Grant, are you tired of typing "I agree with Eirikur"? Because you just did:-D
😎
Crap. Did I? Missed it somehow. Reading isn't always my best ability. Ha!
Well, so... I agree with Eirikur.
There.
"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
November 9, 2016 at 11:32 am
Grant Fritchey (11/9/2016)
Eirikur Eiriksson (11/9/2016)
Grant Fritchey (11/9/2016)
Beatrix Kiddo (11/9/2016)
There is absolutely nothing when working on an SQL Server that requires RDP to the server
Not quite true- if you work in a bank or other high security environment you may find that you have to use a 3rd party tool to log on to every instance, and this forces you to RDP to the server (and it "films" every single thing you do). There's no connecting via SSMS in those environments. I hate it.
One solution that I've seen to this issue is to have a common server to which everyone connects through RDP. That server then connects to the production servers through standard means, not RDP. That way you don't have to have a GUI on the working server, just on the access servers. This approach has passed auditing. It may depend on the auditor.
Grant, are you tired of typing "I agree with Eirikur"? Because you just did:-D
😎
Crap. Did I? Missed it somehow. Reading isn't always my best ability. Ha!
Well, so... I agree with Eirikur.
There.
Thanks Grant 😉
😎
On a more serious node, I find too much of servers' pressure coming from routine activities which can either be avoided or are entirely unnecessary but are placed on the servers because of the absence of the basic understanding of command line or T-SQL knowledge, have to have a "wizard" guidance being on of those. Multiple RDP sessions left open can easily kill the performance of a healthy server, try debug that!
The best way of mitigating such risks that I have found is barring RDP to the servers, my favorite is to use SSH and local CMD if one has to do something that cannot be done via normal data connection (please read up on wmic etc.)
November 10, 2016 at 7:25 am
One solution that I've seen to this issue is to have a common server to which everyone connects through RDP. That server then connects to the production servers through standard means, not RDP. That way you don't have to have a GUI on the working server, just on the access servers. This approach has passed auditing. It may depend on the auditor.
Oh, I agree, but a former employer didn't allow it. It was maddening.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply