russell-mcdaniel / sql-server Goto Github PK

Create an auto-failover group in Azure SQL along with supporting test harness and monitoring scripts.

• Automate creation and deletion of resources. Consider Bicep and PowerShell.
• Create simple application or script to provide workload during failover.
• Create control scripts (e.g. initiate failover).
• Create monitoring scripts.

Implement the category hierarchy using the nested sets pattern in the Demo database.

Explore table partitioning functionality in SQL Server.

Create unit tests to enforce the database design guidelines.

• Primary keys.
• Clustering keys.
• Indexes supporting foreign keys.
• Object naming.

Explore in-memory OLTP functionality in SQL Server.

This is a running list of ideas for enhancing the Learning database and related projects.

Database

• Implement table partitioning. Consider appropriate application of partitions. Review hash partitioning strategy.
  • https://www.brentozar.com/sql/table-partitioning-resources/
  • https://www.brentozar.com/archive/2012/03/how-decide-if-should-use-table-partitioning/
  • https://www.sqlshack.com/database-table-partitioning-sql-server/
  • https://www.sqlshack.com/hash-partition-in-sql-server/

Tooling

Data Generator

• Support continuous data generation.
• Generalize generator classes. There is substantial code pattern repetition.
• Integrate campus into data generation context. There is no stickiness of students and professors to a particular campus within an institution yielding unrealistic data.
• Implement logging to files. Requires third party library.
• Migrate generation parameters to the configuration file.

Query Generator

• Create query generator. Provide continuous workload for performance measurement.

Implement the category hierarchy using the materialized path pattern in the Demo database.

Explore JSON functionality in SQL Server.

Explore the graph database functionality in SQL Server.

Consider implementing the category hierarchy in the Demo database using this functionality.

Some refactoring opportunities exist for the initial version of the data generator:

• Decide database connection configuration approach: Options pattern or connection string.
• Move the generation parameters to the configuration file.
• Use parallel processing. To what level? Institution? Every logically possible entity?
• Integrate Spectre.Console (GitHub).
• Report progress during generation because some steps take significant time.
• Generalize the generator classes.
• Interleave data generation and database insertion. There are some long phases of pure data generation that could overlap with I/O. Could records be fed to the repository using an enumerator with yield at generation time?

Explore temporal table functionality in SQL Server.

This is a running list of business questions to pose using queries to the Learning database.

NOTE: Try Azure Data Studio notebooks for this.

• How many students have declared for each program combination?

• e.g. major-major, major-minor, major-only

• What is the student's average score for their courses?

• Only program courses?
• Only non-program courses?

• Has the student met the requirements for their declared programs?

• Passing score in all required classes.
• The model ignores general education credit requirements.
• The current data generation strategy makes it unlikely for any student to meet their program requirements.

Implement the category hierarchy using the hierarchyid data type in the Demo database.