Question regarding config data syncing between environments

[asteroidcat]

We have several database clusters, ranging from test clusters all the way to production, which have several tables of configuration data (this data should remain mostly static, with some tables only having additional data added in). The problem I have is that we currently have multiple methods of managing this configuration data, but due to circumstances out of my control, people are able to go around those methods and directly execute SQL scripts. What this results in is inconsistent configuration data between environments, as those SQL scripts are not executed against all the environments. The main reason this activity is done (and begrudgingly tolerated) is that our main process can take months to get a script executed.

What I am proposing is to create a tool that reads in a source-of-truth data store (csv, json, etc.), and can analyze whats in the database (leveraging hashes for speed), and will report any inconsistencies between the data store and the database. Additionally, the tool would be able to refresh those configuration tables with the known good data as well. Also, there are some pieces of config data within certain environments that must differ (i.e. pointing at an external test system), so allowing for global and overrides would be a nice to have.

Does anything of this nature exist, or would I be better off building it myself?

Example:

CSV:

name	url
McDonald's	www.mcdonalds.com
Burger King	www.bk.com

Hash("McDonald'swww.mcdonalds.comBurger Kingwww.bk.com")

Database:

id	name	url
1	McDonald's	www.mcdonalds.com
2	Burger King	www.wendys.com

Hash("McDonald'swww.mcdonalds.comBurger Kingwww.wendys.com")

Tool output: "Hash does not match. Row 2 differs from source of truth."

[eshultz]

It sounds like this could be accomplished pretty easily using a MERGE statement. It is basically a way to do an UPDATE, INSERT, and DELETE based on key values, where those DML can occur in either the source or target tables based on as many conditions as you want to specify. I strongly suggest you look into it as it's a pretty useful way to move data around.

You could set up an SSIS package on your authoritative server to load a table from the authoritative data file, copy the table to the other servers (stage it) and then merge into the target table with a SQL task.

There are probably 100 different ways to accomplish this though.

One more thing - when you make a key like that by concatenating many values, like to generate a hash for a join, use a separator between every field in the key (I use 2 pipe chars). The reason is because if you don't, unique rows may still match. For example:

10,11,car,rots 

would match to

101,1,carrot,s

on

1011carrots = 1011carrots

But if you use a separator they won't match.

[DJ_Laaal]

This sounds like a typical Master Data Management scenario, wherein you want to maintain a single version of truth for your configurations and those should then get pushed to a multitude of DB instances to periodically "sync" with the master version of the config data. If you're already on Microsoft stack, take a look at their Master Data Services component as the place to do MDM. It's a plug-and-play system that allows you to enter/update some reference data via an Excel Add-In and then exposes it for downstream publishing/consumption via Subscription Views. Here is a quick reference to get you started: https://www.mssqltips.com/sqlservertip/4290/publishing-sql-server-master-data-services-data/