Check Backup\Restore status

When running the AG wizard it is not clear what the percentage completion figure is of each stage.

Use the query below to examine the status of backup and restore operations.

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

 

 

Ref:https://www.mssqltips.com/sqlservertip/2343/how-to-monitor-backup-and-restore-progress-in-sql-server/

Check Backup\Restore status

SQL Server - Use specific IP/Ports not all

Hi All,

Just something that threw me this morning and took a while to understand what was going on.

note: to disable dynamic ports, just delete the value in the field. To reenable just add 0 and restart the sql instance.

If you need\want your SQL server instance to only listen on specific IPs (and not all IPs in the machine), then you must set “Listen All” on the protocol tab to “No”, whilst this seems intuitive now there is an IP All section on the IP Addresses tab, that when you look at articles on the interwebs, this is the setting you change.

IPAll section on the IP Addresses tab

Then in the IP section you want to apply to, set the Enabled setting to Yes, restart the SQL server instance and the server should now be listening on only the server IP.

This does not affect the listeners which are on different IPs, and handled by the cluster and alwayson services.

SQL Server - Use specific IP/Ports not all

SQL Server 2012: AlwaysOn High Availability error 9692: Endpoint port

Had an issue where I was unable to get the “New Availability Group” wizard to successfully complete.

It would continually error with a 9692 Error, indicating that the endpoint couldn't be setup on port 5022.

I had expected this and on the endpoint tab I had changed to a different port (the servers already had other instances running on port 5022), however even though I changed the wizard to an unused port (i.e 5025), the error kept coming back with could not create end point on port 5022.

I had not come across this before and I cannot find anything about it on the interwebs..

The way I got around this was to manually setup the endpoints, before running the wizard (Changing the port to the desired value).

-- Create a new Database Mirroring Endpoint on Replica 1
CREATE ENDPOINT Hadr_Endpoint
AS TCP
(
   LISTENER_PORT = 5023
)
FOR DATA_MIRRORING
(
   ROLE = ALL,
   ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

-- Start the Endpoint on Replica 1
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED

DROP ENDPOINT Hadr_Endpoint

When the wizard got to the endpoint it then listed these endpoints and I was able to continue through (the ports are greyed out and can no longer be changed)

Would love to know why on this occasion the wizard failed, as I have happily changed the port in the past but this work around got me through.

SQL Server 2012: AlwaysOn High Availability error 9692: Endpoint port

Changing SQL Server Named Instance from dynamic port to static port - (“The target principal name is incorrect. Cannot generate SSPI context. “) fun!!

Recently had an issue where an engineer had decided to change an existing SQL Server from using dynamic ports to a static port.

The server was a named instance.

servername\instancename

Having made the change using SQL Server Management Studio (SSMS) on the actual server he did not notice that access to the server instance via SSMS remotely had stopped working.

Trying to connect remotely to the server resulted in a

“The target principal name is incorrect.  Cannot generate SSPI context. “

error dialog appearing.

after a bit of investigation it was clear this was a problem with the Service Principal Name (SPN) that had been created. There are a number of troubleshooting articles about this issue and I list them in the references below.

One of the solutions is to remove and recreate the SPN. This involves using the setSPN.exe, however you need to know structure the URLs. However whilst looking at this I cam across this

Kerberos Configuration Manager for SQL server

This tool actual checks your SPNs and will generate a script to fix it, or you can just click fix and it will run the script.

In the end this is all I used to fix my issue

 

 

References

https://support.microsoft.com/en-us/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message

https://support.microsoft.com/en-in/help/2985455/kerberos-configuration-manager-for-sql-server-is-available

https://blogs.msdn.microsoft.com/farukcelik/2013/05/21/new-tool-microsoft-kerberos-configuration-manager-for-sql-server-is-ready-to-resolve-your-kerberosconnectivity-issues/

https://social.technet.microsoft.com/Forums/systemcenter/en-US/b879b1a7-c996-4a3f-8e9d-a70ebdffca44/spns-for-named-sql-instances?forum=operationsmanagergeneral

https://support.microsoft.com/en-us/help/823938/how-to-configure-sql-server-to-listen-on-a-specific-port

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/65bcf7a3-ae42-43ae-958e-11dccb123e80/setspn-kerberos-and-named-instances?forum=sqlsecurity

https://social.technet.microsoft.com/wiki/contents/articles/18996.active-directory-powershell-script-to-list-all-spns-used.aspx

Changing SQL Server Named Instance from dynamic port to static port - (“The target principal name is incorrect.  Cannot generate SSPI context. “) fun!!