I was asked to integrate two SM systems in order to refresh some data on test system which data will come from production system. I would like to hear thoughts, suggestions on this challenge.
We do the same thing; we have the team that manages databases at our company copy the contents of the production database down into the lower environments (Staging, Release/Test and Development) at least once a year, if not more often.
But, if you're not talking about a full system refresh, and more of a scheduled update of data, there's a couple of reasons you'd want to do something like that, and a couple of ways to go about it.
For example, we have several integrations into our HP Service Manager instance that use data in HPSM to drive automation. We don't want them to test against the Production instance because we don't want them to break anything, but they need data to look a specific way - for example, CI data, operator data and assignment group data - so that their automation can work.
So depending on the tables and the frequency of your transfer, one solution is to use ConnectIt as your integration tool. ConnectIt is an HPE application used to connect systems together. It can be used for a variety of purposes, but the one that makes sense in this case would be an HPSM to HPSM connection. You can use webservices in HPSM and use ConnectIt as your scheduler and the method used to invoke the webservices.
Alternatively, you could run a scheduled load/unload of records. You could create an Unload record that queries your records using something like 'sysmodtime>date(tod())' and it will grab whatever data was created or modified since the beginning of the day. Then schedule that to run once a day and dump the data in a location that can be accessed by both your HPSM systems. Then, you can have a scheduled load of records in your other system that runs once a day and pulls in the unload.
Or you could even get really fancy and create an HPSM to HPSM integration using webservices inside HPSM and update records in your secondary system as those records are created in the primary system. That'd be a little more complex, but it's something that could certainly be done.
But, if it's for occasional refreshes of data, a database copy/load is probably the simplest method.