Problem solve Get help with specific problems with your technologies, process and projects.

Setting up a SQL server hosting multiple databases

Learn what the requirements are (if any) for setting up a database server hosting multiple databases in a development environment.

What are the requirements for setting up a database server which will be used by various project teams to host their development databases? It will be hosting multiple databases in a development environment.

There are no specific requirements for setting up a database server for certain purposes; instead, you have a lot of things to consider, and in the best cases experience with other setups will lead you to making a good setup. But if you don't have that experience, you can start right now.

Ok, like an administrator, you have to think first of the core objectives of this database server. Most of the time this means taking care of response time and permissions.

Response Time
When you start planning the setup, you have to take into account the size of the databases that you will manage. From there, you will define the size of your disk(s). To do this, think about your network speed combined with the database size so you can choose between an entire disk for each database (that's rare in a development environment) or a shared disk for all of the databases. Because this is for development projects, you will have a lot of connections making X-thing in your server. The process that they will run could be from a simple SELECT statement to complex stored procedures or triggers. And do not forget that they will use different kinds of cursors. That will cause from time to time a lot of overhead in the server, so, my friend, you are going to need at least 256MB of RAM or more, 1GH processor or more.

This is what really matters because you don't want your users to mess with each other's work, so you have to take care to define the users and their permissions that they will have in their own databases. But as a developer, I must tell you that sometimes we need to share some code (mostly stored procedures and triggers) to improve performance and save time in the development. So, just take care when defining the permissions. Talk with the project leaders about what access levels are going to be defined and the objects involved, as well as what objects they want to share with the others and the access level.

Always remember a developer does not care about anything but faster response and processing, and they do not like an outsider messing with their stuff.

For More Information

Dig Deeper on SQL Server Database Modeling and Design

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.