For the longest time now, my group has been doing a lot with databases. We provide databases for classes and research. In addition, we use it for our people registry (a.k.a. CACTUS). What the heck Movable Type uses a back-end database. So we have instances of DB2, Oracle and loads of little MySQL instances installed on separate boxes. From a maintenance and application development standpoint, this has become a real nightmare. My lone administrator has to ensure that things are patched in a timely fashion not only across the databases but also the platforms.
Last year, before we made the decision that things would go forward with MT 4, we needed to solve a fundamental database problem. The web servers were going to be load balanced, but in the past the instances were not. So I did some digging around and found two solutions for MySQL. They were clustering and replication. Clustering looked very attractive in that it did hot fail-over to other nodes. However as I dug deeper into the documentation , I came across a lot of deficiencies too. The software just didn't look like it was ready for prime time. However replication looked like a really good solution. We were already using a concept like it with our LDAP servers. In that you have master nodes and replicas. If a master fails, you can switch a replica node over to be the new master. So for MySQL what we have is a master with two replicas. In the works is probably another master for master-to-master replication. The nice thing about this little cluster is we can place all of our tiny little databases on one MySQL cluster. As you can probably guess its really great from a maintenance standpoint. Performance is really good too. Right now, we have both MT versions using the cluster along with our Jabber server. So from a strategic standpoint all our MySQL-like databases will use the cluster.
Now on to the bigger databases. We have been using Oracle for our people database since 1999. Oracle has loads of great features that do come at a pretty high cost. For other applications, we have been using DB2. We are mainly an IBM shop and DB2 has some attractive features (like built-in Kerberos authentication). And after version 7.x, it added a stored procedure language. However that language did not rival Oracle's PL/SQL, and the name was confusing it is called SQL/PL. Right now we have a our classes databases, Friends of Penn State and some other databases on DB2. Our strategic plan is to move all of those databases to Oracle. A wise man said a long time ago that "Oracle gets you by the tools". In reality that person was correct, their tools far exceed anything that I have seen on DB2 (have you seen APEX?). Just like with MySQL, the benefits of a single large database like Oracle are really going to outweigh the pain its going to take to migrate some of these databases from DB2.
So where are we at with all of this? Well from the MySQL side, I would say we are making excellent progress. We have moved a number of our "baby" databases over to the cluster. From the large scale side, we are waiting until we migrate to Oracle 10/11 and then we will start the migration of the DB2 databases over to Oracle.
Last year, before we made the decision that things would go forward with MT 4, we needed to solve a fundamental database problem. The web servers were going to be load balanced, but in the past the instances were not. So I did some digging around and found two solutions for MySQL. They were clustering and replication. Clustering looked very attractive in that it did hot fail-over to other nodes. However as I dug deeper into the documentation , I came across a lot of deficiencies too. The software just didn't look like it was ready for prime time. However replication looked like a really good solution. We were already using a concept like it with our LDAP servers. In that you have master nodes and replicas. If a master fails, you can switch a replica node over to be the new master. So for MySQL what we have is a master with two replicas. In the works is probably another master for master-to-master replication. The nice thing about this little cluster is we can place all of our tiny little databases on one MySQL cluster. As you can probably guess its really great from a maintenance standpoint. Performance is really good too. Right now, we have both MT versions using the cluster along with our Jabber server. So from a strategic standpoint all our MySQL-like databases will use the cluster.
Now on to the bigger databases. We have been using Oracle for our people database since 1999. Oracle has loads of great features that do come at a pretty high cost. For other applications, we have been using DB2. We are mainly an IBM shop and DB2 has some attractive features (like built-in Kerberos authentication). And after version 7.x, it added a stored procedure language. However that language did not rival Oracle's PL/SQL, and the name was confusing it is called SQL/PL. Right now we have a our classes databases, Friends of Penn State and some other databases on DB2. Our strategic plan is to move all of those databases to Oracle. A wise man said a long time ago that "Oracle gets you by the tools". In reality that person was correct, their tools far exceed anything that I have seen on DB2 (have you seen APEX?). Just like with MySQL, the benefits of a single large database like Oracle are really going to outweigh the pain its going to take to migrate some of these databases from DB2.
So where are we at with all of this? Well from the MySQL side, I would say we are making excellent progress. We have moved a number of our "baby" databases over to the cluster. From the large scale side, we are waiting until we migrate to Oracle 10/11 and then we will start the migration of the DB2 databases over to Oracle.
Leave a comment