Availability-Groups
Always On：SQL 代理仍然可以在非活動伺服器上執行儲存過程
我們有 AO 集群 - HA&DR 混合配置。在十幾個數據庫中，只有這個數據庫/應用程序遇到了這個問題。
問題是我們有一個 SQL 代理作業計劃在 PRIMARY（活動）上執行，但由於某種原因，它也在 SECONDARY（非活動）上執行，但正確指向 PRIMARY（活動），這通常會使我們插入的記錄加倍來自外部機器的 FTP。
YET - 我們無法連接到 SECONDARY 或手動執行儲存過程，但 SQL 代理作業似乎能夠覆蓋和取代 AO！！！！
根據 Bren Ozar 的影片： https ://youtu.be/YNyY_A5733E?t=12m58s — 工作和安全不能故障轉移 HA SyncMirroring
有任何想法嗎？還有人有這個嗎？
我想知道是否需要修補程序。
射線
喬治
Sean，
Bren Ozar 的影片給了我一些新線索，但確認它並確定它為什麼仍然發生，甚至為什麼代理作業在輔助伺服器上重新啟用並並行執行仍然是一個謎。何時應返回“無法訪問數據庫 EEOutageTracking。（ObjectExplorer）”。這是一個 1 步 SQL Server 代理作業
exec SP_ATSImport—– 我如何附加/粘貼程式碼？
USE [EEOutageTracking] GO /****** Object: StoredProcedure [dbo].[SP_ATSImport] Script Date: 8/19/2016 2:49:36 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --- Lewycky Dec 10, 2014: modifications for date header record to verify file xmitted --- is correct and recent ALTER PROCEDURE [dbo].[SP_ATSImport] AS BEGIN BEGIN SET XACT_ABORT ,NOCOUNT ON; DECLARE @starttrancount int; BEGIN TRY IF @@TRANCOUNT > 0 --- added BEGIN ROLLBACK TRANSACTION; END; SELECT @starttrancount = @@TRANCOUNT; ---- SET @starttrancount = 0; --<----------- ---- SET @starttrancount = @@TRANCOUNT; ---- should be zero and not trancount IF @starttrancount = 0 BEGIN BEGIN TRANSACTION ATSIMPORT; END; DECLARE @dtCurrent datetime; SET @dtCurrent = GETDATE( ); ----------Lewycky 12/2/14: added to handle verifying incoming file FTPd from ATS that it was sent the date ---------- SP_ATSImport is scheduled to run. Should be same date! DECLARE @datestring char( 08 ); SET @datestring = CONVERT( varchar( 8 ) ,GETDATE( ) ,112 ); ---------- ------ FOR FUTURE USE --EXEC sp_configure 'show advanced options', 1 --GO --RECONFIGURE --GO --EXEC sp_configure 'xp_cmdshell', 1 --GO --RECONFIGURE --GO --exec sp_configure --go --exec sp_configure 'Ole Automation Procedures', 1 ---- Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install. --go --reconfigure --go DECLARE @filename varchar( 40 ); -- In FTP Scheduled DOS job: the file from AS/400 is placed in this subdir under this filename -- indicating FTP was successful -- PRODUCTION SET @filename = 'S:\ATSJobs\Archive\ATSToday.txt'; --- where FTP placed file ( FTPATSProd.CMD ) -------------------------------------------------------------------------------------------------- -- use for TESTING BELOW on 10.8.86.78 -- SET @filename = 'E:\ATSJobs\Archive\ATSTodayX.txt'; --- on 10.8.86.78 ----SET @filename = 'C:\ATSJobs\Archive\ATSTodayX.txt'; --- where FTP placed file ( FTPATSProd.CMD ) -------------------------------------------------------------------------------------------------- -- PROD SET @filename = 'S:\ATSJobs\Archive\ATSToday.txt'; --- where FTP placed file ( FTPATSProd.CMD ) --*&SET @filename = 'E:\ATSJobs\Archive\ATSToday.txt'; --- where FTP placed file ( FTPATSProd.CMD ) SET @filename = 'D:\ATS2EandEJobs\Archive\ATSToday.txt'; --- where FTP placed file on 10.9.76.27 ( FTPATSProd.CMD ) DECLARE @i int; EXEC master..xp_fileexist @Filename ,@i OUT; IF @i = 1 BEGIN IF OBJECT_ID( 'tempdb..#tempfile' )IS NOT NULL BEGIN DROP TABLE #tempfile; END; DECLARE @TotalRecordsForImport int , @EmptyInput char( 1 ); CREATE TABLE #tempfile( Inputline varchar( 250 )); EXEC ( 'bulk INSERT #tempfile FROM "'+@filename+'"' ); SET @EmptyInput = 'N'; SET @TotalRecordsForImport = ( SELECT COUNT( * ) FROM #tempfile ); IF @TotalRecordsForImport = 0 BEGIN SET @EmptyInput = 'Y'; --- used to report if FTPd file is empty END; --------- immediately delete the ATSToday.txt file after bulk inserted -------- EXEC xp_cmdshell 'del "S:\ATSJobs\ATS_Yada20141010_1652.txt" ' END; ELSE BEGIN SET @TotalRecordsForImport = 0; SET @EmptyInput = 'N'; --- indicates file not received since count will also be zero END; -------------------------------------------------------------------------------- ----------Lewycky 12/2/14: added to handle verifying incoming file FTPd from ATS that it was sent the date ---------- SP_ATSImport is scheduled to run. Should be same date! ------------------------------ IF @i = 1 BEGIN DECLARE @ATSDateIn varchar( 08 ); DECLARE @DateCheck varchar( 300 ); SET @DateCheck = ( SELECT TOP 1 * FROM #tempfile ); SET @ATSDateIn = CAST( SUBSTRING( @DateCheck ,1 ,8 )AS char ); ----------Lewycky 12/2/14: added to handle verifying incoming file FTPd from ATS that it was sent the date ---------- SP_ATSImport is scheduled to run. Should be same date! IF @ATSDateIn <> @datestring BEGIN SET @TotalRecordsForImport = 9999999; --- overriding logic for zero in code below with ambiguous value for bypass code SET @EmptyInput = 'X'; --- Dummy override value not to interfere with logic below EXEC sp_SendCDOSysMail END; END; -------------------------------------------------------------------------------- ---- SPECIAL LOGIC if file is empty with zero records - drop temp and rollback IF @TotalRecordsForImport = 0 AND @EmptyInput = 'Y' BEGIN EXEC sp_SendCDOSysMail 'EandEATSImport@nyct.com' ,'george.lewycky@nyct.com,brian.tabb@nyct.com,Mathew.Varghese3@nyct.com,Shahreen.Ali@nyct.com,Yefim.Kogan@nyct.com' ,'' ,'ATS Input file received is empty' ,''; ----- still keep the file ????? END; -------------------------------------------------------------------------------- ---- LOGIC if file is NOT received via STEP - drop temp and rollback IF @TotalRecordsForImport = 0 AND @EmptyInput <> 'Y' BEGIN EXEC sp_SendCDOSysMail END; -- Reverse special value back to Zero to prevent INSERTs IF @EmptyInput = 'X' BEGIN SET @TotalRecordsForImport = 0; --- reverse override value of 9999999 for Date conflict END; ---- if valid file insert and archive the flat file with a date & time stamp ----- Bypass if "X" signifying Date Stamp on input file isn't run date IF @TotalRecordsForImport > 1 BEGIN INSERT INTO EEOutageTracking.dbo.ATStoAFCSpearInterface( EMPL_ID , C_EMPL_FIRST_INIT , C_EMPL_MIDD_INIT , C_EMPL_LAST_NAME , C_FORM_DATE , C_CODE_1 , C_HOURS_1 , C_JOB_NO_1 , C_CODE_2 , C_HOURS_2 , C_JOB_NO_2 , C_CODE_3 , C_HOURS_3 , C_JOB_NO_3 , C_CODE_4 , C_HOURS_4 , C_JOB_NO_4 , C_CODE_5 , C_HOURS_5 , C_JOB_NO_5 , C_CODE_6 , C_HOURS_6 , C_JOB_NO_6 , C_CODE_7 , C_HOURS_7 , C_JOB_NO_7 , C_CODE_8 , C_HOURS_8 , C_JOB_NO_8 , C_CODE_9 , C_HOURS_9 , C_JOB_NO_9 , C_CODE_10 , C_HOURS_10 , C_JOB_NO_10 , C_CODE_11 , C_HOURS_11 , C_JOB_NO_11 , C_CODE_12 , C_HOURS_12 , C_JOB_NO_12 , C_START_DATE , C_END_DATE , C_MOD_FLAG , C_WORK_SHIFT , C_LAST_UPDATE_DATE , C_UPDATED_BY ) SELECT CAST( SUBSTRING( InputLine ,1 ,6 )AS char ) , CAST( SUBSTRING( InputLine ,7 ,1 )AS char ) , CAST( SUBSTRING( InputLine ,8 ,1 )AS char ) , CAST( SUBSTRING( InputLine ,9 ,16 )AS char ) , SUBSTRING( InputLine ,29 ,4 ) + '-' + SUBSTRING( InputLine ,25 ,2 ) + '-' + SUBSTRING( InputLine ,27 ,2 ) , CAST( SUBSTRING( InputLine ,33 ,3 )AS char ) , --CODE 1 CAST( SUBSTRING( InputLine ,36 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,40 ,5 )AS char ) , CAST( SUBSTRING( InputLine ,45 ,3 )AS char ) , CAST( SUBSTRING( InputLine ,48 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,52 ,5 )AS char ) , CAST( SUBSTRING( InputLine ,57 ,3 )AS char ) , CAST( SUBSTRING( InputLine ,60 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,64 ,5 )AS char ) , CAST( SUBSTRING( InputLine ,69 ,3 )AS char ) , CAST( SUBSTRING( InputLine ,72 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,76 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,81 ,3 )AS char ) , CAST( SUBSTRING( InputLine ,84 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,88 ,5 )AS char ) , CAST( SUBSTRING( InputLine ,93 ,3 )AS char ) , CAST( SUBSTRING( InputLine ,96 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,100 ,5 )AS char ) , --jobno 6 CAST( SUBSTRING( InputLine ,105 ,3 )AS char ) , CAST( SUBSTRING( InputLine ,108 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,112 ,5 )AS char ) , CAST( SUBSTRING( InputLine ,117 ,3 )AS char ) , CAST( SUBSTRING( InputLine ,120 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,124 ,5 )AS char ) , CAST( SUBSTRING( InputLine ,129 ,3 )AS char ) , CAST( SUBSTRING( InputLine ,132 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,136 ,5 )AS char ) , CAST( SUBSTRING( InputLine ,141 ,3 )AS char ) , CAST( SUBSTRING( InputLine ,144 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,148 ,5 )AS char ) , CAST( SUBSTRING( InputLine ,153 ,3 )AS char ) , CAST( SUBSTRING( InputLine ,156 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,160 ,5 )AS char ) , CAST( SUBSTRING( InputLine ,165 ,3 )AS char ) , CAST( SUBSTRING( InputLine ,168 ,4 )AS char ) , CAST( SUBSTRING( InputLine ,172 ,5 )AS char ) , --jobno 12 CASE WHEN SUBSTRING( InputLine ,177 ,12 ) = '000000000000' THEN NULL ELSE SUBSTRING( InputLine ,181 ,4 ) + '-' + SUBSTRING( InputLine ,177 ,2 ) + '-' + SUBSTRING( InputLine ,179 ,2 ) + ' ' + SUBSTRING( InputLine ,185 ,2 ) + ':' + SUBSTRING( InputLine ,187 ,2 ) END , CASE WHEN SUBSTRING( InputLine ,189 ,12 ) = '000000000000' THEN NULL ELSE SUBSTRING( InputLine ,193 ,4 ) + '-' + SUBSTRING( InputLine ,189 ,2 ) + '-' + SUBSTRING( InputLine ,191 ,2 ) + ' ' + SUBSTRING( InputLine ,197 ,2 ) + ':' + SUBSTRING( InputLine ,199 ,2 ) END , --SUBSTRING( InputLine ,181 ,4 ) + '-' + SUBSTRING( InputLine ,177 ,2 ) + '-' + SUBSTRING( InputLine ,179 ,2 ) + ' ' + SUBSTRING( InputLine ,185 ,2 ) + ':' + SUBSTRING( InputLine ,187 ,2 ) , --SUBSTRING( InputLine ,193 ,4 ) + '-' + SUBSTRING( InputLine ,189 ,2 ) + '-' + SUBSTRING( InputLine ,191 ,2 ) + ' ' + SUBSTRING( InputLine ,197 ,2 ) + ':' + SUBSTRING( InputLine ,199 ,2 ) , CAST( SUBSTRING( InputLine ,201 ,1 )AS char ) , CAST( SUBSTRING( InputLine ,202 ,9 )AS char ) , SUBSTRING( InputLine ,215 ,4 ) + '-' + SUBSTRING( InputLine ,211 ,2 ) + '-' + SUBSTRING( InputLine ,213 ,2 ) , CAST( SUBSTRING( InputLine ,219 ,9 )AS char ) --CAST( SUBSTRING( InputLine ,220 ,1 )AS char ) , --CAST( SUBSTRING( InputLine ,221 ,16 )AS varchar ) FROM #tempfile WHERE SUBSTRING( INPUTLINE ,1 ,8 ) <> @ATSDateIn; ----- exclude the header record containing the date stamp ---- Lewycky ---- dbo.ATSStaging; --DROP TABLE #tempfile created from the flat file DROP TABLE #tempfile; SELECT @starttrancount = @@TRANCOUNT; IF @starttrancount --- set to 1 from begin trans ---@starttrancount <> 0 AND @TotalRecordsForImport > 0 BEGIN COMMIT TRANSACTION ATSIMPORT; --- relocated **& Lewycky EXEC sp_SendCDOSysMail 'EandEATSImport@nyct.com' ,'george.lewycky@nyct.com,brian.tabb@nyct.com,Mathew.Varghese3@nyct.com,Shahreen.Ali@nyct.com,Yefim.Kogan@nyct.com' ,'' ,'ATS loaded successfully' ,''; END; ELSE BEGIN ROLLBACK TRANSACTION; END; END; -- RENAME INPUT FILE ATSToday.txt WITH DATE & TIME STAMP AND ARCHIVE IT IF @TotalRecordsForImport > 0 BEGIN --- create string to copy or rename file with a date and time stamp DECLARE @dt datetime , @SQLstmt varchar( 250 ); SET @dt = GETDATE( ); -- Important: double quotes ARE NEEDED around full filenames from & to work in T-SQL -- George -- Important: double quotes ARE NEEDED around full filenames from & to work in T-SQL -- George --------------------------------------------------------------------------------------------------------------- ----SET @SQLstmt = 'MOVE /Y "' + @filename + '"' + ' "S:\ATSJobs\Archive\ATS_Archive' + CONVERT( varchar( 15 ) ,@dt ,112 ) + '_' + REPLACE( CONVERT( char( 5 ) ,GETDATE( ) ,108 ) ,':' ,'' ) + '.txt' + '"'; ----EXEC xp_cmdshell @SQLstmt; ------------------------------ DECLARE @oFS int; DECLARE @FS int; DECLARE @oFol int; DECLARE @oFC int; DECLARE @ERR int; DECLARE @FD int; DECLARE @oFil int; DECLARE @Path varchar( 255 ); DECLARE @Path1 varchar( 255 ); --DECLARE @FileName VARCHAR(255) DECLARE @NumFiles int; DECLARE @RetCode int; DECLARE @ErrObject int; --- DECLARE @src varchar( 250 ) , @desc varchar( 2000 ); DECLARE @source varchar( 255 ) , @dest varchar( 255 ); DECLARE @newname varchar( 250 ); SET @dt = GETDATE( ); --------------------------------------------------------------------------------------------------------------- SET @newname = 'ATS_Archive' + CONVERT( varchar( 15 ) ,@dt ,112 ) + '_' + REPLACE( CONVERT( char( 5 ) ,GETDATE( ) ,108 ) ,':' ,'' ) + '.txt'; EXEC @RetCode = sp_OACreate 'Scripting.FileSystemObject' ,@oFS OUTPUT; IF @RetCode <> 0 BEGIN -- ROLLBACK TRANSACTION; EXEC sp_OAGetErrorInfo @oFS ,@src OUT ,@desc OUT; EXEC sp_SendCDOSysMail 'EandEATSImport@nyct.com' ,'george.lewycky@nyct.com,brian.tabb@nyct.com' ,'' ,'ATS - Unable to archive incoming file (1 Create)' ,''; RAISERROR( 'Object Creation Failed 0x%x, %s, %s' ,16 ,1 ,@retcode ,@src ,@desc ); RETURN; END; SET @Path = 'D:\ATS2EandEJobs\Archive\ATSToday.txt'; SET @Path1 = 'D:\ATS2EandEJobs\Archive\' + @newname; --&SET @Path = 'E:\ATSJobs\Archive\ATSToday.txt'; --&SET @Path1 = 'E:\ATSJobs\Archive\' + @newname; EXEC @RetCode = sp_OAMethod @oFS ,'GetFile' ,@oFol OUTPUT ,@Path; IF @RetCode <> 0 BEGIN -- ROLLBACK TRANSACTION; EXEC sp_SendCDOSysMail ' EXEC sp_OAGetErrorInfo @oFS ,@src OUT ,@desc OUT; RAISERROR( 'OA Get File failed 0x%x, %s, %s' ,16 ,1 ,@retcode ,@src ,@desc ); RETURN; END; -- http://www.vbforums.com/showthread.php?367306-Move-file-with-sp_OAMethod EXECUTE @err = sp_OAMethod @ofs ,'MoveFile' ,NULL ,@Path ,@Path1; IF @RetCode <> 0 BEGIN -- ROLLBACK TRANSACTION; EXEC sp_SendCDOSysMail EXEC sp_OAGetErrorInfo @oFS ,@src OUT ,@desc OUT; RAISERROR( 'OA Move File Failed 0x%x, %s, %s' ,16 ,1 ,@retcode ,@src ,@desc ); RETURN; END; ------------------------------ END; ------------------------------------------------------------------------------------------------------------------- ----- DELETE ATSTODAY.TXT to be ready for next run - message already emailed above ----- NOTHING TO ARCHIVE - also for when Import job is accdentally re-run without the FTP preceding it ------------------------------------------------------------------------------------------------------------------- IF @TotalRecordsForImport = 0 ------------AND @EmptyInput - culprit ? 10/15 **& ------------ = ------------ 'Y' BEGIN SELECT @starttrancount = @@TRANCOUNT; IF @starttrancount > 0 BEGIN ROLLBACK TRANSACTION; END; --- create string to copy or rename file with a date and time stamp --DECLARE -- @SQLstmt2 varchar( 250 ); --SET @dt = GETDATE( ); ---- Important: For Deletes quotes are NOT NEEEDED around filenames to work in T-SQL -- George ---- Important: For Deletes quotes are NOT NEEEDED around filenames to work in T-SQL -- George ----------------------------------------------------------------------------------------------------------------- --SET @SQLstmt2 = 'del S:\ATSJobs\Archive\ATSToday.txt'; --EXEC xp_cmdshell @SQLstmt2; ----EXEC sp_SendCDOSysMail ---------------------------------------------------------------------------------------------------------------------------------- -- using OLE to delete the file DECLARE @Result int; DECLARE @FSO_Token int; EXEC @Result = sp_OACreate 'Scripting.FileSystemObject' ,@FSO_Token OUTPUT; EXEC @Result = sp_OAMethod @FSO_Token ,'DeleteFile' ,NULL ,'D:\ATS2EandEJobs\Archive\ATSToday.txt'; --&EXEC @Result = sp_OAMethod @FSO_Token ,'DeleteFile' ,NULL ,'E:\ATSJobs\Archive\ATSToday.txt'; EXEC @Result = sp_OADestroy @FSO_Token; END; END TRY BEGIN CATCH BEGIN SELECT @starttrancount = @@TRANCOUNT; IF @starttrancount > 0 BEGIN ROLLBACK TRANSACTION; END; ---- IF SP needs to be aborted for whatever reason ----------- Delete input file if problem encountered ----------- -- using OLE to delete the file DECLARE @Result2 int; DECLARE @FSO_Token2 int; EXEC @Result = sp_OACreate 'Scripting.FileSystemObject' ,@FSO_Token2 OUTPUT; --EXEC @Result = sp_OAMethod @FSO_Token2 ,'DeleteFile' ,NULL ,'D:\ATS2EandEJobs\Archive\ATSToday.txt'; --&EXEC @Result = sp_OAMethod @FSO_Token2 ,'DeleteFile' ,NULL ,'E:\ATSJobs\Archive\ATSToday.txt'; EXEC @Result = sp_OADestroy @FSO_Token2; ----------- Delete input file if problem encountered ----------- EXEC sp_SendCDOSysMail END; DECLARE @ErrorNumber int , @ErrorMessage nvarchar( 4000 ) , @ErrorProcedure nvarchar( 4000 ) , @ErrorLine int; SET @ErrorNumber = ERROR_NUMBER( ); SET @ErrorMessage = ERROR_MESSAGE( ); SET @ErrorProcedure = ERROR_PROCEDURE( ); SET @ErrorLine = ERROR_LINE( ); RAISERROR( 'An error occurred within transaction. Error Number : %d Error Message : %s Procedure : %s Line Number : %d' ,16 ,1 ,@ErrorNumber ,@ErrorMessage ,@ErrorProcedure ,@ErrorLine ); -------------- EXEC SP_SendICESErrorEmail @ErrorNumber ,@ErrorMessage ,@ErrorProcedure ,@ErrorLine ,'' ,'Daily ATS Import'; END CATCH; END; END;
…即使代理作業在輔助伺服器上重新啟用並並行執行仍然是一個謎。
在可用性組環境中，請不要使用啟用和禁用的作業。處理此問題的正確方法是檢查實例目前是否為主副本。如果是，請繼續工作。如果不是，則成功完成。
這是一個使用目前 T-SQL Jobstep 的小範例：
IF sys.fn_hadr_is_primary_replica('EEOutageTracking') = 1 Begin EXEC SP_ATSImport End它應該返回“無法訪問數據庫 EEOutageTracking。（ObjectExplorer）”
如果數據庫不可讀（離線等）或連接字元串未正確設置（又名 ApplicationIntent），則會出現此錯誤。這不是您嘗試寫入只讀數據庫（3906）時遇到的錯誤：
Failed to update database %s because the database is read-only.此錯誤的嚴重性為 16，應觸發 try/catch 塊。
這通常會使我們從外部機器 FTP 後插入的記錄翻倍。
這條線很可疑：
SET @filename = 'D:\ATS2EandEJobs\Archive\ATSToday.txt';我不知道它是否在伺服器本地（FTP 程序真的知道誰是主要的嗎？）或者它是否是“本地”共享/Dfs。如果該伺服器可以“看到”該文件，它可能會嘗試正常執行。
雖然我非常懷疑是否使用了同義詞，但我從不想假設。
額外的想法
使用一些調試輸出/跟踪來執行它是有意義的。在您的內部檢查周圍放置一些列印語句。我只能推測在不了解環境的情況下可能需要的程式碼路徑。
如果您不想這樣做，請按照我上面的說明檢查您的代理工作步驟將阻止這種情況發生。