This morning I installed a new instance of SQL server and while connecting remotely it sent me an error message.

Error Has Occurred While Establishing A Connection To SQL Server 2005 Which Does Not Allow Local and Remote Connections

ITLE: Connect to Server—————————– Cannot connect to .——————————
ADDITIONAL INFORMATION:
An error has occurred 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 a connection to SQL Server) (Microsoft SQL Server, Error: 233)

Solution is here : http://support.microsoft.com/kb/914277

• Please check firewall and make sure it is disabled else create an exception
• Enable the SQL Server Browser Service
• Enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer.

To do this, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.

2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.

3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.

Note Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you restart the Database Engine service.

4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

• Failover Clustering
• Log Shipping
• Database Mirroring

Failover Clustering : Used for entire SQL server Instance , Hardware Solution based on MSCS

Shared disks configured between MSCS cluster and automatic failover Will be achieved if one of the node goes down

Database Mirroring :: High availability for Database, Software solution based on HOT standby (New 2005)

1. Principle database is applied at Mirror database almost real time.
2. There is one t one relationship and means there would be only one HOT standby for any database.
3. The mirrored database is in recovery mode and never available until failed over
4. A witness box could be used to carry automatic failover when needed
5. Will only work with FULL recovery Model
6. Could be configured into 3 different modes- High Availability (Sync operation with Witness server ), High Protection (Sync Operation with NO Witness server ) & High Performance (ASync operation with NO Witness server )

Log Shipping : High availability for Database, Software solution based on WARM standby (Old 2000)

1. Secondary database is not sync with primary . The primary database doesn’t send the transaction logs directly to the Secondary. The moving of logs is done by the SQL agent using a network share. Primary server has a backup Job which backs up the transaction logs to a network share while Secondary server have Job to apply them at destination.
2. The warm standby can have one to one else one to many relationship with Primary database.
3. The Warm standby is always available in read only mode
4. A monitor server could be used to setup all jobs needed between Primary and Secondary server. We Can omit the Monitor server and push jobs into Primary & secondary if needed.
5. Supports Manuel Failover only
6. Can work with FULL else BULK recovery Model

Please navigate to Management ->Activity Monitor -> Double Click