When I'm publishing a new database that contains multiple filegroups and files, the file groups dont get published on the creation of a DB. When the DB is being upgraded it works, which means I need to call sqlpackager.exe twice to publish a database.
Objective: Create a database with filegroups(shared component) that break up configuration data from sales data.
Implementation: Main database project references a common database project that contains a collection of file groups only. The DB reference has been correctly set up. The publish file being used with the deployment of the dacpac correctly sets the property: IgnoreFileGroupPlacement (FALSE) The main database project has a list of files to add to the file groups. I'm creating a filegroup called "defaultworkingspace" to move everything off primary except system objects, i've marked this filegroup as default. When I deploy for the first time, I get the below error:
Initializing deployment (Start)
*** A project which specifies SQL Server 2008 as the target platform may experience compatibility issues with SQL Server 2005.
*** The following SqlCmd variables are not defined in the target scripts: SourceCode SourceId.
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
Creating Register_DB...
An error occurred while the batch was being executed.
Updating database (Failed)
*** Could not deploy package.
Warning SQL0: A project which specifies SQL Server 2008 as the target platform may experience compatibility issues with SQL Server 2005.
Warning SQL72013: The following SqlCmd variables are not defined in the target scripts: SourceCode SourceId.
Error SQL72014: .Net SqlClient Data Provider: Msg 5014, Level 16, State 2, Line 1 The filegroup 'DefaultWorkingSpace' does not exist in database 'Register_DB'.
Error SQL72045: Script execution error. The executed script:
ALTER DATABASE [$(DatabaseName)]
MODIFY FILEGROUP [DefaultWorkingSpace] DEFAULT;
The setting of the default has been made as part of a post deployment script so it should be running with all post deploy scripts. When I deploy it again, now as an upgrade as the db has been created
Initializing deployment (Start)
*** A project which specifies SQL Server 2008 as the target platform may experience compatibility issues with SQL Server 2005.
*** The following SqlCmd variables are not defined in the target scripts: SourceCode SourceId.
*** The source's object [Register_DB] is different than the target's version of the object but the target object will not be updated.
*** The object [Register_DB] already exists in database with a different definition and will not be altered.
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
Creating [Configuration]...
Creating [DefaultWorkingSpace]...
Creating [Giftcards]...
Creating [Logging]...
Creating [MasterData]...
Creating [Mobile]...
Creating [Sales]...
Creating [Config]...
Creating [Working]...
Creating [Giftcards]...
Creating [Logging]...
Creating [Master]...
Creating [Sales]...
It seems odd that the upgrade process supports the filegroups but the initial creation of the DB does not... Has anyone come accross this issue before?
Known issue with dacpac apparently.
I went with the approach of deploying a db project to master prior and creating the db's if they didn't exist in post scripts for that db. Not the cleanest but it'll do for now.