Sql-server – Where to install or use SQL Server

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!).

The standard SQL port is 1433. Though, the provider could be using another port. So, you'll need to ask them what port they're exposing.

Once you know the port, you can connect to the database instance using the following connection string (substituting placeholders with appropriate values):

"Data Source=111.222.333.444,1433;
Network Library=DBMSSOCN;
Initial Catalog=[databasename];
User ID=[username];Password=[password];"

Substitute 111.222.333.444,1433 with the appropriate ip address and port.

The other question I have is that I have a number of databases on other servers, I will like to "copy" these databases to the new server. I need to copy both structure and data. What is the best way to do such?

You can either backup and restore the database or you can generate scripts to create the schema and insert data. To generate the schema and data scripts go to:

object explorer => [right-click] database => select tasks => "generate scripts"

Then, under the "advanced options" make sure schema and data is selected for "Types of data to script":