Single user mode in SQL Cluster

Question

Single user mode in SQL Cluster

p.van.rixel

Old Hand

Points: 302
More actions
April 23, 2008 at 3:28 am

#95858

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 1

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 (SQL 2008)
MCSE: Data Platform (SQL 2012)

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 2

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 (SQL 2008)
MCSE: Data Platform (SQL 2012)

p.van.rixel Old Hand Points: 302 More actions · Answer 3

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

john p SSC Eights! Points: 873 More actions · Answer 4

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

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 5

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 (SQL 2008)
MCSE: Data Platform (SQL 2012)

john p SSC Eights! Points: 873 More actions · Answer 6

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