6

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?

Improve this question

2 Answers 2

Reset to default
4

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.

Improve this answer
3
  • thank you sir for the very informative response and suggestions. I look forward to getting this type of environment set up
    – Juan Velez
    Commented Aug 22, 2012 at 19:58
  • @JuanVelez: You're welcome! As you can probably tell, I highly recommend this type of setup. :)
    – Jon Seigel
    Commented Aug 22, 2012 at 20:08
  • As an aside, this solution eliminates the UAC issues with running IE and BIDS locally on a Report Server.
    – Greenstone Walker
    Commented Dec 17, 2013 at 23:20
1

Ultimately it would depend on how your company is set up. In our company I must use a VPN connection to connect from outside of the corporate WAN. However, this aside, if you have SQL Server setup to accept connections using TCP/IP then there is no real reason why you could not do this.

Your biggest concern will always be how tight your company has it locked down. If you are unsure seek the advice of your companies security specialist - the last thing you want to do is to breach company IT rules without knowing it... :)

Improve this answer
1
  • lol, definitively wouldn't want to do that. Thanks for the response! I do believe the server is setup to accept connections using TCP/IP so i should be good to go. I will check with IT first though! Thanks again!
    – Juan Velez
    Commented Aug 22, 2012 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.