<p>I have an existing system that uses a relational DBMS. I am unable to use a NoSQL database for various internal reasons.</p>

<p>The system is to get some microservices that will be deployed using Kubernetes and Docker with the intention to do rolling upgrades to reduce downtime. The back end data layer will use the existing relational DBMS. The micro services will follow good practice and "own" their data store on the DBMS. The one big issue with this seems to be how to deal with managing the structure of the database across this. I have done my research:</p>

<ul>
<li><a href="https://blog.philipphauer.de/databases-challenge-continuous-delivery/" rel="nofollow noreferrer">https://blog.philipphauer.de/databases-challenge-continuous-delivery/</a></li>
<li><a href="http://www.grahambrooks.com/continuous%20delivery/continuous%20deployment/zero%20down-time/2013/08/29/zero-down-time-relational-databases.html" rel="nofollow noreferrer">http://www.grahambrooks.com/continuous%20delivery/continuous%20deployment/zero%20down-time/2013/08/29/zero-down-time-relational-databases.html</a></li>
<li><a href="http://blog.dixo.net/2015/02/blue-turquoise-green-deployment/" rel="nofollow noreferrer">http://blog.dixo.net/2015/02/blue-turquoise-green-deployment/</a></li>
<li><a href="https://spring.io/blog/2016/05/31/zero-downtime-deployment-with-a-database" rel="nofollow noreferrer">https://spring.io/blog/2016/05/31/zero-downtime-deployment-with-a-database</a></li>
<li><a href="https://www.rainforestqa.com/blog/2014-06-27-zero-downtime-database-migrations/" rel="nofollow noreferrer">https://www.rainforestqa.com/blog/2014-06-27-zero-downtime-database-migrations/</a></li>
</ul>

<p>All of the discussions seem to stop around the point of adding/removing columns and data migration. There is no discussion of how to manage stored procedures, views, triggers etc.</p>

<p>The application is written in .NET Full and .NET Core with Entity Framework as the ORM.</p>

<p>Has anyone got any insights on how to do continious delivery using a relational DBMS where it is a full production system? Is it back to the drawing board here? In as much that using a relational DBMS is "too hard" for rolling updates?</p>

<p>PS. Even though this is a continious delivery problem I have also tagged with Kubernetes and Docker as that will be the underlying tech in use for the orchestration/container side of things.</p>

<p>I work in an environment that achieves continuous delivery. We use MySQL.</p>

<p>We apply schema changes with minimal interruption by using <a href="https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html" rel="nofollow noreferrer">pt-online-schema-change</a>. One could also use <a href="https://github.com/github/gh-ost" rel="nofollow noreferrer">gh-ost</a>.</p>

<p>Adding a column can be done at any time if the application code can work with the extra column in place. For example, it's a good rule to avoid implicit columns like <code>SELECT *</code> or <code>INSERT</code> with no columns-list clause. Dropping a column can be done after the app code no longer references that column. Renaming a column is trickier to do without coordinating an app release, and in this case you may have to do two schema changes, one to add the new column and a later one to drop the old column after the app is known not to reference the old column.</p>

<p>We do upgrades and maintenance on database servers by using redundancy. Every database master has a replica, and the two instances are configured in master-master (circular) replication. So one is active and the other is passive. Applications are allowed to connect only to the active instance. The passive instance can be restarted, upgraded, etc. </p>

<p>We can switch the active instance in under 1 second by changing an internal CNAME, and updating the <code>read_only</code> option in each MySQL instance.</p>

<p>Database connections are terminated during this switch. Apps are required to detect a dropped connection and reconnect to the CNAME. This way the app is always connected to the active MySQL instance, freeing the passive instance for maintenance.</p>

<p>MySQL replication is asynchronous, so an instance can be brought down and back up, and it can resume replicating changes and generally catches up quickly. As long as its master keeps the binary logs needed. If the replica is down for longer than the binary log expiration, then it loses its place and must be reinitialized from a backup of the active instance.</p>

<hr>

<p>Re comments:</p>

<blockquote>
  <p>how is the data access code versioned? ie v1 of app talking to v2 of DB? </p>
</blockquote>

<p>That's up to each app developer team. I believe most are doing continual releases, not versions.</p>

<blockquote>
  <p>How are SP's, UDF's, Triggers etc dealt with?</p>
</blockquote>

<p>No app is using any of those.</p>

<p>Stored routines in MySQL are really more of a liability than a feature. No support for packages or libraries of routines, no compiler, no debugger, bad scalability, and the SP language is unfamiliar and poorly documented. I don't recommend using stored routines in MySQL, even though it's common in Oracle/Microsoft database development practices.</p>

<p>Triggers are not allowed in our environment, because pt-online-schema-change needs to create its own triggers.</p>

<p><a href="https://dev.mysql.com/doc/refman/8.0/en/adding-udf.html" rel="nofollow noreferrer">MySQL UDFs</a> are compiled C/C++ code that has to be installed on the database server as a shared library. I have never heard of any company who used UDFs in production with MySQL. There is too a high risk that a bug in your C code could crash the whole MySQL server process. In our environment, app developers are not allowed access to the database servers for SOX compliance reasons, so they wouldn't be able to install UDFs anyway. </p>
