multi databases or partitions

Hi all

i have 3 million customer stored in a billing system.

currently i dived them to 40 database running sql2000.

i plan to migrate to sql 2005.

Is it better to let all customer in one partioned table based on city.?

or divide them one database for city.

taking into account there is 15 million transaction per month.

i want to get best performance and maintainance for backup.

thanks

Hi,

I would go for the partioned table. It is easier to remain within 1 database. I think there is more overhead in going to multiple databases anyway. With the partioned tables in 2005, you can span your data on multiple disks.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi Geert Verhoeven

Thank you for reply.

I use one storage Raid-5 for database storage composed of of 8 disk*73GB . In that case ,can that fullfill "span data on multiple disks".

Is there overhead for table partition compared to multi databases.

Just i want to know the side effect before starting merging data in one database using partitioned tables.

thanks

|||

Hi,

Indeed since you are using Raid, the spanning multiple disks is not an argument. Partioned tables can be processed by different processors but that is the same for multiple databases.

I still think that multiple databases give you more overhead then remaining in one database but it is hard to tell. Either way, I don't think that partitioned tables will be slower than multiple databases. Partitioned tables are easier to manage then multiple databases.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Since you have 40 different databases, I assume you want to be able to backup a portion (i.e. database in this case) and also that your data for one city does not interact with data in another city. With table partitioning, you can get the same convenience. you can partition by the city. You can possibly put each partition in its own file group there by mapped to diiferentg disk, you can backup/restore at filegroup granularity. Since partitioned table is managed as one logical unit, you won't need to worry about making schema changes in 40 databases, for example if you want to create an index.