February 20, 2009 at 1:56 pm
I have a user who just posed the question as to whether or not I could house a new instance on my SQL Server 2005 clustered environment that would hold a database that contains approximately 50 million records. He said the database would be updated with at least that many more yearly. At this team, he does not know how long of a retention he would have. His team would like to develop a Coldfusion application against it to report against those records. As a DB2 and SQL DBA, I suggested DB2 for this large of volume processing. However, he's asking about SQL Server. Is it feasible to say that he could run queries against a SQL database of this size and get results quickly, assuming good indexing, etc.? The current environment already has a default instance with about 90 databases and 8 other instances, each with only about 4 databases. I realize a database of this size would require its own instance, but I was wondering if anyone else has attempted to create such large databases with successful user processing.
February 20, 2009 at 2:05 pm
Shane Miller (2/20/2009)
I have a user who just posed the question as to whether or not I could house a new instance on my SQL Server 2005 clustered environment that would hold a database that contains approximately 50 million records.
50 million? Is that all?
I've had a database where one table had 4 times that in total and we loaded 2 million a day.
How many GB in total?
Assuming reasonably good indexes and a server less than about 5 years old and SQL will handle a db with 50 million rows in it without any problems.
Now 90 databases on an instance and 9 instances on a server is not a good idea. Too much contention for resources (unless all of those DBs are tiny or barely used)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2009 at 2:06 pm
SQL Server can handle that amount of data.
I'm sure there are many DBAs that would last at 50 million records being called "large".
Oops, Gila beat me to it.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 20, 2009 at 2:13 pm
Awesome! Yes, almost all of our databases are VERY small, hence the reason I am not used to such questions in regards to SQL. (The DB2 database is a warehouse, so it's our "monster".) 🙂 Our default instance is 400 GB. I think the largest database on it out of those 90 is only 16 GB. Most of those databases are for small web apps. On the same box, the largest instance is 100 GB. The box has been purchased in the last five years. Thanks for the input!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply