Azure SQL PaaS Database

  • Author: Ronald Fung

  • Creation Date: 24 May, 2023

  • Next Modified Date: 24 May, 2024

A. Introduction

Microsoft Azure SQL Database is a managed cloud database provided as part of Microsoft Azure. A cloud database is a database that runs on a cloud computing platform, and access to it is provided as a service. Managed database services take care of scalability, backup, and high availability of the database.

B. How is it used at Seagen

As a biopharma research company using Microsoft Azure, you can use Azure SQL Database as a Platform as a Service (PaaS) solution to host your SQL Server databases in Azure, providing a fully managed and scalable database solution for your applications. Here are some ways you can use Azure SQL Database as a PaaS solution:

  1. Database hosting: Azure SQL Database as a PaaS solution can help you to host your SQL Server databases in Azure, providing a fully managed and scalable database solution that can be easily provisioned and managed.

  2. Automatic updates: Azure SQL Database as a PaaS solution provides automatic updates and patches, ensuring that your database is always up-to-date and secure.

  3. Automatic backups: Azure SQL Database as a PaaS solution provides automatic backups and disaster recovery, ensuring that your data is always safe and recoverable.

  4. Elastic scalability: Azure SQL Database as a PaaS solution can scale up or down as needed to meet the demands of your applications, with features such as auto-scaling and elastic pools.

  5. Security: Azure SQL Database as a PaaS solution provides security features such as encryption, access control, and threat detection. It also complies with regulatory requirements such as GDPR and HIPAA.

  6. Integration with other Azure services: Azure SQL Database as a PaaS solution can integrate with other Azure services such as Azure Virtual Machines and Azure Functions. This can help you to extend the functionality of your database solution and create more complex workflows.

  7. Performance monitoring: Azure SQL Database as a PaaS solution provides performance monitoring and tuning features that can help you to optimize the performance of your databases and improve the user experience.

Overall, Azure SQL Database as a PaaS solution can help your biopharma research company to host your SQL Server databases in Azure, providing a fully managed and scalable database solution for your applications, with automatic updates, automatic backups, elastic scalability, security, integration with other Azure services, and performance monitoring. With Azure SQL Database as a PaaS solution, you can simplify your database management, reduce your costs, and ensure the availability and performance of your applications.

C. Features

Azure SQL Database as a Platform as a Service (PaaS) solution provides a range of features that can help your organization to host and manage SQL Server databases in the cloud. Here are some of the key features of Azure SQL Database as a PaaS solution:

  1. Fully managed service: Azure SQL Database is a fully managed service, which means that Microsoft takes care of the underlying infrastructure, maintenance, and management of your database.

  2. High availability: Azure SQL Database provides high availability for your databases, with automatic failover and disaster recovery capabilities.

  3. Scalability: Azure SQL Database can scale up or down as needed to meet the demands of your applications, with features such as auto-scaling and elastic pools.

  4. Security: Azure SQL Database provides advanced security features such as encryption, access control, and threat detection. It also complies with regulatory requirements such as GDPR and HIPAA.

  5. Automatic backups and patching: Azure SQL Database provides automatic backups and patching, ensuring that your database is always up-to-date and secure.

  6. Integration with other Azure services: Azure SQL Database can integrate with other Azure services such as Azure Virtual Machines and Azure Functions. This can help you to extend the functionality of your database solution and create more complex workflows.

  7. Performance monitoring and tuning: Azure SQL Database provides performance monitoring and tuning features that can help you to optimize the performance of your databases and improve the user experience.

  8. Cost-effective: Azure SQL Database is a cost-effective solution for hosting and managing your SQL Server databases in the cloud, with pay-as-you-go pricing and no upfront costs.

Overall, Azure SQL Database as a PaaS solution provides a range of features that can help your organization to simplify your database management, reduce your costs, and ensure the availability, scalability, and security of your databases. With Azure SQL Database, you can focus on your core business activities while Microsoft takes care of the underlying infrastructure and management of your database.

D. Where Implemented

LeanIX

E. How it is tested

Testing Azure SQL PaaS Database involves ensuring that the database is functioning correctly and meeting the needs of all stakeholders involved in the project. Here are some steps to follow to test Azure SQL PaaS Database:

  1. Define the scope and requirements: Define the scope of the project and the requirements of all stakeholders involved in the project. This will help ensure that Azure SQL PaaS Database is designed to meet the needs of all stakeholders.

  2. Develop test cases: Develop test cases that cover all aspects of Azure SQL PaaS Database functionality, including creating and managing databases, managing data, and verifying the data integrity. The test cases should be designed to meet the needs of the organization, including scalability and resilience.

  3. Conduct unit testing: Test the individual components of Azure SQL PaaS Database to ensure that they are functioning correctly. This may involve using tools like PowerShell or Azure CLI for automated testing.

  4. Conduct integration testing: Test Azure SQL PaaS Database in an integrated environment to ensure that it works correctly with other systems and applications. This may involve testing Azure SQL PaaS Database with different operating systems, browsers, and devices.

  5. Conduct user acceptance testing: Test Azure SQL PaaS Database with end-users to ensure that it meets their needs and is easy to use. This may involve conducting surveys, interviews, or focus groups to gather feedback from users.

  6. Automate testing: Automate testing of Azure SQL PaaS Database to ensure that it is functioning correctly and meeting the needs of all stakeholders. This may involve using tools like Azure DevOps Pipelines to set up automated testing pipelines.

  7. Monitor performance: Monitor the performance of Azure SQL PaaS Database in production to ensure that it is meeting the needs of all stakeholders. This may involve setting up monitoring tools, such as Azure Monitor, to track usage and identify performance issues.

  8. Address issues: Address any issues that are identified during testing and make necessary changes to ensure that Azure SQL PaaS Database is functioning correctly and meeting the needs of all stakeholders.

By following these steps, you can ensure that Azure SQL PaaS Database is tested thoroughly and meets the needs of all stakeholders involved in the project. This can help improve the quality of Azure SQL PaaS Database and ensure that it functions correctly in a production environment.

Process for Use

Azure SQL Database provides the following deployment options for a database:

Single database represents a fully managed, isolated database. You might use this option if you have modern cloud applications and microservices that need a single reliable data source. A single database is similar to a contained database in the SQL Server database engine. Elastic pool is a collection of single databases with a shared set of resources, such as CPU or memory. Single databases can be moved into and out of an elastic pool.

To understand the feature differences between SQL Database, SQL Server, and Azure SQL Managed Instance, as well as the differences among different Azure SQL Database options, see: SQL Database features.

SQL Database delivers predictable performance with multiple resource types, service tiers, and compute sizes.

It provides dynamic scalability with no downtime, built-in intelligent optimization, global scalability and availability, and advanced security options. These capabilities allow you to focus on rapid app development and accelerating your time-to-market, rather than on managing virtual machines and infrastructure. SQL Database is currently in 38 datacenters around the world, so you can run your database in a datacenter near you.

Standards and Practices

While running SQL Server on Azure Virtual Machines, continue using the same database performance tuning options that are applicable to SQL Server in on-premises server environments. However, the performance of a relational database in a public cloud depends on many factors, such as the size of a virtual machine, and the configuration of the data disks.

There’s typically a trade-off between optimizing for costs and optimizing for performance. This performance best practices series is focused on getting the best performance for SQL Server on Azure Virtual Machines. If your workload is less demanding, you might not require every recommended optimization. Consider your performance needs, costs, and workload patterns as you evaluate these recommendations.

VM Size

The following is a quick checklist of VM size best practices for running your SQL Server on Azure VM:

The new Ebdsv5-series provides the highest I/O throughput-to-vCore ratio in Azure along with a memory-to-vCore ratio of 8. This series offers the best price-performance for SQL Server workloads on Azure VMs. Consider this series first for most SQL Server workloads. Use VM sizes with 4 or more vCPUs like the E4ds_v5 or higher. Use memory optimized virtual machine sizes for the best performance of SQL Server workloads. The Edsv5 series, the M-, and the Mv2- series offer the optimal memory-to-vCore ratio required for OLTP workloads. The M series VMs offer the highest memory-to-vCore ratio in Azure. Consider these VMs for mission critical and data warehouse workloads. Leverage Azure Marketplace images to deploy your SQL Server Virtual Machines as the SQL Server settings and storage options are configured for optimal performance. Collect the target workload’s performance characteristics and use them to determine the appropriate VM size for your business. Use the Data Migration Assistant SKU recommendation tool to find the right VM size for your existing SQL Server workload.

Storage

The following is a quick checklist of storage configuration best practices for running your SQL Server on Azure VM:

Monitor the application and determine storage bandwidth and latency requirements for SQL Server data, log, and tempdb files before choosing the disk type. To optimize storage performance, plan for highest uncached IOPS available and use data caching as a performance feature for data reads while avoiding virtual machine and disks capping/throttling. Place data, log, and tempdb files on separate drives. For the data drive, use premium P30 and P40 or smaller disks to ensure the availability of cache support For the log drive plan for capacity and test performance versus cost while evaluating the premium P30 - P80 disks. If submillisecond storage latency is required, use Azure ultra disks for the transaction log. For M-series virtual machine deployments consider Write Accelerator over using Azure ultra disks. Place tempdb on the local ephemeral SSD (default D:) drive for most SQL Server workloads that aren’t part of Failover Cluster Instance (FCI) after choosing the optimal VM size. If the capacity of the local drive isn’t enough for tempdb, consider sizing up the VM. See Data file caching policies for more information. For FCI place tempdb on the shared storage. If the FCI workload is heavily dependent on tempdb disk performance, then as an advanced configuration place tempdb on the local ephemeral SSD (default D:) drive, which isn’t part of FCI storage. This configuration will need custom monitoring and action to ensure the local ephemeral SSD (default D:) drive is available all the time as any failures of this drive won’t trigger action from FCI. Stripe multiple Azure data disks using Storage Spaces to increase I/O bandwidth up to the target virtual machine’s IOPS and throughput limits. Set host caching to read-only for data file disks. Set host caching to none for log file disks. Don’t enable read/write caching on disks that contain SQL Server data or log files. Always stop the SQL Server service before changing the cache settings of your disk. For development and test workloads consider using standard storage. It isn’t recommended to use Standard HDD/SDD for production workloads. Credit-based Disk Bursting (P1-P20) should only be considered for smaller dev/test workloads and departmental systems. Provision the storage account in the same region as the SQL Server VM. Disable Azure geo-redundant storage (geo-replication) and use LRS (local redundant storage) on the storage account. Format your data disk to use 64-KB allocation unit size for all data files placed on a drive other than the temporary D:\ drive (which has a default of 4 KB). SQL Server VMs deployed through Azure Marketplace come with data disks formatted with allocation unit size and interleave for the storage pool set to 64 KB.

F. 2023 Roadmap

????

G. 2024 Roadmap

????

H. Known Issues

There are several known issues that can impact Azure SQL PaaS Database. Here are some of the most common issues to be aware of:

  1. Performance issues: Performance issues can arise when Azure SQL PaaS Database is under heavy load, leading to issues with network traffic and latency. It is important to monitor performance and address any issues that arise.

  2. Configuration issues: Configuration issues can arise when configuring Azure SQL PaaS Database, leading to issues with database connectivity and data storage. It is important to ensure that the service is properly configured to avoid these issues.

  3. Compatibility issues: Azure SQL PaaS Database may not be compatible with all platforms, devices, or languages. It is important to ensure that Azure SQL PaaS Database is compatible with the organization’s existing infrastructure before implementation.

  4. Security issues: Security is a critical concern when it comes to Azure SQL PaaS Database. It is important to ensure that Azure SQL PaaS Database is secured and that access to the solution is restricted to authorized personnel.

  5. Backup and restore issues: Backup and restore issues can arise when Azure SQL PaaS Database is not configured correctly, leading to data loss and other issues. It is important to ensure that backup and restore procedures are in place and tested regularly.

  6. Maintenance issues: Maintenance issues can arise when Azure SQL PaaS Database is not properly maintained, leading to issues with data integrity and performance. It is important to ensure that maintenance procedures are in place and tested regularly.

Overall, Azure SQL PaaS Database requires careful planning and management to ensure that it is functioning correctly and meeting the needs of all stakeholders involved in the project. By being aware of these known issues and taking steps to address them, you can improve the quality of Azure SQL PaaS Database and ensure the success of your project.

[x] Reviewed by Enterprise Architecture

[x] Reviewed by Application Development

[x] Reviewed by Data Architecture