Development and shadow databases
Published 10 March 2016
SQL Change Automation maintains two databases:
- Development database
This is the development database that you use for debugging and to edit schema objects (e.g. using SQL Server Management Studio). When you deploy, SQL Change Automation executes your migration scripts against this database to upgrade it. You shouldn't drop the development database from your SQL Server instance.
- Shadow database
This is an exact copy of your database schema created automatically from your project scripts (001.sql, 002.sql, 003.sql, etc). It's created every time you use the SQL Change Automation tool-window to view pending changes or import. The shadow database is used by the SQL Compare engine (that powers SQL Change Automation) as the base from which to generate a new migration script. It is safe to remove the database from your development instance at any time, in fact SQL Change Automation will automatically drop the shadow db whenever you Clean or Rebuild your project.
For details of how the shadow databases is used during the verification stage, see script verification.
Changing the development database connection
The Target Connection string can be set by clicking the Edit button within the Debug tab of the project designer.
This will display the connection dialog, from which you can either browse to the desired server or enter the connection details:
Build your solution to update the connection within the SQL Change Automation tool-window.
Changing the Shadow database connection
By default, the Shadow database will be deployed to the same SQL Server instance that you've chosen for your development database.
If you would prefer not to deploy the Shadow database to this instance, check the following option on the Debug tab: Always use default connection string for Shadow database.
This will cause the Shadow database to be deployed to the connection displayed in the Default Connection String box, typically (localdb)\ProjectsV13.
Build the solution to update the Shadow connection within the SQL Change Automation tool-window.
Setting the Shadow database connection string
You may configure the Shadow database to be created on a specific SQL Server instance by adjusting the default connection string in the project.
Firstly, under the Target Connection String, click Edit to display the connection dialog. Specify the SQL Server instance that you would like to deploy the Shadow database to.
Then, click Set As Default to set this connection as the Default Connection String.
The connection string will then be displayed in the Default Connection String text box.
The Default Connection String is a setting that is included in your .sqlproj file. This means that this setting will be shared with others when you check the project into source control, so the connection details you provide should be valid for everyone in your team.
Click Edit... once again in order to set the Target Connection String back to its previous setting.
Ensure that the following option is checked: Always use default connection string for Shadow database.
Build the solution to update the connection details within the SQL Change Automation tool-window.
When you next Import changes into your project, the Shadow database will be deployed to the specified server. Feel free to drop any shadow databases created by SQL Change Automation on any of your other servers (this can be identified by the _SHADOW database name suffix).
