Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-06-28 : 07:41:47
|
| James writes "HelloWe have a knowledge management web app that needs to create a new database whenever a new 'instance' of the application is created (ie. when a company subscribes to the service)This means that we will have one database per customer.Each database created is a perfect copy of the structure (no data) of the app's "master" database - this DB contains all the required tables, triggers, SPs etc. All new databases are children of this master database and all child databases (at the moment of creation) are identical to each other.I've tried using the 'Generate SQL script' option for creating the SQL to build new databases but it doesn't seem to allow for the dynamic database name creation that I need for each customer. I guess I could do a search and replace all hard-coded instances of a DB name in the SQL script with a dynamic variable name, put this into a SP in the master DB and send it the appropriate name at runtime. Is that a feasible approach?Also, I'd like to 'share' stored procedures, but I don't think this is possible. Perhaps someone could shed some light on this. In other words, I'd like to make changes to the app's master database, test it and when I'm happy that all is well, automatically update the databases that have already been created AND ensure that any new DBs created also contain the changes. Al child DBs must always be schematically/structurally identical to each other.Perhaps I'm barking up the wrong tree with this approach, so any guidance is very much appreciated.Warm regards, James" |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-28 : 08:36:55
|
| Could you restore a "know good" backup into teh new database to kick it off?To deploy your changes I expect you will need a script.All the changes we make to datbase sctructure are stored in files on the fileserver (PATCH_01.SQL, PATCH_02.SQL ...). Each "Patch" stores its Version number in an "Updates" table - so a SELECT MAX(Version) FROM UpdateTable tells us the latest installed patch. We then run, in order, all the patches since then.(In Enterprise Manager if you RightClick a table and choose "Design" there is a little Scroll+Floppy icon ("Save Change Script") which will give you a script of any changes you make to a table [BEFORE you press SAVE!!], and at the bottom is an "Automatically generate change script on every save" check box which will ensure that you get the change script even if you accidentally press SAVE!)We do the same for the SProcs and Triggers. Each one is in a separate file; (we never use "Properties" in EM to change them, or Object Browser RightClick in QA) each of our scripts "inserts" a row in the Updates table. From that we can work out which ones have been modified/added since the last release and we bundle those into a single file to freshen the target databases.Kristen |
 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-06-28 : 08:38:39
|
| You could set up the model database in the way it should be then all newly created databases will follow the pattern.-------Moo. :) |
|
|
|
|
|
|
|
|
Sharing SPs and dynamically creating databases