what are steps should i do to make my local sql server connects to a remote sql server on my server?

Question

I want my sql server to remote connect to the server sql server, so that I can access databases from my local machine.

How to do that in steps, or if there is an article online that will be nice as well.

What I am doing now is trying to connect to the remote db from my local sql server using the server IP, but it tell me "Cannot connect to ..."

Answer 1

Are you talking about just accessing the databases and the instance? Or do you mean implementing high availability?

If its the former, then all you technically need to do is ensure tcp/ip is an enabled protocol, install SSMS, and if you can see the server then you should be able to login to the remote instance, provided you have the proper credentials.

Edit: if its not the default instance then you need to ensure the SQL Browser service is started.

Answer 2

There are a number of reasons why you might not be able to connect to a remote SQL Server. Given the error message you're receiving then it may be that you're just not specifying the instance name in the connection dialog.

I'm assuming you're using SQL Server Management Studio to try to connect. If the IP Address is correct then adding "\Name of instance on server" following the IP Address should give you a connection, e.g. 192.168.1.1\TestInstance.

A test I've occasionally found useful is to try to open a telnet session to the SQL Server port on the server. If you know that SQL Server is running on that IP Address then this can give some indication if there is a network or firewall issue:

C:\>telnet 192.168.1.1 1433

where the IP address is for your server and 1433 is the default port that SQL Server listens on for connections. If the command window goes blank then you have established a connection with SQL Server. Use CTRL+] followed by quit to terminate the telnet session.