Very large tables are large because they’re holding data that’s not only current but historical. Companies are keeping more data around longer and wanting to better evaluate that data for trends over time. These larger-and-larger data sets become difficult to deal with as there are many distinct and often conflicting access patterns, from the critical “hot” data needing to be inserted, to the somewhat recent “read-mostly” data that’s still fairly active, to the older data that’s rarely used but still needed. All of this is “sales” data and it seems appropriate to have it stored in our “sales” table – but how do we do this efficiently and effectively so that modifications do not affect queries and queries do not affect modifications. Worse yet, maintenance is needed for the volatile data but not the older / static data so how can we do this on our large tables when really only a small portion of the table needs maintenance.
But, it’s really a lot more than this. There have long been features (partitioned tables since SQL Server 2005, and partitioned views since SQL Server 7.0) that tackle various aspects of the “VLT” problem but each feature has problems in and of itself. What this class does is directly address all of the problems to show you a realistic and powerful architecture that gives you the best of all worlds: scalability, maintainability, and high-availability.
This class is delivered live via online streaming.
Instructor: Kimberly L. Tripp
Ready to register? Please see our Immersion Events Schedule for class dates.
Module 1: Horizontal Partitioning Strategies
The first module has us looking at the problem space and the primary areas of interest – the workload and the varying access patterns that drive our partitioning strategies and architectures. Topics covered include:
Module 2: Partitioned Views
While partitioned views are the original and “old way” of partitioning – first available in SQL Server 7.0 – there are still many reasons to consider their use. This module will introduce them as well as discuss their requirements and limitations. Topics covered include:
Module 3: Partitioned Tables
Partitioned tables are the “newer” way of partitioning – available from SQL Server 2005 onward – and while they are the newer feature with many benefits, there are still limitations (even in SQL Server 2016 and SQL Server 2017) that make fully understanding their architecture critical for VLTs. Partitioned tables are a bit more complicated to learn as there are many features that are designed for them. This module will introduce the structures and the syntax for creating them as well as their requirements and limitations but later modules will cover some of their complementary features. Topics covered include:
Module 4: Tables, Indexes, Keys, and Relationships
Verifying relationships, defining keys, and knowing exactly where your data resides becomes more and more difficult when you have more complicated architectures. Additionally, creating indexes becomes more important. In this module we’ll look at the concerns and considerations you’ll have to make with your existing structures to support your desired partitioning strategy. Topics covered include:
Module 5: Implementing the Sliding Window Scenario
As time moves forward, data changes. New data is added and old data needs to: be deleted, be archived, or just become less and less used. How do you deal with this? Topics covered include:
Module 6: Key Partitioning Concerns and Considerations
Even with everything we’ve discussed there are still other related features that might give you grief, that’s where this module comes in. We’ll go over some of the special considerations and even some of the things that are often confused with partitioning (like filtering) and why they may or may not be used in some situations. Topics covered include:
Module 7: Partitioning Techniques Combined
Ultimately, the most effective partitioning solutions are those where a layered approach is used. This architecture combines the best of both features and subsequently resolves all of their negatives. Topics covered include:
Module 8: Review
We will have covered a lot of content in our course but what should you do from here before you implement a partitioned solution? I’ll give you an ordered set of scripts and written exercises/lab that will give you a practical approach to partitioning so that your adequately prepared for your next VLT discussion!
Ready to register? Please go to the class page for the class cost, logistical details, and the registration link.
If you have any questions not answered by our F.A.Q., please contact us.