Attempting to apply any Before-After auditing configuration leads to a 'saving configuration was not successful' message
说明
Attempting to apply any Before-After auditing configuration leads to a 'saving configuration was not successful' message
解决办法
Run the bellow script, ApexSQL Audit - Remove Before-After triggers, on a problematic SQL Server instance/replica in SSMS, refresh the server in SSMS and and then try to apply the configuration. Note: This should be done on all replicas when Always On is used
ApexSQL Audit - Remove Before-After triggers script: -- DROP TRIGGER DECLARE cTrigger CURSOR FOR SELECT sysobjects.name AS trigger_name, s.name AS table_schema FROM sysobjects INNER JOIN sys.tables t ON sysobjects.parent_obj = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TA' AND sysobjects.name LIKE '%_ApexSQLAudit_%';
OPEN cTrigger; FETCH NEXT FROM cTrigger INTO @trigger_name, @trigger_schema
WHILE @@FETCH_STATUS = 0 BEGIN SET @queryDropTrigger = 'DROP TRIGGER ' + @trigger_schema +'.' + @trigger_name; EXEC(@queryDropTrigger); FETCH NEXT FROM cTrigger INTO @trigger_name, @trigger_schema END CLOSE cTrigger; DEALLOCATE cTrigger;
-- DROP ASSEMBLIES DECLARE cAssemblies CURSOR FOR SELECT name FROM sys.assemblies WHERE name LIKE 'ApexSQL.Audit.BeforeAfter.%.%' AND is_user_defined = 1;
OPEN cAssemblies; FETCH NEXT FROM cAssemblies INTO @assembly_name
WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY SET @queryDropAssemblies ='DECLARE @installedAsmHash varbinary(64); SELECT @installedAsmHash = hash from sys.trusted_assemblies where description = ' + @assembly_name + ' IF @installedAsmHash is not NULL BEGIN EXEC sys.sp_drop_trusted_assembly @hash = @installedAsmHash; END'; EXEC(@queryDropAssemblies); END TRY BEGIN CATCH END CATCH
SET @queryDropAssemblies = 'DROP ASSEMBLY [' + @assembly_name + ']'; EXEC(@queryDropAssemblies); FETCH NEXT FROM cAssemblies INTO @assembly_name END CLOSE cAssemblies; DEALLOCATE cAssemblies;
IF EXISTS(SELECT 1 FROM sys.assemblies WHERE name = 'ApexSQL.Audit.BeforeAfter') DROP ASSEMBLY [ApexSQL.Audit.BeforeAfter]