CODEWITHSUNDEEP
c#sql-serveramazon-rds
kSeudo
kSeudo

Reputation: 619

Automating the creation of a database in SQL Server hosted in Amazon RDS

I have been doing some research with SQL Server hosted in Amazon RDS. I am planning on having an application that requires multi tenancy solution - one database per customer with a core database for login etc.

So Im playing with RDS/SQL Server but there are come problems I need to overcome. Firstly how do automate the creation of a database? I RDS I cannot just restore from a bak file and I want to create database schema from template. Does anybody have any experience with this and if so can you guide me with some tips?

Thanks,

kseudo

Upvotes: 2

Views: 1516

Answers (1)

Chris Mann
Chris Mann

Reputation: 36

Generate Scripts is a good starting point for this but if you truly want to automate the database creation you'll want to convert the script that is output from SQL Server's Generate Script into a template.

A good way to do this would be to create a .NET application or even just a stored procedure that will do a find and replace on the script once you've made it into a template.

  1. Create your template customer database with all the objects you want in it and then script out the database (all database objects).

  2. In C# or in SQL build a dynamic query by including a variable in every object name:

     CREATE DATABASE CustomerDB_1 ...

 "CREATE DATABASE CustomerDB_{0}" //Formatted String in C#
 'CREATE DATABASE CustomerDB_' + @vchCustomerID //Dynamic SQL

    If every object name has the customer ID in it and every database has a unique customer ID, then you can simply run the query after replacing the variables with the appropriate values and you're all set.

Upvotes: 1

Related Questions