Single user mode in SQL Cluster

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » In The Enterprise » Single user mode in SQL Cluster

Single user mode in SQL Cluster

Rate Topic

Display Mode

Topic Options

Author

Message

p.van.rixel

Posted Wednesday, April 23, 2008 3:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 9:06 AM
Points: 6, Visits: 203

Hi,

Have a active/passive sql server cluster (2005) and have to go into single user mode. Have checked a few options already but still the cluster admin is (I think) the process that prevents me to go in single user mode.

If I shutdown the cluster I cannot connect to my (shared) disks, so what do I have to do to go into single user mode in a 2-node cluster?

Rgrds,
Paul

Post #489137

Todd Carrier

Posted Wednesday, April 23, 2008 11:30 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 1,070, Visits: 667

What a puzzle! And I can't seem to find anything about it documented anywhere. Sort of a catch-22 right? Start the SQL instance in single-user mode, but some connection is coming in first. I suspect that you are right, that the clustering service is connecting to do its IsAlive and LooksAlive polling. Perhaps SQL Agent? Can you stop the agent service first?

The only idea I had was to change the configuration of SQL Server in Cluster Administrator first... settings on the advanced tab, to do not restart, and change the poll interval on the Looks Alive and Is Alive to a 5 minute period or something.

Darn, we are trying to build a recovery plan in case we need to restore system databases, so I am very interested in hearing how one gets connected after starting in single-user mode.

Any Clustering Gurus out there?

Todd Carrier
MCITP - Database Administrator

Post #489456

Todd Carrier

Posted Wednesday, April 23, 2008 1:21 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 1,070, Visits: 667

Found it! these folks ran accross the same issue. Looks like taking SQL resource offline from cluster administrator first does the trick.

http://www.sqlservercentral.com/Forums/Topic485250-357-1.aspx#bm485257

Todd Carrier
MCITP - Database Administrator

Post #489546

p.van.rixel

Posted Friday, May 02, 2008 6:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 9:06 AM
Points: 6, Visits: 203

Todd,

Finally succeeded in moving those databases. You also have to pause the other node so the node will not failover to the other host.
Had also some sqlserver -m and sqlcmd connection problems but after that it was a piece of cake.

Such a simple solution and why one has to take such an effort in this matter. Microsoft should do better on this!

Paul van Rixel
Oracle and SQLserver DBA

Post #494167

john p

Posted Friday, January 16, 2009 3:30 PM

Valued Member

Group: General Forum Members
Last Login: Monday, June 25, 2012 10:56 AM
Points: 55, Visits: 86

Hi Todd and p.van.rixel,

I am trying to move msdb and model datafiles to different locations on my sql server 2005(sp3) cluster per KB224071(sounds like what you were trying to do) and am having problems.

This is what I am doing:

1)pause the failover node
2)change the startup parameters for the instance to use -m -c -T3608
3)bring the sql server cluster group offline (to make the startup parameters take effect)
4)bring the sql server cluster group online. all services start except for the sql server agent (this is by design i assume)
5)then i try to connect to the instance from sql server management studio from a remote machine and get the "login faile for user 'sa'. Reason: server is in gingle user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)" error message.

It seems that this should work based on the info that you posted. Can you review the steps that I took above and let me know what I am doing wrong?

I appreciate it!

Thanks,

John

Post #638569

Todd Carrier

Posted Friday, January 16, 2009 5:19 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 1,070, Visits: 667

Hi John P,

I sounds like the cluster service is connecting to the SQL server to perform it's LooksAlive and IsAlive checks. So try taking the SQL Server Service offline from cluster administrator. At this point you'll probably have to restart the service with your switches. I wonder if you'll be able to connect to SQL by name or perhaps you'll have to by it's IP address.

Todd

Todd Carrier
MCITP - Database Administrator

Post #638602

john p

Posted Friday, January 16, 2009 6:18 PM

Valued Member

Group: General Forum Members
Last Login: Monday, June 25, 2012 10:56 AM
Points: 55, Visits: 86

Hi Todd - thanks for the tip!

I was able to get the startup parameters in an also connect without any issues...

I got this to work by using these steps exactly:

1)From Cluster Administrator: stop the cluster service for the failover node
2)From Cluster Administrator: take the SQL Server resource offline
3)From SQL Server Configuration Manager: add the startup parameters -c;-m;-T3608
4)From Cluster Administraror: bring the SQL Server resource online again

This was the same as what is listed above except pausing the node didn't work...I had to completely stop the failover node...now I feel like kicking myself! Hehe

Regards,

John

Post #638612

« Prev Topic | Next Topic »

Permissions