When to separate 2 databases

When do you decide two databases cannot be together and how do you decide/justify to separate them. Please let me know if anyone has a checklist. Thank you.

more ▼

asked Oct 15, 2011 at 05:38 AM in Default

avatar image

434 33 33 38

How do you have these 2 databases "together" now? Is there any particular situation you are having with them now that brings you to ask this question? [Adding some of this info to your question may get more detailed responses...]

Oct 16, 2011 at 01:00 AM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

If your amount of data is not totally extreme (we're talking terabytes here) I wouldn't recommend it out of a pure capacity perspective. If you think historical data should move to its own database, I would rethink, and instead create filegroups on cheaper, slower disk for historical data (given that it's not queried as much).

If it's for security management that you're thinking of splitting, I would manage it through different schemas instead (unless you already manage permissions on a more granular level, but I doubt that).

If you have run into performance problems, you could follow the advice from @Fatherjack and either replace the hardware with better hardware, or consider clustering. The former might be expensive, and the later might be both expensive and complicated. But managing two databases instead of one is also complicated and expensive (labour hours are not for free). Make sure you have done what you can to make the most of your current hardware: Keep your indexes defragmented, keep your statistics up to date, make sure you have the indexes you need, make sure your queries are efficient, make sure your application is making use of the fact that SQL Server is a database server (so that the application is not "downloading" data to perform joins in dataset relations etc).

Could you spill some more information on your problem? There's no general advice to split databases when this or that scenario occurs. And it's hard to give more specific advices without knowing something about your specific problem.

more ▼

answered Oct 15, 2011 at 11:16 AM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

(comments are locked)
10|1200 characters needed characters left

I would be very surprised if you find a checklist for this sort of decision, it will need individual consideration with every different case.
Why do you think your database might need to be split?
Some common factors might be:
- capacity of hardware to cope with system activity, but you could implement clustering or move to better hardware to resolve that.
- security might be easier to manage/implement on a pair of databases rather than one but making it easier may also make it a bigger task as you have more systems to manage.

more ▼

answered Oct 15, 2011 at 09:42 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left

It depends! One obvious reason is need for different SQL server version and you can't upgrade one of them because the application doesn't support the new version.

more ▼

answered Oct 16, 2011 at 12:58 PM

avatar image

Håkan Winther
16.6k 38 46 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 15, 2011 at 05:38 AM

Seen: 1064 times

Last Updated: Oct 15, 2011 at 05:38 AM

Copyright 2018 Redgate Software. Privacy Policy