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”

Troubleshooting Performance issues Like Microsoft Engineers


IF you are a Database administrator and managing SQL Server Database so this article for you, this article is a parent for 3 parts for How to troubleshoot the performance issues on SQL Servers such as Microsoft engineers and support team.

Part 1

In the first part, I explained the built tools and reports in SQL Server and some other scripts free you can use it for troubleshooting performance issues.

✅Check the first part for here 👉 https://mostafaelmasry.com/2020/09/09/troubleshooting-performance-issues-like-Microsoft-engineers-part-1/

Part 2

In the second part, I explained How we can collect all of the SQL Server logs during the performance issues using the PSSDIAG tool.

✅Check the second part for here 👉 https://mostafaelmasry.com/2020/09/09/troubleshooting-performance-issue-like-Microsoft-engineers-part-2/

Part 3

In the third part, I explained How we can load and analyze the output of the PSSDIAG tool using the SQL Nexus tool and Performance Analysis of Logs (PAL) tool.

✅Check the third part for here 👉 https://mostafaelmasry.com/2020/09/09/troubleshooting-performance-issues-like-Microsoft-engineers-part-3/

PART 4

In this part we will talk about Some of the cases that can lead us for AG latency

✅Check PART 4 for here 👉 https://mostafaelmasry.com/2020/09/10/troubleshooting-performance-issues-like-microsoft-engineers-part-4/

Keep Following

Troubleshooting Performance issues like Microsoft Engineers Part 3


If you don’t read the previous parts you can read it from here (PART1, PART2), in this part of today we will explain how we can analyze the output of PSSDIAG using:

  1. SQL Nexus
  2. SQL-Nexus-and-ReadTrace-Analysis-Scripts
  3. Performance Analysis of Logs (PAL)

in the previous part we explained how we can collect the most sensitive information and logs during the performance issue using PSSDIAG and today we will take the output of PSSIDAG and we will analyze it using SQL Nexus

All Parts (Part 1, Part 2, Part 3, Part 4)

PART 3
Continue reading “Troubleshooting Performance issues like Microsoft Engineers Part 3”

Troubleshooting Performance issue like Microsoft Engineers Part 2


In the first PART we talked about the built-in tools in SQL Server management studio and some other custom packages scripts can help us in troubleshooting performance issues, but what we can do if these tools did not support us by what we expecting and we need more information for more investigation, Most of us in the critical cases we will open case wit Microsoft support, and the first thing Microsoft engineer do it, is collecting the data during the issue, to be able to analysis it and today in this part we will cover the tools that are used by Microsoft engineers for collecting the data such as PSSDIAG tool and the upcoming part No 3 we will talk about the tools used for analyzing the output results of PSSDIAG tool such as (SQL Nexus, Performance Analysis of Logs (PAL))

All Parts (Part 1, Part 2, Part 3, Part 4)

This image has an empty alt attribute; its file name is image.png
Part 2
Continue reading “Troubleshooting Performance issue like Microsoft Engineers Part 2”

Troubleshooting Performance issues Like Microsoft Engineers Part 1


Introduction

Most of us as DBA (Database administrators) have the base knowledge about the performance issues, and during our work days we faced many of performance issues, and I believe that troubleshooting performance issues is something not easy and not straightforward so there is no documented steps for How you can troubleshoot the performance issue because the troubleshooting depend on the type of the performance issue but most of the technical support teams used some common tools and scripts for troubleshooting performance issues, and the most important thing that you should build your own scripts library that can help you for troubleshooting the most common cases of Performance issues such as (Locking and Blocking on Database, High CPU , Timeout, Slowness , …etc) So my advice Start to build your own library and try to be more proactive and don’t waited the issue come to you , try to find it before it is fire, in this article of today I will try to explain some tools and scripts can help you while troubleshooting any performance issues and this tools used by the most expert guys and some of them used by Microsoft technical Support team.

This article for how to capture the workload during the performance issue to be able to know what the issue is but not for how to solve this issue.

If you have any other helpful tools or scripts not mentioned here and it can help for troubleshooting the performance issue share it in the comment to be the article as a repository for all of us.

Check the other parts (Part 2, Part 3, Part 4)

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

Azure DP-900 (Data Fundamental) Certification perpetration


Nothing Is hard when you decide to do it, from two weeks I earned my Certification of Azure Data Engineer and today I passed Exam DP-900 and I got my new badge for Azure Data Fundamental DP-900 with Score 882 and I passed this Exam by 3 resources only.

Azure Data Fundamentals
Continue reading “Azure DP-900 (Data Fundamental) Certification perpetration”

Azure Data Engineer Certification Preparation


This week i got my new Azure Certification badge (Azure Data Engineer) and today i am writing this article to share all of the resources i collected it during my study this is not a technical article I will not explain any concept or any technical information here this article is repository article.

Exam information

To get Azure Data Engineer Badge you should pass two exams DP-200 and DP-201

  1. DP-200 (Implementing an Azure Data Solution)
  2. DP-201 (Designing an Azure Data Solution)

Both exams are They are very similar DB-200 related for How to do and How to Implement and DB-201 related for How to design, how to provide a solution, how to choose between data sources, what is the best solution for data movement …etc.

DP-201 Exam contain many cases questions you should read the case very well and answer the questions on this case

Also, during the study, you can prepare for the other two exams because both of them are covering the same concept of Azure Data Engineer. Study well and enter the 4 exams (DP-200, DP-2001, DP-300, DP-900)

  1. DP-300 (Administering Relational Databases on Microsoft Azure)
  2. DP-900 (Microsoft Azure Data Fundamentals)
Azure Data Engineer Associate

After passing two exams you should earn the badge like this https://www.youracclaim.com/badges/b14fd62d-59ca-44c2-aa6d-b0ff85140b0b

Continue reading “Azure Data Engineer Certification Preparation”

What you need to know About Azure SQL backup


Most of DBA are coming from On-premise Database Servers they know very well How we can manage SQL backup using SQL Server native backup or even using SQL Server Backup third party tool but when we moved our database to cloud (Azure SQL) all of us starting to ask How the backup is taken in Azure for SQL Database, what is the RTO and RPO, How I can restore the backup all of this information I will list them in my article of today.

Continue reading “What you need to know About Azure SQL backup”

How Azure RBAC Work


Azure RBAC is Role-based Access Control it is the gateway for any access (access management of Azure resources) and this service will help you to manage the access and to know who has access and what is kind of access he should take it.

RBAC Diagram
Continue reading “How Azure RBAC Work”

Azure Cosmos DB High Availability and Disaster Recovery Architecture


Part 6 in Azure Cosmos DB architecture Series Keep Learning and Spread your Knowledge if you don’t read the previous parts I highly recommend you to take a look into them:

Continue reading “Azure Cosmos DB High Availability and Disaster Recovery Architecture”

Azure Cosmos DB API Architecture


Again, still we have a lot of information we can spread our knowledge on it for Cosmos DB today I will talk about Cosmos DB API options and How I can choose between them and what is the key factor I should consider it when I choosing the Cosmos DB API. IF you don’t read my last articles in Azure Cosmos DB so here is your road map to learn Cosmos DB:

Continue reading “Azure Cosmos DB API Architecture”

Distributed Tables in Azure Synapse SQL


Azure Synapse is the new Generation of SQL DW (Azure SQL Data Warehouse), that is launched in the past year In November 2019 the First announced for Azure SQL Synapse was in Microsoft Ignite 2019, With Azure Synapse you can bring together cloud data warehousing and big data analytics into a single service platform, Azure Synapse is massively parallel processing (MPP) Engine. Microsoft Added on it many features, With Azure, Synapse Compute is separate from storage, this means you can scale compute independently for the data in your System (APP), With Azure synapse, you can bring the relational Data and Non-relational data together in one place and easily you can do your query and your analytics on a huge amount of information, Azure Synapse build for a massive parallel query for Analytics not for transaction database, Azure Synapse uses Azure Data Lake Storage Gen2 for more information About Azure Data Lake Gen2 Check this Post, Azure Synapse Support (Apache Spark integration, Power BI, Azure ML), Also you can take a look to this demo with Rohan Kumar this demo is part from Microsoft Ignite 2019 Keynote and you can Follow Rohan Posts from here

Continue reading “Distributed Tables in Azure Synapse SQL”

Consistency Architecture in Azure Cosmos DB


In my first article in Azure Cosmos DB, we talked about What is Cosmos DB and How to provisions it, then in the Second article we talked about Partitioning Architecture in Azure Cosmos DB, then in third Article we talked about Azure Cosmos DB Global data distribution Architecture, today we will complete our series to talk about Consistency Architecture in Azure Cosmos DB For more information about my Azure articles check this link, Keep Following to learn More about Azure and Cosmos DB

Introduction

Microsoft Azure Cosmos DB Provide 5 level of Consistency (Strong, Bounded staleness, Session, Consistency prefix, Eventual) Before Explaining What is Consistency, Why I should take care about Consistency, Which Consistency is best for my APP design before all of that let us talk first about CAP Theorem

Consistency in Azure Cosmos DB
Continue reading “Consistency Architecture in Azure Cosmos DB”

SQL Server table partitioning – planning and implementation guidelines


Nowadays, the big environments that have large databases with a heavy amount of transaction they had one common performance issue in the OLTP transaction when the database size increased to the hundreds of gigabytes or more it will be difficult to load data.

Microsoft SQL Server provides us table partitioning to make this OLTP operation more either and manageable. Applying the partitioning on the large table it will split the table and its indexes into the smaller partition, at this time the maintenance procedure can be applied to an exact partition instead of doing it on the whole table. Additionally, the SQL Server engine will direct the filtered queries to the suitable partitions instead of the entire table. All of this will enhance the performance of OLTP transactions significantly. I will list Commons steps for database migration, including the following:

  • Concept of table partitioning
  • Why we need to implement Table Partitioning
  • Table partitioning limitation and restriction
  • Table partitioning type
  • Table partitioning dependence
  • Table partitioning implementation
  • Conclusion
  • Useful links
Continue reading “SQL Server table partitioning – planning and implementation guidelines”

Azure Cosmos DB Global data distribution Architecture


In my first article in Azure Cosmos DB, we talked about What is Cosmos DB and How to provisions it, then in the Second article we talked about Partitioning Architecture in Azure Cosmos DB. And today we will complete our learning to understand What is global distribution in Azure Cosmos DB? For more information about my Azure articles check this link

When we say global distribution you Should think Automatically in Cosmos DB because it is the most solution in the market provide this feature global distribution, But why global distribution we should be interested in it for couple of reasons unlimited elastic write and read scalability, Yes Cosmos DB provides a solution to read and write option in multiple regions most of the databases solutions provide read scalability but in Azure Cosmos DB we have also write scalability.

Continue reading “Azure Cosmos DB Global data distribution Architecture”

Partitioning Architecture in Azure Cosmos DB.


Introduction

In my first article in Cosmos DB, I talked about the basic information about How to provision Cosmos DB, and today we will complete our discussion in How Cosmos DB Partitioning work, and What are the Logical Partition and Physical Partition then in the upcoming posts we will talk more and more about Designing and Architecture Layers for Cosmos DB Such as Consistency levels in Cosmos DB Which Consistency levels are suitable with your APP, What are Cosmos API choices, Disaster Recovery and High Availability in Cosmos DB Keep Following ..

  • Cosmos DB Physical Partition
  • Cosmos DB Logical Partition
  • Cosmos DB Partition Key
  • How to Choose the Right Partition Key
  • Resources
Continue reading “Partitioning Architecture in Azure Cosmos DB.”

Full Architecture for SQL Backup to Blob Storage in Cloud


One of the tasks that any DBA should know How to take backup to URL or restore a backup from URL and this’s the easiest part very simple steps and command but this is not our main objective, we should understand all of the components how it is working, what is the different options, what is the changes happened before SQL Server 2016 and After SQL Server 2016 to take the maximum benefits from this service or feature here in my article I will try to conclude all of this things as much as I can.

  • Introduction.
  • Benefits from taking backup to URL.
  • What is Azure Storage Blob?
  • What are the Pre-requisites to do SQL backup on the URL?
  • What is the Difference between SAS Token and access key Azure Storage
  • When I Should use SAS Token or access key Azure Storage
  • When the Backup files will be saved as Block Blob or Page Blob
  • How to Generate and configure Access Policy, Access key, and Shared Access Signature
  • Create SQL Server Credential using access key or SHARED ACCESS SIGNATURE
  • Backup to Azure Storage and restore from Azure Storage.
  • Validate the Backup File.
  • Notes and Limitation for SQL backup to URL.
  • References and Resources.
SQL backup to Azure Storage
Continue reading “Full Architecture for SQL Backup to Blob Storage in Cloud”

Big Data Storage in Microsoft Azure


Azure storage is a Microsoft solution designed to store your data on it with different models (Blobs, Tables, Queue, File Shares, and Disks) to know the differences, usage, High Availability and the architecture for Azure storage I highly recommend you to check this path you will find a lot of tips and articles About Azure storage subject.

Now let us take a look about storage technology when we talk about Big data, what is the best option for our data and how I can choose the best option, based on what so let us think from architecture and design point of view.

Microsoft provided a variety of options to store your big data on it based on your requirements and based on the data type, in this article we will discuss 4 options that can be used as Azure storage solution for big data.

  • Azure Storage Blobs
  • Azure Data Lake Store
  • Azure Cosmos DB
  • Azure HBase on HDInsight
Continue reading “Big Data Storage in Microsoft Azure”

SQL Server Stretch Database to Azure


One of the interesting feature announced in SQL Server 2016 and later to move your cold data to Azure and the data will be accessible from your APP with ZERO development cost no change in the APP level at all and no change on the Query level in SQL Server it is just configuration and implementation for Stretch Database from on-premises to Azure, IF you are DBA and you have tables with big size and you need to reduce it, if you have Cold data and the APP not accessing it frequently, If you need to reduce the backup and restore time at this time this feature for you.

Before starting on the benefits of this feature and How to implement it I highly recommend you to take a look into this page to learn more about Azure SQL https://lnkd.in/edn6nyY/#AzureSQL

  • Benefits from SQL Server Stretch Database
  • How to Configure the SQL Server Stretch Database to Azure
  • Validate the Database Stretch configuration and Data movement
  • How to Disable the Stretch data
  • References
  • Keep Following
  • Cloud Tech Website blog survey
SQL Server Stretch Database
Continue reading “SQL Server Stretch Database to Azure”

Azure SQL Database Tiers Options


Microsoft azure providing multiple options for Azur SQL Database Tier, supported Azure Single Database, Azure Elastic Database, and Azure managed instance before starting for what is the options and the details of it if you need to learn more about Azure SQL provisioning, security, High availability, Performance, …etc. check this link you will find very useful articles covering the most basic topics in Azure SQL Database. ➡️ https://lnkd.in/eABFfrs 💨✅ Today I will start to explain the Purchase mode and database tiers related to Azure SQL Single, Azure SQL Elastic Database and Azure SQL Manged instance.

Azure SQL Database Tiers
  • Azure purchasing models
  • Azure SQL Single Database Tiers Options
  • Azure Single Database Tiers on DTU-Based
  • Azure Single Database Tiers on vCore-Based (GP)
  • Azure Single Database Tiers on vCore-Based (Hyperscale)
  • Azure Single Database Tiers on vCore-Based (Business Critical)
  • Azure Elastic Database Tiers Options
  • Azure Elastic Database Tiers on DTU-Based
  • Azure Elastic Database Tiers on vCore-Based
  • Azure SQL Managed Instance Tiers
  • References
  • Keep Following

Azure purchasing models:

Microsoft Azure supported two Purchase model:

  1. DTU-Based Model: Supported Azure SQL Database only
  2. vCore-Based Model: Supported Azure SQL Database and Managed instance

For more information check this article

Azure purchasing models
Continue reading “Azure SQL Database Tiers Options”

Azure Content Hero and Community Hero


On 15 May 2020 I was received Azure Heros badger Content Hero and today 19 June 2020 I received my second Azure Heros badger Community Hero I am So proud 💪 of my activity and contribution to SQL Server and Azure 👏 Glad to know that my contribution to the community is appreciated. Next step is Azure Mentor Badger and Azure Inclusive Leader Badger Coming Soon https://www.microsoft.com/skills/azureheroes

Content Hero badgers are given out for sharing valuable knowledge at conferences, meetups, or other events. Recipients of this rare award have created original content, sample code, or learning resources and documented and shared their experiences and lessons to help others to build on Azure

Azure Content Hero

Community Hero badgers are given out for contributing materially by organizing meetups or conferences or by sharing content and being an active member of the community

Azure Community Hero

For More Information About My Azure articles and posts check here ➡️ https://lnkd.in/edn6nyY

Keep Following

Recover blob data from azure storage after delete


One of the tasks that an Azure administrator should take care of it when you are managing the data on your organization and saving it on Azure Storage you should know How to keep it up and running and also you should know How to recover it after delete

Today we will see what is the required configuration we should do it to be able to recover your deleted data from Azure storage Microsoft Azure has a feature called SOFT DELETE by using this feature you can configure the Azure storage to keep the data available for recovery within the specified data retention period after the delete operation before starting for explaining the soft delete I highly recommend you to take a look into this list of articles you will find very important information and useful content for How to manage Azure storage Check the Articles from ➡️ here

  • Azure Storage Soft delete options and information
  • How to Enable Soft Delete Configuration on Azure Storage account
  • How to check the deleted data and recover it
  • Keep Following
Recover Blob After Delete
Continue reading “Recover blob data from azure storage after delete”

How to Identify and to Delete unused Blobs in Azure Storage


Case Scenario

As Azure administrator, once of the tasks can be assigned to you is identifying all of the blobs that are not modified for the last 100 days and delete them and we need to automate this task to run automatically on Azure Storage

Introduction

Azure Storage services are one of the most important Azure services that many other services depend on it that’s why we should keep monitoring the size of it to reduce the cost, and of the things that we should take care about it, the blobs that are not used from a long time like our case scenario, that’s why Microsoft Azure provided us something called Azure Blob storage lifecycle

Continue reading “How to Identify and to Delete unused Blobs in Azure Storage”

Provisioning a Gen 2 Azure Data Lake


Gen 2 Azure Data Lake It is Microsoft Service designed for big data processing, and it is a combination between azure storage feature and Azure Gen1 Data lake and Microsoft recommend to use GEN2 for X of reasons (Performance, Management, and Security) Azure Gen2 Data Lake is the addition of a hierarchical namespace  , So Gen2 Azure Data Lake is designed for managing and storing a very big large amount of data petabytes. For more information about Gen2 Azure Data Lake Check Microsoft documentation

Continue reading “Provisioning a Gen 2 Azure Data Lake”

Azure Storage Account Monitoring Services


Azure Storage is one of the Azure services and most of the azure services depend on it that’s why we should know how we can monitor the capacity, performance, and so many other things but before listing the Azure Storage account monitoring tools I highly recommend you to take a look into this link that contains a lot of articles related to Azure storage and if you are interested to know more about Azure services such as Azure VM, Data Studio, SQL Database, PowerShell, CLI, Subscription Check this link contain a lot of articles in azure, and if you are preparing for Azure administrator certification check this article

  1. Monitor activity on Azure storage
  2. Azure monitoring classic
  3. Azure Storage insights
  4. Main Azure Monitor
  5. Azure Storage account monitoring Resources

Monitor Activity on Azure Storage

To check the activity executed on Azure storage like data process and actions ingress and Egress you can check it using Activity log and Log Analytics Services from this place you will have full Visibility about what is the process, actions and command executed on Azure storage account For more information I already explained this point in this article (Azure Storage Account and Log Analytics)

Continue reading “Azure Storage Account Monitoring Services”

Azure Cosmos Database Basics


Introduction

Azure Cosmos Database one of the Non-Relational NoSQL Database provided by Microsoft and we have another NoSQL Options in Azure like (Data Lake Storage Gen2, Blob Storage, Redis Cache, HBase in HDInsight), also Azure Cosmos Database Supported multiple API like (Core SQL, Mango DB, Cassandra, Graph, Azure Table) IF you need to learn more about this Point Check this Link Choose the appropriate API for Azure Cosmos DB, Azure Cosmos Database supported Write option in multiple regains in the same Database and the synchronizations tacking care about syncing the data. For more information about Azure Cosmos database, I highly recommend you to look into Microsoft Documentation

  • How to Provision Azure Cosmos DB Account
  • How to Provision Azure Cosmos DB Container
  • Load and Explorer the data into Azure Cosmos DB Container
  • Replication in Azure Cosmos DB
  • Data Consistency in Azure Cosmos DB
  • Cosmos DB and open Source
  • Azure Cosmos Database Resources
Cosmos DB NoSQL

How to Provision Azure Cosmos DB Account

To Create Azure Cosmos Database we need to create First Database account and this like Database instance we can host on it multiple databases, then after this step, we need to create Container ID and this the Database name in this you can find below what is the information required when we need to create Azure Cosmos Database account and for how to do it check below 👇✅ GIF File ✅to know how you can do it and For more information Check this Article

  • Account Name: it is like the instance name in SQL Server
  • Notebooks: Jupyter notebook is an open-source web application that allows you to create and share documents it is like the Azure Data Studio Notebook For more information read this Article
  • Location: The location you need to host on it your Azure Cosmos DB account
  • Account Type: It is something like a definition or categorized, we can consider it like Azure Resource Tag.
  • Geo-Redundancy: Enabling this Option automatically replicates your data to the region geo-paired with your current region you selected it. For more information about the Region geo-paired check this link Azure Regional Pairs IF you don’t enable it during the setup you can enable it after the configuration from the main overview of the Azure Cosmos Account
Enable Geo-Redundancy
  • Multi-region Write: Enable the write option on the Regions you selected it on the geo-redundancy this means If you need to enable the Multi Regions Write option you Should Enable the Geo-Redundancy option with it this option you can enable it after the installation from Replicate Data Globally option If you need to learn more about this option check this Link

Continue reading “Azure Cosmos Database Basics”

Azure SQL Database Automatic Tuning Feature


What is Azure Automatic Tuning Feature

Microsoft Azure SQL Database Automatic tuning one of the monitoring and tuning features we can use it on Azure SQL for more information about other monitoring and tuning services check this Article, Automatic tuning is a fully managed intelligent performance service used for collecting the Azure SQL Database Workload and monitor queries executed on a database continuously, and it is working based on AI Artificial intelligence and ML Machine Learning technologies, with Automatic tuning Azure SQL will automatically capture all of the performance issues and analyze it then will create a new index or drop index  based on the performance case, even one of the capabilities of this services after applying the performance solution it will be under validating based on the AI and ML technologies and if the Azure Automatic tuning found this solution not fixed the issue, it will rollback it automatically, all of this actions will be recorded in the logs to be able to track it

Continue reading “Azure SQL Database Automatic Tuning Feature”

Security Layers on Azure SQL Database


In this article I will explain the options available on Azure we can use it for securing Azure SQL Database, as we know that we have azure deployment model options (Single Database, Elastic Pool, Managed instance, SQL on Azure VM) our article today focused on Azure Paas Database deployment option (Single Database, Elastic Pool, Managed Instance) for more information about Database Deployment option and more details Azure SQL Database deployment models check below articles and if you are interested in more Azure Articles in Database and other services check this Link, also IF you are preparing for Azure AZ-103 so this Post for you check it now.

  1. Azure Database Deployment model options
  2. Azure Single Database Fundamental
  3. Azure SQL Database Elastic Pool Fundamental
  4. Azure SQL Database Managed Instance Fundamental
Azure Database Security Layers
Continue reading “Security Layers on Azure SQL Database”

Azure SQL Database Data Sync Group


Introduction

Azure Data Sync is Microsoft service announced on June 18, 2018, and it is used to sync the data from Azure SQL to another Azure SQL Database or from SQL Server on-premises to Azure SQL Bi-Directional this services used for data sync only and you should not use it as disaster recovery or as migration tool or as read-only replica IF you need to know when you should use azure data sync check this Microsoft article, and if you need to know What is the best option for Azure SQL Database Migration, replication and read-only check this link you will find very useful articles

Azure Data Sync Concept

  • Sync Group: is one of the databases that you want to synchronize
  • Hub Database: is one the synchronize Databases
  • Member database: another database in the data sync synchronization is member databases
  • Synchronization: Happened between Hub Database and member databases this means when you do change on one of the member databases the changes reflected on the other member databases through the Hub database
Continue reading “Azure SQL Database Data Sync Group”

Azure SQL Elastic Database Job Agents Configuration


Introduction

As Database administrator we know that SQL jobs managed by SQL Server agent services, but this concept on SQL Server on-premises, in Microsoft cloud we have another something called Azure SQL Elastic job. Before starting in the definition of it I highly recommend you to understand first what is Azure database deployment model options and How we can configure Azure single database, Azure elastic Pool database and Azure Database Managed Instance Check Below Articles 👇

In this article of today I will cover below points and these points from my view are the basic points still we have many others points in this subject and I will cover it in the upcoming posts, Keep Following me and if you are interested to learn more about Azure SQL Database check this link and if you are interested to prepare for Azure administrator certification AZ-103 or AZ-104 check this link

  • What is the Azure SQL Elastic Job?
  • Differences between Azure SQL Database Jobs and SQL Agent
  • Azure SQL Elastic Database jobs Component
  • Configuration Prerequisites
  • Azure SQL Elastic Job Full Configuration (8 Steps)
  • Review the Elastic Job Configuration
  • References
Continue reading “Azure SQL Elastic Database Job Agents Configuration”

Azure SQL Database Managed Instance Fundamental


SQL Server Managed instance is one of the Azure SQL deployment model options and it is providing 100 % Compatibility with SQL Server on-premises so managed instance it the best migration option when you need to migrate the on-premises database without changing any configuration or losing any features not supported by Azure Single database and Azure SQL Database elastic pool. IF you need to know more information about Azure Single database and Azure elastic pool check this post and if you are interested and preparing for azure administrator AZ-103 certification check this link

  • Azure SQL Manged instance Tips
  • Differences between SQL Server On-Premises and Azure SQL Managed instance
  • Azure Managed instance Service Tier
  • Azure SQL Managed instance Security
  • References
Azure Deployment Model Options
Continue reading “Azure SQL Database Managed Instance Fundamental”

Move Azure Single Database to SQL Elastic Pool


AS I explained before in my previous articles How to provision Azure Single Database and How to Provision Azure SQL Database elastic pool today I will explain How we can move Azure Single Database to New Elastic Pool, For more information about Azure Database Deployment model Check this Post and If you are interested in More Azure Database Articles to check this Link and if you are Preparing for AZ-103 Azure Administrator Certification check this Article, In Microsoft documentation, you will find How to Provision Single Database or Elastic pool Server but you will note find information for How to move Single database to Elastic pool server

Case Scenario

Let us assume that we have 2 Azure Single Database Servers contain 3 Database with different Service Tier and we need to move them to Azure elastic pool

Continue reading “Move Azure Single Database to SQL Elastic Pool”

Azure SQL Database Elastic Pool Fundamental


As we know Azure SQL Database elastic pool is one of the Microsoft Azure database deployment model options to know more about these models check this post and to know more about Azure Single Database check this article and if you interested to check all of the Azure SQL Articles check this link, IF you are Preparing for AZ-103 Azure Administrator Certification check this Article

Azure SQL Deployment Model options
Continue reading “Azure SQL Database Elastic Pool Fundamental”

Azure Single Database Fundamental


AS we now Azure SQL Single database is one from deployment Azure Database models if you need to know more information check this post-Azure Database Deployment model options today I am writing about Fundamental information for Azure Single Database

For More Azure Database Articles Check this Link and If you are Preparing for AZ-103 Azure Administrator Certification check this Article

Create Azure Single Database
  1. The Single Database deployment option creates a single isolated database in Azure SQL Database
  2. Azure Single Database Support DTU-based and Vcore-based Purchased Model
  3. The internal of SQL Database server is hidden from the end-user and all are managed by Microsoft Azure
  4. Azure Single Database has it is own Resources (DTU or V-Core) and this resource will not be shared with other databases in the same single instance. Each Database has dedicated resources.
  5. Single Database Isolated meaning if you created two single databases on the same single database server each Database has dedicated resources and the two databases cannot be communicated together and I think this one is a security feature
  6. This means Azure Single Database is the best option for small workloads with applications connects to one DB.
  7. To Provision Azure SQL Single Database, you need (resource Group, Database name, Server Name, User Name, Password, Location, deployment option either single or elastic pool, Compute Storage and purchase model either DTU-based or vCore-based)
  8. In Azure SQL Single database provisioning you can create new or select existing one from (Resource group and Server name)
  9. In Azure SQL Single database provisioning the (resource Group, Database name, Server Name) Should be a unique name.
Continue reading “Azure Single Database Fundamental”

Azure Database Deployment model options


Azure Database Deployment model options

Azure Administration AZ-103 Exam preparation and learning Resources 2020


Introduction

undefinedMy Name Is Mustafa Elmasry I am Database Consultant MCTS, MCTIP, MCSA, MCSE, MCT for more information about me check this page Today I need to announce that I am Very Excited and Thrilled to have passed my AZ-103 I am now Microsoft Certified: Azure Administrator Associate, That’s how I pass my time during #COVID19 days. From 5 months I passed AZ-900 Microsoft Certified: Azure Fundamentals and I started to prepare for Azure Administrator Exam AZ-103 and I am preparing now for the AZ-104 beta new version. I know that I am Database Engineer and this certification not related directly to my carrier objective but I have believed that before starting to learn any cloud services you should have full visibility about azure infrastructure to be able to manage your Azure services well. During the last 5 months, I started to my study journey for Azure administrator and from the first day, I took a covenant on myself to write down all of the notes, hints, and tricks to be a repository that can help the others for how to prepare for Azure administrator certification in this post you will find all of the resources, notes and even the articles I write it during this journey. I take 3 Courses to prepare for the exam AZ-103. In this article, you will find two documentation The First one contain all of the tips and hints I recorded during my study, and the second one it is full documentation explain most of the features and services in azure administration track. In the end, I will be so happy if you share with me your comment and feedback on this post and documents also if anyone has any issue in SQL Server or Azure SQL don’t hesitate to contact me

Microsoft Certified: Azure Administrator Associate
  • Azure AZ-103 Posts and Articles
  • Microsoft Azure AZ-103 Tips, Hints and Notes 2020
  • Azure Administration AZ-103 Documentation 2020
  • Azure Administration AZ-103 Free Resource
  • Another Important Azure Resource
  • Azure Administration AZ-103 Non-Free Courses
  • Keep Following
Continue reading “Azure Administration AZ-103 Exam preparation and learning Resources 2020”

Microsoft Azure AZ-103 Tips,Hints and Notes Part 3



Today in this Part i will list some important notes related to Deploy and Manage Virtual Machine and the previous parts check it here ➡️ Part 1 Manage Azure Subscription ➡️ Part 2 Implement and Manage Storage

For more information about other Azure posts and articles, all of them are collected in one link ➡️  HERE 

Manage Azure VM Articles

  1. Azure Virtual Machines Types,Series
  2. What is Azure AD Connect Sync Engine
  3. Virtual Machine (VM) Backup and Restore (Vault)
  4. Moving Azure VM Using Recovery Service Vault
  5. Azure VM Backup Soft Delete Security features

Deploy and Manage Virtual Machine Tips

Continue reading “Microsoft Azure AZ-103 Tips,Hints and Notes Part 3”

Microsoft Azure AZ-103 Tips,Hints and Notes Part 2


Today in this Part i will list some important notes related to Implement and Manage Storage to Check Part 1 got to this link Microsoft Azure AZ-103 Tips, Hints and Notes Part 1

For more information about other Azure posts and articles, all of them are collected in one link ➡️  HERE 

keep following for the upcoming parts in Azure Tips, Hints, and Notes series.

Manage Azure Storage Account Articles

  1. Managing and implementing Azure storage
  2. Microsoft Azure Storage Kind and Architecture
  3. Create Storage Account Using PowerShell and CLI Command
  4. How To Monitor Azure Storage Account
  5. Sync your local files with Azure Storage Sync Service
  6. Copy Files to or from Azure Storage using Azcopy
  7. Azure Storage Geo-Replication
  8. Azure Date Transfer Solutions
  9. Microsoft Azure Storage Permission

Implement and Manage Storage Tips

Continue reading “Microsoft Azure AZ-103 Tips,Hints and Notes Part 2”

Microsoft Azure AZ-103 Tips , Hints and Notes Part 1


During my study for AZ-103 Exam, I take some notes and hints from the resources, materials, and videos I study from it and from some exam templates, based on that I need to share this points with you and I hope it will be useful for all of us and the series will be around 4 or 5 parts. The first part related to “Manage Azure Subscription and Resources” in the part of today I will list some important articles I write it in this subject and around 20 points as notes related to the same subject

For more information about other Azure posts and articles, all of them are collected in one link ➡️  HERE

keep following me for the upcoming parts in Azure Tips, Hints, and Notes series.

Manage Azure Subscription

  1. Azure Subscription Administrator Roles Types
  2. Manage Azure Policy Using PowerShell
Continue reading “Microsoft Azure AZ-103 Tips , Hints and Notes Part 1”

Enabling Auto-Failover Group on Azure SQL


Today in Article I will explain How we can enable the Auto Failover group on Azure SQL and what is different between Auto-Failover Group and Active Geo-Replication in Azure SQL. Before starting in How to implement the Azure SQL Auto Failover Group I recommend you to take a look on this ➡️ POST to understand How you can configure  Active Geo-Replication on Azure SQL and for more Azure SQL articles check this ➡️ LINK  and for more other Azure Posts and articles check this ➡️ LINK for all of my azure articles

  • Active Geo-Replication VS Failover Group
  • How to enable Auto-Failover Group on Azure Single SQL Server
  • Check the Failover Group Configuration and Connections
  • Test the Failover
  • Conclusion
  • Resources
  • Follow Me on Social Accounts
Continue reading “Enabling Auto-Failover Group on Azure SQL”

KQL in Azure Data Studio April 2020 Release


As I explained before that #Azure Data Studio is a magic tool, with Azure data Studio you can write all of your query language (T-SQL, #PowerShell, #CLI) you can manage your On-premises SQL Server and your Azure #sqlserver and today with the new updates April 2020 release you can write your #KQL (#Kusto) Query With Azure notebook It allows you to connect, query and explore Azure Data Explorer, ApplicationInsights and LogAnalytic

For more information about How to Manage Azure with PowerShell Command from Azure Data Studio check this Post ➡️ https://mostafaelmasry.com/2020/04/22/how-to-manage-azure-with-powershell-command-from-azure-data-studio/

For More Information About KQL Query Check these two Courses ➡️ https://www.pluralsight.com/courses/microsoft-azure-data-exploring & https://www.pluralsight.com/courses/kusto-query-language-kql-from-scratch

or you can download Kusto Explorer client tool from here ➡️ https://aka.ms/Kusto.Explorer

To learn more about the new Features in Azure data Studio released in April 2020 Check this post ➡️ https://cloudblogs.microsoft.com/sqlserver/2020/04/28/the-april-2020-release-of-azure-data-studio-is-now-available/

📊MY Azure Posts Road-Map💯

👉All of my Azure Posts ➡️ https://lnkd.in/edn6nyY/#AllAzurePosts 💨✅
👉 Azure Data Studio Posts ➡️ https://lnkd.in/edn6nyY/#AzureDataStudioPosts 💨✅
👉 Azure Storage Account Posts ➡️ https://lnkd.in/edn6nyY/#AzureStorageAccount 💨✅
👉 Azure VM Posts ➡️ https://lnkd.in/edn6nyY/#AzureVM 💨✅
👉 Azure SQL Database Posts ➡️ https://lnkd.in/edn6nyY/#AzureSQL 💨✅
👉 Azure PowerShell and Cloud Shell Posts ➡️ https://lnkd.in/edn6nyY/#AzurePowerShell 💨✅
👉 Azure Subscription Posts ➡️ https://lnkd.in/edn6nyY/#AzureSubscription 💨✅
👉 Azure Errors and issues Posts ➡️ https://lnkd.in/edn6nyY/#AzureErrors 💨✅
👉 Other Azure Posts for other Services ➡️ https://lnkd.in/edn6nyY/#Others 💨✅

Keep Following

Microsoft Azure Storage Permission


Before starting for How we can access the storage account using an access key or SAS and what is the difference between them I highly recommend you to take a look about this articles series for how to manage azure storage account using Azure portal, PowerShell and Cloud Shell ➡️ https://lnkd.in/edn6nyY/#AzureStorageAccount 💨✅

  • Storage account Firewall
  • Access Key
  • Shared Access Signature
  • Azure Blob Data Permissions Deep Dive (360 in 360) Video by John Savill
  • Database Cloud Tech Azure Posts Road-Map
  • Follow Up Database Cloud Tech on LinkedIn , Facebook and twitter
Continue reading “Microsoft Azure Storage Permission”

Microsoft Azure Events for Upcoming 30 Days


Microsoft Event for Upcoming 30 days if you are looking for more events in other fields check Microsoft Event Site https://events.microsoft.com/

IF you need to check all of my azure posts ➡ Latest Microsoft Azure Articles and Posts

Azure SQL

Azure DP-200 and DP-201

Azure Data AI

Keep Following Me

How to Manage Azure with PowerShell Command from Azure Data Studio


All of us know Azure Data Studio is new Microsoft tool like SQL Server Management Studio but with highly advanced features like Azure Data Studio Notebook, one of the features that you can PowerShell extension on Azure data Studio to connect to your Azure Portal and do your homework easily, Azure data studio PowerShell extension supported PowerShell and Cloud Shell (CLI) AZ Command, did you imagine this powerful tool from one place you can write your T-SQL, PowerShell, and CLI and you can manage your SQL Server on Premises and your Azure SQL and the other Azure services, all of these services and these languages from one place Using Microsoft Azure data Studio 🚀💪😱

IF you need to check all of my azure posts ➡ Latest Microsoft Azure Articles and Posts

Managing Azure With PowerShell From Azure Data Studio

Connect to Azure with PowerShell from Azure Data Studio

Continue reading “How to Manage Azure with PowerShell Command from Azure Data Studio”

Azure Date Transfer Solutions


Microsoft Azure provided verity of options for how to transfer your data from or to your storage or from on-premises to Azure or from Azure to on-premises many of options and I explained before some of them like Azcopy check this  ➡  post , Azure Data Migration Services (DMS) Check this  ➡  Post  , Azure Storage Explorer Check this ➡  Post.

And to know more about this this solution easily you open your storage account and in the left side you will find Data Transfer if you click on it Microsoft give you service to search on it about the most optimal offline or online data transfer based on your need and this search depend on 3 keys (Network Speed, Data Size, Transfer Frequency option).

IF you need to check all of my azure posts ➡ Latest Microsoft Azure Articles and Posts

So before doing any data transfer check this service to Search from among the common Azure data transfer solutions to select the best option for your data. For more information about Azure solution for data transfer check ➡  Microsoft article.

Continue reading “Azure Date Transfer Solutions”

Azure Storage Geo-Replication


I explained before How we can use the Geo-replication on Azure SQL and how is very helpful for SQL replication and failover for more information about this subject check this ➡ Article Today we will use the same services for replicated the azure storage data.

  • Previous articles in azure Storage services
  • What is Geo-Replication
  • Azure Storage replication Types
  • How to Implement Geo-Replication on Azure Storage

IF you need to check all of my azure posts ➡ Latest Microsoft Azure Articles and Posts

Previous articles in azure Storage services

Continue reading “Azure Storage Geo-Replication”

Azure VM Backup Soft Delete Security features


I talked before about How we can implement backup on Azure VM using Recovery services vault and we mentioned that recovery services vault can be used for two purpose backup and site recovery but today we will take in one of the azure security feature Soft Delete but before this point, if you need to know how you can implement backup on VM using recovery services vault check this ➡ article and if you need to know how you can implement the site recovery using azure backup vault check this ➡ article.

IF you need to check all of my azure posts ➡ Latest Microsoft Azure Articles and Posts

Soft Delete Life Cycle on Azure Recovery Services vault
Continue reading “Azure VM Backup Soft Delete Security features”

Copy Files to or from Azure Storage using Azcopy


Before starting on How to move files hosted on the azure container using Azcopy command line I highly recommend you to read below articles to know more information about Azure Storage types, features, how to create it, storage replication types, azure storage explorer and How to monitor your azure storage

  • Azure Storage information
  • What is Azcopy
  • Prerequisites
  • Azcopy command details
  • Check File using Azure Storage Explorer

IF you need to check all of my azure posts ➡ Latest Microsoft Azure Articles and Posts

Azure Storage information

Continue reading “Copy Files to or from Azure Storage using Azcopy”

Create Deadlock Alert on Microsoft Azure SQL


Deadlock all of us know the meaning of it and how to manage it on SQL Server on-premises and for how to get alert by it normally we are using Database Mail to generate alert from SQL Server but in Azure, the situation is different because there is no Database Mail in Azure there are services called “Azure Monitor” centralized services for managing all of the alerts on all of the Azure resources and this in of the difference between SQL on Azure and SQL on-premises. From this service we can manage our alerts with defining what is the action we need it (Send Email, Execute Function, Call Services, Send SMS ..etc.), You can access the Monitoring Services from this ➡ link or simply by searching azure portal by (Monitor).

  • Azure Alert Component
  • How to create Deadlocks Alert
  • List by alerts created on Azure
  • Alert Dashboard
  • How to Get the deadlock reports Using Azure Matrices
  • How to check Deadlock using SQL Server Script
  • Other Articles For How to Manage Azure SQL Database

IF you need to check all of my azure posts ➡ Latest Microsoft Azure Articles and Posts

Azure Alert Component

  • Resource: The Azure Resource you need to apply for this Alert role on It and it can be done on the azure subscription itself like alert on cost and budget of the subscription.
  • Condition: it is the logic of the alert what you need to monitor exactly and when
  • Action Group: the action you need to do it when the alert fire on the condition you configure it
  • Matrices: It is like dashboard and reports you can configure it to get some statistics on time and you can build alert on it
  • ALERT DETAILS: alert name and alert description and severity for this alert from (0) to (4)
Continue reading “Create Deadlock Alert on Microsoft Azure SQL”