Best deployment strategy for my environment

lgrieser

Hello,

My team is currently preparing to deploy SQL Source Control in our environment, and manufacturing and B2B sales company. We currently have two SQL Servers, production and test, with production creating nightly database level backups that are restored to test every morning. The test server is both a development environment and a "training" server that is used when we onboard new employees company wide, and while this isn't ideal, it probably won't be changing any time soon. 

I'm testing SQL Source Control using the working folder in shared database mode. My main issue is after I commit a change, the next morning the database restores and SQL Source Control is asking me to commit changes again that undo the change I committed the day before. I realize this is an issue with my environment and not SQL Source Control itself, but I'm wondering if anyone else here has deployed in a similar scenario and could give some input on what they did to work around this issue. 

lgrieser

@David Atkinson That's perfect, thank you so much!

SamTouray

You could create a database on your local PC and point Redgate SQL Source Control to it, and also change your development model to dedicated, then you won't be affected by the nightly restore.

Although, this does mean that you won't automatically have a fresh copy of Production data on your local PC every day, although this may not be desirable anyway if your production database contains customer data, think data protection/GDPR etc.

lgrieser

@SamTouray Thanks for your response, we've talked about doing this, but yeah it would contain customer data and we'd like to avoid that if possible. 

David Atkinson

I presume that the reason the tool is suggesting undoing your changes is that it simply thinks that you've undone the changes as the database has reverted to a previous state.

The solution would be to adapt your nightly restore process to run a sync from the latest revision in VCS to your newly restored development database. 

You can do this using a combination of your VCS command line and sqlcompare.exe.

- Use the VCS command line to check out the latest revision from VCS.
- run Sqlcompare.exe using /scripts1: to specify the database scripts folder and /database2: to specify the development database, and /sync to push the changes.

The above solution works if you haven't got any uncommitted changes in your dev environment. If you need to preserve these uncommitted changes, there is a similar process you can use:

1) Use sqlcompare.exe to create a schema snapshot
2) Restore your production backup to dev
3) Use sqlcompare.exe to sync the changes from the schema snapshot to dev
4) It is now safe to delete the snapshot.

More information on how to use the sqlcompare.exe command line here.