Database Deployment
Home / Developer /Database Deployment/ Summary
Database PxStat Database Tier.
When the database is to be deployed into production, the changes will be delivered via a script. It is essential that the changes to the test environment be delivered via the same script. This means that all testing will result in the upgrade script being tested as well as the changes themselves.
- Create a script for the version and keep it as a living source-controlled document for the duration of the script.
- Any changes to the development database structure must be delivered via this script
- If you are delivering a change based on an issue, mark off the section of the document that responds to this issue.
- If the issue requires a large number of changes or changes over a period of time, you may break the issue into a number of described sub-sections
- Run the individual change into the development database by selecting the query and executing it.
- Always ensure that the sprint database script is checked into source control after use.
General Recommendations for changes to non table entities (e.g. stored procedures, views, user defined types).
- Non table entities can be deleted and recreated (and altered where possible) without affecting the data structure.
- Where possible, ensure that the script is repeatable. This may mean using "Create or Update" where possible, otherwise by using an IF statement that depends on the existence of the condition we are trying to change.
- Use a separate file for each entity and ensure that all files are subject to source control.
- When deploying to production, run all scripts for non table entities irrespective of whether or not changes have been made to them.
Where possible, the database for an existing installation should be created using the /db/update.ps1 powershell script. The script will automatically deploy all of the entities, along with the update script for the sprint.
This could be necessary for situations such as a deployment into a Cloud environment where it may not be possible to access the database from powershell.
Where possible, a database for a new site should be created using the /db/install.ps1 powershell script.
This could be necessary for situations such as a deployment into a Cloud environment where it may not be possible to access the database from powershell.
Data Tier Applications is a method of managing SQL Server database projects. Databases can be entirely designed within Visual Studio and we can create DACPAC files which reflect the design. The design can then be implemented either by deploying the DACPAC to the database or by running it in SSMS.
- A DACPAC can be created for each version and it is then possible to move from any version to any other version by choosing the target DAPAC.
- If a Database project in Visual Studio is used, the creation of database entity files is implicit in the process.
- Additional changes can be bundled into the DACPAC by including pre-deployment and post-deployment scripts.
- DACPACs may fail if they lead to constraint voilation. This will make it necessary to split the creation of new entities over the entity file itself and the post deployement script.
- Data Tier Applications require access to the Server Admin group. This is not available in serverless Cloud deployments, therefore this approach limits the choices available for hosting.
