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.

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.

sql

ms-sql to sapdb

hi there,

we have a ms-sql server 7.0 and want to migrate to sapdb on unix.
are there any tools to convert a ms-sql to sapdb, including
stored procedures, keys and views?

tia

stefanHi

I have never heard of sapdb, but you should ask your supplier. Microsoft
provides many tools to port to SQL Server, and there are alot of other third
party tools that will also help, but they are obviously not going to provide
you with assistance if you wish to move to a rival vendor.

John

"stefan" <stefanluedecke@.gmx.de> wrote in message
news:ea0e4b2e.0308022043.73010886@.posting.google.c om...
> hi there,
> we have a ms-sql server 7.0 and want to migrate to sapdb on unix.
> are there any tools to convert a ms-sql to sapdb, including
> stored procedures, keys and views?
> tia
> stefan|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f2cc1e9$0$15038$ed9e5944@.reading.news.pipex.net>...
> Hi
> I have never heard of sapdb, but you should ask your supplier. Microsoft
> provides many tools to port to SQL Server, and there are alot of other third
> party tools that will also help, but they are obviously not going to provide
> you with assistance if you wish to move to a rival vendor.
hi john,

sapdb (http://www.sapdb.org) is an open source db from sap.

with the export tool from ms-sql we are able to export the pure
data, but none of the things arround the db, such as stored procedures,
trigger etc.

with access (the db-frontend from microsoft) we could also transfer
data.

microsoft provide really a lot of tools to migrate to sql-server, but not the
other way ;-))

thank you

stefan|||Ray Higdon <rayhigdon@.higdonconsulting.com> wrote in message news:<3f2d13c1$0$195$75868355@.news.frii.net>...
> Are you talking about transferring data to an SAP database? If so, hehe,
> no simple answer exists. SAP uses business and function calls for all
> data entry, you input data by calling their functions, not going to the
> underlying data. One tool I have seen out there to help with this
> process is the Business Connector (read more here
> http://www.plurb.com/ebXML/Scalable...operability.pdf) it uses XML
> in and out and you can call it by a web service and run it as a Windows
> service. I have a buddy that wrote a paper on the BC, if this is what
> you are talking about I'll see if it is published yet and get you a copy
> if you want.
hi ray,

thank you for your help, but we can transfer data. the problem is
to transfer stored procedures, keys and trigger. and it would be a
few weeks work to do it manually.

stefan|||Hi

It is highly unlikely that your database will support T-SQL, and if it does
it is probably violating copyright. Therefore about all you can do is to
script them and maybe write a few macros to globally convert the syntax,
then it will be a manual task to finish it off.

John

"stefan" <stefanluedecke@.gmx.de> wrote in message
news:ea0e4b2e.0308031042.552d0eb0@.posting.google.c om...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<3f2cc1e9$0$15038$ed9e5944@.reading.news.pipex.net>...
> > Hi
> > I have never heard of sapdb, but you should ask your supplier. Microsoft
> > provides many tools to port to SQL Server, and there are alot of other
third
> > party tools that will also help, but they are obviously not going to
provide
> > you with assistance if you wish to move to a rival vendor.
> hi john,
> sapdb (http://www.sapdb.org) is an open source db from sap.
> with the export tool from ms-sql we are able to export the pure
> data, but none of the things arround the db, such as stored procedures,
> trigger etc.
> with access (the db-frontend from microsoft) we could also transfer
> data.
> microsoft provide really a lot of tools to migrate to sql-server, but not
the
> other way ;-))
> thank you
> stefan