Red构建错误:数据库中没有FILESTREAM文件组

Question

SQLCI支持FILESTREAM吗？

当我试图在Synchronization of 'Scripts.state' and ... failed: Default FILESTREAM filegroup is not available in database数据库中运行Red 生成步骤(其中包括FILESTREAM列)时，我得到了错误。

我的真实来源是由Git支持的SQL源代码管理。由于其他错误，我使用SqlServer 2012实例而不是LocalDB进行验证。此服务器上还有其他具有FILESTREAM内容的数据库，因此我不认为问题是由于功能限制造成的。

以前有人见过这个问题吗？

全错误列表

(匿名*)

    [Step 1/2] Disabled build step database (Red Gate (SQL Server)) is skipped
    Step 2/2: Red Gate SQL CI Build (Red Gate SQL CI Build) (3m:07s)
    [Step 2/2] Starting: C:\BuildAgent\plugins\sqlci-teamcity-agent\assets\sqlCI.exe build /temporaryDatabaseUserName=******** /temporaryDatabaseServer=*********** /scriptsFolder=SqlSourceControl /packageId=*********** /packageVersion=423
    [Step 2/2] in directory: C:\BuildAgent\work\fbc3771fbc95579a
    [Step 2/2] sqlCI.exe -- Red Gate's SQL Continuous Integration v2.1.1.1028
    [Step 2/2] 
    [Step 2/2] Creating scratch database sqlCI_ac1a4566-1268-47ee-a255-a0c7e751511c (1s)
    [Creating scratch database sqlCI_ac1a4566-1268-47ee-a255-a0c7e751511c] STARTING: Creating scratch database sqlCI_ac1a4566-1268-47ee-a255-a0c7e751511c
    [Creating scratch database sqlCI_ac1a4566-1268-47ee-a255-a0c7e751511c] COMPLETED SUCCESSFULLY: Creating scratch database sqlCI_ac1a4566-1268-47ee-a255-a0c7e751511c
    [Step 2/2] Validating database state (2m:46s)
    [Validating database state] STARTING: Validating database state
    [Validating database state] Starting: C:\BuildAgent\plugins\sqlci-teamcity-agent\assets\SC\SQLCompare.exe /include="Identical" /include="StaticData" /scripts1="C:\BuildAgent\temp\buildTmp\tbudqiij.eej\db\state" /server2="**.**.**.**" /database2="sqlCI_ac1a4566-1268-47ee-a255-a0c7e751511c" /username2="******" /password2=****** /synchronize /options="DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,UseMigrationsV2"
    [Validating database state] SQL Compare: in trial, expires 2015/06/18 15:03:41 +01:00
    [Validating database state] Automation License: in trial, expires 2015/06/18 15:03:41 +01:00
    [Validating database state] SQL Compare Command Line V11.2.0.22
    [Validating database state] ==============================================================================================================================================================
    [Validating database state] Copyright Copyright ¸ 1999 - 2015 Red Gate Software Ltd
    [Validating database state] 
    [Validating database state] Registering data sources
    [Validating database state] Creating mappings
    [Validating database state] Comparing
    [Validating database state] 
    [Validating database state] Applying Command Line Items
    [Validating database state] 
    [Validating database state] Checking for identical databases
    [Validating database state] Creating SQL
    [Validating database state] Inserting static data SQL into deployment script
    [Validating database state] Deploying changes (from DB1 to DB2)
    [Validating database state] Error: Synchronization of 'Scripts.state' and '172.16.1.202.sqlCI_ac1a4566-1268-47ee-a255-a0c7e751511c' failed: Default FILESTREAM filegroup is not available in
    [Validating database state] database 'sqlCI_ac1a4566-1268-47ee-a255-a0c7e751511c'. Error executing the following SQL: CREATE TABLE [dbo].[tbl_************] ( [*******ID] [int] NOT NULL
    [Validating database state] IDENTITY(1, 1) NOT FOR REPLICATION, [******Name] [nvarchar] (250) COLLATE Latin1_General_CI_AS NULL, [*******Type] [nvarchar]...
    [Validating database state] ##teamcity[buildStatus status='FAILURE' text='Validating database state failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare']
    [Validating database state] FINISHED WITH ERROR: Validating database state
    [Step 2/2] ##teamcity[buildStatus status='FAILURE' text='Running SQLCompare failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare']
    [Step 2/2] Process exited with code 32
    [Step 2/2] Step Red Gate SQL CI Build (Red Gate SQL CI Build) failed

Investigations

通过运行SQL跟踪，在发生故障后数据库被自动删除之前，我成功地拔出了网络电缆。

基于@DevOps，回答我能够确定SQLCI没有在数据库中创建FILESTREAM文件组。通过手动创建它，CREATE能够成功。

我还没有设法让SQLCI自动执行这个步骤。

Answer 1

基于这个论坛帖子

• 创建临时数据库
• 在临时数据库中创建FileGroup
• 在TeamCity 的构建步骤中指定“临时数据库名”
  • (Effectivley在/temporaryDatabaseName运行程序中呈现TeamCity )

注意:您可能需要在前面的构建步骤中删除并重新创建数据库--我似乎无法重复使用。

Answer 2

使用Redgate SQL，这将创建一个具有唯一名称的新DB，作为构建过程的一部分。在尝试使用FILESTREAM列创建表之前，数据库似乎没有启用FILESTREAM，即使在服务器上启用了该功能。应该有一些脚本来设置数据库的FILESTREAM，因此，通过一个简单的查询，我们可以验证这一点，前提是它之后没有撕毁数据库

SELECT database_id, type_desc, name, physical_name
FROM sys.master_files WHERE database_id = DB_ID('StreamTestDb')

或

SELECT 1 from sys.database_files where name = 'LogicalFileName'

您是使用SQL源代码管理来编写数据库或SSDT的脚本吗？我也有一个包含Regadate的完整CI管道，所以我将看看是否可以复制这个问题，但听起来它似乎不是在编写FILESTREAM的设置。

例如：

ALTER DATABASE StreamTestDb
ADD FILEGROUP [StreamTestFileStreamGroup1]
CONTAINS FILESTREAM
GO
ALTER DATABASE StreamTestDb
ADD FILE
(
    NAME = N'StreamTestFileStreamGroup1File',
    FILENAME = N'D:\PATH\FSDATA'
)
TO FILEGROUP [StreamTestFileStreamGroup1]
GO