Peekaboo (Interview Questions) - Azure SQL Server DBA

Interview questions and answers for an Azure SQL Server DBA (Database Administrator):

What is Azure SQL Database, and how does it differ from SQL Server on-premises?

Azure SQL Database is a cloud-based relational database service provided by Microsoft Azure. It is built on the SQL Server engine and offers a fully managed platform for hosting and managing databases in the Azure cloud. The main differences between Azure SQL Database and SQL Server on-premises are:

Azure SQL Database is a cloud-based service, while SQL Server on-premises is installed and managed locally within an organization's infrastructure.

Azure SQL Database provides scalability, high availability, automated backups, and other cloud-specific features, whereas SQL Server on-premises offers more control and customization options.

Azure SQL Database has a different pricing model based on performance tiers, while SQL Server on-premises typically requires upfront licensing costs.

How do you create an Azure SQL Database?

To create an Azure SQL Database, you can follow these steps:

Sign in to the Azure portal.

Click on "Create a resource" and search for "SQL Database."

Provide the necessary details, such as database name, server name, resource group, and select the appropriate pricing tier and configuration options.

Configure additional settings like collation, security, and connectivity.

Review the summary and click on "Create" to provision the database.

What are the different deployment options for Azure SQL Database?

Azure SQL Database offers two deployment options:

Single Database: This option allows you to create individual databases with dedicated resources and control over their configuration settings.

Elastic Pool: An elastic pool allows you to manage and share resources (such as compute power and memory) across multiple databases. It enables better resource utilization and cost optimization.

How do you scale up and scale out an Azure SQL Database?

Scaling up an Azure SQL Database involves increasing its performance level by upgrading to a higher pricing tier with more resources. You can do this through the Azure portal, Azure PowerShell, or Azure CLI.

Scaling out an Azure SQL Database involves partitioning the data across multiple databases or adding read replicas to distribute the workload. This can be achieved using technologies such as Elastic Pools or database sharding.

How do you ensure high availability in Azure SQL Database?

Azure SQL Database provides built-in high availability features, including:

Automatic backups: Azure SQL Database takes automated backups, allowing point-in-time restores within the retention period.

Automatic Patching: Microsoft handles the patching and maintenance of the underlying infrastructure, ensuring that the database remains up to date.

Active Geo-Replication: This feature enables asynchronous replication of the database to secondary regions for disaster recovery and read-only access.

Failover Groups: It allows automatic failover to a secondary region in case of a primary region outage.

Geo-Restore: Azure SQL Database enables restoring a database to a different Azure region in the event of a region-wide outage.

How do you monitor and optimize performance in Azure SQL Database?

To monitor and optimize performance in Azure SQL Database, you can utilize various tools and techniques, including:

Azure Portal: Use the Azure portal's monitoring and performance insights to analyze database metrics, query performance, and resource utilization.

Dynamic Management Views (DMVs): DMVs provide valuable information about database health, query execution, and resource usage.

Query Performance Insight: It offers query performance metrics and recommendations to optimize query execution.

Index optimization: Analyze and optimize database indexes to improve query performance.

Query tuning: Use execution plans, statistics, and query hints to optimize query performance.

Azure SQL Analytics: It provides advanced monitoring, troubleshooting, and diagnostics for Azure SQL Database.

How do you implement backups and disaster recovery for Azure SQL Database?

To implement backups and disaster recovery for Azure SQL Database, you can utilize the following approaches:

Automated backups: Azure SQL Database automatically takes regular backups, including full backups, differential backups, and transaction log backups. The backups are stored in Azure Blob Storage, allowing point-in-time restore within the retention period.

Long-term retention backups: Azure SQL Database enables you to store backups for an extended period by configuring long-term retention policies.

Geo-Replication: Implement active geo-replication to replicate the database to secondary regions asynchronously. This provides a disaster recovery solution and allows failover in case of a primary region outage.

Failover Groups: Configure failover groups to automate the failover process in case of a disaster. It ensures continuity of operations by redirecting traffic to a secondary region.

Export/Import: Use the export feature to export a database to a BACPAC file and store it in Azure Blob Storage. You can later import the BACPAC file to restore the database.

How do you secure Azure SQL Database?

To secure Azure SQL Database, you can implement the following security measures:

Firewall rules: Configure firewall rules to allow access only from trusted IP addresses or Azure resources.

Virtual Network service endpoints: Use virtual network service endpoints to secure database access within a virtual network.

Authentication: Implement strong authentication methods, such as Azure Active Directory authentication or SQL authentication with strong passwords.

Encryption: Enable transparent data encryption (TDE) to encrypt data at rest. Additionally, you can use Always Encrypted to encrypt sensitive data in transit and Azure Key Vault for managing encryption keys.

Auditing and threat detection: Enable auditing to track database activity and implement Azure SQL Database's built-in threat detection capabilities to identify potential security threats.

Role-based access control (RBAC): Assign appropriate roles and permissions to database users to ensure least privilege access.

Regular patching: Keep the database up to date by applying regular patches and updates provided by Microsoft.

How do you troubleshoot and resolve performance issues in Azure SQL Database?

To troubleshoot and resolve performance issues in Azure SQL Database, you can follow these steps:

Identify the problem: Analyze performance metrics, query execution plans, and resource utilization to identify the source of the performance issue.

Query tuning: Optimize poorly performing queries by reviewing the query execution plans, analyzing indexes, updating statistics, and rewriting the query if necessary.

Index optimization: Review and optimize database indexes to improve query performance.

Resource scaling: Consider scaling up or out the Azure SQL Database resources to handle increased workload demand.

Azure SQL Analytics: Leverage Azure SQL Analytics for advanced monitoring, troubleshooting, and diagnostics to identify and resolve performance issues.

Performance monitoring: Continuously monitor performance metrics and alerts to proactively detect and resolve performance bottlenecks.

How do you handle database schema changes in Azure SQL Database?

To handle database schema changes in Azure SQL Database, you can follow these best practices:

Use version control: Implement a version control system (such as Git) to track and manage database schema changes.

Database deployment tools: Utilize tools like SQL Server Data Tools (SSDT) or Azure DevOps to automate the deployment of schema changes and maintain consistency across environments.

Change management process: Establish a well-defined change management process that includes code reviews, testing in a non-production environment, and proper approvals before deploying schema changes to production.

Rollback strategy: Have a rollback strategy in place to revert schema changes if issues arise during deployment.

Continuous integration and continuous deployment (CI/CD)

I hope learning was good today, right?! I would love to know your thoughts.