Manage remote instances using SQL Server 2005 tools

I'm trying to manage remote instances using the following SQL Server 2005 tools:

SQL Server Configuration Manager (SSCM)

SQL Server Surface Area Configuration (SSSAC)

It seems that SSSAC is designed to manage remote instances since it has the "change computer" link. On the other hand, SSCM does not have such link or something similar. I read this MS article http://msdn2.microsoft.com/en-US/library/ms190622.aspx so it seems that this is a way to run SSCM to manage remote instance. I was having problem to show SQL Server 2005 Services by following that article. However, the problem is now gone (don't know why yet).

What are the reasons that the SSCM is not designed to manage remote instances but SSSAC is? If SSCM is really designed not to manage remote instances, what is the purpose of having the word (Local) next to the SQL Server Configuration Manager?

If I understand correctly, the only main difference between SSCM and SSSAC is that SSCM does not have the functionality to turn on and off features according to http://www.microsoft.com/technet/prodtechnol/sql/2005/mgsqlexpwssmse.mspx

So, if I don't need to turn on and off features, SSCM will be the only tool needed if it can manage remote instance.

With SQL Server Management Studio (SSMS) open and Registered Servers visible, right-click a remote reristered server and click SQL Server Configuration Manager. You will see SQL Server Configuration Manager(remote server name).|||

Hi lkh,

Thanks for the information. I wonder why MS does not implement the change computer in SSCM. Is there a way to run SSSAC in SSMS?

|||

it is a command prompt utility see it can be run in Query Editor in sqlcmd mode

See SQL Server 2005 Books Online topic:

sac Utility

http://msdn2.microsoft.com/en-us/library/ms162800.aspx

you can add in in SSMS under Tools\External Tools. Its located at C:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe

Manage instances in another machine

I'm trying to manage another SQL Server 2005 instance in another machine. I'm doing this by connecting to another computer thru Computer Management. When I go the SQL Server 2005 Services, the right pane is showing There are no items to show in this view. How can I view the services?

Another problem that I have is that I have to turn off the Windows Firewall in the other machine. What exceptions are needed? I have tried by adding the specific TCP used by the instance.As far as I know the Computer management console can only adminsiter the local instances fpr Sql Server 2005.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens is right, you have to enable SQL Server remote access from the console of the server. Here's a couple relevant pages explaining how to enable remote access to SQL Server:

http://support.microsoft.com/default.aspx?scid=kb%3bEN-US%3b914277

http://www.aspcode.net/articles/l_en-US/t_default/Databases/SQL-Server/SQL-Server-2005-Expressremote-connection_article_123.aspx

Once you have remote access enabled, you can manage your server using SQL Server Management Studio from other machines.

Hope this helps,
Steve

|||

Hi Steven,

I'm following this MS article: http://msdn2.microsoft.com/en-US/library/ms190622.aspx

I can connect to the SQL Server Configuration Manager of the remote computer thru Computer Management. I can enable and disable protocols under both SQL Server 2005 Network Configuration and SQL Native Client Configuration. However, SQL Server 2005 Services will only show "There are no items to show in this view". It does not make sense to me since I can control the services thru Services but not thru SQL Server 2005 Services under SQL Server Configuration Manager.

I have disabled Windows Firewall and SQL Browser service is running.

Peter

|||If you login is a part of local administrator group on that server then using Computer Management console can do the job as it relies on the user privileges, as explained above for the SQL Configuration manager you can only manage local instances.sql