x

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 '11 at 05:38 AM in Default

ebzm gravatar image

ebzm
423 27 33 35

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 '11 at 01:00 AM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

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 '11 at 11:16 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

(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 '11 at 09:42 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 73 77 107

(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 '11 at 12:58 PM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1816
x672
x84

asked: Oct 15 '11 at 05:38 AM

Seen: 755 times

Last Updated: Oct 15 '11 at 05:38 AM