How to effectively patch code running on multiple hosts?

Question

A product is a CMS under constant development and new features patches are always being rolled out. The product is hosted on multiple servers for some of which I just have FTP credentials and access to db and for others I have full root access. Now, as soon as a new feature is released after testing and all I have to manually ftp files and run SQL queries on all servers. This is very time consuming, error prone and inefficient. How can I make it more robust, fool proof or automate parts of it? The CMS is based on PHP, MySQL, JS, HTML and CSS. The admin part is common for all. Skins and some custom modules are developed for different clients and the only part we update is admin.

Update

For managing code we use GIT, SQL is not part of this GIT structure and I will be talking to product manager/owners to have it under version control.

Answer 1

This is one of the big questions of unpackaged code.

Personally, I have a PHAR, which when executed, extracts code to the specific folder and executes needed queries.

Answer 2

I've run web deployments across dozens of servers handling hundreds of millions of visitors/month.

SQL change management is always going to be a beast. You're only hope is either rolling your own in house (what I did) or using something like EMS DB Comparer.

To handle the file syncing, you're going to need a number of tools, all expertly crafted to work together, including:

1. Source code version control (bzr, svn, etc.), that is properly branched (stable branch, dev branch, testing branch are required),
2. A Continuous Integration server,
3. SFTP support on every server,
4. Hopefully unit and integrative tests to determine build quality,
5. Rsync on every server,
6. Build scripts (I do these in Phing),
7. Deployment scripts (in Phing as well),
8. Know how.

The general process takes approximately 20 hours to research thoroughly and about 40 hours to set up. When I drew up my documentation, there were over 40 distinct steps involved. Creating the SQL change management utility took another 20-30 hours. Add in another 10 hours of testing, and you're looking at a 100-120 hour project, but one which will save you considerably in botched deployments in the future as well as reduce deployment time to the time it takes to click a button.

That's why I do consultations on setting up this entire process, and it usually takes ~5 hours to set up on a client's network.