sql-databases

This repo holds VS Sql Server Database Projects for sql databases (currently just eaze main db).

Usage

Schema Comparison

1. Load solution in Visual Studio 2017
2. Build solution
3. 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.
4. Enter / update target connection information if required
5. Run comparison, see results
6. 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.
7. 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.
8. If some updates fail, additional comparisons can be run as often as required.
9. 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.

Publish

1. Get latest from branch (master for Development, production for Production, or your own feature branch for local)
2. Load solution
3. BUild solution
4. Right click on project and select "Publish"
5. Fill in target connection information - Use Load Values and update as appropriate for sql cmd variables There is an advanced button that will open up a lot of options. Uncheck block incremental deployment if data loss might occur and uncheck drop objects in target but not in source to allow data-lossy schema changes. Also, to run the scripts, one needs to enable "Sql Command Mode (SQLCMD)"
6. Can either choose Publish or Generate Script as appropriate - Publish WILL IMMEDIATELY EXECUTE AGAINST THE TARGET DB.
7. If the script is selected, it can be run from VS or within Sql Management Studio (Sql Command mode must be enabled)
8. Important: This method will run all pre/post deployment scripts, including seed data alignment.

Change Tracking

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

1. 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'

2. 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'

3. 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.