Sync live and staging databases while retaining sales data

[mousepad]

PLEASE... I have asked a bunch of time and have yet to get an answer from anyone on this.

We have a staging and live server. Managing the code with GIT or manually is no problem. However, while we are doing edits on the staging site, orders continue to come in on the live site.

Now I am ready to push live but I have to upload the new database too. This will overwrite any orders, customers or edits we have made on the live system.

How do I sync those databases but not lose my latest live orders etc. ??

[mousepad]

Nobody can answer this ??

Seems like something that every Magento developer would be doing daily. There must be a way to merge the databases and both keep new additions and changes, without losing orders and customers 

[Tarandeep Singh]

@mousepadWhy you need to sync the staging database with Production? If you had a copy of production database and did some development and testing then you should be good. For any database changes you should have created installation script or have recorded database update.

If you have created installation script then replace staging DB with production DB and browse the staging environment. Your database update should be updated and staging environment should work. In case of error you need to debug and find what did go wrong.

If you did not do the installation script then you need to run the SQL's to make database updates before browsing the staging website.

[mousepad]

@Tarandeep Singh - I'm not concerned about the staging server being synced with the live site but eventually the live site's database needs to be merged with the staging one. I cannot simply overwrite the live db with the staging one or it will overwrite all the new orders in the current live db.

For example. I need to make a whole bunch of config changes, template changes, install some modules etc. All of these things could affect many many tables. I don't know what tables exactly it is changing. 

In the mean time, while I have been working on on the staging server, orders have been flowing into the live site. So I have new customers, new orders, adjusted inventory, shipping info added etc. Again affecting many tables.

There has to be a real solution to merge the edits I have made on the staging site with the live site's realtime data.

[Tarandeep Singh]

@mousepadYou cannot have 2 system staging and production in sync. Both system will have their on settings. You can find some solution to sync the data both way which could be very expensive. Even if you have such solution in place your production environment may have issues as changes you are making in staging are syncing with production but relevant code is not deployed on production.

If you are coding right way then once you migrate or update your code from staging to production then all the database update you made on staging will also be updated on the production. Once you do the production deployment you can take a snapshot from Production and install it on staging.

[mousepad]

@Tarandeep SinghYour second paragraph says it all... "update your code from staging to production then all the database update you made on staging will also be updated on the production" YES! That's exactly the problem... Doing this will also overwrite the sales on the production server. Your senario is assuming that your business is shut down, while you are editing and no sales are coming into the live site.

You HAVE to have 2 databases. One for the Staging and one for Production. When you make changes to the staging, you have to get those changes to the live site without over writing the NEW sales and customer info on the LIVE site.

Surely this is something that every Magento developer has to do. Is there a script or a module to get this done ? 

[Tarandeep Singh]

What kind of updates you are making on staging and why would it overwrite the sales or any other data on production?

[Finestshops]

Hi mousepad,

when our developers install the finished customizations from a development to a live store, they upload new files and run MySQL queries to implement the changes in the live database. we never had any problems with this approach and, as I know, all developers do this.

Your developers should be able to give you SQL statements to run to implement the changes in the live database without erasing the data or make you a conversion script if some existing data has to be converted somehow. if they can not do this, just get better developers.

[mousepad]

 Hi Anton,

Thank you. I'm the developer, just trying to learn the correct way to do this.

Your suggestion of SQL scripts works for the basic installation of a module. However, what if you are making a whole range of edits in a major revamp, including to the template, shipping methods, merchant account, pricing rules, customer groups, multiple module installations etc. Or for that matter, what if you are developing and entirely new site. Obviously this affects a lot of tables, including Magento core tables and it's very difficult to track down all the tables that it affects.

How do you suggest creating the sql statements to make all these changes ?

If there was a script to export/import or merge sales and customer data, it seems that would be the easiest. 

[Tarandeep Singh]

@mousepadWhatever updates you are making you should make it using modules/extensions. Whenever you alter and add data create using sql files under your module. This way you can track all the updates in database.

If you won't do it right way and modify Magento core than you won't be upgrade safe.