The current environment:
SQL server 2005 hosted on clustered environment with Active and Passive nodes. We started with passive node. Ran the upgrade advisor; found no issues. Proceeded with in-place upgrade. Upgrade completed with no issues. We rolled the cluster. SQL server service failed to start.
Got following error
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 33009, state 2, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
For more information, see Help and Support Center at _http://go.microsoft.com/fwlink/events.asp.
When searched on this forum, I got following article that pretty much ran to in to the same problem that we encountered.
Ran following commandfollowing command to start SQL Server and to bypass any upgrade scripts:
net start mssqlserver /f /T3608
Based on following article -http://support.microsoft.com/default.aspx/kb/960781, tried to login to SQL using the 'admin-only' connection from Management Studio and from command prompt from the serveritself.
sqlcmd -A -d MASTER -E -S BAINBRIDGE
Got error saying DAC needed to be enabled.
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>sqlcmd -A -
HResult 0x274D, Level 16, State 1
SQL Network Interfaces: Could not establish dedicated administrator connection (
DAC) on default port. Make sure that DAC is enabled .
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>
Searching through MSDN again, found that DAC on cluster is by default off. On cluster configurations, the DAC will be off by default. Users can execute the remote admin connection option of sp_configure to enable the DAC listener to access a remote connection. But , can not run the SP since I am not able to log in.
I would like toknow how toenable DAC on SQL server 2008.Read more...