High Availability and Scalability ...

We have a multi-lingual website (English, Spanish and German). We have a table called Posts that is potentially getting really big.

We are in the initial design phase of the database and would like to know what the experts are suggesting to keep our database mean and lean in the long run.

We have been talking about splitting the database up into 3 separate databases, one for English, one for Spanish and one for German. The language specific databases would also be hosted in countries where the language is spoken eg. the German database would be hosted in Germany.

Or maybe database partitioning by language?

Making changes to 3 databases once launched seems like a nightmare. It would be nice to have one main database and maybe 2 (Spanish and German) “satellite” databases or something like that – any ideas?.

Any suggestions of how to deal with this problem the best way would be greatly appreciated. We are using SQL Server 2005.

Newbie!

moving out of replication forum and into TSQL forum.

What is your business requirement exactly? It's not clear what you mean by "lean and mean", nor is it clear exactly what problem you think you're going to have. Disk space? Performance?

|||

i think you need to design an application that service these different languages.

there are two approach that you can use. one is to have a central database that holds all the data. the other is to partition this into different site and database.

for the first approach say you are to design an inventory database. for your product table you will a design such as these:

product_id

barcode

productname_english

productname_spanish

productname_german

quantity

the advantage of this design is that

1. you have a live count of your inventory without querying so many database.

2. you use less machine

3. administered by less people

the disavantage of this design is

1. its difficult to implement.

2. there is no site autonomy

3. if the system fails every site fails.

4. difficult to maintain

the second approach is to break the database into a per site basis. that is you have an english database, spanish database and german database. if this approach is taken i suggest also that there must be a separate system for each language.

the advantage of this approach

1. there is site autonomy

2. easy to implement

3. faster site query due to less data.

the downside

slow query for comprehensive and overall reports

need lot of people

need several machine and server.

after some consideretion i would suggest that the second approach be taken due to the following reason. machine and people are cheap if it is to be compared with the confusion between people and the importance of autonomy of each site.

furthermore after implementation of OLTPs on different site i suggest a datawarehouse that shall consolidate data from different sites

regards

|||

We are worried that our posts table (blog entires, forum entries and pictures, everything is a post in our system) is getting too big with all the posts from the different languages. We are thinking of ways that we could avoid our performance going down when a German speaking user is coming to our site and we only need to show content in German. If all the languages are together in the same table we worry that the queries would take a long time to find the language specific content.

So we are debating if we should have a separate database for each language or if we could somehow have one main database in the United States for example and 2 satellite databases in different countries. The satellite databases which would hold only the records for a specific language for example German in Germany would once a day synchronize their data with the main database.

I hope this makes it a little clearer what our dilemma is.