RateControl's questions

Problema: comparar os dados de um determinado intervalo de datas com os mesmos dados de até 3 anos anteriores e exibir os dados tabulares e um gráfico em um relatório do SSRS. No entanto, atualmente, meu procedimento armazenado que criei para o relatório do SSRS não funciona, pois usa tabelas temporárias, que não são expostas ao SSRS. Entendo totalmente o motivo, o SSRS não tem ideia das estruturas nas tabelas temporárias que criei.

Por exemplo, quero ver os widgets de contagem feitos de 01/01/2012 a 01/04/2012, agrupados por hora.

Portanto, o layout lógico da tabela é:

 Hour Count
    0   100
    1   240
    2   34
    3   24
    4   55
    5   90
    …
    23  7657

Portanto, para esses dados, quero compará-los com o desempenho da planta no(s) ano(s) anterior(es) (até 3), para o mesmo intervalo de datas. Parece tão simples, mas a solução que encontrei é tudo menos isso.

Eu trabalhei para que eu tenha uma função com valor de tabela que calcula os dados necessários para as datas_inicial e_final fornecidas e extrai os dados da tabela de origem. Eu acredito que é a maneira certa de fazer isso?

ALTER FUNCTION [dbo].[Count_TS] (@SDate DATETIME2,@EDate DATETIME2)
    RETURNS @t TABLE ([Hour] INT,[Count] INT) AS
    BEGIN
     INSERT @t ([Hour],[Count])
            SELECT DATEPART(HOUR,AC.[FIELDC]) AS [Hour],COUNT(DISTINCT(AC.[FIELDD])) AS [CFS_Count_1]
            FROM [LINKEDSERVER].[DATABASE].[dbo].[TABLE] AS AC WITH (NOLOCK)
            WHERE (AC.[FIELDA] LIKE 'VALUE' OR AC.[FIELDA] LIKE 'VALUE')  
            AND AC.[FIELDB] = 'VALUE'
            AND (CAST(AC.[FIELDC] AS DATE) >= @SDate AND CAST(AC.[FIELDC] AS DATE) <= @EDate)
            GROUP BY DATEPART(HOUR,AC.[FIELDC])

    RETURN
    END

A parte em que desce é a maneira como construí o procedimento armazenado para exibir os dados no relatório do SSRS. O procedimento armazenado não precisa de explicação, é bastante evidente do que eu fiz. Qual não pode ser a maneira “certa” de fazer isso?

 ALTER PROCEDURE [rptCount_TS] 
      @PREYears INT = NULL
     ,@SDate DATE = NULL
     ,@EDate DATE = NULL
    AS
    BEGIN
    IF DATEDIFF(MONTH,@SDate,@EDate) <=3 AND DATEDIFF(YEAR,@SDate,@EDate) =0
    BEGIN

    IF @PREYears = 0
        BEGIN
            IF OBJECT_ID('tempdb..#CountPrime') IS NOT NULL DROP TABLE dbo.[#CountPrime]; 
            CREATE TABLE #CountPrime ([Hour] INT,[CountPrime] INT)
            INSERT INTO #CountPrime([Hour], [CountPrime]) 
                SELECT [Hour],[Count] FROM [Connector].dbo.[Count_TS] (@SDate,@EDate)
            SELECT  CCP.[Hour]
                        ,CCP.[CountPrime]
            FROM    #CountPrime AS CCP
        END
    ELSE
        IF @PREYears =1
            BEGIN
                IF OBJECT_ID('tempdb..#CountPrime1') IS NOT NULL DROP TABLE dbo.[#CountPrime1]; 
                CREATE TABLE #CountPrime1 ([Hour] INT,[CountPrime] INT)
                IF OBJECT_ID('tempdb..#Count-11') IS NOT NULL DROP TABLE dbo.[#Count-11]; 
                CREATE TABLE [#Count-11] ([Hour] INT,[Count-1] INT)
                INSERT INTO #CountPrime1([Hour], [CountPrime]) 
                                SELECT [Hour],[Count] FROM [Connector].dbo.[Count_TS] (@SDate,@EDate)
                INSERT INTO [#Count-11]([Hour], [Count-1]) 
                    SELECT [Hour],[Count] AS [Count-1] FROM [Connector].dbo.[Count_TS] (DATEADD(YEAR,-1,@SDate),DATEADD(YEAR,-1,@EDate));
                SELECT  CCP.[Hour]
                        ,CCP.[CountPrime]
                        ,SC1.[Count-1]
                FROM    #CountPrime1    AS CCP
                INNER JOIN [#Count-11] AS SC1 ON CCP.[Hour] = SC1.[Hour]
            END
        ELSE
            IF @PREYears =2
                BEGIN
                    IF OBJECT_ID('tempdb..#CountPrime2') IS NOT NULL DROP TABLE dbo.[#CountPrime2]; 
                    CREATE TABLE #CountPrime2 ([Hour] INT,[CountPrime] INT)
                    IF OBJECT_ID('tempdb..#Count-12') IS NOT NULL DROP TABLE dbo.[#Count-12]; 
                    CREATE TABLE [#Count-12] ([Hour] INT,[Count-1] INT)
                    IF OBJECT_ID('tempdb..#Count-22') IS NOT NULL DROP TABLE dbo.[#Count-22]; 
                    CREATE TABLE [#Count-22] ([Hour] INT,[Count-2] INT)     
                    INSERT INTO #CountPrime2([Hour], [CountPrime]) SELECT [Hour],[Count] FROM [Connector].dbo.[Count_TS] (@SDate,@EDate)
                    INSERT INTO [#Count-12]([Hour], [Count-1]) 
                        SELECT [Hour],[Count] AS [Count-1] FROM [Connector].dbo.[Count_TS] (DATEADD(YEAR,-1,@SDate),DATEADD(YEAR,-1,@EDate));
                    INSERT INTO [#Count-22]([Hour], [Count-2]) 
                        SELECT [Hour],[Count] AS [Count-2] FROM [Connector].dbo.[Count_TS] (DATEADD(YEAR,-2,@SDate),DATEADD(YEAR,-2,@EDate));
                    SELECT  CCP.[Hour]
                            ,CCP.[CountPrime]
                            ,SC1.[Count-1]
                            ,SC2.[Count-2]
                    FROM    #CountPrime2    AS CCP
                    INNER JOIN [#Count-12] AS SC1 ON CCP.[Hour] = SC1.[Hour]
                    INNER JOIN [#Count-22] AS SC2 ON CCP.[Hour] = SC2.[Hour]
                END
            ELSE
                IF @PREYears =3
                    BEGIN
                        IF OBJECT_ID('tempdb..#CountPrime3') IS NOT NULL DROP TABLE dbo.[#CountPrime3]; 
                        CREATE TABLE #CountPrime3 ([Hour] INT,[CountPrime] INT)
                        IF OBJECT_ID('tempdb..#Count-13') IS NOT NULL DROP TABLE dbo.[#Count-13]; 
                        CREATE TABLE [#Count-13] ([Hour] INT,[Count-1] INT)
                        IF OBJECT_ID('tempdb..#Count-23') IS NOT NULL DROP TABLE dbo.[#Count-23]; 
                        CREATE TABLE [#Count-23] ([Hour] INT,[Count-2] INT)
                        IF OBJECT_ID('tempdb..#Count-33') IS NOT NULL DROP TABLE dbo.[#Count-33]; 
                        CREATE TABLE [#Count-33] ([Hour] INT,[Count-3] INT)
                        INSERT INTO #CountPrime3([Hour], [CountPrime]) SELECT [Hour],[Count] FROM [Connector].dbo.[Count_TS] (@SDate,@EDate)
                        INSERT INTO [#Count-13]([Hour], [Count-1]) 
                            SELECT [Hour],[Count] AS [Count-1] FROM [Connector].dbo.[Count_TS] (DATEADD(YEAR,-1,@SDate),DATEADD(YEAR,-1,@EDate));
                        INSERT INTO [#Count-23]([Hour], [Count-2]) 
                            SELECT [Hour],[Count] AS [Count-2] FROM [Connector].dbo.[Count_TS] (DATEADD(YEAR,-2,@SDate),DATEADD(YEAR,-2,@EDate));
                        INSERT INTO [#Count-33]([Hour], [Count-3]) 
                            SELECT [Hour],[Count] AS [Count-3] FROM [Connector].dbo.[Count_TS] (DATEADD(YEAR,-3,@SDate),DATEADD(YEAR,-3,@EDate));
                            SELECT  CCP.[Hour]
                                    ,CCP.[CountPrime]
                                    ,SC1.[Count-1]
                                    ,SC2.[Count-2]
                                    ,SC3.[Count-3]
                            FROM    #CountPrime3    AS CCP
                            INNER JOIN [#Count-13] AS SC1 ON CCP.[Hour] = SC1.[Hour]
                            INNER JOIN [#Count-23] AS SC2 ON CCP.[Hour] = SC2.[Hour]
                            INNER JOIN [#Count-33] AS SC3 ON CCP.[Hour] = SC3.[Hour]
                    END
    END
    ELSE
    PRINT 'Sorry, at maximum you can only search a 3 month time span.'

Se esta é a maneira de fazer o projeto, como posso expor as tabelas dentro do procedimento armazenado ao SSRS? Tenho que me atrapalhar na conversão para outro TVF? Pesquisei SET'ing FMTONLY e sp_describe_first_result_set, mas essas não pareciam soluções viáveis. Devo acumular todas as entranhas do procedimento armazenado no primeiro TVF? ,posso manter o procedimento armazenado e usar CTEs no lugar de tabelas temporárias?

É para isso que serve o SSAS?

Abaixo está um procedimento armazenado para contar o número de widgets feitos em um mês. Se nenhum widget for feito, nenhum registro existirá.

O plano de execução mostra uma INNER JOINdas tabelas M e A, onde na instrução estou fazendo um LEFT OUTER JOIN.

Eu gostaria de contar o número de widgets feitos em um período de tempo, juntar isso a uma tabela mensal (janeiro a dezembro) e ter os resultados mostrados em um relatório do SSRS. A junção é porque não consigo COUNTdados que não estão lá. Atualmente estou recebendo:

MonthName   Widget Count
February    2
March       3
April       4
May         6
June        5
July        4 
August      6
September   2
October     4
November    1
December    2

Gostaria de incluir na lista os meses em que não foram feitos widgets.

Aqui está o código:

DECLARE @OName varchar(50)
DECLARE @Start_Date DATE 
DECLARE @End_Date DATE

SET @OName = 'John'
SET @Start_Date = '01/01/2012'
SET @End_Date   = '12/31/2012'

SELECT   M.[MonthName]
    ,COUNT(A.[Widget_ID]) AS 'Widget Count'
FROM [Connector].dbo.[Months] AS M 
        LEFT OUTER JOIN  [SERVER].[DATABASE].[dbo].[Widget] AS A
        ON MONTH(A.[Widget_Date]) = M.[MonthID]  
WHERE     (A.[Operator_Name] LIKE '%'+ @OName +'%')
          AND A.[PlantID] = '00000001'
          AND (
               (A.Widget_Date >= @Start_Date AND @End_Date IS NULL)
            OR (A.Widget_Datet <= @End_Date AND @Start_Date IS NULL)
            OR (A.Widget_Date >= @Start_Date AND A.Widget_Date <= @End_Date)
            OR (@Start_Date IS NULL AND @End_Date IS NULL)


GROUP BY  M.[MonthName], M.MonthID 
ORDER BY  M.[MonthID]

Abaixo está o plano de execução para esta consulta.

::UPDATE1:: Acabei de inserir os nomes dos meses nesta tabela. Não consigo alterar a tabela remota.

CREATE TABLE [dbo].[Months](
    [MonthID] [smallint] IDENTITY(1,1) NOT NULL,
    [MonthName] [varchar](25) NOT NULL,
 CONSTRAINT [PK_Months] PRIMARY KEY CLUSTERED ([MonthID] ASC)
)
GO
INSERT INTO [dbo].[Months]
           ([MonthName])
     VALUES
           (<MonthName, varchar(25),>)
GO

A tabela "widget" tem mais de 250 campos, o que vai demorar muito para anonimizar.

Estou obtendo resultados diferentes entre meu procedimento armazenado e meu UDF.

O problema de ambos é que ele consulta um servidor vinculado remotamente, atola esses resultados em uma tabela temporária/variável de tabela e, em seguida, substitui os NULLs por '00:00:00' para que eu possa somar os valores. Em seguida, pega essa data e faz um DATEDIFFcálculo nela para me dar o total de horas trabalhadas.

Se eu inserir os valores ('John Doe', '04/05/2013', '04/16/2013'), o procedimento armazenado está retornando a resposta que estou procurando, o SVF não. Onde o procedimento armazenado resultaria em "23" para as horas trabalhadas, o SVF retornará "1900-01-24 00:00:00".

Percebo que é com o que estou returningno SVF, mas não consigo entender. Eu tentei return TIME(0), VARCHAR(5). É como se o SVF não estivesse fazendo a parte DATEDIFF.

Estou trabalhando com SQL Server 2012 (11.0.3000)

Abaixo está o SVF e o procedimento armazenado.

o SVF

ALTER FUNCTION [dbo].[fnFindHoursWorked] 
    (@Name varchar(50)
     ,@Start_Date DATE = NULL
     ,@End_Date DATE = NULL)
RETURNS DATETIME
AS
BEGIN
    -- Declare the return variable here
    DECLARE @HoursWorked DATETIME

    -- Add the T-SQL statements to compute the return value here
    /** create a table variable to store the results of the query **/
    DECLARE @Duty TABLE (Time_on_Duty TIME(0))

    INSERT INTO @Duty(Time_on_Duty)

    /** The actual query that grabs the data from the  server **/
    SELECT   
        CAST(UL.[Date_Time] - LAG(UL.[Date_Time], 1) OVER (PARTITION BY CAST(UL.[Date_TIME] AS DATE) ORDER BY UL.[Date_TIME]) AS TIME(0)) AS 'Time On Duty'
    FROM [LinkedServer].[database].dbo.[tablename] AS UL
    WHERE UL.[Department] = 'Department'
      AND ((UL.[Action] = 'OnDuty' OR UL.[Action] = 'Login') OR UL.[Action] = 'OffDuty')
      AND (UL.[Name] = @Name)
      AND ((CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND @End_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) <= @End_Date AND @Start_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND CAST(UL.[Date_TIME] AS DATE) <= @End_Date)
            )

    /** Setting all of the NULLS in the table variable to a value on which we can do math **/
    UPDATE @Duty 
    SET Time_on_Duty = '00:00:00'
    WHERE Time_on_Duty IS NULL

    /** The select statement to grab the total hours worked for the date range **/
    SET @HoursWorked = (SELECT DATEDIFF(hour,'1900-01-01 00:00:00',CAST(SUM(CAST(CAST(Time_on_Duty AS DATETIME) AS FLOAT)) AS DATETIME))  AS 'Time_On_Duty' FROM @Duty)

    -- Return the result of the function
    RETURN @HoursWorked
END

Aqui está o procedimento armazenado:

ALTER PROCEDURE [dbo].[rptTotal_Time_On_duty] 
    @Name varchar(50)
    ,@Start_Date DATE = NULL
    ,@End_Date DATE = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF OBJECT_ID (N'tempdb..#Duty', N'U') IS NOT NULL
       TRUNCATE TABLE #Duty
    ELSE
       CREATE TABLE #Duty (Time_on_Duty TIME)

    INSERT INTO #Duty(Time_on_Duty)
        SELECT   
            CAST(UL.[Date_Time] - LAG(UL.[Date_Time],1) OVER (PARTITION BY CAST(UL.[Date_TIME] AS DATE) ORDER BY UL.[Date_TIME]) AS TIME) AS 'Time On Duty'
        FROM [LinkedServer].[database].dbo.[tablename] AS UL
        WHERE UL.[Department] = 'Department'
          AND ((UL.[Action] = 'OnDuty' OR UL.[Action] = 'Login') OR UL.[Action] = 'OffDuty')
          AND (UL.[Name] = @Name)
          AND (
           (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND @End_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) <= @End_Date AND @Start_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND CAST(UL.[Date_TIME] AS DATE) <= @End_Date)
        )

    UPDATE #Duty 
    SET Time_on_Duty = '00:00:00'
    WHERE Time_on_Duty IS NULL

    SELECT DATEDIFF(hour,'1900-01-01 00:00:00',CAST(SUM(CAST(CAST(Time_on_Duty AS DATETIME) AS FLOAT)) AS DATETIME))  AS 'Time_On_Duty'
    FROM #Duty

END

Desejo criar um agendamento de agente SQL que ocorra durante um intervalo de datas. Não posso usar "recorre a cada n semanas" porque o processo que será acionado mudará a cada mês.
Eu preciso definir a frequência para ser semelhante a occurs monthly, 1st Monday through the 2nd Friday of every 1 month. Dessa forma, tenho a certeza de executar o processo apenas a partir da primeira semana completa de cada mês

Eu sou louco por pensar em uma programação como essa? Shirley(sic) Não posso ser a única. Procurei em sp_add_schedule por dicas, mas nenhuma foi encontrada lá. Eu tentei pesquisar no Google, mas não consigo encontrar a sintaxe de consulta correta , portanto, continuo vazio.

Talvez se alguém pudesse me ajudar a fazer a pergunta correta, isso poderia me levar na direção certa.

::Update1: Vou adicionar lógica de data ao script.

Em meu escritório, temos uma consulta que é bem feia, mas roda muito bem em produção e em ambiente de desenvolvimento (20seg e 4seg respectivamente). No entanto, em nosso ambiente de teste, leva mais de 4 horas. SQL2005(+latest patches) está rodando em produção e desenvolvimento. SQL2008R2 está sendo executado em teste.

Dei uma olhada no plano de consulta e mostra que o SQL2008R2 está usando o TempDB, por meio de um Table Spool (lazy spool) para armazenar as linhas retornadas do servidor vinculado. A próxima etapa é mostrar Nested Loops (esquerda anti-semi join) consumindo 96,3% da consulta. A linha entre as duas operadoras está em 5.398MB!

O plano de consulta para o SQL 2005 mostra nenhum uso de tempdb e nenhum uso de Left Anti Semi Join.

Abaixo está o código higienizado e os planos de execução do plano 2005 na parte superior, o 2008R2 na parte inferior.

O que está causando a drástica desaceleração e mudança? Eu esperava ver um plano de execução diferente, então isso não me incomoda. A dramática desaceleração no tempo de consulta é o que me incomoda.

Tenho que olhar para o hardware subjacente, já que a versão 2008R2 está usando tempdb, tenho que dar uma olhada em como otimizar o uso disso?

Existe uma maneira melhor de escrever a consulta?

Obrigado pela ajuda.

    INSERT INTO Table1_GroupLock (iGroupID, dLockedDate)
SELECT 
 Table1.iGroupID,
 GETDATE()
FROM Table1
WHERE 
 NOT EXISTS (
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE 
   (
    Alias2.FirstName + Alias2.LastName = dbo.fnRemoveNonLetter(Table1.FullName)
    AND NOT dbo.fnRemoveNonLetter(Table1.FullName) IS NULL
    AND NOT Alias2.FirstName IS NULL 
    AND NOT Alias2.LastName  IS NULL
   ) OR (
    Alias2.FamilyName = dbo.fnRemoveNonLetter(Table1.FamilyName)
    AND Alias2.Child1Name = dbo.fnRemoveNonLetter(Table1.Child1Name)
    AND NOT dbo.fnRemoveNonLetter(Table1.FamilyName) IS NULL
    AND NOT dbo.fnRemoveNonLetter(Table1.Child1Name) IS NULL
    AND NOT Alias2.Familyname IS NULL
    AND NOT Alias2.Child1Name IS NULL
   ) OR (
    Alias2.StepFamilyName = dbo.fnRemoveNonLetter(Table1.StepFamilyName)
    AND Alias2.StepFamilyNameChild1 = dbo.fnRemoveNonLetter(Table1.StepFamilyNameChild2)
    AND NOT Alias2.StepFamilyName IS NULL
    AND NOT Alias2.StepFamilyNameChild1 IS NULL
    AND NOT dbo.fnRemoveNonLetter(Table1.StepFamilyName) IS NULL
    AND NOT dbo.fnRemoveNonLetter(Table1.StepFamilyNameChild2) IS NULL
   )  
 ) AND NOT EXISTS (
  SELECT 1
  FROM Table3
  INNER JOIN Table4
   ON Table4.FirstNameType = Table3.FirstNameType 
  INNER JOIN table5
   ON table5.LastNameType = Table3.LastNameType 
  WHERE 
   Table3.iGroupID = Table1.iGroupID
   AND Table3.bIsClosed = 0
   AND Table4.sNameTypeConstant = 'new_lastname'
   AND table5.sFirstNameConstant = 'new_firstname'
 )

:: EDIT :: Executou a consulta de uma instância SQL2005 diferente, praticamente o mesmo plano de execução do "bom". Ainda não tenho certeza de como as duas versões de 2005 estão funcionando melhor no servidor vinculado 2008R2 do que as instâncias 2008R2 nas instâncias 2008R2.

Embora eu não negue que o código poderia usar algum trabalho, se fosse o código sendo o problema, eu não veria os mesmos planos executivos em todos os meus testes? Independentemente da versão do SQL?

:: EDIT :: Apliquei SP1 e CU3 a ambas as instâncias 2008R2, ainda sem dados. Eu defini especificamente a colocação no servidor vinculado, sem dados. Eu defini especificamente as permissões da minha conta de usuário para ser administrador do sistema em ambas as instâncias, sem risco. Também me lembrei dos internos e da solução de problemas do SQL Server 2008, veremos se consigo rastrear isso de alguma forma.

Obrigado a todos pela ajuda e pelas dicas.

:: EDIT :: Fiz várias alterações de permissão no servidor vinculado. Usei logins SQL, logins de domínio, personifiquei usuários, usei a opção "ser feito usando este contexto de segurança". Eu criei usuários em ambos os lados do servidor vinculado que possuem direitos de administrador de sistema no servidor. Estou sem ideias.

Ainda gostaria de saber por que o SQL2005 está executando a consulta de maneira tão diferente do SQL2008R2. Se fosse a consulta ruim, eu veria o tempo de execução de mais de 4 horas no SQL2005 e no SQL2008R2.

Tenho o requisito de espelhar alguns bancos de dados e também usar criptografia de dados transparente (TDE) neles, pois nossos dados devem ser criptografados enquanto 'em repouso'.

Eu configurei o TDE no principal e no espelho. O problema que tenho surge quando estou configurando o espelhamento dos dois bancos de dados. Como estou usando o TDE, não conheço uma maneira de configurar o espelhamento por meio do gui, então sou forçado a usar o t-sql para fazer o trabalho.

Abaixo está o código que usei no servidor espelhado

--Restore the full backup to the mirrored mdf and ldf
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
RESTORE DATABASE TDE
   FROM disk = '\\SERVERNAME\SQL_Stuff\Backup\TDE_FULL.bak'
      WITH NORECOVERY,
       REPLACE,
       MOVE 'TDE' TO 'E:\TDE.mdf',
      REPLACE,
      MOVE 'TDE_log' TO 'G:\TDE.ldf'
CLOSE MASTER KEY 
GO

--Restore the log backup to the mirrored db
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
RESTORE LOG TDE
    FROM DISK = '\\SERVERNAME\SQL_Stuff\Backup\TDE_LOG.trn'
    WITH NORECOVERY;
CLOSE MASTER KEY
GO


--Drop/Create Mirroring endpoint on mirror
--DROP ENDPOINT TDE
CREATE ENDPOINT TDE
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7025 )
    FOR DATABASE_MIRRORING (
        ROLE = PARTNER
        );
GO

--Check the endpoints for the mirror
USE MASTER
SELECT * FROM sys.database_mirroring_endpoints
GO

--Set the principal on the mirrored db
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
ALTER DATABASE TDE SET PARTNER = 'TCP://PRINCIPAL.DOMAIN.local:7022'
GO
CLOSE MASTER KEY
GO

Abaixo está o código que eu uso no servidor principal.

----------------------Mirroring Section----------------------------------

--Full Backup of Principal
USE TDE
GO
BACKUP DATABASE TDE
TO DISK = '\\SERVERNAME\SQL_Stuff\Backup\TDE_FULL.bak'
    WITH COMPRESSION,
         NAME = 'Full Backup of TDE';
GO

---Log Backup of Principal
USE TDE
GO
BACKUP LOG TDE
TO DISK = '\\SERVERNAME\SQL_Stuff\Backup\TDE_LOG.trn'
    WITH COMPRESSION,
         NAME = 'Log backup of TDE'
GO

--Drop/Create Mirroring endpoint on principal
--DROP ENDPOINT TDE
CREATE ENDPOINT TDE
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (
        ROLE = PARTNER
        );
GO

--Check the endpoints for the princple
USE master
select * from sys.database_mirroring_endpoints
GO

--Set the mirror db on the principal db
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
ALTER DATABASE TDE SET PARTNER = 'TCP://MIRROR.DOMAIN.local:7025'
CLOSE MASTER KEY
GO

Eu configuro o endpoint de espelhamento primeiro, depois o endpoint principal. Em seguida, emito o ALTER DATABASEno espelho e, em seguida, no principal, onde obtenho o erro resultante:

 Msg 1416, Level 16, State 31, Line 2
Database "TDE" is not configured for database mirroring.

Estou sem saber o que fazer sobre isso. O espelho está no estado "restaurando", mas tenho certeza que o erro está falando no db principal.

Obrigado por qualquer ajuda que você pode dar!

Código de atualização para o TDE principal:

--Create Master Key in Master Database
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1Password';
PRINT 'created master key'
go

--Backing up the master key file
USE master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password';
BACKUP MASTER KEY TO FILE = '\\SERVERNAME\TDE_Master_Key.key' ENCRYPTION BY PASSWORD = '1Password';
GO

--Create Server Certificate in the Master Database encrypted with master key (created above) which would be used to create USER database encryption key.
USE Master
CREATE CERTIFICATE Cert_For_TDE WITH SUBJECT = 'Master_Cert_for_TDE', EXPIRY_DATE = '3500-Jan-01';
Go

--Backing up the server cert file
--USE master;
BACKUP CERTIFICATE Cert_For_TDE TO FILE = '\\SERVERNAME\TDE_Cert.cer' 
    WITH PRIVATE KEY ( FILE = '\\SERVERNAME\TDE_Cert_Key.key', ENCRYPTION BY PASSWORD = '1Password');
GO

--Create user database key
USE TDE
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE Cert_For_TDE;
GO

--Enabling Transparent Database Encryption for the USER Database
USE master;
GO
ALTER DATABASE TDE SET ENCRYPTION ON
GO

Código no espelho para TDE:

--restore the backed up key to the mirror
use master
RESTORE MASTER KEY
    FROM FILE = '\\SERVERNAME\TDE_Master_Key.key'
    DECRYPTION BY PASSWORD = '1Password'
    ENCRYPTION BY PASSWORD = '1Password';
GO

--restore the backed up cert to the mirror
USE Master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
CREATE CERTIFICATE Cert_For_TDE    
FROM FILE = '\\SERVERNAME\TDE_Cert.cer' WITH PRIVATE KEY ( FILE = '\\SERVERNAME\TDE_Cert_Key.key', DECRYPTION BY PASSWORD = '1Password');
GO

Update2 sys.database_mirroring_endpoints unido a sys.tcp_endpoints no Principal show:

endpoint_id name    principal_id    state_desc  role_desc   connection_auth_desc    certificate_id  encryption_algorithm_desc   port    ip_address
65545   TDE 261 STARTED PARTNER NEGOTIATE   0   RC4 7022    NULL

sys.database_mirroring_endpoints unido a sys.tcp_endpoints no programa Mirror:

endpoint_id name    principal_id    state_desc  role_desc   connection_auth_desc    certificate_id  encryption_algorithm_desc   port    ip_address
65537   TDE 261 STARTED PARTNER NEGOTIATE   0   RC4 7025    NULL