1

We have been assigned a new application where the vendor is expecting the database to grow to around 100 TB. They have mentioned SQL Server Standard Edition is compatible.

My question is whether SQL Server Standard Edition can adequately handle a database of that size?

The Enterprise Edition can handle online index rebuild, but overall can SQL Server can handle such big database?

The biggest database I have managed is 3 TB and I am worried how a 100 TB database will perform with SQL Server 2019.

Any advises from experts will be greatly appreciated.

Improve this question
1
  • 1
    Whether or not Standard Edition is capable of handling a 100TB database depends on the workload and SLAs.
    – Dan Guzman
    Sep 28 at 15:40

2 Answers 2

Reset to default
3

Some things to think about with databases of this size:

  • Backup/Restore operations are going to take some time. If you are planning to use native backups you might want to look at partitioning and online piecemeal restores. You might also want to explore other options like snapshot backups. Whatever option you decide on, test it regularly.

  • Maintenance operations are also going to be slow and resource intensive. DBCC checks, index maintenance etc. Partitioning might help here also.

  • You probably want to have HA/DR (Availability groups, log shipping etc) to reduce the chance that you need to recover from backup in the case of hardware failure.

  • Cost. Consider the storage for your primary and secondary servers and database backups.

SQL Server can handle databases of this size - I've worked with DBs of up to 160TB. I would recommend doing what you can to reduce the size of the database:

  • If you are storing blobs in the database - store them elsewhere.
  • Consider archiving data
  • Consider data compression
  • Consider clustered column store indexes
  • Use the most optimal data types - really important for table sizes in billions of rows.

You might not have control over a lot of this stuff though if it's a vendor database.

Improve this answer
3
  • Thank you for the recommendation. Unfortunately its a vendor database. Max I can do it to recommend SQL Enterprise instead of SQL standard so that Online indexing saves a lot of time and I can prevent table locking.
    – SQL_NoExpert
    Sep 28 at 16:34
  • @SQL_NoExpert If you don't have any say over hardware provisioning, then table locking won't matter when the database is under extreme resource contention and can't get anything done anyway unfortunately. But understandably there's limited solutions to fixing someone else's database when it's a vendor app. Which at that point, the Vendor should be advising you on the best way to handle index changes and other management tasks. Best of luck!
    – J.D.
    Sep 28 at 18:02
  • If you come up with a viable plan, test it, and present it to the vendor, then you'll meet less resistance. I've done just that with a number of very large databases, and they were receptive. The worst thing they can say is "No". Provided the application(s) don't check for specific filegroups, partition schemes etc., then everything should run normally for the application.
    – Stephen Falken
    Sep 28 at 23:34
-3

Handling a 100TB database with SQL Server Standard Edition can be challenging due to certain limitations inherent in this edition. While SQL Server Standard Edition technically supports databases up to 524 PB in size, there are other factors to consider. First, Standard Edition has limitations in terms of memory utilization and CPU support compared to SQL Server Enterprise Edition, which can impact performance for such a massive database. To effectively manage and optimize a database of this size, you might need to scale up your hardware infrastructure significantly.

Additionally, consider the administrative overhead of maintaining such a vast database, including backup and recovery strategies, indexing strategies, and query optimization. It's advisable to conduct thorough performance testing and closely monitor database operations to ensure that SQL Server can meet the expected workload and response time requirements. Depending on the specific needs and performance expectations, you may want to evaluate whether SQL Server Enterprise Edition or alternative database solutions better suit your requirements for handling a 100TB database. Consulting with experts in database management and scaling may provide valuable insights to help you make an informed decision tailored to your organization's needs and resources.

Improve this answer
1
  • 2
    This sounds like something ChatGPT would say
    – mustaccio
    Sep 28 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.