LinkedIn | FaceBook | Twitter
I've got another question for you - where should the tools be? Let me explain.
Most of the time when you connect to SQL Server, you open SQL Server Management Studio (SSMS) on a client machine and then type in the name of a server somewhere on the network. What you type on the client is then sent to the server to be executed. This is standard client/server stuff.
As I've talked with a number of DBA's that have production systems in data centers and the like, I've found an interesting behavior. They are actually using Remote Desktop to connect to the server, and then they open SSMS there. I'm told they do this for a few reasons, but the main one I heard was "so that I know I'm on the production box."
One method of solving this problem would be a notification that you're on a particular box. We could set a color on the background or a special title in the top. Using colors is difficult for us, as you know, because we support those with color-blindness, grey-scale screens, and high-contrast modes. Those render the colors less useful as a warning mechanism.
Another method would be to use the web interface for SQL Server that I've mentioned before. This is a different connection on a different system, so you would be "local" when you connected to the server.
What are your thoughts? Do you operate in this mode? If so, why are you doing that?
Depending on company policies permissions may vary if you remote in. One example I've come across is SSIS/2005 permissions.
When you execute code on a local machine it looks to the local providers which may differ from the providers on the server. I have 2000 and 2005 installed on my machine so I don't create a DTS package on a 2000 server.
I've got another question for you - where should the tools be? Let me explain. Most of the time when
I remote desktop to the box about half the time. It started with SQL2000. In enterprise manager, there wasn't a way to view the windows event logs unlike SSMS. So created and saved a Remote Desktop Connection file for every database server. It was simple to double click the file, RDP in, and pull up Event Viewer from there. I can't really do the same thing with the Event Viewer MSC, as it doesn't save the connection string. Same thing with WINMSD.
On a failover cluster, I always RDP in when I need to work with the cluster administrator. I guess I'm a little spooked. A few times in the past, when I didn't bring a resource on-line fast enough, the cluster group would failover.
With SQL2005, there are three reasons why I still like to RDP in. If there is a low disk space issue, it's a lot easier to figure out what's the culprit from RDP. I sometimes use a freeware utility called WINDIRSTAT that quickly identifies what's chewing up disk space, including virtual memory. I really wish Windows had that functionality built-in: http://windirstat.info/.
The second common reason is that at my present company, we have two domains and they don't trust each other. I pull up SSMS using "run as" with the other domain account -- but it doesn't work either. My company's network is more secure than the FBI. We have some kind of filtering/spy software. We have a server in the other domain, that I can RDP into and pull up SSMS from there. But it isn't a terminal services server and is limited to the standard 2RDP +1Console connections.
The third reason is that SQL were not installed in a uniforum way. The location of the backups, MDF, LDF, and sql error logs are all in different locations. I tried to standardize things by creating fileshares to abstract away the differences. e.g. \\<servername>\sqlbackup, \\<servername>\sqllog, \\<servername>\sqldbalog.
Sometimes I remote in because 1) some of the servers are behind DMZ's or something and I cannot manage some function from my local machine. 2) I have been told that some thing runs faster if done on locally on the server.
Good feedback. as to anything running quicker, the only thing that comes to mind would be a large result-set, since you would bypass the network layer if you were local. Other than that, it'sx all the same.
Permissions are another issue. What Steve reports is accurate, and something I'm interested in addressing.