asked
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.
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...]
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.
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.
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.
