What are the challenges you face when working across database platforms? Take the survey
Options

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

mr_goodingmr_gooding Posts: 5 Bronze 1
edited March 2, 2018 11:34AM in SQL Source Control
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

Comments

  • Options
    AlexYatesAlexYates Posts: 264 Rose Gold 2
    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.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • Options
    mr_goodingmr_gooding Posts: 5 Bronze 1
    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.
  • Options
    NeosphereNeosphere Posts: 1 New member
    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..
  • Options
    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...
  • Options
    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.
  • Options
    mr_goodingmr_gooding Posts: 5 Bronze 1
    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).
  • Options
    AlexYatesAlexYates Posts: 264 Rose Gold 2
    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.)
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
Sign In or Register to comment.