Best approach to group databases within an instance.

Question

Best approach to group databases within an instance.

Maxalama-RC

Grasshopper

Points: 21
More actions
January 25, 2013 at 11:49 am

#269477

Hi All,
The project I am currently working for is going to have multiple apps each pointing to a separate database. Some examples: Case Mgmt, Data Mining, Chat etc. Of course the sizes of the databases are going to be different, as well as the usage. There are not going to be separate servers, but rather different instances with multiple databases. What would be a good practice for grouping these databases in order to maintain a high performance ? Also, a reference to a good documentation would be very much appreciated. Thanks in advance for your help.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

maxalama-861292 (1/25/2013)
Hi All,
The project I am currently working for is going to have multiple apps each pointing to a separate database. Some examples: Case Mgmt, Data Mining, Chat etc. Of course the sizes of the databases are going to be different, as well as the usage. There are not going to be separate servers, but rather different instances with multiple databases. What would be a good practice for grouping these databases in order to maintain a high performance ? Also, a reference to a good documentation would be very much appreciated. Thanks in advance for your help.

Can you explain more clearly what the plan is? Are you creating a new sql instance for each application? I don't think I understand what you mean by grouping your databases.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Maxalama-RC Grasshopper Points: 21 More actions · Answer 2

There are going to be 8-10 databases, but not all of them with its dedicated instance; maybe we have to group these databases on 3-4 servers. Four databases will be around 1-2 Tb in size each, the rest much smaller. Also, I'm thinking of the usage of the databases: read vs write. I can see the reasoning behind grouping some of the databases based on the usage. But what about the size ?

Lowell SSC Guru Points: 323755 More actions · Answer 3

i hope you mean multiple databases on a single SQL Server installation, and not multiple SQL Server Installations, one for each "application"

If there are going to be any reports or functionality that need data from more than one of those databases, i'd recommend a second look at using a single database shared between all the applications. it might make more sense to merge them, or it might not;

the downside is merged database make each application kind of need to release all together, so the schema changes match the expected application requirements.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Maxalama-RC Grasshopper Points: 21 More actions · Answer 4

Correct: it will be multiple databases within one instance. Each instance will be on a different server, so one instance per server.