This repo holds VS Sql Server Database Projects for sql databases (currently just eaze main db).
- Load solution in Visual Studio 2017
- Build solution
- Load a schema compare file, either a .local.scmp custom or the ones provided with repot - you will need to 'show all files' to find the compare files.
- Enter / update target connection information if required
- Run comparison, see results
- Individual differences can be checked and unchecked to include / exclude from update script generation. Note that these selections are saved with the schema compare file, and that the template schema compare files (i.e. ModelToDevelopment) will have specific objects excludeed by design.
- The differences can then be directly pushed or generated into a sqlcmd file, which can be run from VS or used in Sql Management Studio, in whole or in part.
- If some updates fail, additional comparisons can be run as often as required.
- Important: This method does NOT publish seed data or run any pre / post deployment scripts, and the template schema files by default will not include non-application (database or server instance level) objects.
- Get latest from branch (master for Development, production for Production, or your own feature branch for local)
- Load solution
- BUild solution
- Right click on project and select "Publish"
- Fill in target connection information - Use Load Values and update as appropriate for sql cmd variables
There is an
advancedbutton that will open up a lot of options. Uncheck
block incremental deployment if data loss might occurand uncheck
drop objects in target but not in sourceto allow data-lossy schema changes. Also, to run the scripts, one needs to enable "Sql Command Mode (SQLCMD)"
- Can either choose Publish or Generate Script as appropriate - Publish WILL IMMEDIATELY EXECUTE AGAINST THE TARGET DB.
- If the script is selected, it can be run from VS or within Sql Management Studio (Sql Command mode must be enabled)
- Important: This method will run all pre/post deployment scripts, including seed data alignment.
All new tables should have basic change tracking enabled in their table definition, and change tracking will need to be manually set on the replica. Instructions can be found here: https://sites.google.com/delyv.com/eaze/teams/services/sql-server
Additional Information about MSSQL DB
Getting a backup from development to use locally
- Run the following stored procedure on dev sql server (repalce filename, and db name if required):
exec msdb.dbo.rds_backup_database @source_db_name='eaze-qa', @s3_arn_to_backup_to='arn:aws:s3:::eaze-rds-sql-backup/dev/<filename>.bak'
- Will get an immediate return, can verify status of the job as it runs via:
exec msdb.dbo.rds_task_status @db_name='eaze-qa'
- Once task is complete, the completed backup file can be downloaded via our S3 bucket
eaze-rds-sql-backup. This is not public so you'll need to go into thw aws console for the east region.