SQL SERVER DBA and Azure SQL: Azure

Will post shortly. Bandaru Ajeyudu

SQL Server 2019 – Compatibility Level in Azure

I am not sure when this became available but for Azure SQL Database 150 compatibility level is now available. Last time I created a database few weeks ago, only level 140 ( SQL Server 2017) was available so I think it is a recent thing.

Upon some testing, if you create a new Azure SQL Database by default it is 150 as per the screen shot below (from the script action command)

Via SSMS (SQL Server Management Studio) you can view the database properties, you will clearly see the new level.

How to find blocking queries in SQL Azure

The query below will display the top ten running queries that have been the longest total elapsed time and are blocking other queries.

SELECT TOP 10 r.session_id, r.plan_handle, r.[sql_handle], r.request_id, r.start_time, r.[status], r.command, r.database_id,
r.[user_id], r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level, r.row_count, st.[text]
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st
WHERE r.blocking_session_id = 0
and r.session_id IN (SELECT distinct(blocking_session_id) FROM sys.dm_exec_requests)
GROUP BY r.session_id, r.plan_handle, r.[sql_handle], r.request_id, r.start_time, r.[status], r.command, r.database_id, r.[user_id], r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level, r.row_count, st.[text]

ORDER BY r.total_elapsed_time DESC

1. What is SQL Azure?

SQL Azure is a cloud-based relational database as a Service offered by Microsoft. Conceptually it is SQL server in the cloud.

2. What is cloud computing?

National Institute of standards and computing definition of cloud computing:

Cloud computing is a model for enabling ubiquitous, convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, servers, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction.

[TIP: Remember Keywords On Demand, Scalable, Self-service, and Measurable. Now take the first word from each keyword which gives us OSSM which can be pronounced as Awesome. Thus remember cloud computing is Awesome! Tip courtesy: Dave Nielsen]

3. How is SQL Azure different than SQL server?

SQL Azure is a cloud-based service and so it has own set of pros and cons when compared to SQL server. SQL Azure service benefits include on-demand provisioning, high availability, reduced management overhead and scalability. But SQL Azure abstracts some details from the subscriber which can be good or bad which depends on the context of the need.

4. How many replicas are maintained for each SQL Azure database?

For each database, three replicas are maintained for each database that one provisions. One of them is a primary replica. All read/write happen on primary replica and other replicas are kept in sync with the primary replica. If for some reason, primary goes down, another replica is promoted to primary. All this happens under the hood.

5. How can we migrate from SQL server to SQL Azure?

And for schema Migration, we can use Generate Script Wizard. Also, we could use a Tool called DMA (Database migration Assistant) tool.s

6. Which tools are available to manage SQL Azure databases and servers?

We can manage SQL Azure database using SQL server management studio and also, we can manage SQL Azure databases and servers through a Azure management portal.

7. Tell me something about security and SQL Azure.

SQL Azure service allows blocking a request based on its IP address through SQL Azure firewall. It uses SQL server Authentication mechanism to authenticate connections. Also connections to SQL Azure are SSL-encrypted by default.

8. What is SQL Azure Firewall?

SQL Azure firewall is a security mechanism that blocks requests based on its IP address.

9. What is the difference between web edition and business edition?

SQL Azure Web edition database Max Size is 5 GB whereas the business edition supports Max Size up to 50 GB. The size of a web edition database can be increased (/decreased) in the increments (/decrements) of 1 GB whereas the size of a business edition can be increased in the increments of 10 GB.

10. How do we synchronize On-Premise SQL server with SQL Azure?

We can use transactional replication, merge replication to Sync the on-premise DB.

11. How do we Backup SQL Azure Data?

SQL Azure keeps three replicas of a database to tackle hardware level issues. To tackle user level errors, we can use COPY command that allows us to create a replica of a SQL Azure database. We can also backup SQL Azure data to local SQL server using BCP, SSIS, etc. but as of now, point in time recovery is not supported.

12. What is the current pricing model of SQL Azure?

Charges for SQL Azure consumption is based on 1) Size 2) Data Transfer.

13. What is the current limitation of the size of SQL Azure DB?

At the current time, the maximum SQL Azure database size can be 250 GB (s0, s1, s2, and s3) or 500GB (p1, p2, p3, and p6 ) or 4TB (p11 and p15).

14. What happens when the SQL Azure database reaches Max Size?

Read operations continue to work but create/insert/update operations are throttled. You can drop/delete/truncate data.

16. How many databases can we create on a single server?

150 databases (including master database) can be created in a single SQL Azure server.

17. How many servers can we create in a single subscription?

As of now, default limit is 6, the maximum limit is 150.

18. How do you improve the performance of a SQL Azure Database?

We can tune a SQL Azure database using information available from execution plan and statistics of a query. We could use SQL Azure’s Dynamic Management views to monitor and manage SQL Azure database.

Also, SQL Azure performance is affected by network latency and bandwidth. Considering this, code near application topology gives the best performance.

19. What is code near application topology?

Code near application topology means that the SQL Azure database and the windows azure hosted service consuming the data are hosted in the same Azure datacenter.

Note: in the code far application topology, the app connects to SQL Azure from outside the Microsoft data center]

21. When does a workload on SQL Azure get throttled?

When database reaches its maximum size update/insert/create operations get throttled. Also, there are policies in place that does not allow to a workload to exploit a shared physical server. In other words, the policies make sure that all workload get a fair share of the shared physical server. Now, a workload can get soft throttled that means that the workload has crossed the safety threshold. A workload can also get hard throttled which means that a SQL Azure machine is out of resources and it does not accept new connections. We can know more about what happened by decoding reason codes.

22. What Is The Index Requirement In Sql Azure?

All tables must have clustered index. You can't have a table without clustered index

23. What is an Availability Set?

Explanation: An availability set is a logical grouping of VMs that allows Azure to understand how your application is built to provide redundancy and availability. It is recommended that two or more VMs are created within an availability set to provide for a highly available application and to meet the 99.95% Azure SLA. When a single VM is used with Azure Premium Storage, the Azure SLA applies for unplanned maintenance events.

24. Why is Azure Active Directory used?

Explanation: Azure Active Directory is an Identity and Access Management system. It is used to grant access to your employees to specific products and services in your network. For example: Salesforce.com, twitter etc. Azure AD has some in-built support for applications in its gallery which can be added directly.

25. What are the various power states of a VM?

26 ) How can I do a point-in-time restore?

The point-in-time restore is available on all the three editions – Basic, Standard and Premium. Be aware that each one of the editions has different backup retention policies.

A restore will always create a new database. This database can be used to recover data to the original database or to fully replace the original database, which can be performed by renaming the restored database, using the ALTER DATABASE command:

27) How can I recover a dropped Azure SQL database?

We can recover the accidentally deleted databases on the PAAS SQL server using deleted databases tab under SQL server options.

28)

Can we able to restrict database port number to communicate only to Application Server?

Ans: Yes, that could be possible in Network Security Group.

29)

If I have more than 3 application servers, I require the database port number needs to communicate to all the 3 application servers. How could be possible?

Ans: I will provide the Application Subnet ranges in Network Security Group on Database Port number rule.

30)

Does Oracle as a Database Service Available in Azure?

Ans: No, Azure doesn’t have a Database as a Service for Oracle.

Azure SQL issues and potential solutions:

Azure SQL Database, being a cloud-based relational database service provided by Microsoft Azure, can encounter various issues. Here are some common Azure SQL issues and potential solutions:

Performance Issues:

Issue: Slow query performance or high resource utilization.
Solution:

Use the Azure SQL Database Advisor and Query Performance Insight to identify and optimize poorly performing queries.
Consider scaling resources (CPU, memory, and DTUs) based on workload requirements.
Review and optimize database design, indexing, and query execution plans.

Connectivity Issues:

Issue: Difficulty connecting to the Azure SQL Database.
Solution:

Ensure that firewall rules are configured to allow access.
Verify that the connection string is correct.
Check for service outages or maintenance on the Azure Status page.

Firewall Issues:

Issue: Unable to connect due to firewall restrictions.
Solution:

Adjust Azure SQL Database firewall rules to allow connections from specific IP addresses or ranges.
Use server-level firewall rules to control access.

Backup and Restore Issues:

Issue: Backup or restore operations fail.
Solution:

Check storage account credentials for backup storage.
Ensure that the database is not in use during restore operations.
Monitor backup retention policies and storage quotas.

Security and Access Control:

Issue: Issues with authentication, authorization, or compromised security.
Solution:

Regularly review and audit user permissions.
Implement Multi-Factor Authentication (MFA) for added security.
Use Azure Active Directory for user authentication.

Scaling Challenges:

Issue: Need to scale resources up or down.
Solution:

Use the Azure Portal or PowerShell to scale resources (DTUs, storage, etc.).
Consider auto-scaling based on workload patterns.

Long-running Transactions:

Issue: Transactions taking a long time to complete.
Solution:

Monitor and optimize long-running transactions.
Implement proper error handling and transaction management.

Query Timeouts:

Issue: Queries timing out due to resource constraints.
Solution:

Optimize queries for better performance.
Consider increasing resources or scaling to a higher service tier.

Geo-Replication Lag:

Issue: Replication lag in a geo-replicated scenario.
Solution:

Adjust the database priority and replication settings.
Monitor network latency and connectivity between regions.

Data Synchronization Issues:

Issue: Challenges with data consistency and synchronization in distributed environments.
Solution:

Implement proper data synchronization mechanisms.
Use features like Azure SQL Database geo-replication for read-scale and disaster recovery.

For specific issues, it's always recommended to refer to Azure documentation, utilize Azure Monitor and Azure Advisor for insights, and seek assistance from Microsoft support if needed. Additionally, staying updated with the latest Azure SQL Database features and best practices is crucial for effective management and troubleshooting.

SQL SERVER DBA and Azure SQL

Pages

Azure