James D's questions

Estamos enfrentando erros persistentes ao tentar instalar o SSMS em um de nossos servidores de banco de dados de desenvolvimento. Tentamos os instaladores das versões 17.4 e 17.9.1, mas as mensagens de erro são as mesmas.

Extratos de SSMS-Setup-ENU_20190319131949.log (17.4)

10C0:10B8][2019-03-19T13:28:23]i000: MainViewModel.OnPackageActionProgress: Percent completed: 50, Overall progress: 65
[1E58:1E70][2019-03-19T13:28:24]e000: Error 0x80070643: Process returned error: 0x643
[1E58:1E70][2019-03-19T13:28:24]e000: Error 0x80070643: Failed to execute EXE package.
[10C0:10B8][2019-03-19T13:28:24]e000: Error 0x80070643: Failed to configure per-machine EXE package.
[10C0:10B8][2019-03-19T13:28:24]i000: MainViewModel.OnPackageAction: Install Completed for package Microsoft Visual Studio Tools for Applications 2015 Language Support
...
[10C0:10B8][2019-03-19T13:33:28]e000: MainViewModel.OnBundleAction: Bundle action failed: Fatal error during installation (0x80070643)
[10C0:10B8][2019-03-19T13:33:28]i399: Apply complete, result: 0x80070643, restart: None, ba requested restart:  No
[10C0:1C40][2019-03-19T13:33:42]i000: MainViewModel.OpenUrl: Opening url: C:\Users\******~1\AppData\Local\Temp\SsmsSetup\SSMS-Setup-ENU_20190319131949.log

Extratos de SSMS-Setup-ENU_20190319134134.log (17.9.1)

[16C4:2298][2019-03-19T13:49:35]i000: MainViewModel.OnPackageActionProgress: Percent completed: 50, Overall progress: 67
[2324:1D80][2019-03-19T13:49:35]e000: Error 0x80070643: Process returned error: 0x643
[2324:1D80][2019-03-19T13:49:35]e000: Error 0x80070643: Failed to execute EXE package.
[16C4:2298][2019-03-19T13:49:35]e000: Error 0x80070643: Failed to configure per-machine EXE package.
[16C4:2298][2019-03-19T13:49:35]i000: MainViewModel.OnPackageAction: Install Completed for package Microsoft Visual Studio Tools for Applications 2015 Language Support
[16C4:2298][2019-03-19T13:49:35]i319: Applied execute package: VSTALS2015, result: 0x80070643, restart: None
[16C4:2298][2019-03-19T13:49:35]e000: Error 0x80070643: Failed to execute EXE package.
[2324:1D80][2019-03-19T13:49:35]i351: Removing cached package: VS2015KB3095681Update, from path: C:\ProgramData\Package Cache\02A26E554FBB4232ACD36E70D09F2C7893D399CD\
...
[16C4:2298][2019-03-19T13:55:14]e000: MainViewModel.OnBundleAction: Bundle action failed: Fatal error during installation (0x80070643)
[16C4:2298][2019-03-19T13:55:14]i399: Apply complete, result: 0x80070643, restart: None, ba requested restart:  No
[16C4:1798][2019-03-19T13:56:28]i000: MainViewModel.OpenUrl: Opening url: C:\Users\******~1\AppData\Local\Temp\SsmsSetup\SSMS-Setup-ENU_20190319134134.log

O instalador leva muitos minutos para chegar a esse ponto, mas sempre falha com o mesmo resultado.

Reiniciamos o servidor entre cada tentativa e executamos as atualizações mais recentes do Windows. Temos o .NET Framework 4.5.2 instalado no servidor. Todas as tentativas foram feitas usando contas com permissões totais de administrador no servidor.

Alguém pode por favor aconselhar como podemos obter isso instalado com sucesso?

Ajuda necessária em um problema de impasse herdado!

O código abaixo parece ter sido escrito há algum tempo, em uma tentativa de resolver problemas de contenção em torno da atribuição de novos valores de ID em uma tabela pai [IDs] muito maior/mais gorda, primeiro criando os valores de identidade em um [ID_Stub menor ] tabela. No entanto, a presença de deadlocks contínuos indica que esse código parece estar causando mais problemas do que resolvendo.

Frequentemente, enfrentamos deadlocks em torno de uma instrução INSERT na tabela a seguir (os nomes de tabela e coluna foram ofuscados). A tabela não tem gatilhos ou dependências de chave estrangeira, mas tem um índice clusterizado e um não clusterizado conforme a seguir.

CREATE TABLE dbo.ID_Stub (
ID int IDENTITY(1,1) NOT NULL,
IDReference nchar(25) NULL,
AdditionalID int NULL,
CreatedBy int NOT NULL,
CreatedOn datetime NOT NULL,
CONSTRAINT PK_ID_Stub PRIMARY KEY CLUSTERED (ID) WITH 
(
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ONLINE = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, 
    FILLFACTOR = 90
)
);
GO

CREATE NONCLUSTERED INDEX idx_IDReference ON dbo.ID_Stub (IDReference) WITH 
(
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = OFF, 
    DROP_EXISTING = OFF, 
    ONLINE = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, 
    FILLFACTOR = 70
);
GO

A tabela contém uma média de cerca de 70.000 linhas a qualquer momento (um processo é executado para reduzir o número de registros todas as noites).

O nível de isolamento de transação de instância padrão é READ COMMITTED, no entanto, isso é substituído (no procedimento armazenado em que os deadlocks ocorrem) para SERIALIZABLE, juntamente com uma transação implícita. Infelizmente, não podemos considerar a mudança para uma estratégia de bloqueio otimizada, como RCSI, neste estágio.

Toda a transação implícita foi fornecida abaixo para completar, mas os deadlocks estão ocorrendo em torno da instrução INSERT final no ID_Stub no final.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION

-- If a reference has been provided...
IF ISNULL(@IDReference, '') > ''
BEGIN
    IF @ID IS NULL
    BEGIN
        -- Attempt to locate record based on provided reference.
        SELECT @ID = MAX(ID)
        FROM dbo.IDs I
        WHERE I.IDReference = LTRIM(RTRIM(@IDReference))
        AND I.CreatedBy = @CreatedBy
        AND I.AdditionalID = @AdditionalID

        IF @ID IS NULL
        BEGIN
            -- If there is no corresponding record, the subsequent ID creation after the stub failed.
            SET @OriginallyCreated = 
            (
                SELECT MAX(CreatedOn) 
                FROM dbo.ID_Stub IDS 
                WHERE IDS.IDReference = LTRIM(RTRIM(@IDReference))
                AND IDS.CreatedBy = @CreatedBy
                AND IDS.AdditionalID = @AdditionalID
            );

            -- Delete the stub record if created more than 90 seconds ago.
            IF @OriginallyCreated IS NOT NULL
            BEGIN
                IF DATEDIFF(s, @OriginallyCreated, GETDATE()) < 90
                    SELECT @FailureMessage = 'The ID for reference ' + RTRIM(@IDReference) + ' is still being processed.  Please try later.';
                ELSE
                    DELETE dbo.ID_Stub
                    WHERE IDReference = LTRIM(RTRIM(@IDReference))
                    AND CreatedBy = @CreatedBy
                    AND AdditionalID = @AdditionalID;
            END
        END
    END
    ELSE
    BEGIN
        IF NOT EXISTS 
        (
            SELECT ID
            FROM dbo.IDs I
            WHERE I.ID = @ID
            AND I.CreatedBy = @CreatedBy
        )
            SELECT @FailureMessage = 'You have no record stored against the supplied ID (' + RTRIM(CONVERT(Char, @ID)) + ')';
        ELSE
            IF NOT EXISTS 
            (
                SELECT ID
                FROM dbo.IDs I
                WHERE I.ID = @ID
                AND I.IDReference = @IDReference
                AND I.CreatedBy = @CreatedBy
            )
                SELECT @FailureMessage = 'The ID does not match the reference you supplied.';
        END
END
ELSE -- No ID Reference provided.
BEGIN
    IF @ID IS NOT NULL
    BEGIN
        IF NOT EXISTS 
        (
            SELECT ID
            FROM dbo.IDs I
            WHERE I.ID = @ID
            AND I.CreatedBy = @CreatedBy
        )
            SELECT @FailureMessage = 'You have no record stored against the supplied ID (' + RTRIM(CONVERT(Char, @ID)) + ').';
    END
END

IF @FailureMessage <> ''
BEGIN
    ROLLBACK;
    RETURN 1;
END

-- If it's a new submission, create a stub for it.
IF @ID IS NULL
BEGIN
    INSERT dbo.ID_Stub (IDReference, AdditionalID, CreatedBy, CreatedOn)
        VALUES (@IDReference, @AdditionalID, @CreatedBy, GETDATE());

    SELECT @ID = SCOPE_IDENTITY();
END

COMMIT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

O relatório de deadlock XML é o seguinte.

<deadlock>
    <victim-list>
        <victimProcess id="process1e7fd5d8108"/>
    </victim-list>
    <process-list>
        <process id="process1e7fd5d8108" taskpriority="0" logused="336" waitresource="KEY: 30:72057596372844544 (4f8ff66d381b)" waittime="4265" ownerId="1879373839" transactionname="user_transaction" lasttranstarted="2018-12-29T22:19:51.357" XDES="0x1dfe81c9538" lockMode="RangeI-N" schedulerid="6" kpid="2084" status="suspended" spid="182" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-29T22:19:51.307" lastbatchcompleted="2018-12-29T22:19:51.293" lastattention="1900-01-01T00:00:00.293" clientapp=".Net SqlClient Data Provider" hostname="<obfuscated>" hostpid="3648" loginname="<obfuscated>" isolationlevel="serializable (4)" xactid="1879373839" currentdb="30" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="<obfuscated>" line="640" stmtstart="68006" stmtend="68296" sqlhandle="0x03001e002acd7f07eca57a012ea9000001000000000000000000000000000000000000000000000000000000">  INSERT dbo.ID_Stub (IDReference, AdditionalID, CreatedBy, CreatedOn)       VALUES (@IDReference, @AdditionalID, @CreatedBy, GETDATE();    </frame>
            </executionStack>
            <inputbuf>  Proc [Database Id = 30 Object Id = 125816106]   </inputbuf>
        </process>
        <process id="process1fd327c7c28" taskpriority="0" logused="336" waitresource="KEY: 30:72057596372844544 (4f8ff66d381b)" waittime="4265" ownerId="1879373837" transactionname="user_transaction" lasttranstarted="2018-12-29T22:19:51.357" XDES="0x1e76d97ebd8" lockMode="RangeI-N" schedulerid="3" kpid="9084" status="suspended" spid="208" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-29T22:19:51.300" lastbatchcompleted="2018-12-29T22:19:51.293" lastattention="1900-01-01T00:00:00.293" clientapp=".Net SqlClient Data Provider" hostname="<obfuscated>" hostpid="3648" loginname="<obfuscated>" isolationlevel="serializable (4)" xactid="1879373837" currentdb="30" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="<obfuscated>" line="640" stmtstart="68006" stmtend="68296" sqlhandle="0x03001e002acd7f07eca57a012ea9000001000000000000000000000000000000000000000000000000000000">  INSERT dbo.ID_Stub (IDReference, AdditionalID, CreatedBy, CreatedOn)       VALUES (@IDReference, @AdditionalID, @CreatedBy, GETDATE();    </frame>
            </executionStack>
            <inputbuf>  Proc [Database Id = 30 Object Id = 125816106]   </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057596372844544" dbid="30" objectname="<obfuscated>.<obfuscated>.ID_Stub" indexname="idx_IDReference" id="lock1e566d4a380" mode="RangeS-S" associatedObjectId="72057596372844544">
            <owner-list>
                <owner id="process1fd327c7c28" mode="RangeS-S"/>
                <owner id="process1fd327c7c28" mode="RangeI-N" requestType="convert"/>
            </owner-list>
            <waiter-list>
                <waiter id="process1e7fd5d8108" mode="RangeI-N" requestType="convert"/>
            </waiter-list>
        </keylock>
        <keylock hobtid="72057596372844544" dbid="30" objectname="<obfuscated>.<obfuscated>.ID_Stub" indexname="idx_IDReference" id="lock1e566d4a380" mode="RangeS-S" associatedObjectId="72057596372844544">
            <owner-list>
                <owner id="process1e7fd5d8108" mode="RangeS-S"/>
                <owner id="process1e7fd5d8108" mode="RangeI-N" requestType="convert"/>
            </owner-list>
            <waiter-list>
                <waiter id="process1fd327c7c28" mode="RangeI-N" requestType="convert"/>
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>  

O plano de consulta para a primeira instrução pode ser encontrado no link abaixo.

https://www.brentozar.com/pastetheplan/?id=BkVfC8qbN

Quaisquer dicas ou conselhos sobre a melhor forma de lidar com esses impasses seriam muito apreciados.

Historicamente, era recomendado não usar as portas padrão para conexões com o SQL Server, como parte das práticas recomendadas de segurança. Em um servidor com uma única instância padrão, as seguintes portas seriam usadas por padrão:

• Serviço SQL Server - Porta 1433 (TCP)
• Serviço SQL Server Browser - Porta 1434 (UDP)
• Conexão de administrador dedicada - Porta 1434 (TCP)

PERGUNTAS:

• Este conselho ainda é relevante?
• TODAS as portas acima devem ser alteradas?

Avaliamos as políticas de "práticas recomendadas" da Microsoft (localizadas em C:\Program Files (x86)\Microsoft SQL Server\number\Tools\Policies\DatabaseEngine\1033) em cada uma de nossas instâncias do SQL Server (exemplo aqui ). O seguinte problema foi sinalizado para nós:

Isso fornece um link para uma página da Microsoft que declara o seguinte como prática recomendada:

Não conceda permissões de servidor à função pública de servidor.

Ao revisar a segurança, podemos ver que a função pública tem permissões de servidor para visualizar qualquer banco de dados por padrão. Executamos o seguinte e reavaliamos a política, mas o problema ainda está sinalizado.

REVOKE VIEW ANY DATABASE TO [public] AS [sa];

As únicas outras permissões atribuídas a essa função de servidor são permissões de ponto de extremidade para poder se conectar via TCP/VIA/pipes nomeados/máquina local. Se os removermos, certamente o papel será supérfluo.

PERGUNTAS

• Devemos nos preocupar com isso? Isso é algo que realmente devemos procurar resolver?
• O que mais precisamos fazer para resolver adequadamente esse problema?
• Existem implicações para fazer isso ou não fazer isso?

Configuramos Alertas para as principais gravidades e códigos de erro em nossas instâncias do SQL Server. Cada alerta então executa o mesmo trabalho do SQL Server Agent, para acionar um alerta em nosso sistema de monitoramento (para mensagens SMS).

Seria útil poder recuperar o texto de descrição do alerta (consulte "Isso foi apenas um teste!" no exemplo abaixo) de algum lugar, para alimentar nosso sistema de monitoramento.

DATE/TIME:   27/09/2018 16:08:19
DESCRIPTION: Error: 50000 Severity: 20 State: 1 This was just a test!
COMMENT:     (None)
JOB RUN:     SystemAlerts

Este texto está armazenado em qualquer lugar no banco de dados msdb? Ou não é persistente como o histórico de trabalhos do SQL Server Agent?

Recentemente, e em mais de uma ocasião agora, tentamos nos conectar de uma de nossas instâncias do SQL Server a outra usando o SSMS apenas para ser recebido com o seguinte erro:

Um erro severo ocorreu no comando atual. Os resultados, se existirem, deveriam ser descartados.

O seguinte erro ocorre quando tentamos conectar usando o SSMS localmente:

Uma conexão foi estabelecida com sucesso com o servidor, mas ocorreu um erro durante o processo de login. (provedor: provedor de memória compartilhada, erro: 0 - nenhum processo está na outra extremidade do pipe.) (Microsoft SQL Server, erro: 233)

As conexões por meio da Conexão Administrativa Dedicada (DAC) resultam nos mesmos erros. SQLCMD revela o seguinte erro:

O SQL Server Configuration Manager informa que o serviço SQL Server e o serviço SQL Server Agent estão em execução.

O log de erros do SQL Server é interrompido ao mesmo tempo em que as entradas no Visualizador de Eventos começam a ser registradas, relatando problemas de conexão para trabalhos do SQL Server Agent.

Trabalho agendado do SQL Server 'sp_WhoIsActive' (0x868EBDEC06F71344AC717E0C6A333D13) - Status: Falha - Invocado em: 2018-09-18 10:38:00 - Mensagem: O trabalho falhou. Não é possível determinar se o proprietário (sa) do trabalho sp_WhoIsActive tem acesso ao servidor (motivo: não é possível conectar ao servidor - verifique os logs de erro do SQL Server e do SQL Server Agent).

Todos os nossos serviços do SQL Server foram configurados para serem executados em Contas de Serviço Gerenciado por Grupo (gMSA's).

A única solução até agora foi reiniciar os servidores, mas claramente esta não é uma solução satisfatória, especialmente porque o problema já ocorreu mais de uma vez.

A única coisa que temos que fazer é que há várias ocorrências para a seguinte mensagem de erro no log de erros do SQL Server antes que os problemas de conexão ocorram.

2018-09-17 13:31:19.20 Logon Falha no login para o usuário '[domínio][gMSA account for SQL Agent]$'. Motivo: Falha ao abrir o banco de dados explicitamente especificado '[database_name]'. [CLIENTE: [máquina local]] 17/09/2018 13:32:03.63 Erro de logon: 18456, Gravidade: 14, Estado: 38.

É possível que o número máximo de conexões tenha sido excedido como resultado desses problemas de login? Em caso afirmativo, por que ainda não é possível conectar via DAC?

Alguém por favor pode aconselhar?

Detalhes adicionais:

• Microsoft SQL Server 2016 (SP1-CU8) (KB4077064) - 13.0.4474.0 (X64) 24 de fevereiro de 2018 13:53:17 Direitos autorais (c) Microsoft Corporation Web Edition (64 bits) no Windows Server 2016 Standard 10.0 (Build 14393: ) (Hipervisor)
• SQL Server Management Studio v17.8.1
• O número máximo de conexões de usuário é definido como ilimitado (0).
• Memória Compartilhada, TCP/IP e Pipes Nomeados estão todos habilitados.

Desculpe se esta é uma pergunta estúpida. Estamos tentando converter nossas funções com valor de tabela de várias instruções (MSTVFs) em funções com valor de tabela em linha (ITVFs), para evitar a serialização forçada de consultas.

Eu tenho a seguinte função ITVF (diluída), mas o Plano de Execução ainda diz que tem um Grau de Paralelismo de 1. A função real em questão tem três instruções SELECT básicas separadas por UNION ALLs.

Eu perdi alguma coisa? Como posso evitar a serialização forçada aqui?

CREATE FUNCTION dbo.Test (@i int)
RETURNS TABLE
AS
RETURN
SELECT @i as [i];
GO

SELECT * FROM dbo.Test (2);
GO

https://www.brentozar.com/pastetheplan/?id=Hyn8o50D7

A instância tem as seguintes configurações:

• Limite de custo para paralelismo = 5
• Grau Máximo de Paralelismo = 0