Monitoring and Tracking SQL Server Blocking & Deadlocks process


Introduction

SQL Server deadlock one of the issues that can be happened in any SQL Server, today in this article I will not explain what is Deadlock and How we can solve it but the main purpose of this article is sharing the scripts I am using it for monitoring this kind of process let us start

It will be great if you share with us your experiences and your scripts in the comment

  1. Demo Preparation
  2. Database Configuration
  3. Extended Event Implementation
  4. SQL Server Custom Jobs Implementation
  5. Deadlock Simulation
  6. Capture the Deadlock information using SQL Server profiler
  7. Capture the Deadlock information using SQL Server Error Log
  8. Capture the Deadlock information using SQL Server Extended Event
  9. Capture the Deadlock information using System_Health Default Extended event
  10. Capture the Deadlock counts per Day
  11. Capture the Deadlock information using SQL Server Custom Jobs
  12. Check the Row Lock Blocking Info
  13. Other Scripts for Monitoring
Continue reading “Monitoring and Tracking SQL Server Blocking & Deadlocks process”

Fundamental Information for Azure Open Source Databases


As all of us know that Microsoft Azure Supporting many different database types such as Azure SQL, Azure Cosmos DB, and Azure support also MariaDB, MySQL, and PostgreSQL so it easily to migrate your current database (MariaDB, MySQL, and PostgreSQL) to Azure , in this post i will share Fundamental information about MariaDB, MySQL, and PostgreSQL on Azur

  • For more information About Azure SQL Check this Link
  • For more information About Azure Cosmos DB check this link
  • For more information About Azure Exam ( Az-103, DP-200, DP-201, DP-900, DP-300)preparations Check this link
  • For more information About other Azure Services check this link
Microsoft Azure Open Source Databases

MariaDB, MySQL, and PostgreSQL on Azure

  • Microsoft Azure Offers 3 different open source Database Platform on Azure (MySQL, MariaDB, PostgreSQL) and this 3 Database platform service comes with native high availability, automatic patching, automatic backups, and the highest level of security protection
  • Service Tiers Supported by open source Database Platform on Azure
  • Basic: Best of a light workload
  • GP (General Purpose): Best for High Workload required high IO
  • Memory-Optimized: Best for High Workload required high performance and in-memory speed.
  • Supported version for open Source database platform on Azure
  • MySQL and MariaDB on Azure Transactions on either platform are written synchronously to storage. If a node interruption occurs, the database server will automatically create a new node and subsequently attach the storage to the new node. Any transactions in flight are not committed and active connections to the database are dropped. As mentioned with Azure SQL Database, it is important to ensure that applications that connect to the database service include retry logic, also known as connection resiliency, in their database connections.
  • Database migration can be done for 3 open source database platform using Microsoft DMS Azure Database Migration Service
  • Azure SQL Database for MySQL and PostgreSQL does not have a TDE: But Microsoft provided a disk encryption method.
  • Azure PostgreSQL Deployment Model (Single Server, Flexible Server {Preview} or Hyperscale {Citus})
  • Flexible Server, a new deployment option now in preview for Azure Database for PostgreSQL and Azure Database for MySQL, offers customers enhanced choice, performance, and scale by building on a new architecture with native Linux integration “Ignite 2020 News
  • Azure PostgreSQL Hyperscale used for large-scale databases that scale-out across multiple nodes
  • Azure PostgreSQL Hyperscale Server Called Nodes and it is working together in a shared-nothing
  • And the nodes are added to the Server Group
  • Each Server group have something called a coordinator node and multiple workers nodes
  • When the APP Sends the Transaction to Azure PostgreSQL Hyperscale, it sends it to the coordinator node and the coordinator node will find the worker nodes to collect the data to the APP.
  • Azure PostgreSQL Hyperscale is sharded, this means the data in a table can be split into multiple nodes using a type of table called a distributed table.
  • During the Deployment of Azure PostgreSQL Hyperscale Microsoft allow you to create additional worker nodes along with a coordinator node
  • You can deploy Up to 20 worker nodes and in case if you need more you should communicate with the Microsoft support team
  • You can connect to Azure PostgreSQL Using SQL or pgAdmin Clint
  • More information Check Documentation: https://docs.microsoft.com/en-us/azure/postgresql/
  • Query Store Supported in Azure MySQL https://docs.microsoft.com/en-us/azure/mysql/concepts-query-store

Azure SQL Database administration Tips and Hints Exam (DP-300)


Finally, I got my certification Azure Database administrator Associate for Exam (DP-300) after two times failure, during the journey of study I watched many courses, videos, and articles, and this post of today is for spreading what I have from the knowledge and what I learned during the journey, and I do two things during my study published around 70 articles in Azure technologies and prepared one document to contain many pieces of information for Azure SQL Database administration

So, in this post, you will find all of the resources that you can start your study from it and the document I created it

Azure Database Administrator Associate
Continue reading “Azure SQL Database administration Tips and Hints Exam (DP-300)”

Solve SQL Server Resource Database Crash


Based on Microsoft Documentation <The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata>

In this article, I will explain below 5 points:

  • information you need to know it about resource Database
  • List of Errors You can See it when mssqlsystemresource Corrupted or lost
  • Methods for troubleshooting these errors
  • Other Solutions to repair ‘mssqlsystemresource’ Database
  • How to take a backup from mssqlsystemresource Using SQL Server T-SQL
Continue reading “Solve SQL Server Resource Database Crash”

Troubleshooting Performance issues Like Microsoft Engineers Part 4


In the previous parts, we explained How we can Troubleshooting the Performance issue using built-in reports in SQL Server and How we can collect SQL Server logs using PSSDIAG and BPCheck then we explained How we can analyze and analyze the output of the PSSDIAG tool using SQL Nexus and PAL tool, check the previous parts from here  (Part 1, Part 2Part 3)

Today in Part 4 we will talk about Some of the cases that can lead us for AG latency Yes (SQL Server Always on Latency) one of the most common performance issues is AG latency especially when you implemented and configure the SQL Server Always on between multi-subnet, at this time you can see (HADR_SYNC_COMMIT) wait are very high or if you found the log send queue size is high at this time the SQL Server Database log files will keep increased in the primary and not reduced because of the Log Queue size on the secondary are very high and this meaning something is blocking the log Queue on the secondary, let us see what is HADR_SYNC_COMMIT and compression on AG Log transport

Check this Article to access all of the Parts of Troubleshooting Performance issues Like Microsoft Engineers

PART 4
Continue reading “Troubleshooting Performance issues Like Microsoft Engineers Part 4”