Removing Log shipping on a database with AlwaysOn configured

AlwaysOn + Log shipping related blogs
Below blog post are all related to AlwaysOn and log shipping configured together,
-> Configuring Log Shipping with AlwaysOn
-> Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing

Environment

-> JBSAG1, JBSAG2 and JBSAG3 are part of a failover cluster without shared storage. Alwayson Availability group is configured between JBSAG1, JBSAG2 and JBSAG3. JBSAG1 is the current primary, JBSAG2 is the synchronous secondary and JBSAG3 is Asynchronous secondary. The database(s) that is part of Always on Availability group has log shipping configured and its secondary is on JBSAG4.

-> The requirement is to remove log shipping from this setup.

-> On Asynchronous secondary JBSAG3. Right click on the log shipped database JBDB and click properties. Click on “Transaction Log Shipping” Tab.

-> Uncheck “Enable this as a primary database in a log shipping configuration” and click OK.

-> On Synchronous secondary JBSAG2. Follow the same steps as above and disable Log shipping,

-> On primary JBSAG1. Follow the same steps as above and disable Log shipping,

-> Once above actions are completed. Check SQL Server agent on JBSAG1, JBSAG2, JBSAG3, JBSAG4 and ensure log shipping related jobs are absent to make sure we have removed log shipping completely.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Configuring Log Shipping with AlwaysOn

AlwaysOn + Log shipping related blogs
Below blog post are all related to AlwaysOn and log shipping configured together,
-> Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing
-> Removing Log shipping on a database with AlwaysOn configured

Environment

-> JBSAG1, JBSAG2 and JBSAG3 are part of a failover cluster without shared storage. Alwayson Availability group is configured between JBSAG1, JBSAG2 and JBSAG3. JBSAG1 is the current primary, JBSAG2 is the synchronous secondary and JBSAG3 is Asynchronous secondary. The database(s) that is part of Always on Availability group will have log shipping configured and its secondary will be JBSAG4.

-> AlwaysOn availability group is already configured. Below is the Availability group properties,

-> Transaction log backups will happen on AlwaysOn secondary as per the settings below.

-> This article will discuss how to configure log shipping. Please note that below steps for setting up Log shipping can be followed for both “AlwaysOn Backup preferences” “Secondary only” or “Primary”.

-> On AlwaysOn primary JBSAG1, right click JBDB database and click on properties. Click on “Transaction Log Shipping”,

-> Check “Enable this as a primary database in a log shipping configuration” and click OK.

-> Click “Backup Settings…”. Provide appropriate location where the backup should be placed,

-> Click on Schedule and set appropriate schedule for backup and click OK,

-> Click OK for “Transaction Log Backup Settings”. Click on “Add..” under “Secondary server instances and databases:”. In “Secondary Database Settings” connect to Log shipping secondary server instance.

-> Under “Initialize Secondary Database”, select appropriate option that suits better. In my case I will select first option, since my database is not that big.

-> Click on “Copy Files” and provide appropriate location where the files should be copied over. Select “Schedule” and change the schedule of copy job appropriately,

-> Click OK on the copy job. Click OK on “Secondary Database Settings” and then Click “OK” for “Transaction Log Shipping” on database properties.

-> On AlwaysOn synchronous secondary JBSAG2, right click JBDB database and click on properties. Click on “Transaction Log Shipping”, provide same settings for “Network path to backup folder” and Change the backup job schedule same as what was provided for log shipping settings in AlwaysOn primary JBSAG1.

-> Make sure it is blank for “Secondary server instances and databases:”. Click OK.

-> On AlwaysOn asynchronous secondary JBSAG3, right click JBDB database and click on properties. Click on “Transaction Log Shipping”, provide same settings for “Network path to backup folder” and Change the backup job schedule same as what was provided for log shipping settings in AlwaysOn primary JBSAG1.

-> Make sure it is blank for “Secondary server instances and databases:”. Click OK.

-> With this log shipping setup is complete. Below are the jobs that are created after the Log shipping setup,

-> You can try failover and failback to see if everything works fine. All these jobs can run on respective SQL Server instances irrespective of which SQL Server instance JBSAG1, JBSAG2 or JBSAG3 is Alwayson primary. There is no requirement to change any of these jobs to run on AlwaysOn primary only.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

The query has been canceled because the estimated cost of this query (37) exceeds the configured threshold of 30. Contact the system administrator.

-> I was applying SQL Server 2019 cumulative update 8. I got an error that SQL Agent service could not be started and the setup failed.

Overall summary:
Final result: The patch installer has failed to update the following instance:. To determine the reason for failure, review the log files.
Exit code (Decimal): -2068709375
Exit facility code: 1202
Exit error code: 1
Exit message: The NT service ‘SQLAgent$IN2019_1’ could not be started.
Start time: 2021-01-02 12:57:47
End time: 2021-01-02 14:39:59
Requested action: Patch
Exception help link: https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=15.0.4073.23&EvtType=0xAE7B8BF3%400xF762992B%401202%401&EvtType=0xAE7B8BF3%400xF762992B%401202%401

Rules with failures or warnings:
Rules report file: C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20210102_125747\SystemConfigurationCheck_Report.htm

Exception summary:
The following is an exception stack listing the exceptions in outermost to innermost order
Inner exceptions are being indented

Exception type: Microsoft.SqlServer.Chainer.Infrastructure.ChainerInvalidOperationException
Message:
The NT service ‘SQLAgent$IN2019_1’ could not be started.
HResult : 0x84b20001
FacilityCode : 1202 (4b2)
ErrorCode : 1 (0001)
Data:
HelpLink.EvtType = 0xAE7B8BF3@0xF762992B@1202@1
DisableWatson = true
Stack:
at Microsoft.SqlServer.Configuration.SetupExtension.RunPatchAllInstanceAction.RestoreServiceStates()
at Microsoft.SqlServer.Configuration.SetupExtension.RunPatchAllInstanceAction.ExecuteAction(String actionId)
at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClass2_0.b__0()
at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)

-> I tried starting the SQL services from SQL Server configuration manager and got below error,

SQL Server Configuration Manager
The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.
OK

-> This seems like a generic error. I then check the SQL Server error log and found below error,

2021-01-02 13:08:34.98 spid10s Database ‘master’ is upgrading script ‘msdb110_upgrade.sql’ from level 251660240 to level 251662313.
2021-01-02 13:08:34.98 spid10s ———————————-
2021-01-02 13:08:34.98 spid10s Starting execution of PRE_MSDB.SQL
2021-01-02 13:08:34.98 spid10s ———————————-

.

.

2021-01-02 13:08:41.09 spid10s Uploading data collector package from disk: C:\Program Files\Microsoft SQL Server\MSSQL15.IN2019_1\MSSQL\Install\SqlTraceCollect.dtsx
2021-01-02 13:08:41.09 spid10s Error: 8649, Severity: 17, State: 1.
2021-01-02 13:08:41.09 spid10s The query has been canceled because the estimated cost of this query (37) exceeds the configured threshold of 30. Contact the system administrator.
2021-01-02 13:08:41.09 spid10s Error: 912, Severity: 21, State: 2.
2021-01-02 13:08:41.09 spid10s Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 8649, state 1, severity 17. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2021-01-02 13:08:41.10 spid10s Error: 3417, Severity: 21, State: 3.
2021-01-02 13:08:41.10 spid10s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2021-01-02 13:08:41.10 spid10s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

-> The script upgrade mode fails with the error “The query has been canceled because the estimated cost of this query (37) exceeds the configured threshold of 30“.

-> I added trace flag 902 to temporarily stop script upgrade mode for troubleshooting purpose. Please note that this trace flag 902 should not be allowed to run continuously in a production environment, but should be strictly used for troubleshooting purpose only.

-> I added trace flag 902 to SQL Server startup parameter and started the SQL Services,

-> Once SQL Services is started after adding trace flag 902. I right clicked SQL server instance and clicked on properties.

-> From the properties window, I navigated to “Connections” tab as below,

-> “Use query governor to prevent long-running queries” is checked and a value of 30 seconds is mentioned. This setting will result in cancelling queries that has an estimated cost of more than 30 seconds. It seems like the query that was cancelled as part of script upgrade mode might have an estimated cost of more than 30 and that would have resulted in cancelling this query.

-> Please refer this article for more details on “Use query governor to prevent long-running queries”. The cost limit option specifies an upper limit on the estimated cost allowed for a given query to run. Query cost is an abstract figure determined by the query optimizer based on estimated execution requirements such as CPU time, memory, and disk IO. It refers to the estimated elapsed time, in seconds, that would be required to complete a query on a specific hardware configuration. This abstract figure does not equate to the time required to complete a query on the running instance. It should be treated as a relative measure. The default value for this option is 0, which sets the query governor to off. Setting the value to 0 allows all queries to run without any time limitation. If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost that exceeds that value.

-> I unchecked this option and clicked “OK”.

-> I removed trace flag 902 and started SQL services again. It worked fine this time and the SQL server version changed to an appropriate value.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.