I made a copy of our production AC7 database, performed the upgrade, and am currently working on getting AC9 set up with our desired layout and configuration changes. Some of the stuff we are doing is being kept in a custom theme which is easy to replicate and port because they are physical files on the disk. However, it seems like many of the changes are being stored at the database level.

My question is, how are we supposed to customize our design in advance of rollout if so many of the layout components are being written to the database? Seems like everything will be overwritten/wiped out when we download and restore the production database again to prior to making AC9 live?

Thanks for the response. The customer wants to use a left navigation, so I created a new "Split Top Section with Left Navbar" page layout for the categories. In that layout I created all of the necessary zones. Then I had to go to the page templates and set them up to use the layout. All of those changes will be blown away when I re-import the production database, correct? There are also several widgets where we changed the parameters that control how the widgets are displayed on the website.

Is there a way to reliably back up all of the changes and new layouts, templates, and widgets, then restore them after a production database has been imported?

Talking about the templates configured within the Admin

You need to use data compare tool like Redgate SQL Compare tool.

1- You took a copy of live AC7 database. lets say it dev
2- You worked on dev and made changes in layouts, templates, zones etc
3- When ready to go for production, take a fresh copy of live database
4- Compare the live and dev databases with in SQL compare tool and merge the CMS changes into the live database

Hope this helps!

We upgraded a large site and it was a nightmare trying to do this. We didn't know until right before the upgrade that they hadn't been maintaining their products on both sites and we already had scripts to import new users and orders which we planned on using. Another developer tried syncing the data into the CMS tables using sql scripts, but ran into a lot of issues and confusion. There are numerous tables involved and you have to do things in a certain order because of foreign keys. So we ended up telling them to put a freeze on product changes the day before the upgrade and upgraded a copy of the Gold site database that day then spent that day recreating the CMS entries manually in the admin.
We are seriously considering not using the CMS much and trying to do more in the files, but haven't experimented much with that to know what we can get away with. The people who hire us to do a site aren't interested in doing any design work themselves so CMS features that a small merchant might use just don't apply except for some html snippets in places.

Thank you both for your feedback, tool recommendations, and sharing info about your experiences. I has taken me a little bit to understand how the layout stuff works, but now starting to make more sense. It is certainly flexible, but it was quite interesting trying to figure out why my layouts broke when updating the design for one of the templates. Seems like any time you change the layout it makes you rebuild the design elements (drop in the widgets, HTML snippets, etc).

It seems powerful and I like the compartmentalized nature of things. Does make finding things interesting at times.

I'm going to sign up for a trial of Redgate and see how it works. Really wish they didn't split up SQL Compare and SQL Data Compare; looks like both could be useful.

My recommendations if you're even going to attempt this, is to keep track of the foreign keys. Using a test database to another test database, do your compare and transfer and see what happens with the foreign keys. If the foreign keys are properly established, then you should be okay.

The main problem arises when foreign keys are dropped in order to transfer data, and then they are difficulties in reestablishing them again.

The layout zone table is the one is going to be problematic, is nothing but a bunch of numbers linking to other tables and there are 2 foreign keys attached. Maybe in the future an export and import procedure can be developed by Ablecommerce for this purpose,but I don't see too many people running into this problem.

If I was in your shoes, I agree with Joe, you don't want future problems to be based on a database that has integrity issues. Tell your customer that the site is going to be down while you make your layout changes. That would be playing it safe.

I also use another tool called Navicat, and I have used this tool in the past to just copy tables over. Obviously these tables don't have foreign keys attached. I had an issue late last year with the order status table, so it was very easy for me to go into an old database, copy just the order status table and pasted into the new database.

The reason they are 2 tools, besides from a marketing point of view, one is for the data itself, the other is for the schema of the database layout.

Good luck with your issue and I hope it all works out.

The challenge is identifying what EXACTLY represents a CMS change inside the Able tables. There is zero documentation on the specifics tables and properties to consider when planning a cutover with a dev site involved. We're left to simply figure it out on our own and hope we didn't just blow up our best client's website. So to sum up the transition of a large upgrade project into 4 tiny bullet points is badly over-simplified.

You have new tables that require full data transfer from the dev tables. SQL Compare does handles this well.

But then there are individual CMS-related properties on a variety of existing table objects. I can't use SQL Compare the entire category object or product object because the upgrade took 5 months to do. In that time, the client has continued to maintain their catalog and products. You can't expect a company to just stop updating the catalog for the duration of the upgrade project.

So by that time, product descriptions, category names etc have changed. And now I have template assignments all over the place that need to sync or nothing will render properly.

Sure in a perfect world where every product uses the same single template, and every category uses the same single template, you can rely on the default-product and default-category display templates. But try that on a catalog with over 700 categories and 215,000 products and see how far it gets you.

It's probably just a matter of preferences, I like dbForge because I have used some of their other products. However, anything that you use must be taken with a grain of salt here because there could be potential future problems.

I don't believe you will find any software that guarantees a total or partial synchronization without caution. Even dbForge will pop up a warning that certain foreign keys may not be re-created after synchronization. For this reason alone, I try not to rely with synchronization unless I have no choice.

Hell on earth is trying to track down foreign keys that somehow got dropped from a database. I have learned a lot in the 10 months that this post originally started. Unless there is a schema change because of a version upgrade, your development, and production databases should be kept up to date by restoring from production to development.

One of the main issues that I've had for a long time, was all the little settings that I had to reestablish in a development database after it's been restored from production. That being said, I have gotten to the point that after I restore a database to my development server, all I need to do is run a two SQL scripts, and everything is done for me.

- The first script, reestablish the user settings in the AbleCommerce database - this usually gets lost when you restore to a different machine.
- My second script, eliminates the Google authentication, so I can log in without a problem.

That said, I don't need to do anything else when it comes to the database.

Total time from restoring from production to development and running the scripts, less than 60 seconds.

I hope some of this helps.

-Ray

Edited by user Friday, May 6, 2022 12:09:43 PM(UTC)  | Reason: Not specified

Lol, I literally just purchased a RedGate SQL Data Compare license right before I saw this.

We want to see if our planned process makes sense to folks:

1. Download a current backup of the LIVE database (7.0.7)
2. Restore the backup onto my DEVELOPMENT machine
3. Perform the AC 9.0.6 Install/Upgrade
4. Take a backup of the DEVELOPMENT database after upgrade
5. Perform all of the settings and template changes, update copy links and photo paths
6. Use SQL Compare tool to compare DEVELOPMENT database to DEVELOPMENT backup from Step 4 to obtain changes generated by Step 5
7. Manually review the change scripts to make sure there is nothing out of the ordinary (e.g. key issues)
8. Stop the LIVE website and take another backup

From here I'm not sure if I should pull the LIVE db back down and perform the upgrade on DEV, or push all of the code up and perform the upgrade on the LIVE server? The first method would require an additional step of backing up the DEV database and restoring it to the LIVE server again after all of the changes have been merged. However, I could at least look at it and confirm the changes.