Amazon RDS - Creating/cloning multiple databases in a single rds instance

2
votes

We are developing a Java/j2ee application which uses RDS.

We want to create a separate database per customer on a single RDS instance.

We want to create a template SQL schema with tables and some metadata.

When the new customer is created we want to clone the template schema and create a separate db for that customer.

Can you let me know if this is possible using AWS SDK APIs? Or if there is any other way?

Regard, Dattatray.

2
amazon-web-servicesamazon-rds

2 Answers

3
votes

The general design for handling individual databases for multi-tenant applications would be like

  • Have a separate DB for identification / allocation of specific database to a particular client [ Meta Data Database ]
  • During a launch / on-boarding of a new client, you need to fire the SQL snippet -> with a unique Client's DB name and have this information updated in the Meta Data Database
  • You can think of dynamically updating the SQL snippet DB NAME and then firing the Schema for the new client or use ORM like Hibernate to create the specified Database elements.

Amazon RDS doesn't impose any restrictions on number of Databases you can created in a single instance, so you need to worry about the upper limit. You do not need to use any of the AWS SDKs or APIs you just need to concentrate on the App and Connection Strings.

Extract from AWS FAQs for RDS :

Q: How many databases or schemas can I run within a DB Instance?

RDS for MySQL: No limit imposed by software

RDS for Oracle: 1 database per instance; no limit on number of schemas per database imposed by software

RDS for SQL Server: 30 databases per instance

RDS for PostgreSQL: No limit imposed by software

1
votes

You wouldn't need to use any SDK for RDS, as you are not really modifying the instance in any way. The instance will always be running, and you just want to create new database schemas on that instance. This would be done using the SQL connector library you are using in your Java code (or could be scripted in another language such as Perl or Python for example).