Red Gate SQL CI Build Error: FILESTREAM filegroup is not available in database

Question

TL;DR Does SQLCI support FILESTREAM?

I get the error Synchronization of 'Scripts.state' and ... failed: Default FILESTREAM filegroup is not available in database when trying to run a Red Gate SQL CI Build step in TeamCity against my database which includes FILESTREAM columns.

My source of truth is SQL Source Control backed by Git. I am using a SqlServer 2012 instance rather than LocalDB for validation due to other errors. There are other databases on this server that have FILESTREAM content, so I don't believe the issue to be due to feature limitations.

Has anyone come across this issue before?

Full error listing

(Anonymized with *)

    [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

By running a SQL trace, I managed to pull the network cable just before the database was auto-deleted after the failure.

Based on @DevOps answer I was able to determine that SQLCI was not creating the FILESTREAM filegroup in the database. By manually creating it, the CREATE TABLE was able to succeed.

I have not yet managed to get SQLCI to perform this step automatically.

Matt · Answer 1 · 2015-05-22T19:51:36.853

With Redgate SQL CI this is creating a new DB with a unique name as part of the build process. It sounds like the database is not FILESTREAM enabled before trying to create a table with a FILESTREAM column, even though the feature is enabled on the server. There should be some script to setup the FILESTREAM for the database so with a simple query we could verify this, provided it hasn't ripped down the database afterwards

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

OR

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

Are you using SQL Source Control to script out the database or SSDT? I have a full CI Pipeline with Regadate DLM too so I'll see if I can replicate this issue, but it sounds like it's not scripting out the setup of the FILESTREAM

e.g.

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

Thanks! I'll check this out now. I'm using SQL Source control backed by GIT and I'm hoping to get to a full CI pipeline with Redgate DLM. — Andy Joiner, May 22 '15 at 09:40
By running a trace, I managed to pull the network cable just before the database was auto-deleted after the failure. No results for the first 2 queries, but after running the final query, I could manually run the captured failing CREATE TABLE statement, so I think you are correct that SQLCI is not scripting out the setup of the FILESTREAM. p.s. typo in "FILESTERAM" — Andy Joiner, May 22 '15 at 15:53

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

Based on this forum post

Create a temporary database
Create the FileGroup in the temporary database
Specify "Temporary database name" in the build step in TeamCity
- (Effectivley renders the /temporaryDatabaseName in the TeamCity runner)

Note: You may need do drop and re-create the database in an earlier build step - I don't seem to be able to re-use.

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 21 '15 at 11:27

Andy Joiner

5,932
3
45
72

Red Gate SQL CI Build Error: FILESTREAM filegroup is not available in database

2 Answers2