Change management of database scripts


In this post I’m going to discuss the strategy used by our team to manage the changes taking place in the database. Most of the time we as developers mainly focus on implementing the feature and put less effort on manage the database changes. For instance individual might not straight away see the importance in the first place and also this may not be very important for projects with smaller teams but for projects with a larger scope, involving multiple teams and frequency of changes in the functionality is high then might feel the importance of coming up with a strategy to manage this.

First lets try to identify situations that favours to coming up with a strategy for projects that requires to maintain a RDBMS as the data store.

  • For a project having multiple teams to carrying out the development.
  • For a project with a larger scope, having fortnight or monthly deliverables.
  • For a projects with a longer duration.
  • For projects having teams distributed across different geographic locations.

Above list might not be a complete one, so the reader is always welcome for suggestions. I’ll be taking the above scenarios as the base for the rest of my discussion. The obvious fact is that the change is inevitable. To accommodate this change almost every development team use version controlling to effectively mange the development carry out by each individual in the team. Similarly it’s best to come up with a strategy early in the development life cycle on managing the changes taking place to the database of the application(s). The changes can take place to the database under following categories:

  1. A way to keep track of the new insertions of table(s).
  2. A way to keep track of insertion, updates and deletion of column(s) in a table.
  3. A way to keep track of insertion, updates and deletion of row(s) in a table.
  4. A way to manage application configurations/fixed data mappings.
  5. A way to manage setting up of the database for fresh installation or upgrades.

Step 1

The Step 1 can be handled by maintaining a separate SQL script (lets name it db_tables.sql) to keep track of all the new insertion of table(s) into the application. Most of the situations inserting of new table(s) arises when there is a requirement to implement a new feature. So this would give a clear picture of the database about when and at which point in time each table got introduced and by whom.

Step 2 & 3

The Step 2 & 3 can be handled by maintaining a separate SQL script (lets name it db_tables_tracker.sql).

Step 4

In the Step 4, maintain individual SQL scripts to represent the data mapping of each identified table(lets name them as sys_config.sql, app_modules.sql, etc…), also the changes required by each can be introduced directly in to mapping itself or can be accommodated via db_tables_tracker.sql. For tables having larger number of records as data mapping it would be easier to alter the existing mappings to reflect the new change rather accommodating it via db_tables_tracker.sql. This way it helps an individual to get an idea of the most recent state of the mapping just by looking at the SQL script, this approach fails when the application required to main foreign key constraints among related tables because this approach requires to drop and reintroduce the table every time the update takes place on the table column(s) or to the data.

Step 5

The Step 5 can be elaborated by broken it down in to two.

Fresh installation of the database

To accommodate a fresh installation scenario of the database, all the SQL scripts created in first 4 steps can be combines into one batch by carefully arranging the SQL scripts as follows.

  • SQL script from step 1 – db_tables.sql
  • SQL script from step 2 & 3 – db_tables_tracker.sql
  • SQL scripts from step 4 – sys_config.sql, app_modules.sql, etc…

Note : Above sequence can be automated using a shell script (db_init.sh).

Upgrading of an existing database

For the purpose of preparing the database to accommodate new changes on to the existing table(s) we can maintain a new SQL script called db_upgrade_tracker.sql. This script will only hold the SQL statements required for updating the previous version of the database to the new state, taken from db_tables.sql and db_tables_tracker.sql scripts. Next step is to sequence the flow of the SQL scripts for the database upgrade as follows:

  • db_upgrade_tracker.sql
  • SQL scripts from step 4 – sys_config.sql, app_modules.sql, etc…

Note : Above sequence can be automated using a shell script (db_upgrade.sh).

Monitoring and Managing Amazon RDS Databases using MySQL Workbench


Last couple of week I was looking for an easier approch to manage the Databases created on Amazon RDS instances. I had to run through a tedious set of steps in carrying out routine stuff like introducing new updates, taking daily backups and moving it to an Amazon S3 bucket, etc… Before getting into touch with MySQL Workbench had to run through several intermediate hops (Connect to one of the EC2 instance via SSH and connect to the RDS instance from there, ;-( ) even to run a small query on the tables.

Let me eleborate the step I followed in setting up the connect with the DB created on a Amazon RDS instance.

Step 1: Launch MySQL Workbench

Launch MySQL Workbench and select New Server Instance found under Server Administration section form the Welcome screen as shown in Figure 1.

MySQl Workbench Welcome Screen

Figure 1: MySQl Workbench Welcome Screen

Step 2: Specify Host Machine of the Database server

Next form the Create New Server Instance Profile wizard, provide the Public DNS of the Amazon EC2 instance under Remote Host as shown in Figure 2 (this EC2 instance will be as the intermediate point to access DB that resides inside the Amazon RDS instance). To find the Public DNS specific to the EC2 instance can be done as illustrated in Figure 3. Click Next button to procced.
Specify Host Machine

Figure 2: Specify Host Machine of the Database screen

Amazon EC2 Instance

Figure 3: Amazon EC2 admin console screen

Step 3: Creating the DB connection

Fill in the values as described below which illustrated in Figure 4.

  • Connection Name – Provide a name to identify your connection
  • Connection Method – Select Standard TCP/IP over SSH from the drop down list instead of Standard(TCP/IP). Then on the appearing two tabbed pane, fill the following values found under Parameters tab.
    • SSH Hostname – Provide the Public DNS of the Amazon EC2 instace (refer Figure 3) which will be used as the intermediate server instance used to create the connection with the DB instance.
    • SSH Username – Provide the user of the Amazon EC2 instance (refer Figure 3) which will be used as the intermediate server instance used to create the connection with the DB instance.
    • SSH Key File – Provide the Private Key (xxx.pem) used to connect Amazon EC2 instace via SSH
    • MySQL Hostname – Provide the Endpoint of the DB instance created in Amazon RDS(refer Figure 5).
    • Username – Provide the Master Username of the DB instance created in Amazon RDS(refer Figure 5).
    • Password – Click on Store in Keychain.. button and type the password provided while creating the DB instance in Amazon RDS.

Click Next button to procced.

Database Connection

Figure 4: Database Connection screen

Amazon RDS DB Instance

Figure 5: Amazon RDS DB Instance

Step 4: Testing the Database Connection

In this step it validates the parameters provided in the previous step and upon success it returns Database connection tested successfully(refer Figure 6). Upon this message the Next button get activate to procceds to Management and OS.

Test DB Connection

Figure 6: Testing the Database Connection

Step 5: Management and OS

Select SSH login based management option and select the appropriate parameters for Operating System, MySQL Installation Type (refer Figure 7).

Management and OS

Figure 7: Specify remote management type and target operating system

Step 6: Setting up remote SSH Configuration

Provide the following parameters

  • Host Name – Provide the Public DNS of the Amazon EC2 instace (refer Figure 3) which will be used as the intermediate server instance used to create the connection with the DB instance.
  • User Name – Provide the user of the Amazon EC2 instance (refer Figure 3) which will be used as the intermediate server instance used to create the connection with the DB instance.
  • Select Authenticate Using SSH Key and provide the SSH Private Key Path – Provide the Private Key (xxx.pem) used to connect Amazon EC2 instace via SSH

Remote SSH Configuration

Figure 8: Remote SSH Configuration

Step 7: Test Host Machine Settings

In this step it validates the parameters provided in the previous step and upon success it returns Testing host machine setting is done(refer Figure 9). Upon this message the Next button get activate and clicking Next buttin it popup Review Settings dialog box (refer Figure 10).

Test Host Machine Settings

Figure 9: Test Host Machine Settings

Review Settings

Figure 10: Review Settings

Step 8: Creating the Instance Profile

Provide a name for the Profile got created.

Creating the Instance Profile

Figure 11: Creating the Instance Profile

Step 9: Done

Success

Figure 12: Workbench Home screen with the new profile