SQL server scalability question_问答_开发者_运维开发者技术经验分享

We are trying to build an application which will have to store billions of records. 1 trillion+

a single record will contain text data and meta data about the text document.

pl help me understand about the storage limit开发者_Go百科ations. can a databse SQL or oracle support this much data or i have to look for some other filesystem based solution ? What are my options ?

Since the central server has to handle incoming load from many clients, how will parallel insertions and search scale ? how to distribute data over multiple databases or tables ? I am little green to database specifics for such scaled environment.

initally to fill the database the insert load will be high, later as the database grows, search load will increase and inserts will reduce.

the total size of data will cross 1000 TB.

thanks.

1 trillion+

a single record will contain text data and meta data about the text document.

pl help me understand about the storage limitations

I hope you have a BIG budget for hardware. This is big as in "millions".

A trillion documents, at 1024 bytes total storage per document (VERY unlikely to be realistic when you say text) is a size of about 950 terabyte of data. Storage limitations means you talk high end SAN here. Using a non-redundant setup of 2tb discs that is 450 discs. Make the maths. Adding redundancy / raid to that and you talk major hardware invesment. An this assumes only 1kb per document. If you have on average 16kg data usage, this is... 7200 2tb discs.

THat is a hardware problem to start with. SQL Server does not scale so high, and you can not do that in a single system anyway. The normal approach for a docuemnt store like this would be a clustered storage system (clustered or somehow distributed file system) plus a central database for the keywords / tagging. Depending on load / inserts possibly with replciations of hte database for distributed search.

Whatever it is going to be, the storage / backup requiments are terrific. Lagre project here, large budget.

IO load is gong to be another issue - hardware wise. You will need a large machine and get a TON of IO bandwidth into it. I have seen 8gb links overloaded on a SQL Server (fed by a HP eva with 190 discs) and I can imagine you will run something similar. You will want hardware with as much ram as technically possible, regardless of the price - unless you store the blobs outside.

SQL row compression may come in VERY handy. Full text search will be a problem.

the total size of data will cross 1000 TB.

No. Seriously. It will be a bigger, I think. 1000tb would assume the documents are small - like the XML form of a travel ticket.

According to the MSDN page on SQL Server limitations, it can accommodate 524,272 terabytes in a single database - although it can only accommodate 16TB per file, so for 1000TB, you'd be looking to implement partitioning. If the files themselves are large, and just going to be treated as blobs of binary, you might also want to look at FILESTREAM, which does actually keep the files on the file system, but maintains SQL Server notions such as Transactions, Backup, etc.

All of the above is for SQL Server. Other products (such as Oracle) should offer similar facilities, but I couldn't list them.

In the SQL Server space you may want to take a look at SQL Server Parallel Data Warehouse, which is designed for 100s TB / Petabyte applications. Teradata, Oracle Exadata, Greenplum, etc also ought to be on your list. In any case you will be needing some expert help to choose and design the solution so you should ask that person the question you are asking here.

When it comes to database its quite tricky and there can be multiple components involved to get performance like Redis Cache, Sharding, Read replicas etc. Bellow post describes simplified DB scalability.

http://www.cloudometry.in/2015/09/relational-database-scalability-options.html