Migrating Data

[Matthew Bonner]

The setup we have is rather quite complicated.

We as an agency have 3 servers:

1. Our local machine (dev server)
2. Our staging server (another dev server for testing code as we merge our code)
3. A server replicating the live environment, which I will refer to as the sign-off server
   

Our client has 4 servers (live environment):

• 2 web servers; and
• 2 database servers

The client uses our sign-off server to approve the work we do and they use one of their web servers as an internal approval/previewer server, and another to serve both static and dynamic content to the public. The two database servers are load balanced.

The problem is our client also uses our sign-off server to add content before they copy it over to the live environment.

I'm aware that we have versioning, staging and even workflow tools at bay, but none of these really achieve exactly what we want.

The client builds up the content on our sign-off server because that server is local to us so if they have any issues fixing these issues is quick and easy for us, as their server has very limited access to both them and us for security reasons. This means we have to use any modules we can get our hands on and have to write a lot of custom code in order to export the content from one server to another as there are a few requirements we have to adhere by.

Firstly is that when we want to migrate the content added to the sign-off server, we are only allowed to export everything after a set date to then import it into the live environment. Not all tables have a created and updated column which makes this a pain as we have to code for the tables which don't to ensure no data is missed.

Secondly, we can't do this by command line as it is a Windows environment where Command Prompt and Powershell are disabled in the live environment for security reasons.

So ideally it would be nice if SilverStripe could come with some data migration tools to help us achieve this. Say for instance if there was a wizard where you could define a start date and end date and any data added between those periods is dumped to SQL and then the same wizard can be used on another website to import the data, a bit like phpMyAdmin's Import/Export tool.

We could do this a few different ways too, we could have an audit of changes, and use this as a basis on what to export, or we could ensure every table has a created and updated date per row. Ideally, this would be a set of audit tables in a separate database entirely, like in SharePoint, but this may not suite the way in which SilverStripe is used. But either way, I am hoping we can move towards an API + CMS tools to allow easier data migrations.

[Will Morgan]

I think this is an exceptional circumstance and outside the scope of what the CMS has to offer. I think a module would best serve this requirement as it's only really a development process dependency: not something to get tangled up in core.

[Matthew Bonner]

I've included the specifics so you have the full picture, I'm aware that in most cases a mysqldump will suffice, but what about atomic changes? There are other frameworks which have better support in this respect, and some which like SilverStripe are lacking. This kind of functionality is what sets the boys apart from the men. :)

[James Turner]

I can't see any major technical difficulties with what you suggested Matthew Bonner. I think this functionality would be quite useful and would help out in many situations for the team I work with and probably many other developers too.

I do agree that this functionality will be best suited for a module though it would be good if it was backed/managed by Silverstripe.

[Cam Findlay]

We are tracking new high level feature requests over at http://silverstripe.uservoice.com - would be well worth adding the request here Matthew.

Allows other devs to vote and show support for the feature and helps the core commuters team decide on features and acts like a roadmap.

[Marcus Nyeholt]

I've got some experimental modules that go someway to providing the functionality you're after

http://github.com/nyeholt/silverstripe-changesets - tracks sets of changes to pages + files + other data objects in a single 'set' of changes

http://github.com/nyeholt/silverstripe-syncrotron - allows for serialising and then pushing content items between two systems (including files as base64, and relationships) using guids for tracking uniqueness

I've got no real doco for these at present, and they're not guaranteed to be in a working state - but conceptually, users build up a set of changes locally, which can then be pushed to another node using the syncrotron module. Happy to talk through things if you want to have a crack at them! 

--
You received this message because you are subscribed to the Google Groups "SilverStripe Core Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to silverstripe-d...@googlegroups.com.
To post to this group, send email to silverst...@googlegroups.com.
Visit this group at http://groups.google.com/group/silverstripe-dev.
For more options, visit https://groups.google.com/d/optout.

[Matthew Bonner]

There are a few technical issues that have to be considered, one example of this is when you have the website which contains the data to be exported, Website X, and then the websites that will import the data, Website Y.

If both Website X and Website Y are having content added to them, when exporting data from Website X to Website Y, you could end up with Website Y containing primary keys that clash with data from Website X, and overwriting the data in Website Y with the data in Website X might orphan or corrupt data. This leads to other problems, such as if you regenerate the ID's, how do you decide whether the data is conflicting, or the same data but just updated? Do you duplicate the data so that someone has to manually cleanse the data? Ideally, you wouldn't have two websites where content is being populated, but try telling a client that when they have no intention of listening.

I'll add the suggestion to the user voice as it sounds like it will be something worth covering off.

I'll also take a look at the modules suggested to see how they tackle this issue so thanks for mentioning them Marcus.

[Marcus Nyeholt]

The modules I put up make use of guids for each content item, as well as a 'source' guid that represents the system that the content was originally authored on. It doesn't do any magic around resolving conflicts or anything at present, though page versions help store old edits in case things do get overwritten. There is a 'listUpdates' webservice available that could be used to check a target for any changes that have been made to content items that are about to be sent... but again, it hasn't been built as yet :) 

--