Troubleshooting Cluster related queries is always a challenge and every once in a while I get mails around these. One of my friend wrote email to me and it caught my attention and I took couple of days to explore and felt worth to share it here:
<EMAIL>
Hi Pinal.
We are running our production on SQL Server 2008 R2 SP1 Cluster. We have noticed in the error log that during failover from NodeA to NodeB OR NodeB to NodeA, the system databases are going to upgrade mode. Here is the error which we get for some time when we try to make connection immediately after failover.
Login failed for user ‘sa’. Reason: Server is in script upgrade mode. Only administrator can connect at this time.
Could you please help us in understanding, why this is happening? How to stop it from doing this every failover?
Awaiting response!
</EMAIL>
I have explained this error in the below blog:
SQL SERVER – Login Failed For User – Reason Server is in Script Upgrade Mode
As I mentioned in my blog, I have asked to share ERRORLOG to check what’s going on. You can refer this blog to find location of ERRORLOG files. He emailed me ERRORLOG and I checked all files and found something interesting.
File Name | Version Message | NETBIOS Message |
Errorlog.6 | Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (X64) | The NETBIOS name of the local node that is running the server is ‘SQLCRM-N02’ |
Errorlog.5 | Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64) | The NETBIOS name of the local node that is running the server is ‘SQLCRM-N03’ |
Errorlog.4 | Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (X64) | The NETBIOS name of the local node that is running the server is ‘SQLCRM-N02’ |
Errorlog.3 | Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (X64) | The NETBIOS name of the local node that is running the server is ‘SQLCRM-N02’ |
Errorlog.2 | Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64) | The NETBIOS name of the local node that is running the server is ‘SQLCRM-N03’ |
Errorlog.1 | Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (X64) | The NETBIOS name of the local node that is running the server is ‘SQLCRM-N02’ |
As we can see above, the version of SQL Server is different on both the nodes 9 (N02 = 10.50.2500 and N01 = 10.50.1600). This would happen if ALL nodes are not patched up to the same version of SQL Server. The resolution of above behavior would be to run setup patch on all nodes as make sure that version is same in ERRORLOG on ALL nodes.
Here is the reply:
Pinal,
Thanks for your quick reply. Your analysis makes sense. I have applied SP1 on SQLCRM-N03 and error disappeared.
Have you ever faced this situation? How did you troubleshoot this? Would love to know if there is any other mechanism to solve this problem and the steps involved.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
I have old software connecting camera ip write my friend but his lost source code and this using SQL Server database.
I’m writing a new website using PHP with MySQL and interacting with an old database using SQL Server.
This means: I want create the new camera, it will add to SQL Server, from php application I want when change data in SQL will fetching to my MySQL to use.
I want to reuse the database of SQL Server. When data from SQL Server changes, database using MySQl should also change and it should auto refresh 1-5 minute to sync data.
How to do it? With 3rd thirty software or I must write services running with windows? If write services, what I have to do?
Thanks.
Hi Pinal,
Thanks for your wonderful tips in your blog.
And regards to Script Upgrade Mode error, recently we had similar issue when we applied SP3 on SQL 2008R2 Cluster instance (Sharepoint applicatio)
But our scenario is bit different in terms of enivironment. It’s sharePoint intance configured with UCP (Utility Control Point) for managing database warehouse. We are not aware of this UCP configuration as it is not actively used. So after applying SP3 successfully we rebooted the server, post reboot the sql intance when to script upgrade mode. As a temp fix, we enabled T902 to suppress the script upgrade mode and made sql instance available to application team.
For permananet fix, we need to diasable the UCP aqnd drop the sysutiluty_MDW db created during UCP configuration and remove the T902 to allow the script mode to continue, max it should not take more than 10 min on each node after this.
Thanks & Regards,
Kotesh.