Tuesday, November 27, 2007

SQL 2005 Mirroring Witness Connection Does Not Estalbish on one node

Symptom

When you've added an Witness Server to the mix, and you start to get the following error message in the Application Log. You are running all of the service under a specific domain user and everything runs but this. The Database Mirroring Monitor shows that Witness Connection is OFF (red X) on only one server.

Event Type: Information
Event Source: MSSQL$XYZ
Event Category: (4)
Event ID: 28048
Date: 11/27/2007
Time: 6:13:38 PM
User: N/A
Computer: SQLServer2
Description:

Database Mirroring login attempt by user 'mydomain\sqlservice.' failed with error: 'Connection handshake failed. The login 'mydomain\sqlservice' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.1.2.3]

Cause

Root cause is really unknown. Probably a bad sequence of installing and configuring database mirroring. But the immediate cause that can be fixed is that the computer node which you have seen the event log is not granting the Connect permission on the Mirroring endpoint object in the securables for the log in account mentioned.

Fix

This is an issue of the Witness Server unable to establish a connection to the Principal or Mirror Server (whichever is broken). I was able to fix this by;

  1. Start the Microsoft SQL Server Management Studio
  2. Connect to the database server that has an issue.
  3. Go to the Security folder
  4. Add the login name that is indicated in the Event Log. In my example MYDOMAIN\sqlservice
  5. Select the account you've just added and open its property (i.e., right click it)
  6. Select Securables
  7. Press Add...
  8. Select (leave it selected as) Specific Objects...
  9. Perss OK
  10. Under Select Objects dialog box press Object Types...
  11. Check Endpoints
  12. Press Browse...
  13. Add [Mirroring] object.
  14. Once back in the main property window, check mark Grant
That has fixed it!

Total hours to figure this stuff: 2 hours.

Monday, November 26, 2007

IIS 6 - "Page Cannot Be Found" When Running .NET Pages

Problem

I have downloaded an ASP.NET application, made sure that the directory property is correct, set the Web Application, the documents contain .aspx and application settings are pointing to correct version of .NET framework.

When I point to the page in the application directory, for example "Default.aspx", the browser comes back and says "Page cannot be found" Even though if you put in index.html file there with some simple test HTML in it, it works.

I have tried to re-install .NET framework...

I have even tried to run C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe /i


Cause

In Windows Server 2003, ASP.NET may be turned off by default, even if you have done bunch of other stuff that I have tried.

Solution

Go back to your computer management and open the Internet Information Service node. You will find one last folder in there that is called Web Service Extensions. You will likely to see that ASP.NET v.2.0.50727 (or whatever the latest version) is in Prohibited state.

As soon as you Allow this, then your pages will run without a problem.

This solution also fixes the problem of ASP.NET tab not appearing in the IIS Web Site properties.

Time took: About 3 hours to find this issue. Whey don't they come up by default!

Wednesday, November 14, 2007

SQL Server Database Mirroing Cannot Establish A Witness

Problem:

I spent about two days on this issue. I could establish a Principal and Mirror pair with the SQL Server 2005, but when an Witness server was added in the mix, the witness could not establish.

I have gotten errors like "Cannot find the server at mirror.xyz.com" and "Alter Database Failed..."

Answer:

You should go through the article on MSDN http://msdn2.microsoft.com/en-us/library/ms189127.aspx to make sure that you did not botch up any other stuff.

The key to this are the following,
  • The mirroring operation is very very sensitive to DNS, if there is anything wrong with it, it won't work.
  • The fact that you can "talk" to the mirror and the principal using all aliases is not a sufficient test. Go to each machine (e.g., the mirror and the witness) involved and make sure that all full and DNS alias names can be resolvable to the (identical) IP address and that you can ping the servers with ALL of the known names. If this does not work then you should "hard wire" all the names and addresses in the local machine's C:\Windows\System32\Drivers\Etc\Hosts file.

What Made This Difficult:

In my specific sitaution, there were couple of DNS aliases for all of the servers, and the host name appeared in all different variety of names while confugiring the mirror. As it turns out that having various names is not actually an issue, but the DNS configuration on the TCP/IP control panel was all slightly different causing one server for not being able to find the server name.

Total Time Saved:

You will save at least one day or more of your time by debugging the DNS first.