Hi,

We have two databases right now that house miscellaneous small applications. We have them in place so developers can develop small applications in them without going through the database request process and bother the DBAs. This was set up before I got here - so I wasn't involved in the decision to do such a thing. The obvious drawback to this is that a restore would wipe out data for multiple applications. As we move to a new 2005 server we are re-evaluating our methods and would like some input on this - and some other aspects.

I know that we could use filegroups and put objects related to certain applications within them so restore is independent of other apps. Each application has its own user which is granted execute on it's stored procedures.

Are we missing anything here? I'm almost tempted to try to get separate databases created to uncomplicate it. If a developer can spend hours working on an app, they can spend 10 minutes on a form and wait 30 for us to create it.

Another thing I've noticed it that it can take quite a while to grant permissions to many stored procedures to a user in 2005. In 2000 there was a grid and you could arrow down and hit space, granting execute. You could also use code to do this, be we never really needed to since developers granted as they went and we would script the object and check the 'script obect level permissions' checkbox. This has since disappeared and granting execute is another step for us.

What do you think of'sub schemas' for each role in the application- which is usually 'User' and 'Admin'? So we would have MylittleApp_User.ProcedureName and MylittleApp_Admin.ProcedureName with execute granted on the schema for each application user. Tables would be placed in MylittleApp schema.

In the past developers simply prefix their procedure with the application name to denote what application it belongs to.

Thanks for your advice on this - we don't want to get headed down the wrong path.

Hi Sam,

I think you are on the right track here. Splitting an app between user functionality and admin functionality seems very reasonable and putting that functionality into separate schemas makes sense from a management perspective.

Hosting multiple appications on the same database seems sub optimal to me because as you say you can't backup and restore one of the multiple applications without impacting the others on the database. You also run the risk that a flaw in one application can expose data or functionality in another application on the database. Isolating each application to its own database would solve both of these problems.

HTH,

-Steven Gott

SDE/T

SQL Server

Hi Sam,

I think you are on the right track here. Splitting an app between user functionality and admin functionality seems very reasonable and putting that functionality into separate schemas makes sense from a management perspective.

Hosting multiple appications on the same database seems sub optimal to me because as you say you can't backup and restore one of the multiple applications without impacting the others on the database. You also run the risk that a flaw in one application can expose data or functionality in another application on the database. Isolating each application to its own database would solve both of these problems.

HTH,

-Steven Gott

SDE/T

SQL Server

Thanks Steven,

We're goingto try thesplit schemaapproach and see how it goes. Also we are going to separate all these applications into separate databasesas we can't find any compelling reasons why this was done, aside from convenience for the developers.

Sam