Script level upgrade for database 'master' failed because upgrade step 'SSIS_Hotfix_Install.sql' encountered error 3906, state 1, severity 16. This is a serious error condtion which might interfere with regular operation and the database will be taken offline



One day we got a request asking for upgrade present sql server environment (Microsoft SQL Server 2014 SP1) to Microsoft SQL Server 2014 (SP2 CU11). So we followed the below process. And the environment is in Always on.

So we have decided first we will do the patching in Secondary replica, then we can do it in Primary replica. So we have ran the. exe file that we have downloaded from the et(https://support.microsoft.com/en-us/help/4052725/cumulative-update-10-for-sql-server-2014-sp2). After we successfully installing the patching in secondary replica and trying to bring the sql server instance is failed with the below error messages in the event viewer 




After seeing the above errors we are under impression that we need rebuild the master database. But don't do that.We are not able to restart the services and it is throwing above error in the event viewer.  So to bring the services up we used -T902 trace flag in the startup parameters of sql server service. Then immediately sql server services came online. Once the services came online we are able to connect to the respective instance thorough management studio (SSMS).  After we connect to the instance we found that there is a database called SSIS in the instance and this database is also part of always on availability group. This database is the main culprit here and as long as this database exists in the instance we cannot connect to the sql server instance normally, instead we have to use -T902 trace flag, so what we did was we removed this database from availability group, once we remove database from the availability group then the status of SSIS database would go into (Restoring…) mode. Now we can delete this database from the database instance, or take the database into offline mode (https://blogs.msdn.microsoft.com/mattm/2012/09/19/ssis-with-alwayson/). Before we are upgrading instance with cumulative update we don’t have idea about this error. So what we did was we remove the SSIS database, now I can also remove –T902 trace flag from the startup parameter and able to start the services successfully. So do the same behavior in another replicas also before you are applying cumulative upgrade. Remove the SSIS database and apply the patch, one the patch is succeeded then we can restore the SSIS database and then add that to the availability group.

Comments

Post a Comment

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more

Pre login Handshake or Connection Timeout Period

Image
Service was unable to open new database connection when requested. SqlException: Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=16225; handshake=14840; Connection string - Data Source=sqlserverintancenamehere;Initial Catalog=CatalogNamehere;Integrated Security=True;Persist Security Info=False;Max Pool Size=1000;Connect Timeout=20;Load Balance Timeout=120;Packet Size=4096;Application Name=Applicationname hereBusinessLayerHost.exe;Workstation ID=HostnameHere setspn -a MSSQLSvc/hostname.doaminname.net:1433 ServiceAccountNameHere need to run the above command in a command prompt, most of the time you would NOT have permissions to run this, please check with Active Directory Team or Wi...
Read more

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server

Image
/*  There is only one master key, you can not create  multiple */ use master go CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPasswordHere' GO /*  Creating certificate in the master database, after creating  master key */ use master GO CREATE CERTIFICATE MyFirstCert WITH SUBJECT = 'TDECertificate' ; GO /* Enable databse encryption key but you can not see under this specific user database. */ USE G GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyFirstCert GO ALTER DATABASE G SET ENCRYPTION ON;  /*  Backing up certificate */ use master go BACKUP CERTIFICATE MyFirstCert TO FILE = 'C:\Certificates\MyFirstCert' WITH PRIVATE KEY ( FILE = 'C:\Certificates\MyFirstCertKey' , ENCRYPTION ...
Read more