Blog de Dje

Un article de Ptit_Dje    Pas de commentaires

De base, il n’est pas possible d’enregistrer l’instance même du CMS enregistré.
Cependant il existe un moyen de contourner cette limitation…

Outre le fait d’utiliser un alias DNS ou un trick du genre référencer le nom du serveur par 127.0.0.1, il est possible de contourner cette limitation.

Il suffit de modifier la stored procedure [msdb].[dbo].[sp_sysmanagement_add_shared_registered_server] comme ceci:

USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[sp_sysmanagement_add_shared_registered_server]    Script Date: 1/30/2012 7:18:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_sysmanagement_add_shared_registered_server]
    @name sysname,
    @server_group_id INT,
    @server_name sysname,
    @description NVARCHAR(2048) = N'',
    @server_type INT,
    @server_id INT OUTPUT
AS
BEGIN
    DECLARE @retval INT
 
    EXECUTE @retval = sp_sysmanagement_verify_shared_server_type @server_type
 
    IF (@retval <> 0)
        RETURN(1) -- Failure
     
    IF( (@server_group_id IS NULL) OR  
        (@server_group_id NOT IN (SELECT sg.server_group_id FROM msdb.dbo.sysmanagement_shared_server_groups_internal sg)))
    BEGIN
        RAISERROR (35001, -1, -1)
        RETURN (1)
    END
 
    IF EXISTS (SELECT * FROM [msdb].[dbo].[sysmanagement_shared_server_groups_internal]  sg  
                WHERE @server_group_id = sg.server_group_id AND @server_type <> sg.server_type)
    BEGIN
        RAISERROR (35002, -1, -1)
        RETURN (1)
    END    
 
    IF (@server_name IS NULL)
    BEGIN
        RAISERROR(14618, -1, 1, '@server_name')
        RETURN(1)    
    END
 
    set @server_name = LTRIM(@server_name)
    set @server_name = RTRIM(@server_name)
 
    -- Disallow relative names
    IF ('.' = @server_name) OR
        (1 = CHARINDEX(N'.\', @server_name)) OR
        (1 = CHARINDEX(N'LOCALHOST\', UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS))) OR
        (UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS) = 'LOCALHOST') OR
        (UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS) = '(LOCAL)')
    BEGIN
        RAISERROR (35011, -1, -1)
        RETURN (1)
    END
 
    -- Autorize to register the CMS instance
    --IF (UPPER(@@SERVERNAME collate SQL_Latin1_General_CP1_CS_AS) = UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS))
    --BEGIN
    --    RAISERROR (35012, -1, -1)
    --    RETURN (1)
    --END
 
    INSERT INTO [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
        (server_group_id, name, server_name, description, server_type)
    VALUES
        (@server_group_id, @name, @server_name, @description, @server_type)
         
    SELECT @server_id = SCOPE_IDENTITY()
    RETURN (0)
END

Vous remarquerez que le bout de code suivant a été commenté:

    -- Autorize to register the CMS instance
    --IF (UPPER(@@SERVERNAME collate SQL_Latin1_General_CP1_CS_AS) = UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS))
    --BEGIN
    --    RAISERROR (35012, -1, -1)
    --    RETURN (1)
    --END

Je ne garanti en aucun cas les potentiels effets de bord pouvant survenir.
Encore une fois la règle est de tester!

Si vous optez pour cette solution, pensez aussi à mettre à jour la procédure suivante, celle d’update d’un serveur avec le code ci-dessous:

USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[sp_sysmanagement_update_shared_registered_server]    Script Date: 1/31/2012 9:02:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_sysmanagement_update_shared_registered_server]
    @server_id INT,
    @server_name sysname = NULL,
    @description NVARCHAR(2048) = NULL
AS
BEGIN
    IF (@server_id IS NULL)
    BEGIN
        RAISERROR (35006, -1, -1)
        RETURN(1)
    END
     
    IF NOT EXISTS (SELECT * FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] WHERE server_id = @server_id)
    BEGIN
        RAISERROR (35007, -1, -1)
        RETURN(1)
    END
 
    IF (@server_name IS NULL)
    BEGIN
        SET @server_name = (select server_name FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] WHERE server_id = @server_id)
    END
 
    set @server_name = LTRIM(@server_name)
    set @server_name = RTRIM(@server_name)
 
    -- Disallow relative names
    IF ('.' = @server_name) OR
        (1 = CHARINDEX(N'.\', @server_name)) OR
        (1 = CHARINDEX(N'LOCALHOST\', UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS))) OR
        (UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS) = 'LOCALHOST') OR
        (UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS) = '(LOCAL)')
    BEGIN
        RAISERROR (35011, -1, -1)
        RETURN (1)
    END
 
    --IF (UPPER(@@SERVERNAME collate SQL_Latin1_General_CP1_CS_AS) = UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS))
    --BEGIN
    --    RAISERROR (35012, -1, -1)
    --    RETURN (1)
    --END
 
    UPDATE [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]
        SET server_name = ISNULL(@server_name, server_name),
            description = ISNULL(@description, description)
    WHERE
        server_id = @server_id
    RETURN (0)
END

Bon hack,
Dje