SQL Server Upgrade Issues

Pre-Requisites

We usually think of SQL Server simply as a database engine, while it is a data platform which supports data warehousing, reporting, data integration and data analytics. An in-depth planning is required when upgrading from one version of SQL Server to a newer version along with the features that SQL Server offers. Upgrading these extensive number of features can be remarkably complicated and time-consuming than just upgrading the database engine alone.  Below mentioned are few of the “lessons learned” that help reduce risks of data losses etc.:

1.       Application compatibility:

Before planning for upgrade activity, make sure that you discuss the compatibility of SQL Server and the associated application with your application vendor. 

2.       Preparation for migration:

In case your upgrade activity fails due to some reason and there is no solution possible other than migration, then be prepared for the migration activity along with the resources required.  

3.       Plan for downtime window:

Start by planning the downtime window that an instance will take to complete the upgrade process. Make sure to keep the software installer files copied to the system well in advance. Install 'sqldom' and 'SQL upgrade advisor' before start of the downtime window. Check msi/msp errors if any using vb script, moksha tool or MSI Fix tool. Check the software and hardware requirements. Do not forget to add roll back time as well in downtime window.

4.       Resolve access issues:

Check for the required level of permissions that a user should have for upgrading a SQL Server environment. It could be the admin level privileges or service account access issues. Keep the passwords of the 'system admin' account and service account handy with you.

5.       Keep Backups:

Be prepared for any kind of failures. It is important to have an OS level snapshot, database level backups on disk or TSM whichever backup tool you are using, login backups, SQL job backups and registry backup. For worst case scenario, keep a copy of system databases file level backups. If there is encryption enabled than keep the backup of keys as well.

6.       Run isolated:

Don't plan for any other application upgrade when you have planned SQL Upgrades. You will not be able to understand which upgrade caused issues if there are any on OS level after the completion.

7.       High availability and disaster recovery instances:

If you have high availability and disaster recovery instances in the environment, make sure that you have well planned the downtime for each node. Ensure that manual failovers and failbacks are working before the upgrade activity starts.

8.       Check for dependencies:

Keep in mind the dependent services as well. Upgrade does not include only databases. It includes services such as reporting services, analysis services and integration services. Be certain that the upgrade will work for these service functionalities as well.

9.       Keep the previous installers ready:

Make sure that for whatever upgrade you are going, you have the previous installer files ready with you. For msi/msp issues and .dll issues, you might require those installer files. These may include the version installer files, update installer files and even the hotfixes or patch installer files.

Assumptions

Below mentioned are the key factors which should be taken into consideration while moving ahead for SQL Server Upgrade procedure:

1.    For Upgrading SQL Server from 2008 to 2012 or 2014, the Operating System on which the SQL Server resides should be at least 2008.

2.    The edition of the SQL Server instance cannot be downgraded. We cannot move from Enterprise to Standard or Express.

3.    If your SQL Server is 2005 then you must first upgrade it to 2008, then only it can be upgraded to 2012 or 2014.

4.    An instance of SQL Server cannot be modified from 32 to 64 bits or from 64 to 32 bits.

5.    You cannot restore systems databases from different builds.

Technical overview

Since version 2008 of SQL Server is already out of support by Microsoft and new versions have got several useful features including performance optimization and security, it is important to upgrade the servers to newer versions. It will keep the data protected, applications more robust and will serve with new features.

While we upgrade SQL Server, we come across different issues and errors which makes it difficult to complete the upgrade within the provided time window. Few errors which were encountered during the upgrade have been enumerated as below:

1.    Service Account Issues: 

Problem:

It often happens that the service account password for the SQL Server Service could be incorrect or service account gets locked out or gets disabled. Such issues arise when SQL server installer performs a rule check while upgrading the server and you will get issues in ‘Engine_SqlEngineHealthCheck’ rule.

Rule name: Engine_SqlEngineHealthCheck

Rule description: Checks whether the SQL Server service can be restarted; or for a clustered instance, whether the SQL Server resource is online. 

Result: Failed

Message/Corrective Action: The SQL Server service cannot be restarted; or for a clustered instance, the SQL Server resource is not online.

You can also check your detail.txt file where you would get an error stating Access is denied. If there are service related issues, you will not be able to see them in SQL Server error log. You would have to check the event log files to get to know that there is a service account related issues.

Cause:

It happens if someone changes the service account password but does not updates the same in SQL Server Service. It will not affect the running SQL Server but whenever you try to restart the service it will not restart.

OR

If a user tries to enter the incorrect password multiple times or a service attempts to submit the stored old password repeatedly, the account gets locked. It will also not affect the running SQL Server but whenever you try to restart the service it will not restart.

OR

If the administrator has configured the SQL Server Service account to require regular password changes. This setting can also get into service account issues.

For the service account related issues, you will get the error in event logs as shown below. Similar type of errors will be seen in case of disabled and locked out account.

Resolution:

In these cases, you can follow the following steps:

·       Password is in error: Change the password

If the service account is a domain account, then it needs to be changed on the active directory first and then in SQL Service. Always use SQL Server Configuration manager to make this kind of changes.

OR

As a workaround, you can change the service account of the service to local account and then try to upgrade.

·       Account is Disabled/locked

If the service account is locked out or disabled, then you can just go to configuration manager and unlock or enable it.

2.    Running different installer: 

It might happen sometimes that by mistake you keep a copy of 64-bit installer on 32-bit system. It will throw you error while you run the installer that “The system cannot find the file specified”. It is a small issue, but it can be useful when you are running short of time and need to complete the upgrade.

Problem:

“The system cannot find the file specified”

Since the 32-bit system looks for 32-bit installation media, it is not able to find it and hence it throws the error that the system cannot find the file specified.[2]

Cause:

Make sure that you go through pre-requisites and copy the correct installer.

Resolution:

Check the type of system well before using “msinfo32” in run. It will show you the complete system information.

3.    Valid database compatibility level and successful connection Rule:

Problem:

If you have the reporting services installed on your system and the service requires upgrade along with your database engine, sometimes it shows the error “Valid database compatibility level and successful connection failed” in the validation rule check of the upgrade installer.

Cause:

This error could be encountered because of the compatibility level of the Reporting database.

OR

If the SQL Server name which is defined in Reporting Service Configuration Manager is incorrect, it might cause validation issues during the upgrade process.

Resolution:

·       If the database compatibility level is wrong, you will have to go to database engine and correct the compatibility level of the Reporting Server database.

·       If the SQL Server name is incorrect then you will have to follow the below steps:

Ø  Go to Start > All programs > MSSQL Server 2008 R2 or 2008 (whichever version you are going to upgrade) > Configuration Tools > Reporting services configuration manager.

Ø  Click on Connect button on the Reporting services configuration connection window.

Ø  Click on Database tab from left hand pane and click on Change Database button.

Ø  Select radio button “Choose an existing report server database” and Click on Next button.

Ø  Under Server name box, enter the correct SQL Server name, keep another settings default and click on Test Connection button.

Ø  It will show Test Connection successful message on which you can finish the wizard.

  

4.    Prior Visual Studio Instance requiring update failed:

Problem:

You might receive an error stating ‘Prior Visual Studio Instance requiring update failed’ on the installation rules step during rule check. The error looks like this:

Cause:

This computer has an installation of Visual Studio 2010 that requires a Service Pack 1 update that is needed for a successful installation of SQL Server based on your feature selection. To continue, install the required Visual Studio 2010 Service Pack 1 from SQL Server media or from http://go.microsoft.com/fwlink/?LinkID=220649. The error showcases that the installation requires SP1 of the Visual Studio 2010. Even though one might not have visual studio installed on their system it asks to update to SP1. The reason for this is that few SQL Server components use Visual Studio Shell, for example SSMS. That’s why it shows the error on the rule check screen where the management studio should be upgraded to the higher version.

Resolution:

·       You can navigate to the path “\redist\VisualStudioShell” in installer folder of SQL Server 2012 and install the file ‘VS10sp1-KB983509.msp’ existing in the folder. After the installation, restart your computer. The file will look like this: 

OR

·       You can try to download VS2010SP1 and install it explicitly and restart your system. Once, it is updated to SP1, you will notice that the rule check is successful.

 

5.    Could not register type library for file:

Problem:

It has been seen in most of the cases that the upgrade is successful, but few components do not get upgraded. Similar issue is seen in case of Management Studio. You might encounter the following error:

“Error installing Microsoft Visual Studio 2010 Shell

Error 1911.Could not register type library for file C:\Program Files (x86) \Common Files\Microsoft Shared\MSEnv\dte90.olb.  Contact your support personnel.

Error code: 1911”

 Cause:

 This could happen because of the following reasons:

·       The file dte90.olb file is not registered correctly.

·       The file dte90.olb is corrupted.

·       KB3072630 update blocks the installation of SQL Server client tools.

             Solution:

·       If the file is not registered correctly then the following steps can be followed:

Ø  Open a command prompt window as administrator and enter the following:

regsvr32 “path and file name with extension of the file that needs to be registered”

Ø  If you want to unregister the file, then you should follow the same procedure. Open a command prompt window as administrator and enter the following:

regsvr32 /u “path and file name with extension of the file that needs to be registered” 

·       If you suspect that even after registering the file, it is not working, and the file might have got corrupted then copy the file from another server which has the same SQL Server version and build and then try to register the file.

·       If you see that the update KB3072630 is installed on your system and the management components are not getting upgraded, then you might want to uninstall this update and try to upgrade the components. You will have to check with your system administrator before uninstalling this update.

·       If nothing works, you could try to install Microsoft Visual Studio 2010 Shell manually. 

6.    Error installing Microsoft Visual Studio 2010 Shell: 

Problem:

We often get error stating “Error installing Microsoft Visual Studio 2010 Shell” while installing Microsoft Visual Studio 2010 Shell while trying to resolve the error code 1911. There could be different cases where you get into this error. One of the most common case is that the file ‘vc_red.msi’ is missing from the path:

‘C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Update Cache\KB3045318\GDR\redist\VisualStudioShell\VC10SP1\vc_red.msi’.

While going through the log file ‘VC10Redist_Cpu64_1.log’, it does not give any specific error that leads to resolution.

Cause:

In this case, the issue could have been caused due to the file unavailability. The file mentioned in the path above i.e., vc_red.msi might not be existing in the path due to some reason. Most of the times, the file is deleted by someone to release disk space.

Resolution:

The KB article KB3045318 can be downloaded and the files should be extracted in a folder. From the extracted files, we can find the file required and paste it in the location mentioned above.  For extraction of the KB article contents, we can open command prompt as administrator and go to the path where the KB article exists. New folder can be created and then the file can be extracted as follows:

Filename /x “path where file has to be extracted” 

7.    Dll, msi and msp missing:

Problem:

It is a very common problem where we fall into missing .msi (Microsoft installer) and .msp (Microsoft patch) files from the installer during the upgrade process. The error will vary depending on the type of the file and the location of file as well as the last installation that was done on the instance. You will also notice that the error may be thrown at the start of the installation or in between. The error itself will guide you that which file is missing and from which installer. It will also give you the path where the file exists. The error will somewhat look like this:

In case of msi missing:

“The cached MSI file 'C:\Windows\Installer\FileName.msi' is missing. Its original file is 'sql_engine_core_inst.msi' and it was installed for product 'Microsoft SQL Server 2008 Database Engine Services' from 'NetworkPath', version 'VersionNumber', language 'ENU'.”

In case of msp missing:

“The cached patch file "C:\Windows\Installer\FileName.msp" is missing. The original file for this cached file is "sql_engine_core_inst.msp", which can be installed from "Service Pack 3 for SQL Server 2008 (KB2546951) (64-bit)", version VersionNumber.”

Cause:

The files that shows as missing are not missing from the installation media. However, they are missing from Windows Installer Cache which is “%windir/installer”. Whenever a product is installed using windows installer, a version of the original .msi file is stored in the installer cache. The information stored in these files is used later by the future installers. If the file does not exist in the cache, the installation cannot move forward and throws error.

Resolution:

We have different resolution procedures available for fixing the missing .msi, .msp and .dll issues:

·       You can try to download a tool which is provided by Microsoft called “FixMissingMSI”. This tool is easy to use and only the original media locations should be provided. It will show you the files that are missing and will also provide the information that which file will be pasted in which location for which corresponding .msi or .msp or .dll file. The interface of the tool looks like below:

·       Another method is by using ‘vbscript’.  You can easily download ‘FindSQLInstalls.vbs’ script from Microsoft website and run it from an elevated command prompt window as follows:

Cscript FindSQLInstalls.vbs %computername%_Output.txt

This will generate a file which will have all the missing .msi errors. You should find the errors in the file using search strings such as “does not” and “!!!”. When you find such strings, you should look for the copy statement in the file. It will guide that which file will be copied where.  Run these copy commands using elevated command prompt and search the file again using same procedure until there are no missing .msi errors left. Once you have completed the search through file and no other errors can be seen, try to run the setup again. It will work as expected.

·       There is one more method where you can run the executable multiple times and as and when the error pops up, you can copy the file from the installer which the error directs to and paste it to C:\Windows\Installer cache and rename it to the file that error suggests. It is time taking process.

10.   MSSQLSERVER.INACTIVE instance:

      Problem:

    While upgrading the SQL Server Instance, on the installation page it was showing the instance as MSSQLSERVER.inactive. It was not allowing the instance for selection and hence I was not able to move forward with the upgrade process.

      Cause:

     It might be possible that the SQL 2008 or 2008 R2 instance components which you are upgrading may not have been installed correctly which makes the component in inconsistent state and results into an orphaned SQL Server Instance. 

       Resolution:

       To fix this issue you should follow the following steps:

·       Search your computer for the most recent “Datastore_Discovery.xml” file. If you open this file, you will find that the file has huge amount of data is very difficult to read. So, open this file using Visual Studio 2008 or 2010. Click on Edit-> Advanced-> Format document to get it into readable format.

·       Once the file is in readable format, try to search for “MSSQLServer.Inactive” as instance ID. The line in document will look somewhat like below:

<InstanceUrn="Machine[@ID='IWE']/Product[@ID='SQLVNEXT']/Instance[@ID='MSSQLSERVER.INACTIVE']" ID="MSSQLSERVER.INACTIVE" Name="MSSQLSERVER.INACTIVE"

 This is just a part of the complete line. As you can see here, the instance ID is shown as “MSSQLServer.Inactive”, you will see something called product code above this line. Copy this product code which is associated with the inactive instance. 

·       Open an elevated command line and paste the following:

msiexec /x {Enter the copied product code here}

·       Repeat the procedure for all the product code associated with inactive instances in the file. Make sure you DO NOT run this for any valid SQL instance. 

·       To make sure that all the inactive instances have been removed, run SQL Server Discovery Report to check that there are no pending inactive instances left. 

11.   Window disappears during upgrade:

          Problem: 

I came across a weird issue where the installer will just disappear after a certain step and you can’t even see it in the task manager. In my case, it was after “Setup Support files” step where it used to disappear and then never used to proceed. 

Cause: 

This happens if there are traces left during install or uninstall of the previous versions. It could also be because of some corrupted registry keys that block you from a successful installation.

Resolution: 

There are two methods (could be more) that I am aware of for resolving this issue:

·       There is a tool available from Microsoft which is called “Microsoft Program Uninstall and Install”. This tool can easily be downloaded from the Microsoft website using the link http://support.microsoft.com/mats/Program_Install_and_Uninstall .

This troubleshooter has step to step guidelines to help you with installation or uninstallation of your program. You can use this tool is on Windows 7, 8, 8.1 and 10.

·       If the above method does not work, you will have to go with taking backup of everything related to SQL Server including the databases and uninstall it completely from Add/Remove Programs. Also, you will have to delete the registry details associated with the instance. To delete the registry, follow the steps below:

Ø  Delete the following registry keys using regedit:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

Ø  Go to the following path and delete all the sub-keys referencing SQL Server HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall

Ø  Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and delete all the keys referencing SQL Server.

Ø  Rename all the SQL Server folders in the computer

Ø  Reboot the system and try to install the updated version of SQL Server. Once the installation is done, restore the databases and all the logins and jobs.