beginner question - instances VS. separate databases?

Hi, I hope this is the right place to ask this question.
I'm trying to get started programming databases against SQL Server Express. I'm pretty confident in understanding the techniques of how to access a database, a specific table, retrieve rows, etc. However, I’m having endless frustration in understanding the very rudimentary basics of how the SQL system even works. I know I don’t need this information to program against it on a simple level, but if I want to develop robust applications that I can distribute properly I need to know this, and it just seems so much more complicated than when I used MSDE.

One of the books I have describes how SQL Management Studio can log on to different server instances. Apparently, one can install SQL Server multiple times on the same system, and each one will create a new instance. And under that, each server instance can have multiple databases. But why would someone do that? Why have multiple instances? Why not just have one instance with multiple databases?

This issue leads into my confusion about the hierarchy of objects and the relevant security levels. For instance, it seems to me that I need a username and password to log onto the general server instance, and then another access permission to log onto the specific database. Isn’t this just a lot of unnecessary confusion, or am I misunderstanding something here?

Some clarification would me most appreciated.

One of the primary reasons to install SQL Server multiple times (instances) is to separate out security or other server-wide settings from each other. You can set one SA account on an instance to a particular Windows user, and the SA account on another instance to be a completely different account. You can also change server-wide settings to have different values on different instances.

As far as understanding the multiple features and architectures for SQL Server, you can check out this site. It will lead you through understanding the whole system from end to end.

http://www.informit.com/guides/guide.asp?g=sqlserver

Best of luck - Buck

|||I prefer to minimize number of instances and only break out over several instances when I have to. Reasons for doing that can be think like: Databases requires different collations for system databases Some database requires SQL Server logins, and other can do with Windows logins Other security aspects, making the databases sharing master database not suitable Memory and/or CPU allocation per instance Basic security isn't that difficult once you get used to terminology: Each person need a login. This can be a "SQL Server login" with a login name and password. Or a Windows login, where you grant a user or group in Windows to login. You also need grant a login "permissions" to use a database. You do this by creating a user name in the database for the login. Then you grant things like SELECT permissions to that user. The person doesn't really have to know what user name he has in the database (most likely the same name as the login name), the person will have to know the SQL Server login or Windows login. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:82c3738e-a9e3-472d-9af9-a89f0acc60f7@.discussions.microsoft.com... Hi, I hope this is the right place to ask this question. I'm trying to get started programming databases against SQL Server Express. I'm pretty confident in understanding the techniques of how to access a database, a specific table, retrieve rows, etc. However, I’m having endless frustration in understanding the very rudimentary basics of how the SQL system even works. I know I don’t need this information to program against it on a simple level, but if I want to develop robust applications that I can distribute properly I need to know this, and it just seems so much more complicated than when I used MSDE. One of the books I have describes how SQL Management Studio can log on to different server instances. Apparently, one can install SQL Server multiple times on the same system, and each one will create a new instance. And under that, each server instance can have multiple databases. But why would someone do that? Why have multiple instances? Why not just have one instance with multiple databases? This issue leads into my confusion about the hierarchy of objects and the relevant security levels. For instance, it seems to me that I need a username and password to log onto the general server instance, and then another access permission to log onto the specific database. Isn’t this just a lot of unnecessary confusion, or am I misunderstanding something here? Some clarification would me most appreciated.|||

To be sure, instances are not the only way to control security. They merely provide a *server* level of separation. If you don't need that, don't use instances.

Production server instances are also used to control service pack levels. One application might dictate a certain service pack level; another might need a different one. Only a separate installation on a different system or an instance.

Buck