Currently there is a post-deployment for the source database, to allow for customizations on the source before it compares it to the target. In our case, I'm trying to create additional filegroups + files, which is achievable in the post-deployment except my source files are targeting specific tables/indexes to be created on these file groups which attempt to get created before the post-deployment runs (so it fails indicating the filegroup is not valid).
Is it possible to have a pre-deployment option on the source database added?
Is there another way to handle multiple file groups that is more elegant as well that I may be missing?
OK I think I'm nearly with you now. Packager Plus created the package OK but when you actually run the package it fails because the empty database it initially creates for population does not contain the file groups. Was the package build from a set of scripts or was it build from an existing database? It might make a difference. I'll do a bit of testing to see what is wrong, it would be nice if the package automatically created the database with the file topology from the original build. I'm surprised it doesn't.
But if this is not working there may be ways to get round it depending on how much control or automation you have over the deployment.
(1) Have you wider deployment process run a an OSQL command that creates an empty database with the file groups you need. Then run the package in commandline mode with /NoGUI /VesselDatabase:emptydb /server:yourserver /database:yourtarget /mode:upgrade or /NoGUI /VesselDatabase:emptydb /server:yourserver /mode:createnew
The package will simply populate the existing database (the empty vessel) you have prepared for it and then optionally upgarde the target.
(2) Or perhaps instead of using an osql command. use two packages. A stage one package that packages an empty database but uses a post build custom script to create the file groups. Then as above use that database as a VesselDatabase for the main package.
(3) Or perhaps the most straight forward; use Change Manager to create a build script from your source scripts and carefully edit the beginnig of the script to create the filegroups. Then use Packager Plus to create the package from the build script instead of from your source scripts. This way the package just runs the build script you gave it including the early statements that create the filegroups. If you choose this route I'll have to give you more detail on how to edit a build script for consumption by Packager Plus. Its easy but there is a trick to it.
I'll do some testing tomorrow and get back to you.
quote:Originally posted by dbghost We will also be making this an automatic feature asap.
Could you please provide some public documentation for this feature? I've been experimenting with the /vesseldatabase switch, but being able to create a package from a build script would save me an awful lot of trouble.
These pre population scripts are now implemented. You will need to code these alter statements carefully.....
1/ use db_name() instead of hardcoding a name. 2/ check the file group does not already exist. 3/ Similarly with the extra files.
e.g. DECLARE @dbName sysname SET @dbName = db_name() DECLARE @alterStmt NVARCHAR(4000) SET @alterStmt = 'IF NOT EXISTS(SELECT * FROM sys.filegroups WHERE NAME='SECONDGROUP') ALTER DATABASE [' + @dbName + '] ADD FILEGROUP [SECONDGROUP]' EXEC (@alterStmt)