Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Single user mode in SQL Cluster


Single user mode in SQL Cluster

Author
Message
p.van.rixel
p.van.rixel
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 209
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
Todd Carrier
Todd Carrier
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1759 Visits: 927
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
Todd Carrier
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1759 Visits: 927
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
p.van.rixel
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 209
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
john p
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 87
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
Todd Carrier
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1759 Visits: 927
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
john p
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 87
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search