Verifying projects
Published 31 July 2019
SQL Change Automation verifies migrations by running them against the shadow database. This identifies problems before deployment occurs. For example:
Missing dependencies
A user that does not have a related login
A stored procedure that depends on a full-text index being present
A view that is missing a dependent view
- Unsupported objects due to dependency chains
Invalid code in legacy objects
Stored procedures that still use old-style outer joins
Syntax errors (only possible if the script was manually edited)
Fixing verification errors
Verification produces a list of any errors and warnings that have been found during the verification process. If the script contains multiple errors, verification can report only the first issue even if multiple are present. You can edit the script manually to fix errors.
If you continue to receive errors, in spite of having fixed the problems in the affected scripts, try deleting the shadow database and verifying the project again.
In some situations, it can be desirable to exclude certain objects or T-SQL batches from the verification process. See excluding objects from script verification.
The difference between verifying a project locally and building it in continuous integration
Verify is doing exactly the same checks as Invoke-DatabaseBuild. It is parsing the syntax of all the migrations and programmable objects and then running a deploy against the shadow database.
The only difference is that as a performance optimisation the shadow database is usually not dropped in advance so the deployment check is performed on new scripts rather than all scripts.
The shadow database will be dropped and a full deployment run if the existing state of the shadow database is considered to no longer match the project. This is typically if a migration is manually edited or deleted or a programmable object is manually deleted.
This logic works in the vast majority of cases and should always work if you only import objects to the project via generating migrations. There are unfortunately a few scenarios where the shadow database invalidation logic doesn't work and the shadow database is not dropped when it ought to be.
This is solved by dropping the shadow database before verify (available via rebuild in Visual Studio).
These are the known scenarios where the shadow database needs to be rebuilt explicitly:
- Changing the value of a SQLCMD variable
- Adding/removing/changing Pre-Deployment and Post-Deployment scripts
- Adding a migration script that refers to a programmable object
