Dave.Gugg's questions

Estou configurando um ambiente de teste/PreProd fazendo backups noturnos de nosso ambiente Prod e aplicando-os ao nosso novo ambiente PreProd. Esta parte está funcionando bem. Infelizmente, temos várias replicações configuradas em nosso ambiente. Eu configurei procs para remover a replicação PreProd logo antes das restaurações noturnas e, em seguida, recriá-la após a conclusão das restaurações. Isso funciona bem, mas leva muito tempo (3 a 4 horas), pois algumas mesas são bem grandes. Gostaria de compactar essas tabelas grandes, mas não quero dobrar o trabalho. A primeira etapa da aplicação do instantâneo é eliminar e recriar a tabela de destino. O segundo passo é inserir todos os dados. Eu poderia compactar logo depois disso, mas basicamente estou construindo a tabela duas vezes, a primeira vez descompactada e a segunda vez com compactação.

Testei o parâmetro @pre_snapshot_script, mas ele é aplicado antes da etapa que elimina e recria a tabela (os arquivos .pre e .sch na pasta Snapshot). Isso não funcionará.

Há alguma maneira de garantir programaticamente que a tabela de assinantes seja compactada ANTES de os dados do instantâneo serem aplicados a ela?

Eu uso certificados de bancos de dados cruzados ( conforme explicado por Erland Sommarskog ) para controlar o acesso a um determinado banco de dados em meu ambiente (SQL Server 2008 R2).

Eu armazenei procedimentos no banco de dados A que atualizam tabelas no banco de dados B. Isso sempre funcionou para uma variedade de procedimentos armazenados no banco de dados A e tabelas no banco de dados B até agora. Estou tentando atualizar uma tabela no banco de dados B, mas a tabela possui um gatilho. Este gatilho está inserindo dados adicionais em outra tabela no db B. Estou recebendo o erro:

Msg 916, Nível 14, Estado 1, Procedimento table_trigger, Linha 11 O servidor principal "sql\login" não pode acessar o banco de dados "B" no contexto de segurança atual.

Tentei conceder permissões de inserção para o usuário do banco de dados B que está vinculado ao certificado para inserir nessa outra tabela, mas não resolveu o erro. Tenho outras opções além de alterar o gatilho para que ele use WITH EXECUTE AS OWNER?

Aqui está o DDL para replicar o problema:

CREATE LOGIN [GuggTest] WITH PASSWORD=N'abcd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE DATABASE A;
CREATE DATABASE B;

USE A;

CREATE TABLE dbo.SPtoUpdate
    (
      ID INT
    , ILoveFishing VARCHAR(255)
    );
INSERT INTO dbo.SPtoUpdate
        ( ID , ILoveFishing )
VALUES  ( 1,'Musky'),( 2,'Pike'),( 3,'Yellow Perch');
CREATE TABLE dbo.TriggerToInsert
    (
      ID INT
    , ILoveFishing VARCHAR(255)
    , ChangeDate DATETIME2
    );
GO

CREATE TRIGGER dbo.SPtoUpdateTrigger ON dbo.SPtoUpdate
    FOR UPDATE
AS
    DECLARE @datetime DATETIME2;
    SELECT  @datetime = GETDATE()

    INSERT  INTO dbo.TriggerToInsert
            ( ID , ILoveFishing , ChangeDate )
    VALUES  ( 1 , 'Yes' , @datetime );
GO

CREATE CERTIFICATE BExecutor
   ENCRYPTION BY PASSWORD = 'Obfuscated'
   WITH SUBJECT = 'Execute sp from B to A',
   START_DATE = '20140101', EXPIRY_DATE = '20300101'
GO

BACKUP CERTIFICATE BExecutor TO FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'Obfuscated',
                  DECRYPTION BY PASSWORD = 'Obfuscated')
GO

CREATE USER BExecutor FROM CERTIFICATE BExecutor

GRANT UPDATE ON dbo.SPtoUpdate TO BExecutor
GRANT SELECT ON dbo.SPtoUpdate TO BExecutor
--Also give insert on dbo.TriggerToInsert
GRANT INSERT ON dbo.TriggerToInsert TO BExecutor

USE B
GO

CREATE USER [GuggTest] FOR LOGIN [GuggTest];
EXEC sp_addrolemember N'db_owner', N'GuggTest'
GO

CREATE PROCEDURE dbo.UpdateTableInA
AS
    BEGIN
        UPDATE  A.dbo.SPtoUpdate
        SET     ILoveFishing = 'Walleye'
        WHERE   ID = 2;
    END

GO


CREATE CERTIFICATE BExecutor FROM FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'Obfuscated',
                  DECRYPTION BY PASSWORD = 'Obfuscated')
GO

EXEC master..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output'
GO

ADD SIGNATURE TO dbo.UpdateTableInA BY CERTIFICATE BExecutor
    WITH PASSWORD = 'Obfuscated'
GO

--Log In or Change execution context to GuggTest, then EXEC dbo.UpdateTableInA