RateControl's questions

问题：将给定日期范围的数据与最多前 3 年的相同数据进行比较，并在 SSRS 报告中显示表格数据和图表。但是目前，我为 SSRS 报告创建的存储过程不起作用，因为它使用临时表，而这些临时表不暴露给 SSRS。我完全明白为什么，SSRS 对我创建的临时表上的结构一无所知。

例如，我想查看从 01/01/2012 到 04/01/2012 制作的计数小部件，按小时分组。

所以表格的逻辑布局是：

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

因此，对于该数据，我想将其与工厂在相同日期范围内的前一年（最多 3 年）的表现进行比较。看起来很简单，但我想出的解决方案却绝非如此。

我已经解决了，我有一个表值函数，它计算给定 start_date 和 end_dates 所需的数据，并从源表中提取数据。我相信这是正确的方法吗？

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

它走下坡路的地方是我构建存储过程以在 SSRS 报告中显示数据的方式。存储过程不需要解释，我所做的事情相当明显。这不是“正确”的方法吗？

 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.'

如果这是执行项目的方法，我如何将存储过程中的表公开给 SSRS？我必须通过将其转换为另一个 TVF 来摸索吗？我查找了 SET'ing FMTONLY 和 sp_describe_first_result_set，但这些似乎不是可行的解决方案。我应该将存储过程的所有内容汇总到第一个 TVF 中吗？，我可以保留存储过程并使用 CTE 代替临时表吗？

这就是 SSAS 的用途吗？

下面是一个存储过程，用于计算一个月内制作的小部件数量。如果没有制作任何小部件，则不会存在任何记录。

执行计划显示了INNER JOINM 和 A 表中的一个，在语句中我正在执行一个LEFT OUTER JOIN.

我想计算一个时间范围内制作的小部件的数量，将其加入月表（1 月至 12 月），并将结果显示在 SSRS 报告中。加入是因为我无法COUNT获取不存在的数据。目前我得到：

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

我希望在列表中包含没有制作小部件的月份。

这是代码：

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]

下面是这个查询的执行计划。

::UPDATE1:: 我刚刚将月份的名称插入到该表中。我无法更改远程表。

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

“widget”表有超过 250 个字段，这将花费我太多时间来匿名化。

我在我的存储过程和我的 UDF 之间得到不同的结果。

它们两者的要点是，它查询一个远程链接的服务器，将这些结果塞进一个临时表/表变量中，然后用“00:00:00”替换 NULL，以便我可以对金额求和。然后获取该日期，并DATEDIFF对其进行操作以提供总工作时间。

如果我输入值('John Doe', '04/05/2013', '04/16/2013')，存储过程将返回我正在寻找的答案，而 SVF 则不是。如果存储过程的工作小时数为“23”，则 SVF 返回“1900-01-24 00:00:00”。

我意识到这与我returning在 SVF 中的身份有关，但我无法弄清楚。我试过return TIME(0)，VARCHAR(5)。好像 SVF 没有做这件事DATEDIFF。

我正在使用 SQL Server 2012 (11.0.3000)

下面是 SVF 和存储过程。

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

这是存储过程：

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

我想创建一个在日期范围内发生的 SQL 代理计划。我不能使用“每 n 周重复一次”，因为它会触发的过程每个月都会改变。
我需要将频率设置为类似于occurs monthly, 1st Monday through the 2nd Friday of every 1 month. 这样我就可以保证只在每个月的第一周开始运行这个过程

我为这样的日程安排而疯狂吗？雪莉（原文如此）我不能是唯一的。我查看了sp_add_schedule是否有任何提示，但在那里找不到任何提示。我试过谷歌搜索，但我想不出正确的查询语法，因此一直空着。

也许如果有人可以帮助我提出正确的问题，那可能会引导我走向正确的方向。

::Update1：​​我将在脚本中添加日期逻辑。

在我的办公室，我们有一个非常丑陋的查询，但在生产环境和开发环境中运行良好（分别为 20 秒和 4 秒）。然而，在我们的测试环境中，它需要超过 4 小时。SQL2005（+最新补丁）正在生产和开发中运行。SQL2008R2 正在测试中运行。

我查看了查询计划，它显示 SQL2008R2 正在使用 TempDB，通过表假脱机（惰性假脱机）来存储从链接服务器返回的行。下一步显示嵌套循环（左反半连接）占用了 96.3% 的查询。两个运营商之间的界线是 5,398MB！

SQL 2005 的查询计划显示没有使用 tempdb，也没有使用 Left Anti Semi Join。

下面是经过清理的代码和执行计划，顶部是 2005 计划，底部是 2008R2。

是什么导致了急剧的减速和变化？我期待看到不同的执行计划，所以这不会打扰我。查询时间的急剧下降让我很困扰。

我是否必须查看底层硬件，因为 2008R2 版本使用的是 tempdb 我必须看看如何优化它的使用？

有没有更好的方法来编写查询？

谢谢您的帮助。

    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'
 )

:: 编辑 :: 从不同的 SQL2005 实例执行查询，与“好”的执行计划几乎相同。仍然不确定两个 2005 版本对 2008R2 链接服务器的运行效果如何优于 2008R2 实例对 2008R2 实例的运行效果。

虽然我不否认代码可以使用一些工作，但如果代码是问题所在，我不会在我的所有试验中看到相同的执行计划吗？不管SQL版本如何？

:: 编辑 :: 我已将 SP1 和 CU3 应用于两个 2008R2 实例，但仍然没有骰子。我在链接服务器中专门设置了搭配，没有骰子。我已将我的用户帐户的权限专门设置为两个实例上的系统管理员，没有骰子。我还记得我的 sql server 2008 内部结构和故障排除，我们将看看我是否能找到一些方法。

感谢大家的帮助和提示。

:: 编辑 :: 我对链接服务器进行了各种权限更改。我使用过 SQL 登录、域登录、模拟用户、使用过“使用此安全上下文”选项。我在链接服务器的两端都创建了在服务器上具有系统管理员权限的用户。我没主意了。

我仍然想知道为什么 SQL2005 执行的查询与 SQL2008R2 截然不同。如果是查询不好，我会在 SQL2005 和 SQL2008R2 上看到 4 小时以上的运行时间。

我需要镜像一些数据库并在它们上使用透明数据加密(TDE)，因为我们的数据必须在“静止”时加密。

我在主体和镜像上都设置了 TDE。当我设置两个数据库的镜像时，我遇到的问题就出现了。由于我使用的是 TDE，我不知道通过 gui 设置镜像的方法，所以我不得不使用 t-sql 来完成工作。

下面是我在镜像服务器上使用的代码

--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

下面是我在主体服务器上使用的代码。

----------------------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

我首先设置了镜像端点，然后是主体端点。然后我在镜像上发出ALTER DATABASE，然后在主体上发出，我得到错误：

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

我不知道该怎么办。镜像处于“正在恢复”状态，但我确定该错误与主体数据库有关。

谢谢你提供的所有帮助！

主要 TDE 的更新 代码：

--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

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 与 sys.tcp_endpoints 在 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 与 sys.tcp_endpoints 在镜像显示中加入：

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