WORKING WITH AZURE SQL DATABASE LEARN ABOUT SQL

WORKING WITH AZURE SQL DATABASE LEARN ABOUT SQL DATABASE

Vitor Fava • MVP Data Platform • Principal Database Architect at Vita Database Solutions • Pass Chapter Leader at SQLManiacs • SQL Server Database Consultant at Pythian • Vitor. fava@vitadbsolutions. com • http: //vfava. wordpress. com • http: //www. youtube. com/vitortff • https: //groups. google. com/group/sqlmaniacs

What is Cloud Computing?

What is Cloud Computing? • What is the cloud? Where is the cloud? Are we in the cloud now? • Cloud computing means storing and accessing data and programs over the Internet instead of your computer's hard drive. • Software-as-a-Service (Saa. S) • Infrastructure-as-a-service (Iaa. S) • Platform-as-a-Service (Paa. S)

What is Cloud Computing?

What is SQL Database?

What is SQL Database? • Relational database service in the cloud based on the market-leading Microsoft SQL Server engine, with missioncritical capabilities. • Delivers predictable performance, scalability with no downtime, business continuity and data protection—all with near-zero administration • Focus on rapid app development and accelerating your time to market, rather than managing virtual machines and infrastructure. • Because it’s based on the SQL Server engine, SQL Database supports existing SQL Server tools, libraries and APIs, which makes it easier for you to move and extend to the cloud.

What is an Azure SQL Database logical server? • An Azure SQL Database logical server acts as a central administrative point for multiple databases. • In SQL Database, a server is a logical construct that is distinct from a SQL Server instance that you may be familiar with in the onpremises world. • Specifically, the SQL Database service makes no guarantees regarding location of the databases in relation to their logical servers, and exposes no instance-level access or features.

What is an Azure SQL Database logical server? Is created within an Azure subscription, but can be moved with its contained resources to another subscription; Is the parent resource for databases, elastic pools, and data warehouses; Provides a namespace for databases, elastic pools, data warehouses; Is a logical container with strong lifetime semantics - delete a server and it deletes the contained databases, elastic pools, data warehouses;

What is an Azure SQL Database logical server? • Collocates resources in a region; • Provides a connection endpoint for database access (. database. windows. net); • Provides access to metadata regarding contained resources via DMVs by connecting to a master database; • Provides the scope for management policies that apply to its databases: logins, firewall, audit, threat detection, etc; • Provides the scope for database quota and DTU quota for the resources it contains (such as 45000 DTU); • Server-level principal logins can manage all databases on a server;

Azure – Service Tiers Basic • Best suited for a small database, supporting typically one single active operation at a given time. Standard • The go-to option for cloud applications with low to medium IO performance requirements, supporting multiple concurrent queries. Premium • Designed for high transactional volume with high IO performance requirements, supporting many concurrent users. Premium RS • Designed for IO-intensive workloads that do not require the highest availability guarantees.

Azure – Service Tiers

What is DTU? • DTU = Database Throughput Units • Resources assigned to a database 40% CPU 30% Writes Utilization Reads • Unified measure of CPU+Memory+Read/write rates 65% 50% Memory

What is DTU – DTU Calculator • How to calculate the needed DTU to my SQL Database? • You can predict the service tier • Too much: Not cost effective. • Too less: You can put your database in trouble (and you!). • DTU Calculator • Online service (http: //dtucalculator. azurewebsites. net/) • Based on collected performance indicators. • Processor - % Processor Time • Logical Disk - Disk Reads/sec • Logical Disk - Disk Writes/sec • Database - Log Bytes Flushed/sec • Does not take into account: • Uptime SLA • Point in time Restore • Performance Objective

Azure SQL Database resource limits One of the design goals of the Basic, Standard, Premium, and Premium RS service tiers is for Azure SQL Database to behave as if the database is running on its own machine, isolated from other databases. Resource governance emulates this behavior. If the aggregated resource utilization reaches the maximum available CPU, Memory, Log I/O, and Data I/O resources assigned to the database, resource governance queues queries in execution and assign resources to the queued queries as they free up. As on a dedicated machine, utilizing all available resources results in a longer execution of currently executing queries, which can result in command timeouts on the client. Applications with aggressive retry logic and applications that execute queries against the database with a high frequency can encounter errors messages when trying to execute new queries when the limit of concurrent requests has been reached.

Azure SQL Database resource limits Recommendations Reduce the number of incoming requests to the database to prevent timeout and the pile up of requests; Assign a higher performance level to the database; Optimize queries to reduce the resource utilization of each query;

Azure SQL Database resource limits Area Limit Description Databases using Automated export per subscription 10 Automated export allows you to create a custom schedule for backing up your SQL databases. Databases per server Up to 5000 databases are allowed per server. 45000 DTUs are allowed per server for provisioning standalone databases and elastic pools. The total number of standalone databases and pools allowed per server is limited only by the number of server DTUs per server

Scaling Up or Scaling Down

Scaling Up or Scaling Down • To downgrade a database, the database should be smaller than the maximum allowed size of the target service tier; • When upgrading a database with Geo-Replication enabled, you must first upgrade its secondary databases to the desired performance tier before upgrading the primary database; • When downgrading from a Premium service tier, you must first terminate all Geo-Replication relationships; • The restore service offerings are different for the various service tiers. If you are downgrading you may lose the ability to restore to a point in time, or have a lower backup retention period; • The new properties for the database are not applied until the changes are complete.

Creating or upgrading P 11/P 15 to 4 TB When creating a P 11/P 15, the default storage option of 1 TB is pre-selected. For databases located in one of the supported regions, you can increase the storage maximum to 4 TB. The price does not change when you select 4 TB of included storage. When creating or upgrading an P 11/P 15 database in an unsupported region, the create or upgrade operation will fail with the following error message: P 11 and P 15 database with up to 4 TB of storage are available in US East 2, West US, South East Asia, West Europe, Canada East, Canada Central, Japan East, and Australia East.

Creating or upgrading P 11/P 15 to 4 TB When creating or updating a P 11 or P 15 database, you can only chose between 1 TB and 4 TB maxsize. Intermediate storage sizes are not currently supported. The 4 TB database maxsize cannot be changed to 1 TB even if the actual storage used is below 1 TB. For Active Geo-Replication scenarios: • Setting up a geo-replication relationship: If the primary database is P 11 or P 15, the secondary(ies) must also be P 11 or P 15; • Upgrading the primary database in a geo-replication relationship: Changing the maxsize to 4 TB on a primary database will trigger the same change on the secondary database Using the Import/Export service for loading P 11 -4 TB/P 15 -4 TB databases is not supported. Use Sql. Package. exe to import and export data.

Business Continuity

Business Continuity • Point-in-time Restore - return the database to an earlier point in time. A kind of regular “restore”. • Automatic backups: • Weekly full backup; • Daily differential backup; • Log backups every 5 minutes; SQL Database backups Geo- replicated sabcp 01 bl 21 Restore from backup Azure Storage sabcp 01 bl 21

Business Continuity • Geo-Restore - default recovery option when a database is unavailable. Restores last daily backup to another region, using a geo-replicated backup copy. SQL Database backups sabcp 01 bl 21 Azure Storage Geo- replicated sabcp 01 bl 21 Restore to any Azure region

Business Continuity • Active Geo. Replication - Up to 4 readable secondaries, with configurable regions. A kind of AG in the cloud. Geo- replicated

Exploring the cloud!!!!!

Managing the SQL Database

Managing the SQL Database Azure Portal • Web-based application where you can create, update, and delete databases and logical servers and monitor database activity SSMS and Data Tools • Client tools that run on your computer and allow you to connect to, manage, and develop your database in the cloud. Command line tools • You can use command line tools such as Power. Shell to manage databases and elastic database pools, and to automate Azure resource deployments.

Monitoring a SQL Database DMVs Extended Events Query Performance Insight

Query Performance Insight • Deeper insight into your databases resource (DTU) consumption; • The top CPU consuming queries, which can potentially be tuned for improved performance; • The ability to drill down into the details of a query, view its text and history of resource utilization; • Performance tuning annotations that show actions performed by SQL Azure Database Advisor;

What is an Azure elastic database pool?

What is an Azure elastic database pool? • Provide a simple cost effective solution to manage the performance goals for multiple databases that have widely varying and unpredictable usage; • Elastic database pools ensure that databases get the performance resources they need, when they need it, while providing a simple resource allocation mechanism within a predictable budget; • Elastic pools enable the developer to purchase elastic Database Transaction Units (e. DTUs) for a pool shared by multiple databases to accommodate unpredictable periods of usage by individual databases; • The e. DTU requirement for a pool is determined by the aggregate utilization of its databases.

What is an Azure elastic database pool? e. DTU • dictates the allocation of capacity (in terms of e. DTUs) on the pool level. This should be determined based on the average utilization levels of all databases that belong to the pool. • guarantees minimum transactional throughput (in terms of e. DTUs) for every database in the pool (you can choose not to provide any guarantees by setting this e. DTU MIN value to 0). • ensures that individual databases in the pool are able to gracefully handle peaks in resource demand, while minimizing impact on other databases sharing the same e. DTU MAX pool and without excessive over-provisioning.

Elastic Pool Limits

Which databases go in a pool? Databases that are great candidates for elastic database pools typically have periods of activity and other periods of inactivity. Databases with varying activity over time are great candidates for elastic pools because they are not all active at the same time and can share e. DTUs. Databases that have a more constant resource demand are better suited to the Basic, Standard, and Premium service tiers where resources are individually assigned.

Which databases go in a pool?

Sizing an elastic pool The best size for a pool depends on the aggregate e. DTUs and storage resources needed for all databases in the pool. This involves determining the larger of the following: • Maximum DTUs utilized by all databases in the pool. • Maximum storage bytes utilized by all databases in the pool. SQL Database automatically evaluates the historical resource usage of databases in an existing SQL Database server and recommends the appropriate pool configuration in the Azure portal.

Sizing an elastic pool 1. Estimate the e. DTUs needed for the pool as follows: • MAX(<Total number of DBs X average DTU utilization per DB>, <Number of concurrently peaking DBs X Peak DTU utilization per DB) 2. Estimate the storage space needed for the pool by adding the number of bytes needed for all the databases in the pool. Then determine the e. DTU pool size that provides this amount of storage. 3. Take the larger of the e. DTU estimates from Step 1 and Step 2. 4. See the SQL Database pricing page and find the smallest e. DTU pool size that is greater than the estimate from Step 5. Compare the pool price from Step 5 to the price of using the appropriate performance levels for single databases.

Pool sizing recommendations • Pools enable the developer to purchase elastic Database Transaction Units (e. DTUs) for a pool shared by multiple databases to accommodate unpredictable periods of usage by individual databases • SQL Database automatically evaluates the historical resource usage of databases in an existing SQL Database server and recommends the appropriate pool configuration in the Azure portal

Scaling Up or Scaling Down – Elastic Pool

Scaling Up or Scaling Down – Elastic Pool • Changing the min e. DTUs per database or max e. DTUs per database typically completes in five minutes or less; • Time to change the pool size (e. DTUs) depends on the combined size of all databases in the pool; • Changes average 90 minutes or less per 100 GB; • For example, if the total space of all databases in the pool is 200 GB, then the expected latency for changing the pool e. DTU per pool is 3 hours or less;

Questions