question

peterbabic-0140 avatar image
0 Votes"
peterbabic-0140 asked ReinaldoJBlancoMedina-4539 answered

Log Shipping not working

Hello guys,

I'm not able to successfully setup log shipping between two servers in one domain.
After configuration, DB full backup and restore to secondary is completed fine.
On primary LS_Backup_DBNAME scheduled job is failing with following errors>


2020-09-13 21:13:07.38 ----- START OF TRANSACTION LOG BACKUP -----
2020-09-13 21:13:07.48 Error: Could not retrieve backup settings for primary ID '682f25fc-7e6e-4886-9d9a-06bb2d275293'.(Microsoft.SqlServer.Management.LogShipping)
2020-09-13 21:13:07.48 Error: Failed to connect to server XXXXXX.(Microsoft.SqlServer.ConnectionInfo)
2020-09-13 21:13:07.48 Error: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)(.Net SqlClient Data Provider)
2020-09-13 21:13:07.48 Error: The certificate chain was issued by an authority that is not trusted()
2020-09-13 21:13:07.48 ----- END OF TRANSACTION LOG BACKUP -----

Exit Status: 1 (Error)

This error I get when I tried to run job command from LS_BACKUP_XXX in cmd sqllogship.exe -Backup 9CB190E0-E70C-45DA-8EE4-93C4370E0F48 -server XXXX

Forced protocol Encryption set to NO
There is no Certificate in Configuration Manager for SQL server
SQL server agent running under domain account which is sysadmin with full rights in shared folder for backup/restore

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

just to add issue is related with jobs, when LS was configured in SSMS GUI configuration went fine, BACKUP and then RESTORE on secondary was completed fine.
When same configuration was done in GUI and LS configuration scripted to new query window, then jobs executed. Non of jobs was completed.

So issue is with executing sqllogship.exe in the job
they are running exe with -RESTORE, -COPY etc parameter file in C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe

0 Votes 0 ·
MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered

Hi @peterbabic-0140,


  1. Please make sure your account under sql service have the full permission.Use domain\Administrator restart bother server's all services;

  2. You can choose option: trust server cetificate as next.
    24769-20200915choosethisoption.jpg


And then follow steps as this doc to create log shipping step by step: step-by-step-sql-server-log-shipping
More information:

BR,
Mia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

peterbabic-0140 avatar image
0 Votes"
peterbabic-0140 answered

Hello Yuximiao,
thanks for comment I don't have issue to connect via SSMS that is ok, initial configuration, backup on primary and restore on secondary works fine.
24730-image.png




But then scheduled backup on primary is failling.


image.png (14.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

peterbabic-0140 avatar image
0 Votes"
peterbabic-0140 answered peterbabic-0140 edited

Hi GuoxiongYuan thanks for reply and for suggestion but my issue is bit different my secondary instance is visible and connectable from primary.
The ERROR which I get is
Error: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)(.Net SqlClient Data Provider)


While Force Encryption is "NO" in Configuration manager connections.
When I query sys.dm.exec.connections I get that connections are not encrypted.

24857-image.png



image.png (32.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered MiaMiao-MSFT edited

Hi @peterbabic-0140,

Please double check as next:

1.Both SQL Native Client 11.0 Configuration(32bit) and SQL Native Client 11.0 Configuration are all set "NO".

2.Both Client Side and Server side in your both servers's : TCP/IP protocol are enabled and port:1433.

You should restart the sqlserver service when you finish the changing each time.

25102-no2.jpg
25004-clientside.jpg
25006-serverside.jpg

BR,
Mia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



no2.jpg (31.3 KiB)
clientside.jpg (66.9 KiB)
serverside.jpg (76.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered

Hi @peterbabic-0140,

Is the reply helpful?

BR,
Mia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

peterbabic-0140 avatar image
0 Votes"
peterbabic-0140 answered MiaMiao-MSFT commented

Hello Yuximiao-MSFT, thx for reply again, yes both native clients have NO in Force Encryption, port is 1433 for both servers TCP/IP enabled.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you try to use domain\Asministrator to restart the two sql services?

0 Votes 0 ·
ReinaldoJBlancoMedina-4539 avatar image
0 Votes"
ReinaldoJBlancoMedina-4539 answered

I have a similar Error but in the Restore Job... But my process work fine a day (36 DDBB), and the next day fail somes DDBB...
Error: No se pudo aplicar el archivo de copia de seguridad de registros 'XXX_20210829185000.trn' a la base de datos secundaria 'XXX'.(Microsoft.SqlServer.Management.LogShipping)
Error: Error al procesar el registro de la base de datos 'XXX'. Si es posible restaure la base de datos a partir de una copia de seguridad. Si no dispone de una copia de seguridad puede ser necesario generar de nuevo el registro. Se ha producido un error durante la recuperación que impide reiniciar la base de datos 'XXX' (21:0). Diagnostique los errores de recuperación y corríjalos o restaure desde una copia de seguridad en buen estado. Si los errores no se han corregido o se espera que haya errores póngase en contacto con el soporte técnico. Fin anómalo de RESTORE LOG. Se han procesado 0 páginas para la base de datos 'XXX' archivo 'XXX_Data' en el archivo 1. Se han procesado 1 páginas para la base de datos 'XXX' archivo 'XXX_Log' en el archivo 1.(.Net SqlClient Data Provider)
Error: No se pudo registrar el historial/mensaje de error.(Microsoft.SqlServer.Management.LogShipping)
Error: ExecuteNonQuery requiere una Connection abierta y disponible. El estado actual de la conexión es cerrada.(System.Data) Omitiendo el archivo de copia de seguridad de registros 'XXX_20210829185000.trn' de la base de datos secundaria 'XXX' porque no pudo comprobarse dicho archivo.
Error: No se pudo registrar el historial/mensaje de error. (Microsoft.SqlServer.Management.LogShipping)
Error: ExecuteNonQuery requiere una Connection abierta y disponible. El estado actual de la conexión es cerrada.(System.Data)
Error: Error al restaurar el modo de acceso a la base de datos.(Microsoft.SqlServer.Management.LogShipping)
Error: ExecuteScalar requiere una Connection abierta y disponible. El estado actual de la conexión es cerrada.(System.Data)
Error: No se pudo registrar el historial/mensaje de error.(Microsoft.SqlServer.Management.LogShipping)
Error: ExecuteNonQuery requiere una Connection abierta y disponible. El estado actual de la conexión es cerrada.(System.Data)
Error: Error al restaurar el modo de acceso a la base de datos.(Microsoft.SqlServer.Management.LogShipping)
Error: ExecuteScalar requiere una Connection abierta y disponible. El estado actual de la conexión es cerrada.(System.Data)
Error: No se pudo registrar el historial/mensaje de error.(Microsoft.SqlServer.Management.LogShipping)
Error: ExecuteNonQuery requiere una Connection abierta y disponible. El estado actual de la conexión es cerrada.(System.Data) Eliminando archivos antiguos de copia de seguridad de registros. Base de datos principal: 'XXX'
Error: No se pudo registrar el historial/mensaje de error.(Microsoft.SqlServer.Management.LogShipping)
Error: ExecuteNonQuery requiere una Connection abierta y disponible. El estado actual de la conexión es cerrada.(System.Data) Eliminando archivo de copia de seguridad de registros. Archivo: 'XXX_20210824142000.trn' Hora de £ltima escritura (UTC): '24/08/2021 14:20:00'
Error: No se pudo registrar el historial/mensaje de error.(Microsoft.SqlServer.Management.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.