SQL DBA learning curve

if you happen to failover a SQL server cluster .. you may have to wait before the SQL comes online .. and you will get below messages

1> SQL error log or while connecting thro SSMS

Login failed for user ‘domain\username’. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: ] 2012-10-18 05:20:16.370 Logon Error: 18401, Severity: 14, State: 1.

2> The SQL error log may also have below errors  (if the Database are involved in replication )

2012-10-18 05:20:29.800 spid9s Executing sp_vupgrade_replication.

2012-10-18 05:20:29.800 spid9s Executing sp_vupgrade_replication.
Database ‘master’ is upgrading script ‘repl_upgrade.sql’ from level 167776160 to level 167777660
Upgrading publication settings and system objects in database [DB1].
Upgrading publication settings and system objects in database [DB2].

Error executing sp_vupgrade_replication.

2012-10-18 05:21:05.090 spid9s Cannot perform this operation while SQLServerAgent is starting. Try again later.2012-10-18 2012-10-18 05:21:05.090 spid9s Saving upgrade script status to ‘SOFTWARE\Microsoft\MSSQLServer\Replication\Setup’

2012-10-18 05:21:05.090 spid9s Saving upgrade script status to ‘SOFTWARE\Microsoft\MSSQLServer\Replication\Setup’.

2012-10-18 05:21:05.090 spid9s Saved upgrade script status successfully.

Cause : This happens because the Databases have never been failed over this cluster node (the current active node) after the successful upgrade of service pack (e.g SQL 2008 SP2 or SP3 ) or are failed over for the First time after upgrade

**** some of the Database do not upgrade if the SQL agent is running **** so you need to let the upgrade run with SQL agent been STOPPED ..

To resolve this issue, follow these steps:

1. In the cluster administrative tools, bring both SQL Server Agent and the SQL Server service offline. 
2. Bring the SQL Server service back online while SQL Server Agent is still offline. 
3. After the startup process has begun, review the error logs to see whether the following entries appear there:If you do not find these entries, the replication upgrade has completed successfully. To verify this, check whether the value of the Upgrade registry key at the following registry entry is updated to 1. (This indicates a successful upgrade.)
   • spid7s Upgrading publication settings and system objects in database [DBName].
   • spid7s Cannot perform this operation while SQLServerAgent is starting. Try again later.
   • spid7s Error executing sp_vupgrade_replication.
   • spid7s Saving upgrade script status to ‘SOFTWARE\Microsoft\MSSQLServer\Replication\Setup’.
   • spid7s Saved upgrade script status successfully.
4. 
   HKLM\SOFTWARE\Microsoft\MSSQLServer\Replication\Setup 

5. Run the following command, and then look for a transaction that is named “tran_sp_MScreate_peer_tables.” If you do not see an entry sthat has this name, you have additional verification that the replication upgrade completed on its own. use [DBName] 
   select * from sys.dm_tran_active_transactions where name = ‘tran_sp_MScreate_peer_tables’
6. Stop the instance of SQL Server, bring both SQL Server Agent and the SQL Server service online on Node1, fail the instance of SQL Server over to the other node, and fail the instance back to the original node. 

If the instance of SQL Server is a stand-alone instance, you can resolve the issue by stopping both SQL Server and the SQL Server Agent service, disabling the SQL Server Agent service, and then restarting just the SQL Server service. This lets the upgrade process complete in the database. After this process is complete, you can restart the SQL Server Agent service.

Reference –> http://support.microsoft.com/kb/2509302

note the 6th point : you may have to failover cluster few times .. if you upgrade does not work clean initially ..

Thanks for reading 🙂

GS