Very Large Databases
Are you experiencing challenges managing Very Large Databases (VLDBs), or anticipate challenges with future growth? In this episode, we are joined by Aaron Hayes to discuss the practical advice on managing very large databases in SQL Server, focusing on common problem areas we have found, along with our own experiences. Topics include storage layout decisions, unique tuning challenges, HA/DR implications, and database restores, along with the challenges of getting backups, re-indexing and DBCC checks into your maintenance windows. No comparisons here — we are sure you will enjoy the discussion regardless of your database size.
“It was said to me probably in the best way, that anything that has to deal with size of data operations is going to affect you and can be considered a large database.”
“We ship the logs over to the local standby. We schedule [our applied process] every 12 hours or however long we want. If something comes up, we can stop that apply process, restore to a point in time, grab the table and then copy that over.”
“Once you get to being a large database, you really have to cater around the business value that database adds, the case you can make for your SLAs on it, and then with that, you can adapt it to be the best solution.”
“Just because you think you have a large database, until you feel you’re going to exceed your maintenance window, keep all of your maintenance plans standardized across your Enterprise. Don’t try to overdo it until you need to, and at that point, have a good strategy for it.”
Listen to Learn
00:40 Intro to the guest and topic
01:52 Compañero Shout-Outs
02:27 Public Service Announcement
03:00 What constitutes a very large database?
07:32 Things you need to keep in mind when you have a very large database
10:29 Object level restores without third-party products
12:22 Having a full copy of your very large database means paying for lots of storage
14:29 Pros and cons to SAN snapshots and third-party backups
19:35 Maintenance changes you might have to make with a very large database
22:51 A little bit about statistics
24:00 Knowing your data is the most critical piece
26:55 Just how big are Aaron’s databases?
27:56 Performance changes that occur with large databases
31:27 Migrating and upgrading that very large database – it’s a team effort
33:45 Last thoughts and advice about very large databases
34:58 SQL Family Questions
39:32 Closing Thoughts
Music for SQL Server in the News by Mansardian
Aaron is a DBA manager who spends his days furiously googling answers to Oracle, SQL Server, and Hadoop questions. After spending all his time in college learning Java, he applied for a developer job and was immediately handed a SQL book. He’s worked as an Oracle developer, lone DBA, system integrator, and data architect. When he isn’t trying to figure out how to stay up to date with technology, he can be found running on the trails around Chicago or trying out the new restaurants in his neighborhood.
Meet the Hosts
With more than 10 years of working with SQL Server, Carlos helps businesses ensure their SQL Server environments meet their users’ expectations. He can provide insights on performance, migrations, and disaster recovery. He is also active in the SQL Server community and regularly speaks at user group meetings and conferences. He helps support the free database monitoring tool found at databasehealth.com and provides training through SQL Trail events.
Eugene works as an independent BI consultant and Pluralsight author, specializing in Power BI and the Azure Data Platform. He has been working with data for over 8 years and speaks regularly at user groups and conferences. He also helps run the GroupBy online conference.
Kevin is a Microsoft Data Platform MVP and proprietor of Catallaxy Services, LLC, where he specializes in T-SQL development, machine learning, and pulling rabbits out of hats on demand. He is the lead contributor to Curated SQL, president of the Triangle Area SQL Server Users Group, and author of the books PolyBase Revealed (Apress, 2020) and Finding Ghosts in Your Data: Anomaly Detection Techniques with Examples in Python (Apress, 2022). A resident of Durham, North Carolina, he can be found cycling the trails along the triangle whenever the weather's nice enough.