March 13, 2010 at 12:13 pm
Hi,
We have 2 node A/P cluster setup with SQL Server 2005 ee x64 with SP3. For the 1st time when SQL Server instance has installed, it's services were set to "MANUAL" by default.
Then I thought, it would be better to put services in AUTOMATIC mode and I changed them to AUTOMATIC.
and when ever the instance fail overs to passive node and when ever the server reboot occurs, I'm seeing the below message in event viewer system log:
Event ID 7024
The SQL Server (MSSQLSERVER) service terminated with service-specific error
17058 (0x42A2).
There are also errors in the application log & also in the error log as below:
Event ID 17058
initerrlog: Could not open error log file 'D:\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. Operating system error = 3(The system
cannot find the path specified.).
After going through the below link,
It's looks like we MUST need to keep the SQL Services to start in MANUAL mode as the cluster service brings the sql service online in cluster environment.
So please advice me that CAN I CHANGE THE MODE TO MANUAL FROM AUTOMATIC NOW???
and if we set to MANUAL, then if a Node restarts, then will the SQL Services starts automatically or do we need to go start the services
Thanks for your inputs
March 13, 2010 at 3:06 pm
Change them both to manual the clustering service will mange which service is started on each node. When the service is manual it won't start without an input of some kind...In clustered environment that should always be carried out by the clustering service.
Gethyn Elliswww.gethynellis.com
March 13, 2010 at 3:18 pm
Yes, the services must be set to manual so cluster adminstrator can manage stopping and starting the services. That's what it is supposed to do - because on a restart of a node, the services have to failover to the other node and be started. Again, that is what the cluster administrator is supposed to do.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 14, 2010 at 1:01 pm
Yes, the services must be set to manual so cluster administrator can manage stopping and starting the services. That's what it is supposed to do
Could you please point me to the link in BOL, where it mention that the SQL services must be set to MANUAL in clustered environment
I appreciate your help
Thanks
March 14, 2010 at 1:43 pm
I can't find the link in BOL but think of it like this:
You have a virtual instance of SQL server that can run on one of two nodes but not both at the same time. The cluster service manages which is the active node (running the SQL service) for the virtual instance. If both physical nodes are set to automatic then both will try and start the SQL service outside of the cluster which is likely to cause you issues.
Just remember the CLUSTER service will manage which service should be running on which physical node. You can use cluster administrator to failover manually...That is stop the service on one node and start it on the other node. But it can't run on both nodes at the same time, so on each physical node the service is set to manual and stopped and started on each, depending on which node is active using the clustering service
Gethyn Elliswww.gethynellis.com
March 14, 2010 at 2:28 pm
Here is the link to the 2008 documentation: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10sq_GetStart/html/309b9dac-0b3a-4617-85ef-c4519ce9d014.htm#Configure_services
You'll note that it states the following:
Set as manual in failover cluster configurations.
After the table of services and startup types. You can find the same documentation in Books Online for 2005, just search for 'setting up windows service accounts' and you should be able to find it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 14, 2010 at 10:50 pm
Thank you,
We have a one more 3 node cluster setup & we restarted the nodes after some windows maintenance (this is the 1st time we rebooted the nodes after the SQL Server has installed on Node1 & Node2 and Node3 is passive). And after the reboot, I'm seeing the below warning messages in event viewer every 1 hr. Are they because of the Services having the START MODE as AUTOMATIC?? please advice
Event Type: Warning
Event Source: Ftdisk
Event Category: (2)
Event ID: 57
Date: 3/14/2010
Time: 9:37:25 PM
User: N/A
Computer: NODE1
Description:
The system failed to flush data to the transaction log. Corruption may occur.
Event Type: Warning
Event Source: Ntfs
Event Category: None
Event ID: 50
Date: 3/14/2010
Time: 9:37:22 PM
User: N/A
Computer: NODE2
Description:
{Delayed Write Failed} Windows was unable to save all the data for the file . The data has been lost. This error may be caused by a failure of your computer hardware or network connection. Please try to save this file elsewhere.
March 14, 2010 at 10:56 pm
Those errors are not related to SQL Server - rather, they are errors with your IO system and will eventually cause corruption in your databases.
You need to call MS support, your SAN support and any other vendors related to that cluster and get them involved before you end up losing all of your data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 19, 2010 at 6:08 pm
Thank you
I have set the SQL Server services Start up mode in cluster environment as below:
sql server --manual
sql server agent--manual
sql server fulltext search --manual
sql server integration services--Automatic
sql server browser --automatic
Please correct me if I miss/or did wrong anything
thanks for your help
March 19, 2010 at 6:16 pm
That looks correct to me. Any services that are managed by the cluster need to be set to manual.
BTW - did you get your SAN issues squared away? If not, I highly recommend that you get that done before you end up with data loss and trying to restore from backups.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 20, 2010 at 12:37 am
I'm NOT seeing these warning messages when I restart the nodes in below order:
Restart Node1 (Active)
Restart Node2 (Active)
Restart Node3 (Passive)
If I did not follow the above order to restart the nodes, then I'm getting same warnings again every 1 hour
Is there any best practice to restart the nodes? Like active nodes 1st & then passive node
Thanks for your help
March 20, 2010 at 8:22 am
pshaship (3/20/2010)
I'm NOT seeing these warning messages when I restart the nodes in below order:Restart Node1 (Active)
Restart Node2 (Active)
Restart Node3 (Passive)
If I did not follow the above order to restart the nodes, then I'm getting same warnings again every 1 hour
Is there any best practice to restart the nodes? Like active nodes 1st & then passive node
Thanks for your help
Once again - those errors are IO related errors. The order of starting the cluster should not have any effect on this, and if it is - that means there is a configuration problem somewhere in the cluster.
You need to get that fixed or you will end up losing data - it's not a question of if, just a question of when that situation will corrupt your databases. Once the database have become corrupt - your only solution to fixing the problem is a restore from a good backup, which you might not have because the IO system is causing problems.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply