SQL Agent does not come online in Failover Cluster Manager after upgrade to SQL 2016

I recently upgraded SQL 2014 SQL Instance in a cluster to SQL 2016. After the upgrade, the SQL Agent fails to come online and the Event Viewer shows the below error messages:

SQLServerAgent could not be started (reason: Unable to connect to server 'ServerName\InstanceName'; SQLServerAgent cannot start).

The description for Event ID 17052 from source MSSQLSERVER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event: 

Severity: 16 Error:-1, OS: -1 [Microsoft][ODBC Driver 13 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 

The error message is related to the ODBC driver 13 that is not compatible with SQL 2016. I had to upgrade that to ODBC 13.1 from the url below. Once the upgrade is done, the SQL Agent service will come back online.

https://support.microsoft.com/en-nz/help/3185365/sql-server-2016-agent-fails-to-start-or-failed-to-retrieve-data-error-when-you-try-to-read-error-log-from-ssms-2016

'VIEW SERVER STATE permission was denied on object 'server''error message

We had a developer encounter this error when trying to perform a right-click on a table and selecting 'Select top 1000 rows'. SSMS would eventually display the results, but the pop-up error message is annoying.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

ADDITIONAL INFORMATION:

VIEW SERVER STATE permission was denied on object 'server', database 'master'. (Microsoft SQL Server, Error: 300)

I was able to identify the root cause as the version of SSMS being used. In this case, the developer was trying to access SQL 2014 Instance using a SQL 2012 SSMS. The issue has gone away when SQL 2014 SSMS was used.

SQL 2012 Install failure

I tried installing SQL2012 and encountered a failure with the below messages in the Summary file in folder C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\

Detailed results:

  Feature:                       Database Engine Services

  Status:                        Failed: see logs for details

  Reason for failure:            An error occurred during the setup process of the feature.

  Next Step:                     Use the following information to resolve the error, uninstall this feature, and then run the setup process again.

  Component name:                SQL Server Database Engine Services Instance Features

  Component error code:          0x851A001A

  Error description:             Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

I looked at the Event Viewer to see what caused it and noticed the below messages logged in there:

The server was unable to initialize encryption because of a problem with a security library. The security library may be missing. Verify that security.dll exists on the system.

TDSSNIClient initialization failed with error 0x139f, status code 0x80. Reason: Unable to initialize SSL support. The group or resource is not in the correct state to perform the requested operation.

TDSSNIClient initialization failed with error 0x139f, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The group or resource is not in the correct state to perform the requested operation. 

Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

I installed SQL 2012 with the same setup file before successfully, but this time it failed. I tried to figure out what changed recently in the company and remembered that TLS 1.0 and SSL 3.0 protocols were disabled (because of known security vulnerabilities) on all the servers and newer version of TLS & SSL was enabled which is TLS 1.1/1.2 . Disabling those protocols will cause problems in SQL Install process. This issue is addressed in CU1 (after SP3) of SQL 2012.

To resolve this, I had go enable the registry keys of the older SSL 3.0 (which is TLS 1.0, Microsoft renamed SSL 3.0 as TLS 1.0) as indicated below

HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL.3.0\Client:

Right–click on DisabledByDefault, click on Modify and enter the value 0 (hexadecimal)

Right click on 'Enabled', click on Modify and enter the value 1 (hexadecimal).

HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL.3.0\Server:

Right–click on DisabledByDefault, click on Modify and enter the value 0 (hexadecimal)

Right click on 'Enabled', click on Modify and enter the value ffffffff (hexadecimal).

HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client:

Right–click on DisabledByDefault, click on Modify and enter the value 0 (hexadecimal)

Right click on 'Enabled', click on Modify and enter the value 1 (hexadecimal).

HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server:

Right–click on DisabledByDefault, click on Modify and enter the value 0 (hexadecimal)

Right click on 'Enabled', click on Modify and enter the value ffffffff (hexadecimal).

After I modified the registrly keys, the SQL Install completed successfully. After the install is complete, make sure you patch it with SP3+CU1. At that point you can modify the registry keys back to normal.

Query to view the progress of a DBCC or INDEX REBUILD command

Imagine you are doing a DBCC or INDEX REBUILD command on a huge database and you are anxious to know when it finishes, below is a command to give you an estimate. If you don't get any result for this query while your command is running. just check for the specific command that spid is doing from sp_who2 and add that command to the where clause at the end of this query.

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]
,CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('DBCC', 'ALTER INDEX')

Query to view the status of BACKUP or RESTORE command

Below is a query to check the status of a backup or restore command:

SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

Query to display the Top 10 tables by space used

Below is a query to find out the Top 10 tables in a database by the amount of space used

 SELECT TOP 10
        a3.name AS SchemaName,
        a2.name AS TableName,
        a1.rows as Row_Count,
        (a1.reserved )* 8.0 / 1024 AS reserved_mb,
        a1.data * 8.0 / 1024 AS data_mb,
        (CASE WHEN (a1.used ) > a1.data THEN (a1.used ) - a1.data ELSE 0 END) * 8.0 / 1024 AS index_size_mb,
        (CASE WHEN (a1.reserved ) > a1.used THEN (a1.reserved ) - a1.used ELSE 0 END) * 8.0 / 1024 AS unused_mb

    FROM    (   SELECT
                ps.object_id,
                SUM ( CASE WHEN (ps.index_id < 2) THEN row_count    ELSE 0 END ) AS [rows],
                SUM (ps.reserved_page_count) AS reserved,
                SUM (CASE   WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                            ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END
                    ) AS data,
                SUM (ps.used_page_count) AS used
                FROM sys.dm_db_partition_stats ps
                GROUP BY ps.object_id
            ) AS a1

    INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )

    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

    WHERE a2.type <> N'S' and a2.type <> N'IT'  
    order by a1.data desc

Script to generate database Role membership and also to generate commands to add the users to database roles

During the migration of a database from one SQL Instance to another, we might have to re-create the permissions on the target SQL Instance. This is not required in all the cases, but only in some cases where the user permissions are not synced for some reason. So an easy way to script them out before performing the database refresh is to use the below script to capture the existing setup of database role membership:

-- List Database Roles and Members with Server Login 
SELECT ROL.name AS RoleName 
      ,MEM.name AS MemberName 
      ,MEM.type_desc AS MemberType 
      ,MEM.default_schema_name AS DefaultSchema 
      ,SP.name AS ServerLogin 
FROM sys.database_role_members AS DRM 
     INNER JOIN sys.database_principals AS ROL 
         ON DRM.role_principal_id = ROL.principal_id 
     INNER JOIN sys.database_principals AS MEM 
         ON DRM.member_principal_id = MEM.principal_id 
     INNER JOIN sys.server_principals AS SP 
         ON MEM.[sid] = SP.[sid] 
ORDER BY RoleName 
        ,MemberName;

Now after this is captured, you still have to manually run the below commands for each of the users captured:

sp_addrolemember 'Rolename','UserName'

It takes a lot of time to generate a script manually if there are more than 5 or 10 users. Below is a script to generate those commands as well by leveraging on the above script:

SET QUOTED_IDENTIFIER OFF
IF EXISTS (SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME="##USER_PERMISSIONS")
DROP TABLE ##USER_PERMISSIONS
CREATE TABLE ##USER_PERMISSIONS
(ROLENAME VARCHAR (100),
MEMBERNAME VARCHAR(100))
INSERT INTO ##USER_PERMISSIONS
SELECT ROL.name AS RoleName 
      ,MEM.name AS MemberName 
      FROM sys.database_role_members AS DRM 
     INNER JOIN sys.database_principals AS ROL 
         ON DRM.role_principal_id = ROL.principal_id 
     INNER JOIN sys.database_principals AS MEM 
         ON DRM.member_principal_id = MEM.principal_id 
     INNER JOIN sys.server_principals AS SP 
         ON MEM.[sid] = SP.[sid] 
ORDER BY MemberName, Rolename;
SELECT ("sp_addrolemember '"+ ROLENAME +"','"+MEMBERNAME+"'") AS CMD FROM ##user_permissions
go