Lesson Learned #220:Hands-On-Labs: Activity Monitor in my Elastic Database Pool

by Contributed | Jul 9, 2022 | Technology

This article is contributed. See the original author and article here.

To be honest, this post is one of my favorites that I was looking to post due to many questions that we get from our customers about how to monitor my elastic database pool. Many customers have a dense elastic database pool and they need a clear picture of what is happening in their elastic database pool. I hope that you can enjoy like as much as I enjoyed during these tests. 

In this article and video we are going to monitor the elastic database pool using the monitor an we are going to share a query to obtain all the current processes that your elastic database pool is running. 

The first thing is to know the main characteristics of an elastic database pool. 

• Databases running on a single SQL Instance.


• Configuration per database

The second, is to know the options that we have to monitor an elastic database pool

• Azure Portal, Azure Monitor, Log Analytics and SQL Auditing


• Select * from sys.dm_db_resource_stats


• Select * from sys.dm_exec_requests in combinations with other


• Query Data Store


• Use the queries provided in the demo

FInally, the best practices:

• Update Statistics with fullscan – How to maintain Azure SQL Indexes and Statistics – Microsoft Tech Community


• Lesson Learned #195: Performance Best Practices for Azure SQL Database and Managed Instance. – Microsoft Tech Community


• Resource management in dense elastic pools – Azure SQL Database | Microsoft Docs

Demo

In this demo I have the following configuration:

• Elastic Database Pool Name: Jmjuradotest


• Elastic Database Pool Configuration:
  
  
  
  • General Purpose 2 vCores
  
  
  • Storage Size: 316 GB
  
  
  • Per Database Setting: Unlimited per Database.
  
  
  
  


• Databases that are part of this Elastic Database Pool:
  
  
  
  • Jmjuradotestdb1
  
  
  • Jmjuradotestdb2
  
  
  • Jmjuradotestdb3
  
  
  
  

How to monitor queries that are running in my Elastic Database Pool.

This is the query that I used to monitor the activity

SELECT
 substring(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset/2) + 1, (
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END
- req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text
,dbs.name
,program_name
,req.session_id
, req.cpu_time 'cpu_time_ms'
, req.status
, wait_time
, wait_resource
, wait_type
, last_wait_type
, req.total_elapsed_time
, total_scheduled_time
, req.row_count as [Row Count]
, command
, scheduler_id
, memory_usage
, req.writes
, req.reads
, req.logical_reads
FROM sys.dm_exec_requests AS req
inner join sys.dm_exec_sessions as sess on sess.session_id = req.session_id
left join [dbo].[master_data] as dbs on dbs.database_id = sess.database_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
where req.session_id <> @@SPID
order by dbs.name

If you run this query connected to any database that belongs to your elastic database pool you could find some useful information:

As you could see this query has a special table called master_data, basically it is an external table that is connecting to master database to obtain the name of the database. Unfortunately, in Azure SQL Database is not possible to connect to others databases once you are connected to another. If you don’t want to create an external table, please, basically, remove the reference like I posted below.

SELECT
 substring(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset/2) + 1, (
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END
- req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text
--,dbs.name
,req.database_id
,program_name
,req.session_id
, req.cpu_time 'cpu_time_ms'
, req.status
, wait_time
, wait_resource
, wait_type
, last_wait_type
, req.total_elapsed_time
, total_scheduled_time
, req.row_count as [Row Count]
, command
, scheduler_id
, memory_usage
, req.writes
, req.reads
, req.logical_reads, blocking_session_id
FROM sys.dm_exec_requests AS req
inner join sys.dm_exec_sessions as sess on sess.session_id = req.session_id
--left join [dbo].[master_data] as dbs on dbs.database_id = sess.database_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
where req.session_id <> @@SPID
--order by dbs.name

Definition of external table

CREATE DATABASE scoped CREDENTIAL CredentialJM WITH IDENTITY  ='username', SECREt = 'Password'

CREATE EXTERNAL DATA SOURCE [RemoteDataJM] WITH (TYPE = RDBMS, LOCATION = N'servername.database.windows.net', CREDENTIAL = [CredentialJM], DATABASE_NAME = N'master')
GO

CREATE external TABLE [dbo].[master_data](
name varchar(120), database_id bigint
)
WITH
(
  DATA_SOURCE = [RemoteDataJM],
  SCHEMA_NAME = 'sys', --schema name of remote table
  OBJECT_NAME = 'databases' --table name of remote table
);

In the following video you could see that giving a special workload (running queries that are taking high CPU, Bulk inserts and TempDB operations) how I monitor my elastic database pool, how I know that queries running and how I know which is the database that is taking more resources. 

Enjoy!

Meet a recent Microsoft Learn Student Ambassador graduate: Jason Chee

by Contributed | Jul 8, 2022 | Technology

This article is contributed. See the original author and article here.

This is the next segment of our blog series highlighting Microsoft Learn Student Ambassadors who achieved the Gold milestone and have recently graduated from university. Each blog in the series features a different student and highlights their accomplishments, their experience with the Student Ambassador community, and what they’re up to now. 

Today we meet Jason Chee who is from Singapore and recently graduated from the National University of Singapore with a Bachelor of Computing (Honors) degree in Computer Science and a Minor in Communications and New Media.

Responses have been edited for clarity and length.  

How has the Student Ambassador community impacted you?

I initially joined the community in July 2012 when it was the Microsoft Student Partners program while I was in Junior College.  I focused on developing Microsoft technologies to the detriment of my studies, but after interning at Microsoft Singapore in the Developer and Platform Evangelism (DPE) Group as a Program Lead for the local charter of Microsoft Student Partners, I pursued further studies at my current university and rejoined the Student Ambassador community in August 2018.

The community holds a dear place in my heart because it played a tremendous role in shaping where I am today. The majority of the DPE team were predominantly ex-Student Partners. The culture this created meant the Student Partner/Student Ambassador network in Singapore was extremely strong with great opportunities to work for or with Microsoft. To this day, many of these people are still in contact. We have formed startups together and still very much keep in contact with the DPE team of the past, even though none of them still work for Microsoft.

Ultimately, it was the idea of a community passionate about technology that enthralled me. This was a group of like-minded people who aimed to make the world a better place through technology and have fun while doing it.

In a unique element to myself, this community is what set me on a path traditionally seen as less successful locally, but one I excelled in. I witnessed firsthand how the combined strengths and wisdom from the local Student Partners came together with Microsoft Singapore’s backing to give aspiring developers the opportunities they could otherwise not afford or come to know of. This is what I wanted to cultivate in my time as a Program Lead.

What were the accomplishments that you’re the proudest of and why?

Overseeing 350 Microsoft Student Partners during my time as Program Lead would be my highlight. Creating this network was an absolute joy and has paid off as the strong friendships and professional connections this cultivated are a sight to behold.  We ran extremely popular and successful events throughout the campuses such as one called Campus Surprise, a collaboration between various departments at Microsoft — DX, Education, Philanthropies, and Xbox. It was a roadshow event we ran simultaneously across every institute of higher learning in Singapore, showcasing the very best of Microsoft technologies and onboarding students to Office 365 and Microsoft Azure. Each segment of Microsoft Student Partners had free reign to craft the event. One institution even ran an Escape Room challenge to bring in visitors.

I was the Microsoft Student Partner of the Year in 2015. Out of 9,000 Student Partners globally, I was one of the few selected to attend the Student Partner Summit in Redmond, where I received the title for my contributions to the program and because of the success of the Singapore charter.

What are you doing now that you’ve graduated? 

I founded a startup with two other ex-Student Partners/Student Ambassadors. We run an events-tech company called FrontHAUS, a play on the term “front of house” which is essentially the ticketing booth. Our company focuses on next generation ticketing experiences and the transformation of traditional events into a digital or hybrid format.

We also run a company called HelloHolo, an official Mixed Reality partner for Microsoft that brings in MR/VR/AR technologies such as the Microsoft HoloLens to the local market and builds solutions for them.

If you could redo your time as a Student Ambassador, is there anything you would have done differently? 

I think it is important to live life in the forward direction without regrets. I am proud to say that there is nothing I would have done differently in my time in Microsoft Student Partners and Microsoft Learn Student Ambassadors.  I did everything in my power to ensure its continued survival.

If you were to describe the community to a student who is interested in joining, what would you say about it to convince him or her to join? 

The community truly provides one with the tools they need to learn more about Microsoft technologies and get ahead of their peers in terms of premier tools and certifications. It also comes with an enormous global network of like-minded people.

What advice would you give to new Student Ambassadors? 

Learn as much as you can, do as much as you can, and above all, connect with as many people as you can.

What is your motto in life, your guiding principle? 

Celebrate others’ success often and know that it does not diminish your own.

What is one random fact about you that few people are aware of? 

I am terrible at math. I never viewed programming as a science or math puzzle and have always viewed it as a language. In that sense, I am more of an “arts” student compared to a “science” / STEM student and therefore am an avid patron of the arts. I seek out musicals and theater when I can, and love absolutely anything Disney.

_________________________________________________________________________________________

Good luck to you in all your endeavors, Jason!

Readers, you can find Jason on LinkedIn.

Cross region replication using Data-in replication with Azure Database for MySQL – Flexible Server

by Contributed | Jul 5, 2022 | Technology

This article is contributed. See the original author and article here.

MySQL workloads are often read-heavy and support customers with operations in different geographical locations. To provide for Disaster Recovery (DR) in the rare event of a regional disaster, Azure Database for MySQL – Flexible Server offers Geo-restore. An alternate option for DR or read scaling across regions is to create an Azure Database for MySQL flexible server as the source server and then to replicate its data to a server in another region using Data-in replication. This set up helps improve the Recovery Time Objective (RTO) as compared to geo-restore and the Recovery Point Objective (RPO) will be equal to the replication lag between the primary server and the replica server.

Data-in replication, which is based on the binary log (binlog) file position, enables synchronization of data from one Azure Database for MySQL flexible service to another. To learn more about binlog replication, see MySQL binlog replication overview.

In this blog post, I’ll use mydumper/myloader and Data-in replication to create cross region replication from one Azure Database for MySQL flexible server to another in a different region, and then I’ll synchronize the data.

Prerequisites

To complete this tutorial, I need:

1. A primary and secondary Azure Database for MySQL flexible server, one in each of two different regions, running either version 5.7 or 8.0 (it is recommended to have the same version running on the two servers. For more information, see Create an Azure Database for MySQL flexible server.
   
   
   

   Note: Currently, this procedure is supported only on flexible servers that are not HA enabled.

   
   

2. An Azure VM running Linux that can connect to both the primary and replica servers in different regions. The VM should have the following client tools installed.
   
   
   
   • The . In this post, we’ll use the mysql client.
   
   
   • mydumper/myloader. For more information about using mydumper/myloader, see https://centminmod.com/mydumper.html.
     
     Tip: After setting up replication, this VM can be deleted.
     
   
   
   
   

3. A sample database for testing the replication. Download mysqlsampledatabase.zip, and then run the included script on the primary server to create the sample classicmodels database.


4. The binlog_expire_logs_seconds parameter on the primary server configured to ensure that binlogs aren’t purged before the replica commits the changes.


5. The gtid_mode parameter set to same value on both the primary and replica servers. Configure this on the Server parameters page.


6. Networking configured to ensure that primary server and replica server can communicate with each other.

• For Public access, on the Networking page, under Firewall rules, ensure that the primary server firewall allows connection from the replica server by verifying that the Allow public access from any Azure service…check box is selected. For more information, in the article Public Network Access for Azure Database for MySQL – Flexible Server, see Firewall rules.


• For Private access, ensure that the replica server can resolve the FQDN of the primary server and connect over the network. To accomplish this, use VNet peering or VNet-to-VNet VPN gateway connection.
  
  

Configure Data-in replication between the primary and replica servers

To configure Data-in replication, I’ll perform the following steps:

1. On the Azure VM, use the mysql client tool to connect to the primary and replica servers.


2. On the primary server, verify that log_bin is enabled by using the mysql client tool to run the following command:
   
   

   SHOW VARIABLES LIKE 'log_bin';​

3. On the source server, create a user with the replication permission by running the appropriate command, based on SSL enforcement.

If you’re using SSL, run the following command:

CREATE USER 'syncuser'@'%' IDENTIFIED BY 'yourpassword';
GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%' REQUIRE SSL;

If you’re not using SSL, run the following command:

CREATE USER 'syncuser'@'%' IDENTIFIED BY 'yourpassword';
GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%';

4. On the Azure VM, use mydumper to back up the primary server database by running the following command:

mydumper --host=<primary_server>.mysql.database.azure.com --user=<username> --password=<Password> --outputdir=./backup --rows=100 -G -E -R -z --trx-consistency-only --compress --build-empty-files --threads=16 --compress-protocol --ssl  --regex '^(classicmodels.)' -L mydumper-logs.txt​

  –host: Name of the primary server

  –user: Name of a user having permission to dump the database.

  –password: Password of the user above

  –trx-consistency-only: Required for transactional consistency during backup.

For more information about using mydumper, see mydumper/myloader.

5. Restore the database using myloader by running the following command:

myloader --host=<servername>.mysql.database.azure.com --user=<username> --password=<Password> --directory=./backup --queries-per-transaction=100 --threads=16 --compress-protocol --ssl --verbose=3 -e 2>myloader-logs.txt

–host: Name of the replica server.

–user: Name of a user. You can use server admin or a user with readwrite permission capable of restoring the schemas and data to the database.

–password: Password of the user above.

6. Read the metadata file to determine the binary log file name and offset by running the following command:
   
   

   cat ./backup/metadata​

   
   In this command, ./backup refers to the output directory specified in the command in the previous step.
   
   The results should appear as shown in the following image:
   
   

7. Depending on the SSL enforcement on the primary server, connect to the replica server using the mysql client tool, and then perform the following the steps.
   
   
   

   If SSL enforcement is enabled, then:
   
   a. Download the certificate needed to communicate over SSL with your Azure Database for MySQL server from here.
   b. In Notepad, open the file, and then copy and paste the contents into the command below, replacing the text “PLACE PUBLIC KEY CERTIFICATE CONTEXT HERE“.
   
   

   SET @cert = ‘-----BEGIN CERTIFICATE-----
   PLACE PUBLIC KEY CERTIFICATE CONTEXT HERE 
   -----END CERTIFICATE-----'
   

   
   c. To configure Data-in replication, run the updated command above along with the following command to set @cert
   
   

   CALL mysql.az_replication_change_master(‘<Primary_server>.mysql.database.azure.com’, ‘<username>’, ‘<Password>’, 3306, ‘<File_Name>’, <Position>, @cert);

    

   
   

   If SSL enforcement isn’t enabled, then run the following command:
   
   

   CALL mysql.az_replication_change_master(‘<Primary_server>.mysql.database.azure.com’, ‘<username>’, ‘<Password>’, 3306, ‘<File_Name>’, <Position>, ‘’);

    

   
   

–Primary_server: Name of the primary server

–username: Replica user created in step 4

–Password:  Password of the replica user created in step 4

–File_Name and Position: From the information in step 7

8. On the replica server, to ensure that write traffic is not accepted, set the server parameter read_only to ON.

call mysql.az_replication_start;

9. On the replica server, to ensure that write traffic is not accepted, set the server parameter read_only to ON.

Test the replication

On the replica server, to check the replication status, run the following command:

show slave status G;

In the results, if the state of Slave_IO_Running and Slave_SQL_Running shows “Yes” and Slave_IO_State is “Waiting for master to send event”, then replication is working well. You can also check Seconds_Behind_Master, which indicates how late the replica is. If the value is something other than 0, then the replica is still processing updates.

For more information on the output of the show slave status command, in the MySQL documentation, see SHOW SLAVE STATUS Statement.

For details on troubleshooting replication, see the following resources:

• Troubleshoot replication latency – Azure Database for MySQL


• Troubleshooting Replication latency in Azure Database for MySQL


• Troubleshooting Problems With MySQL Replication

Optional

To confirm that cross region is working properly, you can verify that the changes to the tables in primary have been replicated to the replica.

1. Identify a table to use for testing, for example the Customers table, and then confirm that it contains the same number of entries on both the primary and replica servers by running the following command on each server:

select count(*) from customers;

2. Make a note of the count of entries in each table for later comparison.

To confirm that replication is working properly, on the primary server, add some data to the Customer table. Next, run the select count command each of the primary and replica servers to verify that the entry count on the replica server has increased to match the entry count on the primary server.

Note: For more information about how to monitor Data-in replication and create alerts for potential replication failures, see Monitoring and creating alerts for Data-in replication with Azure Database for MySQL-Flexible Server.

Conclusion

We’ve now set up replication between Azure Database for MySQL flexible servers in two different regions. Any changes to primary instance in one region will be replicated to the server in the other region by using the native replication technique. Take advantage of this solution to scale read workloads or to address DR considerations for potential regional disasters.

If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!