April 23, 2004 at 10:45 am
This is a best practices DBA / Application design Question
Here’s the scenario:
Currently have multiple MS Access Backend/FrontEnd apps built to handle separate operational duties in the company. They share a lot of information back and forth.
Now -> Moving to SQL Server
What is the best practice for Database separation:
Option 1.) Since the Apps share so much information, we can create one big database and just use a good object naming convention so it’s easy to identify what processes the objects support
<- Or ->
Option 2.) Create multiple databases (to support each operational function) and just develop the applications with full Database+User+ObjectName qualifications to use the different databases.
For reference: these databases will all live on the same server, in the same server instance.
What will give the best performance?
What will give the best recovery model? It seems that Option 2 gives great flexibility should a database have problems.
April 23, 2004 at 11:01 am
I would run option 2 with all the db's on full recovery...this gives you the advantage of having seperate filegroups for each of the apps (even though you could use filegroups within a single db for this), and of course recoverability. Bear in mind that if you had to restore a database due to a problem it would mean that jsut a single app was unavailable, whereas with a single db you lose that then you lose everything unti you are up and running again.
April 23, 2004 at 1:59 pm
It's hard to make a good recommendation from a very limited description. If these are small dbs, I'm not sure the recovery time and all app availability matters. If we're talking MBs, even 10s, we're in the minutes of restore time. That being said, different groups or clients have different tolerances for downtime.
I might be inclined to do something like Andy Warren does. The shared information from his dbs is replicated from a master and then each client has their own tables in each db. This way he has central updates for many things, which get replicated out, but individual apps have their own data separated out.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 26, 2004 at 7:14 am
I've not noticed a difference splitting things out. The biggest caution to consider is if you need to do a restore, making sure you remain transactionally consistent across db's. As long as you can restore any single db, you're fine. I like to keep stuff in a single db until I see a reason to split stuff - easy enough to do if it comes to that, replace tables with views to other dbs. Not sure there is a best practice here, it's knowing your environment and trying to guess growth and direction.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply