Limitations of DLM/SQL Data Compare
leehbi
Posts: 6 New member
Hi,
We use SQL Data Compare as part of our CD process with the DLM scripts to deploy changes from dev to test to prod.
The workload is a data warehouse. We have data for things like ETL audit/last record loaded etc.. This data must not be changed in test/production. Data changes we need to deploy is meta data to drive the ETL processes. We're using BIML to build our packages and they read this meta data to build packages hence this data needs to be deployed to test/prod.
We haven't found a clean way to do this, other than take a copy of previous meta data and re-load after DLM scripts.
I don't like this as there's an extra step that increases complexity/failure rates.
Has anyone else experienced this and taken a different path?
We use SQL Data Compare as part of our CD process with the DLM scripts to deploy changes from dev to test to prod.
The workload is a data warehouse. We have data for things like ETL audit/last record loaded etc.. This data must not be changed in test/production. Data changes we need to deploy is meta data to drive the ETL processes. We're using BIML to build our packages and they read this meta data to build packages hence this data needs to be deployed to test/prod.
We haven't found a clean way to do this, other than take a copy of previous meta data and re-load after DLM scripts.
I don't like this as there's an extra step that increases complexity/failure rates.
Has anyone else experienced this and taken a different path?
Answers
I'm just trying to understand the process a bit better - is the metadata actual data in tables that is used elsewhere? Or is is extended properties or something else?
What is your existing DLM process that you are adding the SQL Data Compare step onto? Have you looked into using Static Data in SQL Source Control that you would then update in dev and commit and would then be rolled out to the target environments with the DLM deployment?
Kind regards,
Alex
Have you visited our Help Center?
Is there an easy way for me to exclude specific columns from a table from being deployed?
Lee
It sounds like you are using DLM Automation (now SQL Change Automation) Powershell cmdlets which are more geared towards schema deployments and include a small static data capability that doesn't allow a lot of configuration.
You should be able to use the SQL Data Compare command line included with DLM Automation (and SQL Change Automation) to run a specific data compare after the schema deployment. It's located in the C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\SDC folder. In the SQL Data Compare command line there is a switch /ExcludeColumns to exclude specific columns from the comparison.
I hope that helps!
Kind regards,
Alex
Have you visited our Help Center?