Best Practice for Identical Multiple Databases

[EriRobert]

Hello All

I am developing a system where I will have multiple databases on the same server which share the same structure (but contain differing data).

What is the best method for handling the identical stored procedures which will live along side each database?

I'm concerned that the sps are duplicated mulltiple times and therefore on a upgrade of the system I have to copy the new set of sps across 20 or 30 databases. Is this the best approach to my problem or is there some way of having a single set of sps which all the databases can reference. I suppose that if this method is possible then the sps wouldn't be optimised for the data they would be used on, but duplicating the sps seems unnatural!

I would welcome any advice from people with this sort of setup.

Thanks


Robert

 

[hmckillop]

Depends on you hardware and the size of data you have in your databases, but I would keep the data all in the same single database, and ensure that the data structure is amended/designed in a way that ensures indexes get to the data required very quickly.

On a different point, I would guess that an optimised stored proc for one database shouldnt differ that much from the same proc on a different database as they do exactly the same work and probably will have a relatively similiar data pool.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[EriRobert]

Thanks for that hmc

The databases in this case represent different clients so it seems natural to keep them distinct - mixing rival client data so close might be seen as too close!

Robert

 

[jgoodman00]

I have faced this exact same problem recently. You want to partition the data & have a single DB, but your clients might not be too happy about this situation.

You are therefore forced to adopt a seperate DB approach.

I also thought about creating a seperate DB containing the sp's, which then dynamically connect to the correct DB. However, this will remove the optimizer as the sql will need to be dynamic sql, & you also get permission problems. All users will need the execute permission on the sp's, but you will also need to manually set the relevant permissions on the underlying objects, rather than using permission chaining.

I eventually came to the same conclusion as you, & have decided to use scripting to update the DB objects.

It seems to be making the best of a bad situation. :-/

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[EriRobert]

It's good to have someone who has been through the same process confirm that there's no real alternative.

Thanks everyone

Robert NOWT, REALLY

 

[EriRobert]

Hello again

I'm now implementing this solution and thinking about the best method of copying the amended stored procedures across to the live databases on release of a new revision.

I'm supposing that DTS is the best route to go down here as opposed to scripting. I've done a bit of DTS but wonder if I can it working in the way I want:

DTS seems to want a me to hardcode the source and destination representing the copy. Although the source is fixed the destinations vary as new databases are created and dropped - I don't really want to have to alter the copying DTS package when a new database is created.

I have a table containing the names of the destination databases. Ideally I would want to scan this table and for each database drop all the stored procedures and copy in the replacement ones from the fixed source.

Is this sort of thing possible?

Robert