sql serverssms
With a SQL Sever database which is on a remote hosted dedicated server I can work using SQL Server Management Studio installed either on the same server or on my local computer. I the first case I should work using Remote Desktop and this makes the work a little bit slower. In the second case I need to open additional port in server's firewall, but I will have more comfortable user experience.
What is the recommended practice of these two?
SSMS2012 is backwards compatible:
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/0151c2e8-2281-4a78-86b0-20cc1bfd57ac
Edit Giving out Aaron's link instead of the old one! Thanks Aaron.
microsoft.com/en-us/download/details.aspx?id=35579
As Kenneth mentioned, you could copy the 2008 BOL (Books online) so when you search the help it'll be relevant to 2008 instead of 2012.
I fixed the same symptoms/issue (which surfaced after I reinstalled the Visual Studio 2010 Isolated Shell, on which SSMS depends as a component) by removing the 2010 Visal Studio Isolated Shell and then repairing SSMS 2014 (download SSMS 2014 SP1 from here):
Best Answer
Opening the SQL server to direct outside connections is an idea to avoid if you can - you are significantly increasing your visible surface area for attack and any communication between you and the server (aside from login credentials in the authentication phase, perhaps) will not be encrypted. If you must open the port, make sure it is open only to your fixed IP address(es) (assuming you have fixed addresses that are not shared by anyone else, i.e. you are not using an ISP that has you behind a NAT arrangement like most mobile internet providers).
A much better option would be to install a VPN setup (such as OpenVPN for instance) or a SSH service (Cygwin includes a port of the common standard OpenSSH which I fund currently runs well as a service under Windows 2003 and 2008) that you can tunnel a connection through. This way SSH or the VPN handle both authentication and encryption, and adding (assuming you have secure passwords/keys) a significant layer of protection that a directly open port would not have. The compression support they offer will help response times for any queries that return more than a small amount of data too. A SSHd based arrangement may be easier to setup, especially if you are familiar with SSH already of course, though most true VPNs (like OpenVPN) have the advantage that communication over them is more likely to survive short communication blackouts (like an ADSL router losing sync and reconnecting). The VPN option is also less hassle if you have multiple instances of SQL server running on the target machine or at the target site. I've used both methods successfully for a number of things, including talking to SQL Server using local versions of the standard tools and found them to work well (though my OpenVPN servers are all on Linux, but I'm told it works well as server on Windows too).
Connecting to the remote machine (in)directly like this can be very convenient, but be aware that even with a careful VPN setup you are increasing the amount of machines your SQL Server is exposed to so, especially if it is a production system with your user's real data in, make sure you take the time to ensure that the machines that can see the server over the VPN/tunnel are secure and uninfected themselves (you probably do this already, of course, but it is always worth restating!).