Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Single user mode in SQL Cluster Expand / Collapse
Author
Message
Posted Wednesday, April 23, 2008 3:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 16, 2013 3:45 AM
Points: 6, Visits: 208
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
Posted Wednesday, April 23, 2008 11:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:38 PM
Points: 1,298, Visits: 783
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)

Post #489456
Posted Wednesday, April 23, 2008 1:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:38 PM
Points: 1,298, Visits: 783
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)

Post #489546
Posted Friday, May 02, 2008 6:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 16, 2013 3:45 AM
Points: 6, Visits: 208
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
Posted Friday, January 16, 2009 3:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Friday, January 16, 2009 5:19 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:38 PM
Points: 1,298, Visits: 783
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)

Post #638602
Posted Friday, January 16, 2009 6:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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!

Regards,

John



Post #638612
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse