Connecting to a Remote SQL Server 2008 Instance – Step-by-Step Guide

sql-server-2008

I work with SQL Server 2008 at my current job and the way I connect to the (backup) server where The SQL Server 2008 instance is installed is by doing a remote desktop from my local work machine into a windows server 2003 computer where the SQL Server 2008 database program and database files are stored (also on our local network)…

This works fine but the only problem is that the SQL Server installation does not have SSRS, SSIS or BI tools installed. I know it is fairly easy to install these features BUT I was wondering If I would be able to connect to the database using a different SQL Server 2008 Installation on a totally different computer through an internet connection (not LAN)?

For Example, If I have a fresh Sql Server 2008 installation on my home computer with all the needed features (SSIS,SSRS,BI Tools), would I be able to Open up Management Studio and connect to the SQL Server 2008 database server/engine at my workplace?

Best Answer

The way you do this really depends on your network setup, corporate security policies, and what kind of infrastructure you have available to use.

What's worked really well for me is creating a totally separate management server on which SSMS, BIDS, etc., is installed, and all management of servers is done from there.

There are many advantages to doing this:

  • Management tools are installed and configured centrally, which itself does a couple of important things:
    • Alleviates the installation of management tools on the actual production servers (SQL Server 2012 can be installed on Server Core, which makes external management a necessity). This saves your production CPU cycles, memory, and (probably) expensive storage.
    • If you retire a server, the management tools don't go down with the ship, too.
  • If you need to manage multiple servers, it's the perfect place to install a CMS, and do things like centralized monitoring and logging.
  • It gives you an area you can test with and not affect production. (If you blow something up, you haven't blown up production!)
  • Eliminates the need to directly connect to the SQL servers from the outside (simply RDP to the management box).
  • You can land the O/S on less expensive or performant storage than the production boxes, thus saving $$.

This kind of thing is super easy to set up if you have a virtualized infrastructure available to use.

Related Question