This post summarizes key concepts from Database Administration 361, focusing on data management policies, SQL Server 2022 capabilities, and the responsibilities shared between Microsoft Azure and a Database Administrator (DBA) in Azure SQL Database environments.
Data Management Policies
Organisations rely on several policies to manage, protect, and maintain their data effectively.
- Data Usage Policy: Defines how data should be accessed and used responsibly by authorised users to ensure compliance and integrity.
- Data Security Policy: Focuses on protecting data from unauthorised access through authentication, encryption, and monitoring controls.
- Backup and Recovery Policy: Ensures regular backups are created and data can be restored in case of accidental loss or system failure.
- Disaster Recovery Policy: Covers restoring entire systems, including infrastructure, applications, and data, after major disruptions.
- Change Management Policy: Governs how system changes are requested, tested, approved, and implemented to avoid instability.
While each policy has a different focus, they all work together to ensure data availability, security, compliance, and reliability within an organisation.
SQL Server 2022 Capabilities
SQL Server 2022 provides a wide range of features that support administrators and users in managing modern database systems effectively.
- Security and Authentication: Supports encryption (Always Encrypted, TLS), role-based access control, and multiple authentication methods including Azure AD integration.
- High Availability and Disaster Recovery: Features such as Always On Availability Groups, Failover Cluster Instances, and log shipping ensure system uptime and data resilience.
- Performance Monitoring and Tuning: Tools like DMVs, Query Store, and Extended Events help monitor workloads and optimise query performance.
- Optimised Data Storage: Supports data virtualization, cloud integration, and improved storage management for better efficiency and scalability.
These features enable DBAs to maintain performance, ensure security, and support scalable and reliable database systems.
Separation of Duties in Azure SQL Database
When moving to Azure SQL Database, responsibilities are shared between Microsoft and the organisation’s DBA under the shared responsibility model.
- Infrastructure & Maintenance: Microsoft manages hardware, networking, patching, and platform availability. The DBA manages logical database objects and configurations.
- Security: Microsoft secures the infrastructure, while the DBA manages access control, authentication, and data-level security.
- Backup & Recovery: Microsoft provides automated backups, while the DBA defines retention policies and performs restore testing.
- Monitoring & Performance: Microsoft provides monitoring tools, while the DBA analyses performance, tunes queries, and optimises workloads.
- Configuration & Development: The DBA handles database design, schema creation, and support for application development.
- Disaster Recovery & High Availability: Microsoft ensures built-in availability, while the DBA configures geo-replication and disaster recovery strategies.
- Governance & Compliance: Microsoft maintains platform compliance, while the DBA enforces organisational policies and auditing.
Conclusion
Database Administration involves more than just managing data - it requires understanding policies, leveraging platform capabilities like SQL Server 2022, and clearly defining responsibilities when operating in cloud environments such as Azure SQL Database. Together, these elements ensure secure, reliable, and efficient database systems.
Thanks for reading! Want to discuss this topic?
