Azure SQL Database Content Synchronisation to Downstream Environments

Data Flow ====> Production –> Staging –> Integration –> Test –> Dev

I have been working with various clients with different kind of requirements for each one of them. Thinking about it all of them lead to one common purpose. What is that? We develop applications and write automated tests to validate them on regular basis or on each release and even on nightly to run wide range of tests. One of the challenges are find the answers of how did we miss this in our testing and customer have found an issue. This could happen because of various reason of having difference in the environments and lot more .. But here we have covered those aspect of problems by source controlling the infrastrucre and following rigorous automated way of provisioning the environments and associated components. Now, it’s more about test data management, How to ship the database content from the production environments into test environments on regular basis.

Here is one of many ways to do it. We are talking here about Microsoft Azure and Azure SQL databases. Again, there are lot of tools available in the market to do this for you but I’m not sure about any tools available for SQL PaaS Databases.

I have put together the scripts which will help you to get started.

So this article was born to make this happen in azure and I have tried to make things much simple by scripting out most part of it. Feel free to take it and enhance the same.

Below are the list of key things which are quite important in this process

  1. Understand the requirements of data Obfuscation and make necessary amendments on the temporary server
  2. How frequent you need to push the production data to the downstream environments
  3. Understand the operational impacts on the team
  4. Learn about the test data, customize and automate it specific to environments
  5. Run the tools and make it happen
  6. Monitor the changes by running the test so the data is intact.

The concepts and technologies used as described below

  1. Azure ARM Templates

    This is used to provision the test application and the databases.

  2. Microsoft Azure PowerShell

    To automate the complete data migration process.

  3. Azure Automation
    1. Assets

      You need to Import all the latest version of cmdlets for the scripts to work as expected.

    2. Credential

      Store the credential so that it can be accessed by the runbooks.

    3. Runbooks

      Powershell runbooks is the engine behind all this.

    4. Schedules/webhooks

      Use schedule if you need a nightly and a simple solution

      Use webhooks if you are going to run against multiple databases

    5. Source Control Integration

      Make sure you check in the changes

    6. Azure SQL Database Server/Databases

Below are the three scripts required

Now, we have learnt a bit about the concepts. This should get started for most people. Again, In the Next blog post I’m going to expand into the details of all nuts and bolts along with using Octopus to Orchestrate the whole process.

Happy reading !!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.