Designing Database Solutions for SQL Server 2016

Introduction

• Welcome
• Using the exercise files
• What you should know

1. Database Structure

• Translate business needs to structures
• Physical database design
• Leverage file groups
• Understanding RAID arrays
• Develop an indexing strategy
• Upgrade a SQL Server database
• Create additional SQL Server instances

2. Backup and Recovery Solutions

• Backup and recovery strategies
• Database snapshots
• Backup encryption
• Restore tail-log backups

3. Database Design

• Create a database schema
• Use schemas to enhance database security
• Data access and data layer architectures
• Linked server database models
• Automation strategies

4. Table Design

• Create temp tables and table variables
• Store FILESTREAM and FileTable data
• Row and Page compression strategies
• Create table expressions
• Sort data with columnstore indexes

5. Data Management Considerations

• Locking and concurrency strategy
• Create and manage transactions
• Transaction isolation strategy
• Create stored procedures
• Modify stored procedure parameters
• Error handling in stored procedures

6. Database Security

• Introduction to SQL Server security
• Establishing user login roles
• Database and table permissions
• Object owner permissions
• Encrypting data

7. Troubleshooting and Optimization

• Database server maintenance plans
• Verify integrity with console commands
• Repair fragmented indexes
• Database auditing strategies
• Investigate waits and deadlocks
• Introduction to activity monitoring
• SQL Server utility

8. Planning High Availability and Disaster Recovery

• High-availability concepts and options
• Log shipping
• Leverage Always On Failover Cluster Instances
• Work with Always On Availability Groups

Conclusion

• Next steps