January 28, 2020 at 11:32 pm
Hi,
I'm getting a connection error when I try to run even the most basic plan, fails with scheduled or manual runs.
The user is both a local-admin on all 3 nodes, and a SQL SA member. All engine processes and agent processes run-as that same AD account too. Everything works great (failovers etc), just the jobs won't run.
I even tried a FQDN on the hostname and a manual SQL-Login with the SA credentials in the job plan and that connection still didn't work.
I can run both SSMS and connect to the database locally and remotely without error with that AD account, but running the jobs: no luck.
Instance name is default MSSQLSERVER for all replicas, in a primary+1 synchronous-commit for all 3 nodes (15.0.4003.23)
All the databases are fully synched.
Prod is configured the same (but without HA) and the backup jobs work fine.
I'm fairly certain it would run, if it could somehow successfully connect.
Error Msg as per Job history:
Executed as user: DomainName\DEV-SQL-HA. Microsoft (R) SQL Server Execute Package Utility Version 15.0.2000.5 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. Started: 2:46:06 PM Could not load package "Maintenance Plans\Test" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.). The SQL statement that was issued has failed. Source: Started: 2:46:06 PM Finished: 2:46:06 PM Elapsed: 0.188 seconds. The package could not be loaded. The step failed.
I even thought it was the job's complexity so I simplified to just a single step basic file del cleanup task and even that didn't help any:
EXECUTE master.dbo.xp_delete_file 0,N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Daily',N'bak',N'2019-12-31T14:53:57'
If I manually run a backup to the same path that is successful, so it's not a file-path or file-permission thing.
BACKUP DATABASE [DEV_SensorsDB] TO
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Daily\fdsa2.bak'
WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'DEV_SensorsDB-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Processed 616 pages for database 'DEV_SensorsDB', file 'Sensors' on file 2.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 246 pages for database 'DEV_SensorsDB', file 'Sensors_log' on file 2.
BACKUP DATABASE successfully processed 862 pages in 0.470 seconds (14.320 MB/sec).
Completion time: 2020-01-28T15:04:05.5541443-08:00
I'm out of ideas...
Thanks,
-Shawn
January 29, 2020 at 8:14 am
Have you created the same maintenance plan on all 3 nodes? They are stored in MSDB and as MSDB is not part of the availability groups, you need to have the same plan on all 3 nodes, make a change on one, you need to make a change on the other.
That being said, what are the maintenance plans doing? Just backups and index rebuilds? Have you tried taking a look at Ola Hallengren's maintenance solution or things like Minion Ware etc, or even better writing your own routines which can be scheduled via a job instead of having the extra maintenance overhead with maintenance plans?
MP's are a crude way to do things, they are not flexible enough for the modern way of doing things these days. They are a means to and end for single server, traditional FCI's but not for AG's in my opinion. Even then on single servers, FCI's I would stay away from maintenance plans.
January 29, 2020 at 10:41 pm
I'm trying to create daily backups of all databases.
Preference is set to Primary only.
I tried scripting the backup task to t-sql and manually creating the job.
That works.
I tried to install SSIS to export/import the packages between nodes, but that also won't open.
Can't connect, no default instance it says. Firewall is off, and I even tried localhost SSMS.
I can manually create the same plan on each node, but running it fails with the same OLE connection error.
Cloning the subplan via to-script also fails with the same OLE connection error.
The original nodes were 2017 Ent, and I did upgrades to 2019 Ent.
I'm wondering if perhaps that upgrade wasn't entirely clean?
I know MP's are crummy but they are easy to use and advanced-enough to perform the tasks I need, it's too bad they won't connect.
I'm wondering: Do Maintenance Plans execute on HA nodes at-all in 2019 or is this a just-me thing?
I tried every Backup Preference, no luck.
It's like it can't connect to the package table in msdb in HA mode from the agent service, or fails during some sort-of package manger connstr connection back to the node.
The agent can run the raw T-SQL script just fine, which has no dependencies on packages and the SISP subsystem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply