Copy Changes on Certain Tables Down to Sub-Environments Daily

[jmiskey]

We currently clone down from our Prod to our sub-environments (Test and Dev) about once a quarter.  We have frequent updates to the following tables in Production:

- sys_user

- sys_user_group

- sys_user_grmember

- sys_group_has_role

- sys_user_has_ role

A lot of these updates are needed in the lower environments (especially Test) for testing purposes.  We are trying to figure out a way if we we could automatically capture the daily changes/updates from Prod, and push them down to the lower environments.  I know how to do it manually.  Simply query each of those tables on "Updated" field to identify just the recent changes, export them to an XML file, and then manually import them into the other environments.  But would like to figure out a way to automate this process.

We thought there might be a way to simply "clone" those 5 tables (and nothing else), but after we thought about it, determined that is not a good idea.  That is because there may also be changes "bubbling up" from Dev that would be creating things like new groups and service accounts that are not in Prod yet.  So we would not want to lose them.  That is why we were thinking we would need to do a "delta" file, of recent changes only, coming down from Prod to be loaded in Dev and Test.

Has anyone done anything like this?  Any suggestions on how we may be able to do this?

Thanks

[Mark Manders]

Using the OOB cloning process for just cloning 5 tables is also a risk. You will be adding loads of table excludes to a profile and you always run the chance of missing one. And, like you said: it will copy over new things.

With updates on all instances (or at least PROD and DEV) it will be difficult. I think the best way is just to create an integration between the instances, with triggers on those tables. You will have a real time integration.

I did see another solution a couple of years back, but it's up to you if you want it: they had a scheduled report, including the sys_id's and emailed that to the lower instance. An inbound action created a datasource and a transform map ran to update everything. It worked and they never let us touch it to make it better.

[jmiskey]

Mark,

Thank you for those ideas.  It looks like we have a few options to investigate.  We typically have emails shut off in our sub-environments, so the integration option sounds like it may be the best solution.  I will have to do some research on that, as we have never done that before.

And thanks for further confirming that cloning just those tables is not a good solution.  

[Dr Atul G- LNG]

Hi @jmiskey 

Not 100% sure, but check the instance data replication https://docs.servicenow.com/bundle/washingtondc-servicenow-platform/page/administer/instance-data-re...

[jmiskey]

Oooh!  That is an interesting option.  It sounds like it may do exactly what we need.  And if we do one-directional, it shouldn't overwrite any new data rising up from Dev.

I am curious what the "seeding" will do, if we elect that.  Hopefully, it will not erase any non-matching data.  We can test that out to see what happens.