I am Microsoft database consultant working as a Database administrator for more than +10 Years I have very good knowledge about Database Migration, Consolidation, Performance Tuning, Automation Using T-SQL, and PowerShell and so many other tasks I do it in multiple customers here in KSA and as of now, I am working in Bank Albilad managing the core banking system that is hosted in SQL Server Database 8 TB. Also, I am Microsoft certified 2008 and 2016 in SQL Server (2x MCTS, 2x MCTIP, MCSA, MCSE) and I am Microsoft Certified Trainer (MCT) also I am azure Certified (AZ-900, AZ-103) also I was awarded by Microsoft Azure Heroes 3 times as (Azure Content hero, Azure Community hero and Azure Mentor) For more information check my page
https://mostafaelmasry.com/about-me/
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
Demo Preparation
Database Configuration
Extended Event Implementation
SQL Server Custom Jobs Implementation
Deadlock Simulation
Capture the Deadlock information using SQL Server profiler
Capture the Deadlock information using SQL Server Error Log
Capture the Deadlock information using SQL Server Extended Event
Capture the Deadlock information using System_Health Default Extended event
Capture the Deadlock counts per Day
Capture the Deadlock information using SQL Server Custom Jobs
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 SourceDatabases
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 SQLor pgAdmin Clint
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
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
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 2, Part 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
DB Cloud TECH 