>source

SQL Server의 저장 프로 시저를 사용하여 모니터링 솔루션 (저장 프로 시저 결과가 전자 메일을 통해 전송 됨)을 구축하려고합니다.

약 100 개의 서로 다른 서버가 있으며 보안으로 인해 연결된 서버가 구성되지 않았습니다.

SSMS 중앙 관리 서버를 사용하여 각 서버에 저장 프로 시저를 배포했습니다. (모든 시스템에서 DB 메일을 활성화 한 경우 이러한 SP의 출력을 DBA로 직접 보내지 만 CMS 서버에서만 메일을 사용할 수 있습니다.)

링크 된 서버가 활성화되지 않은 경우 이러한 저장 프로 시저의 출력을 얻으려면 어떻게합니까?

링크 된 서버를 통해 저장 프로 시저를 실행하려고했지만 Kerebos 오류가 발생합니다. 시스템이 너무 잠겨 있고이를 변경하는 데 관료주의가 너무 많습니다.

논리의 의사 코드는 다음과 같습니다. 현재 직면하고있는 주요 문제는이 코드 부분과 관련이 있습니다.

 SELECT *  
    FROM OPENROWSET('SQLNCLI', 'Server=BBMAG552;Trusted_Connection=yes;',  
         'EXEC master.dbo.usp_sizing '''database''') AS a;

아래의 전체 코드 :

-- Pseduocode
--Build list of all servers registered in CMS
SELECT DISTINCT groups.name AS 'Server Group Name'
     ,svr.server_name AS 'Server Name'
FROM msdb.dbo.sysmanagement_shared_server_groups_internal groups 
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr
 ON groups.server_group_id = svr.server_group_id;
GO
-- execute the stored procedure against list of all servers using a
-- sp_MSforeachdb 
SELECT *  
FROM OPENROWSET('SQLNCLI', 'Server=BBMAG552;Trusted_Connection=yes;',  
     'EXEC master.dbo.usp_sizing '''database''') AS a; 

-- This script is run on all our servers.
CREATE PROCEDURE dbo.usp_Sizing @Granularity VARCHAR(10) = NULL, @Database_Name sysname = NULL AS   
DECLARE @SQL VARCHAR(5000)   
IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME = '##Results')    
   BEGIN    
       DROP TABLE ##Results    
   END   
CREATE TABLE ##Results ([Database Name] sysname, 
[File Name] sysname, 
[Physical Name] NVARCHAR(260),
[File Type] VARCHAR(4), 
[Total Size in Mb] INT, 
[Available Space in Mb] INT, 
[Growth Units] VARCHAR(15), 
[Max File Size in Mb] INT)   
SELECT @SQL =    
'USE [?] INSERT INTO ##Results([Database Name], [File Name], [Physical Name],    
[File Type], [Total Size in Mb], [Available Space in Mb],    
[Growth Units], [Max File Size in Mb])    
SELECT DB_NAME(),   
[name] AS [File Name],    
physical_name AS [Physical Name],    
[File Type] =    
CASE type   
WHEN 0 THEN ''Data'''    
+   
           'WHEN 1 THEN ''Log'''   
+   
       'END,   
[Total Size in Mb] =   
CASE ceiling([size]/128)    
WHEN 0 THEN 1   
ELSE ceiling([size]/128)   
END,   
[Available Space in Mb] =    
CASE ceiling([size]/128)   
WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)   
ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)   
END,   
[Growth Units]  =    
CASE [is_percent_growth]    
WHEN 1 THEN CAST(growth AS varchar(20)) + ''%'''   
+   
           'ELSE CAST(growth*8/1024 AS varchar(20)) + ''Mb'''   
+   
       'END,   
[Max File Size in Mb] =    
CASE [max_size]   
WHEN -1 THEN NULL   
WHEN 268435456 THEN NULL   
ELSE [max_size]   
END   
FROM sys.database_files   
ORDER BY [File Type], [file_id]'   
--Print the command to be issued against all databases   
PRINT @SQL   
--Run the command against each database   
EXEC sp_MSforeachdb @SQL   
--UPDATE ##Results SET [Free Space %] = [Available Space in Mb]/[Total Size in Mb] * 100   
--Return the Results   
--If @Database_Name is NULL:   
IF @Database_Name IS NULL   
   BEGIN   
       IF @Granularity = 'Database'   
           BEGIN   
               SELECT    
               T.[Database Name],   
               T.[Total Size in Mb] AS [DB Size (Mb)],   
               T.[Available Space in Mb] AS [DB Free (Mb)],   
               T.[Consumed Space in Mb] AS [DB Used (Mb)],   
               D.[Total Size in Mb] AS [Data Size (Mb)],   
               D.[Available Space in Mb] AS [Data Free (Mb)],   
               D.[Consumed Space in Mb] AS [Data Used (Mb)],   
               CEILING(CAST(D.[Available Space in Mb] AS decimal(10,1))/D.[Total Size in Mb]*100) AS [Data Free %],   
               L.[Total Size in Mb] AS [Log Size (Mb)],   
               L.[Available Space in Mb] AS [Log Free (Mb)],   
               L.[Consumed Space in Mb] AS [Log Used (Mb)],   
               CEILING(CAST(L.[Available Space in Mb] AS decimal(10,1))/L.[Total Size in Mb]*100) AS [Log Free %]   
               FROM    
                   (   
                   SELECT [Database Name],   
                       SUM([Total Size in Mb]) AS [Total Size in Mb],   
                       SUM([Available Space in Mb]) AS [Available Space in Mb],   
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]    
                   FROM ##Results   
                   GROUP BY [Database Name]   
                   ) AS T   
                   INNER JOIN    
                   (   
                   SELECT [Database Name],   
                       SUM([Total Size in Mb]) AS [Total Size in Mb],   
                       SUM([Available Space in Mb]) AS [Available Space in Mb],   
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]    
                   FROM ##Results   
                   WHERE ##Results.[File Type] = 'Data'   
                   GROUP BY [Database Name]   
                   ) AS D ON T.[Database Name] = D.[Database Name]   
                   INNER JOIN   
                   (   
                   SELECT [Database Name],   
                       SUM([Total Size in Mb]) AS [Total Size in Mb],   
                       SUM([Available Space in Mb]) AS [Available Space in Mb],   
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]    
                   FROM ##Results   
                   WHERE ##Results.[File Type] = 'Log'   
                   GROUP BY [Database Name]   
                   ) AS L ON T.[Database Name] = L.[Database Name]   
               ORDER BY CEILING(CAST(D.[Available Space in Mb] AS decimal(10,1))/D.[Total Size in Mb]*100) 
           END   
   ELSE   
       BEGIN   
           SELECT [Database Name],   
               [File Name],   
               [Physical Name],   
               [File Type],   
               [Total Size in Mb] AS [DB Size (Mb)],   
               [Available Space in Mb] AS [DB Free (Mb)],   
               CEILING(CAST([Available Space in Mb] AS decimal(10,1)) / [Total Size in Mb]*100) AS [Free Space %],   
               [Growth Units],   
               [Max File Size in Mb] AS [Grow Max Size (Mb)]    
           FROM ##Results    
       END   
   END   
--Return the Results   
--If @Database_Name is provided   
ELSE   
   BEGIN   
       IF @Granularity = 'Database'   
           BEGIN   
               SELECT    
               T.[Database Name],   
               T.[Total Size in Mb] AS [DB Size (Mb)],   
               T.[Available Space in Mb] AS [DB Free (Mb)],   
               T.[Consumed Space in Mb] AS [DB Used (Mb)],   
               D.[Total Size in Mb] AS [Data Size (Mb)],   
               D.[Available Space in Mb] AS [Data Free (Mb)],   
               D.[Consumed Space in Mb] AS [Data Used (Mb)],   
               CEILING(CAST(D.[Available Space in Mb] AS decimal(10,1))/D.[Total Size in Mb]*100) AS [Data Free %],   
               L.[Total Size in Mb] AS [Log Size (Mb)],   
               L.[Available Space in Mb] AS [Log Free (Mb)],   
               L.[Consumed Space in Mb] AS [Log Used (Mb)],   
               CEILING(CAST(L.[Available Space in Mb] AS decimal(10,1))/L.[Total Size in Mb]*100) AS [Log Free %]   
               FROM    
                   (   
                   SELECT [Database Name],   
                       SUM([Total Size in Mb]) AS [Total Size in Mb],   
                       SUM([Available Space in Mb]) AS [Available Space in Mb],   
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]    
                   FROM ##Results   
                   WHERE [Database Name] = @Database_Name   
                   GROUP BY [Database Name]   
                   ) AS T   
                   INNER JOIN    
                   (   
                   SELECT [Database Name],   
                       SUM([Total Size in Mb]) AS [Total Size in Mb],   
                       SUM([Available Space in Mb]) AS [Available Space in Mb],   
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]    
                   FROM ##Results   
                   WHERE ##Results.[File Type] = 'Data'   
                       AND [Database Name] = @Database_Name   
                   GROUP BY [Database Name]   
                   ) AS D ON T.[Database Name] = D.[Database Name]   
                   INNER JOIN   
                   (   
                   SELECT [Database Name],   
                       SUM([Total Size in Mb]) AS [Total Size in Mb],   
                       SUM([Available Space in Mb]) AS [Available Space in Mb],   
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]    
                   FROM ##Results   
                   WHERE ##Results.[File Type] = 'Log'   
                       AND [Database Name] = @Database_Name   
                   GROUP BY [Database Name]   
                   ) AS L ON T.[Database Name] = L.[Database Name]   
               ORDER BY D.[Database Name]   
           END   
       ELSE   
           BEGIN   
               SELECT [Database Name],   
               [File Name],   
               [Physical Name],   
               [File Type],   
               [Total Size in Mb] AS [DB Size (Mb)],   
               [Available Space in Mb] AS [DB Free (Mb)],   
               CEILING(CAST([Available Space in Mb] AS decimal(10,1))/[Total Size in Mb]*100) AS [Free Space %],   
               [Growth Units],   
               [Max File Size in Mb] AS [Grow Max Size (Mb)]    
               FROM ##Results    
               WHERE [Database Name] = @Database_Name   
           END   
   END   
DROP TABLE ##Results

  • 답변 # 1

    이러한 모든 인스턴스에 직접 연결할 수 있다고 가정하면 서버에 연결하고 저장 프로 시저를 실행하고 결과를 얻은 다음 표시하는 C # 유틸리티를 로컬로 실행할 수 있습니다.

  • 이전 chef - 방랑자 - 클라이언트에서 로컬 https 사이트로 연결
  • 다음 laravel"\ request - : route ()-> getname ()"은 null 결과를 제공합니다