SQL SERVER – Script Upgrade Mode on Failover – Why?

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:


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!


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:

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 (http://blog.sqlauthority.com)

SQL SERVER – Thinking about Deprecated, Discontinued Features and Breaking Changes while Upgrading to SQL Server 2012 – Guest Post by Nakul Vachhrajani

nakul SQL SERVER   Thinking about Deprecated, Discontinued Features and Breaking Changes while Upgrading to SQL Server 2012   Guest Post by Nakul VachhrajaniNakul Vachhrajani is a Technical Specialist and systems development professional with iGATE having a total IT experience of more than 7 years. Nakul is an active blogger with BeyondRelational.com (150+ blogs), and can also be found on forums at SQLServerCentral and BeyondRelational.com. Nakul has also been a guest columnist for SQLAuthority.com and SQLServerCentral.com. Nakul presented a webcast on the “Underappreciated Features of Microsoft SQL Server” at the Microsoft Virtual Tech Days Exclusive Webcast series (May 02-06, 2011) on May 06, 2011. He is also the author of a research paper on Database upgrade methodologies, which was published in a CSI journal, published nationwide. In addition to his passion about SQL Server, Nakul also contributes to the academia out of personal interest. He visits various colleges and universities as an external faculty to judge project activities being carried out by the students. Disclaimer: The opinions expressed herein are his own personal opinions and do not represent his employer’s view in anyway.

Blog | LinkedIn | Twitter | Google+

Let us hear the thoughts of Nakul in first person –

Those who have been following my blogs would be aware that I am recently running a series on the database engine features that have been deprecated in Microsoft SQL Server 2012. Based on the response that I have received, I was quite surprised to know that most of the audience found these to be breaking changes, when in fact, they were not! It was then that I decided to write a little piece on how to plan your database upgrade such that it works with the next version of Microsoft SQL Server.

Please note that the recommendations made in this article are high-level markers and are intended to help you think over the specific steps that you would need to take to upgrade your database.

Refer the documentation – Understand the terms

Change is the only constant in this world. Therefore, whenever customer requirements, newer architectures and designs require software vendors to make a change to the keywords, functions, etc; they ensure that they provide their end users sufficient time to migrate over to the new standards before dropping off the old ones. Microsoft does that too with it’s Microsoft SQL Server product. Whenever a new SQL Server release is announced, it comes with a list of the following features:

  1. Breaking changes
    1. These are changes that would break your currently running applications, scripts or functionalities that are based on earlier version of Microsoft SQL Server
    2. These are mostly features whose behavior has been changed keeping in mind the newer architectures and designs
    3. Lesson: These are the changes that you need to be most worried about!
  2. Discontinued features
    1. These features are no longer available in the associated version of Microsoft SQL Server
    2. These features used to be “deprecated” in the prior release
    3. Lesson: Without these changes, your database would not be compliant/may not work with the version of Microsoft SQL Server under consideration
  3. Deprecated features
    1. These features are those that are still available in the current version of Microsoft SQL Server, but are scheduled for removal in a future version. These may be removed in either the next version or any other future version of Microsoft SQL Server
    2. The features listed for deprecation will compose the list of discontinued features in the next version of SQL Server
    3. Lesson: Plan to make necessary changes required to remove/replace usage of the deprecated features with the latest recommended replacements

Once a feature appears on the list, it moves from bottom to the top, i.e. it is first marked as “Deprecated” and then “Discontinued”. We know of “Breaking change” comes later on in the product life cycle.

What this means is that if you want to know what features would not work with SQL Server 2012 (and you are currently using SQL Server 2008 R2), you need to refer the list of breaking changes and discontinued features in SQL Server 2012.

Use the tools!

There are a lot of tools and technologies around us, but it is rarely that I find teams using these tools religiously and to the best of their potential. Below are the top two tools, from Microsoft, that I use every time I plan a database upgrade.

The SQL Server Upgrade Advisor

Ever since SQL Server 2005 was announced, Microsoft provides a small, very light-weight tool called the “SQL Server upgrade advisor”. The upgrade advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade. The analysis examines objects that can be accessed, such as scripts, stored procedures, triggers, and trace files. Upgrade Advisor cannot analyze desktop applications or encrypted stored procedures.

Refer the links towards the end of the post to know how to get the Upgrade Advisor.

The SQL Server Profiler

Another great tool that you can use is the one most SQL Server developers & administrators use often – the SQL Server profiler. SQL Server Profiler provides functionality to monitor the “Deprecation” event, which contains:

  • Deprecation announcement – equivalent to features to be deprecated in a future release of SQL Server
  • Deprecation final support – equivalent to features to be deprecated in the next release of SQL Server

You can learn more using the links towards the end of the post.

A basic checklist

There are a lot of finer points that need to be taken care of when upgrading your database. But, it would be worth-while to identify a few basic steps in order to make your database compliant with the next version of SQL Server:

  1. Monitor the current application workload (on a test bed) via the Profiler in order to identify usage of features marked as Deprecated
    1. If none appear, you are all set! (This almost never happens)
    2. Note down all the offending queries and feature usages
  2. Run analysis sessions using the SQL Server upgrade advisor on your database
  3. Based on the inputs from the analysis report and Profiler trace sessions,
    1. Incorporate solutions for the breaking changes first
    2. Next, incorporate solutions for the discontinued features
  4. Revisit and document the upgrade strategy for your deployment scenarios
  5. Revisit the fall-back, i.e. rollback strategies in case the upgrades fail
    1. Because some programming changes are dependent upon the SQL server version, this may need to be done in consultation with the development teams
  6. Before any other enhancements are incorporated by the development team, send out the database changes into QA
    1. QA strategy should involve a comparison between an environment running the old version of SQL Server against the new one
    2. Because minimal application changes have gone in (essential changes for SQL Server version compliance only), this would be possible
  7. As an ongoing activity, keep incorporating changes recommended as per the deprecated features list
  8. As a DBA, update your coding standards to ensure that the developers are using ANSI compliant code – this code will require a change only if the ANSI standard changes

Remember this: Change management is a continuous process. Keep revisiting the product release notes and incorporate recommended changes to stay prepared for the next release of SQL Server.

May the power of SQL Server be with you!

Links Referenced in this post

  • Breaking changes in SQL Server 2012: Link
  • Discontinued features in SQL Server 2012: Link
  • Get the upgrade advisor from the Microsoft Download Center at: Link
  • Upgrade Advisor page on MSDN: Link
  • Profiler: Review T-SQL code to identify objects no longer supported by Microsoft: Link
  • Upgrading to SQL Server 2012 by Vinod Kumar: Link

Reference: Pinal Dave (http://blog.sqlauthority.com)