Answered Splitting Database

Potish

Member
I have a database and some of the tables have fairly static business content. I would like to split the database into two with one for static business content and another for dynamic customer content. I have a dump and load utility I use for creating backups of the database. The static and dynamic tables and indices on the current database are currently under separate data areas.

1. Is there a recommended process for splitting/breaking a database into more than one?

2. Could I use the dump and load utility to split the databases by editting the schema.df and dbstrct.st files to create new versions for each database. Are there risks with using this method?
 

TheMadDBA

Active Member
Step 1.. don't split the databases. Unless they are terabytes in size there is little value to it and just more complications.

You are seriously much better making sure your data and index areas (Type II right?) are properly set up and that you are taking advantage of -B and -B2 properly.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I would like to split the database into two with one for static business content and another for dynamic customer content.
To solve what problem?

I agree with TheMadDBA. In one well-configured database, it can worry about managing the application's working set in a single cache and all you have to do is size it appropriately. Even if you can't cache everything you need to, you can use ABP to ensure that hot static objects remain memory-resident. Hopefully you are on a recent release.

If you split, you have to guess right, now and in the future when users, code, and the business change their behaviour, how to split system resources between the two.
 

RealHeavyDude

Well-Known Member
There might be another serious downside to split tables across multiple database. That is when a business transactions updates tables from multiple databases. If you are not using two-phase commit, which I haven't seen being use by anybody so far, you could corrupt your data. A transaction is atomic for a database - so if more than one database is involved in a transaction, neither database does know about the transaction status of the other. So you could have a transaction that is committed on one database but rolled back in another database and you have effectively corrupted your data.

Plus, I agree on all the above.
 

Cringer

ProgressTalk.com Moderator
Staff member
Our application has 9 databases and I seriously wish I could merge them into one today. In addition to all the points already made, I have to perform all maintenance activities 9 times for each client.
 

TomBascom

Curmudgeon
You mention that you use dump & load as your backup tool... perhaps you could elaborate on that. If true that is 1) highly unusual and 2) possibly dangerously ineffective.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Our application has 9 databases and I seriously wish I could merge them into one today. In addition to all the points already made, I have to perform all maintenance activities 9 times for each client.
Just do it and see if development even notices. :p
Just kidding (mostly). If their code is written properly they shouldn't care.
 

Cringer

ProgressTalk.com Moderator
Staff member
Unfortunately there are a few issues, such as the db name prefixing tables in a lot of cases, and overlaps of table names. Plus the fact half the tables aren't even used anymore so it would be better to have a formal exercise and working out what is and isnt' used and purging.
 
Top