SQL Server 2008 Cluster Instalation

http://www.mssqltips.com/tip.asp?tip=1709

When using SQL Server Management Studio Express I get the error "SQL Server does not allow remote connections". How to resolve this error?

Question:

In SQL Server 2005 when trying to connect to a remote database with SQL Server Manager Studio Express, I get this error: "An error has occured while establishing a connection to the server. When connecting to SQL Server 2005,this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider:Named Pipes Provider,error:40-Could not open connection to SQL Server))" How to resolve this error?

Answer:

In addition to the above error the user might get the following errors too:

*
SQL Server does not allow remote connections
*
SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
*
Server does not exist or access denied

The errors are self-explanatory for a Database Administrator, but for a beginner or Developer these errors will be more frustating.

It should be noted that SQL Server 2005 Express is not automatically configured for 'remote access' during the installation. This is not a bug merely the default configuration. The simple solution for this problem is to enable the network protocols for the SQL Server Express instance using Surfact Area Configuration (SAC) utility for SQL Server 2005 programs group on your machine.

Surface area reduction is a security measure that involves stopping or disabling unused components. Surface area reduction helps to improve security by providing fewer avenues for potential attacks on a system.

For new installations of Microsoft SQL Server 2005, some features, services, and connections are disabled or stopped to reduce the SQL Server surface area. For upgraded installations, all features, services, and connections remain in their pre-upgrade state."

SQL Server Surface Area Configuration is available on the SQL Server Start menu:

*
On the Start menu, point to All Programs, Microsoft SQL Server 2005, Configuration Tools, and then click SQL Server Surface Area Configuration.
*
Choose SQL Server 2005 Network Configuration and select the installed SQLEXPRESS instance.
*
On right-hand pane select the required protocol either TCP/IP or Named-Pipes (or both), right-click with the mouse to set it to 'Enabled'.

In some special cases it may be an additional requirement to start the SQL Browser service, the usage and benefit of having this service run is that users connecting remotely do not have to specify the port in the connection string. It is a Best Practice in Security aspects to not to run the SQL Browser service as it reduces the attack surface area by eliminating the need to list on to an UDP port from client.

If your network is tightened with relevant firewall and security access then (in special cases) you might need to enable SQL Server and SQL Server Browser to be exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall or any other firewall software application that is used.

Lastly, you might need to reboot the machine in order for these network configuration changes to take affect as simply restarting SQLServer and SQL Browser services may be insufficient.

For more information on usage of Surface Area Configuration tool refer to updated SQL Server Books OnLine.

SQL Server Management Studio takes a long time to load, how to improve the load performance of this tool?

http://www.sql-server-performance.com/faq/sql_server_management_studio_load_time_p1.aspx

Good Link.

Microsoft SQL Sever management Studio: Failed to open connection dialog

Error : Microsoft SQL Sever management Studio: Failed to open connection dialog. Even after reinstalling the client tools still the error persists.

Solution:
This is a common question in the forums and newsgroups when any of the SQL Client tools gets corrupted. There might be chances that few times by reinstalling the client tools and if there is an issue with a corrupted or deleted (mistakenly) a .DLL file then the only way is to repair the tools.

You can either follow the GUI with SETUP of Management Studio tools or use the command-line operation.

Repairing SQL Server is a command-line operation. You can repair by inserting the installation media into the disk drive and typing the following:

start /wait \setup.exe /qb REINSTALL=SQL_Tools90 REINSTALLMODE=OMUS

Surface Area Config - Missing - SQL Server 2008 R2

http://technet.microsoft.com/en-us/library/cc281850.aspx

Improving SQL Server Performance - CHECK LIST

http://msdn.microsoft.com/en-us/library/ff647793.aspx
http://msdn.microsoft.com/en-us/library/ff647681.aspx

Detect CPU pressure

if you have a large number of workers in RUNNABLE state, it is symptom of CPU bottleneck. On the other hand, if your workers are spending most time in SUSPENDED state, it is indicative of excessive blocking in your SQL Server.

Detect CPU pressure
===================
SELECT COUNT(*) AS workers_waiting_for_cpu, t2.Scheduler_id
FROM sys.dm_os_workers AS t1, sys.dm_os_schedulers AS t2
WHERE t1.state = 'RUNNABLE' AND
t1.scheduler_address = t2.scheduler_address AND
t2.scheduler_id < 255
GROUP BY t2.scheduler_id


Time spent by workers in RUNNABLE state:
============================================
SELECT SUM(signal_wait_time_ms)
FROM sys.dm_os_wait_stats