Changing the version controlled database - anything to be aware of?

mr_gooding

Hi All,

I'm looking to change our source controlled database from the production environment to a pre production one (on separate database servers). Is it as simple as making sure anything that is under source control (currently just objects, not data) is aligned across the two databases and unlinking from one and linking to the other, or is there anything else to be aware of?

I've ensured all users (windows Authentication and SQL) are present on both to ensure no permission based dependencies cause issues.

This will allow us to develop locally, promote code into the pre-production (source controlled and not a shared database), which is then released to production

Appreciate any tips from anyone who has done this before.

James

AlexYates

In theory you should be using source control in dev, not production. You should be deploying to production from source control, not retrospectively commiting changes to source control after they have been applied in prod. If what you are looking for is an audit log of changes you should look at DLM Dashboard instead.

With regards moving your source control to the pre-prod DB, the simple answer is to use something like SQL Compare to sync yp the schemas and then unlink prod and link pre-prod.

But as I mentioned, you really you should be doing that all the way down to dev and then using source code as the source of truth for all deployments to your test/pre-prod/prod dbs.

mr_gooding

Hi Alex, absolutely - we're looking to do to make sure things are done in the right way, rather than, as you say, retrospectively committing changes.Moving it from prod to pre prod (also Dev as we only have the two environments at present) is the first step towards doing things correctly.

Appreciate you taking the time to respond.

Neosphere

AlexYates wrote: »

In theory you should be using source control in dev, not production. You should be deploying to production from source control, not retrospectively commiting changes to source control after they have been applied in prod. If what you are looking for is an audit log of changes you should look at DLM Dashboard instead.

With regards moving your source control to the pre-prod DB, the simple answer is to use something like SQL Compare to sync yp the schemas and then unlink prod and link pre-prod.

But as I mentioned, you really you should be doing that all the way down to dev and then using source code as the source of truth for all deployments to your test/pre-prod/prod dbs.

Sorry, I have not done this before yet..

marathiunlimited

mr_gooding wrote: »

Hi All,

I'm looking to change our source controlled database from the production environment to a pre production one (on separate database servers). Is it as simple as making sure anything that is under source control (currently just objects, not data) is aligned across the two databases and unlinking from one and linking to the other, or is there anything else to be aware of?

I've ensured all users (windows Authentication and SQL) are present on both to ensure no permission based dependencies cause issues.

This will allow us to develop locally, promote code into the pre-production (source controlled and not a shared database), which is then released to production

Appreciate any tips from anyone who has done this before.

James

Sorry. No idea...

way0utwest

As Alex mentioned, you want to sync things.

I'd ensure nothing from production is shown in the Commit tab. Be sure you have no filters.

Then unlink and you can link the VCS to dev. That's where you should be committing from. For pre-prod, send changes from the VCS in the same manner you'd deploy to prod. This way you practice deployments, which is why you have pre-prod.

If you need to get pre-prod ready, a restore from production (assuming no sensitive data) or a schema compare deployment from prod->pre-prod is a way to get started.

mr_gooding

Thanks all, we are now using development for source control and the change was seamless (we committed all changes and then used a backup to ensure the two were the same).

AlexYates

Great news.

If you had any sensitive data in prod you might want to consider using SQL Data Masker or something similar to ensure this data is not being used in dev. (Especially if you have any PII data regarding EU citizens. #GDPR)

https://www.red-gate.com/products/dba/data-masker/index

(Of course, if you do have such data in dev  I strongly recommend that you don't admit that fact in a public forum.)