I'm migrating over to SSDT for schema control of the DB structure for a large project with accompanying database. This project already has hundreds of integration tests inside of it that are in various states of brittleness. Many of these use what I would call "known data" to perform the integration tests with. The tests are relying on this data to be there for them to run successfully.
In the past, we've done a 1:1 copy of our integration test database for testing in feature branches, but i'm not wild about this process as it adds bloat to the server.
Using SSDT, is there a way to incorporate this "known data" into either a SSDT project, or a process that can be used to copy after the fact? I already have post-deploy scripts that load seed data into the system (types and the like), so I could put all known data there, but this could be dozens of extra sql files to potentially manage so i'm not wild about it.
Due to the complexities and the amount of known data, using the application sitting on top of the DB to insert it is not practical.
I was thinking about exploring a bacpac option, but before doing that wanted to see if there were any other ways people have managed integration testing of feature branches with known data in the past.
There are a couple of approaches you could take:
1. Use backups/snapshots
You could get a database in a known state and then use backups or snapshots. I am not a great fan of this as you always need to keep your base up to date or every now and then refresh it.
2. Integration tests / test data in a separate .sqlproj / dacpac
This is what I would usually do for unit tests (integration tests, for me, are normally in the language of the app that calls the database).
I would have a separate .sqlproj in my solution that has a "Same Database" reference to the main project. Put into this the integration tests and the Post-Deploy.sql for the test seed data.
If you have multiple files use ":r" imports to include those and also use an ":r" import to include the post-deploy from the main project as when the publish happens, dacpacs that are referenced do not have their pre/post-deploy scripts executed.
If you haven't used references like this before you deploy the test project and /p:IncludeCompositeObject=True
3. Setup data as part of your tests
This might be harder if you already have a large suite but I would normally advise that as part of the setup for each test that you get the data in a state that you know it is right for that test.
This way it stops any brittleness from running tests in different order etc.
I haven't looked at bacpac's for this, that might be an interesting way to deal with it as well.