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 postand 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
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
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
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
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
The Single Database deployment option creates a single isolated database in Azure SQL Database
Azure Single Database Support DTU-based and Vcore-based Purchased Model
The internal of SQL Database server is hidden from the end-user and all are managed by Microsoft Azure
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.
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
This means Azure Single Database is the best option for small workloads with applications connects to one DB.
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)
In Azure SQL Single database provisioning you can create new or select existing one from (Resource group and Server name)
In Azure SQL Single database provisioning the (resource Group, Database name, Server Name) Should be a unique name.
My 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
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.
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
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
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
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
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 🚀💪😱
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).
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.
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 ➡ ArticleToday we will use the same services for replicated the azure storage data.
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.
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
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
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
When I am creating Alert from Azure Monitor services I found below massage restricted me to complete the process of creating alert after few search on Microsoft document I found that I need to register for the Microsoft.insights provider to my subscription and they we can do it from azure portal or using PowerShell.
Azure Policy is a Microsoft service in Azure used for creating and managing policies , assignments and definition and it is used to force some roles on some resources in case of some actions.
Policy definition: is the conditions of the policy
Policy assignment: in the part we can define which resource or services you need to apply this policy on it , add in your note that the policy assignment by default inherited so for example if you applied Policy on Subscription it will be inherited on all of the resource under this subscription
Policy parameters: it will give you the availability to custom your policy
Azure initiative Policy: it the like Azure policy but we can use when you need to do group of policy in one step and it is containing the 3 parts (initiative definition, initiative assignment, initiative parameters) “Policy Combo
Today we will create Azure policy to enforce any new resource for using tag let’s go to see how we can create this policy using PowerShell Command.
Today when I am doing cleaning for the azure resources that I don’t need it to reduce the cost, I tried to remove Resource Group contain Recovery Service vault Services but it gives me error
Failed to delete resource group prodresouorcegroup: Deletion of resource group ‘prodresouorcegroup’ failed as resources with identifiers ‘Microsoft.RecoveryServices/vaults/elmasryvmbackupvault1’ could not be deleted
Then when I tried to remove the recovery services vault it gives me another error
Vault cannot be deleted as there are existing resources within the vault. Please ensure there are no backup items, protected servers or backup management servers associated with this vault. Unregister the following containers associated with this vault before proceeding for deletion. For instructions, see https://aka.ms/AB-AA4ecq5
After some Google Search I know the correct steps for deleting Azure Recovery services vault
We talked before about Storage and the types of storage and how to manage the storage but today we will talk about the file share that’s one of the types of azure storage and the questions now after creating the file share how I can sync my files from my V-Center VM to Azure File share, Microsoft provided us services called Azure file sync so to let us know how we can configure it and what is the prerequisites we should configure it on the V-Center VM
As I explained before on my last article [Virtual Machine (VM) Backup and Restore (Vault)] that we can use the Azure recovery services vault for taking backup and doing a restore from azure VM using recovery services vault also the same services can be used to migrate your local VM from V-center to Azure using (Recovery services vault site recovery) and in this post, we will explain how to use this services recovery vault to Move VM between two rejoin and to do failover easily for the VM. Let’s go for technical steps
Azure Recovery service vault It is azure built in services doesn’t required any infrastructure preparation and it is supported multiple types of backup (VM , File , Disk and SQL Database) it is one of the built options in the VM , so easily when you open Azure virtual machines and select your VM you will find in the left side Backup from this point and this place you can start to manage your backup and restore process as well you can keep monitoring your backup jobs and restore jobs let us go for demo and how to do VM backup and restore it and what is the restore types supported.
Azure backup Process.
Azure backup hints
How to start to take backup from VM
Azure Recovery Services vault
Azure Restore Options
Azure Restore Hints
How to do VM restore from Azure recovery services vault
I think all of us know how to move files for SQL Server databases from disk to another disk but today I will explain how we can do it on SQL Server always on to reduce the downtime and taking new backup to return the database to always-on again. Let us start on the technical steps.
Check the current DB Files locations
Check Always on Health Status
Disable all backup jobs on Primary and secondary servers
Suspend the data movement on Secondary server
Remove Database from Primary server
Update DB files by new disk location on secondary server
Stop SQL Server instance on Secondary server
Move (CUT) DB Files from old location to new Location (Past)
Start the SQL Server instance again
Verify the DB File Location Using T-SQL on the post
Join the DB to availability group from Primary server
Check SQL Server always on Health Status Using T-SQL on the post
Enable the SQL Server Jobs\
Failover the SQL Server for Primary (X) to Secondary Server (Y) and do the same steps on (X)
One of the common tasks as an azure administrator you should know it when you deployed azure resource on resource group related for testing as example and you need to move it to new resource group or resource group of production we can do this activity with one PowerShell command and if you are not interested in PowerShell don’t worry you can do also from Azure Portal.
Alert 💣:Moving Azure resource will create new resource id this meaning if you any subscription or APP referencing this resource-id it will break be caution ✍ (Read More, Document the Steps, Test the Steps ) then do your implementation
Open Microsoft Azure Portal select the source resource group and from the top, you have two option (Move or Change) both you will direct you to move your resource by the selecting the resource you need to move it then the new resource group (Destination) during this steps you can create new resource group easily.
I know the First question you will ask it “Why we need to hidden it IF the user doesn’t have access on it and he can’t do any operation on this DB 🤔” Simple answer “Business needs & Business Case & Management Request” Whatever the answer but this is the request from management So let us see How we can do it using two solutions.
First Solution hide the DB using Permissions
Before starting on the solution, I need to highlight important point “to implement this solution the user will take ownership of the DB” and this will break the security role. To hide the DB, we need two steps after it the user when he will log in to SQL Server management studio, he will see only this database:
Deny or remove the permission of View on databases from the Public User on the instance level.
At this time no one will able to see any databases on the SQL Server management studio except the admins only this means you should grant this permission for specific users that you need them to view all databases “It is just view Permission, accessing the DB is another permission”.
Change the DB Authorization by the user name you need him to view this DB only or you can do this step on two or three or more databases at this time this user when he login to SQL Server Management studio he will view these databases only the other databases will be hidden.
Monitoring Azure Storage activities depend on Azure activity log services and to access this activity log we have three options and to know more information about Azure Storage account check below posts and for all of my posts in azure check this link (Latest Microsoft Azure Articles and Posts)
Activity log: it is like the event viewer in windows when you open the storage account you will find in the left side activity log if you open it you will find all of the activities you do it and when and who do it
Log analytics: Search about log analytics on the Azure portal search and create new work space then on the left you will find container call “Work space data source” under it you will find Azure activity log if you click on it you will find your azure subscription just only connect the subscription to the log analytics then Go for the left side again and select Log and from here you can query your activity log using KQL query (Kusto Query Language). Look at this post to know more information about (Microsoft Azure Log Analytics).
Log Analytics on Activity log: we can add Log analytics as a solution on the activity log on Azure storage account to do that ( You Should Create first step 2 Log Analytics workspace > open Storage account > in the left side select activity log > on the top right side click on log > add > Select the log analytics Workspace you created it > then save > the refresh) IF you return to Log Analytics workspace in the left side select solution you will find the solution you created it.
Azure Storage it is the container for your data that you can save on it the Hot data and Cool data. but what is the best storage types i need it (Blob, File Shares, Tables, Queues)? and what is the Storage Performance and Storage kind (V1, V2,..) and what about the high availability and disaster recovery for my data.based on all of this options you will be able to determined the best azure storage kind for your Business. So before creating any Storage account you should consider the (Location, performance, Kind, replication, access tier)
After reading this post i recommend you to read this post to complete the full picture about how to manage azure storage and for more azure posts check this link by all of my azure posts and articles
IF you are interested to read more articles in the same subject here is below :
When you are creating any resource in Azure Microsoft asking you about the location because not all locations have full azure features. That’s why the location is the the key that will determined on it the features you will have it on the Storage account. e.g. If you selected Australia Center and selected the performance premium storage you will have two kind of storage account only general Purpose V2 and General Purpose V1 but if you selected East US 2 you will have another choices in the storage kind like BlockblobStorage and FileStorage.
Azure Data Explorer it is fully Microsoft managed services for data analytics that we can use it for real time analysis for big large of data streaming. With azure data Explorer we will have the availability to ingest massive of data from multiple sources and with azure data explorer we can start to query this massive of record using KQL (Kusto Query Language).
Keep Following this link to check all of my posts/ articles in azure services
Most of the peoples now looking for how to migrate the DB from on-premises to Azure and for this subjects, Microsoft provided us multiple solutions and services can be used for this request and for more information you can check my post (How to Migrate Database to Azure) that l already explained all of these services on it.But the question now can we take a backup from Azure SQL DB and restore it on our local SQL Server on-premises?
Answer yes but not with a normal process we know it backup and restore to know more information about azure backup check my post (Backup Tips on SQL Azure) we will use bacpac file using the Export option in Azure portal
To check my previous posts / articles on azure technology follow up this link
Azure Active Geo-Replication Services is one of the powerful services in Azure used for replicating the SQL database data to another Regions using the same concept of always on data are readable on the another regions and synced on time , With Active Geo-Replication we can configure up to four readable secondary databases in the same or different data center locations (regions).
For more information about Azure Log Analytics check this post
Services Depend on Log Analytics
Azure SQL Analytics it is Azure solution for advanced monitoring scenarios that allows you to collect and visualize important data related to the performance of your Azure SQL Database, elastic pools, and managed instances in a single place. It is based on Log Analytics, so it relies on the Log Analytics for more information check Microsoft article
For How to Start on Azure SQL Analytics check this post
Azure Log Analytics advanced services you can use it to dump all of your logs in one place and you can query the log to dive deep on it is very powerful tool from Microsoft azure Azure Log Analytic the simple definition for it , is Microsoft services used for log analysis from multiple sources Example we can configure the diagnostics setting in Azure SQL to push the logs for Log analytics direct or to keep it saved in Azure storage blog container and log analytics services can pull the logs from the storage containers. But How you can filter and search on this massive of logs. Microsoft provide us amazing option inside the log analytics that you can query your data on it and the use of it is very easy , this query depend on KQL Language (Kusto Query)
Congratulation Microsoft announced the beta exam Az-104 Microsoft Azure Administrator and this is the replacement of Az-103 that will be retires on August 31, 2020. This mean still you can take the old exam version till August 31, 2020 when you pass the Exam Az-104 you’ll have earned your Azure Administrator Associate certification and it will be valid for two years.
Remember, the number of spots is limited to the first 300 candidates taking the AZ-104 beta exam on or before May 31, 2020. Please note that the beta exam is not available in Turkey, Pakistan, India, or China.
When you are planning for Hyper model and you need to sync the identity between your local active directly on-premises to azure active directory at this time you will need to know what is Azure AD connect
Microsoft provided us multiple types and series from Azure VM supported all of the workloads with many of options that we should consider it before going for moving your services to azure VM we should understand very well what is the VM types and what is the Microsoft recommendation for choosing the VM type based on the workload.
In this article I’ll cover +10 important points you need to cover them when you are going to understand Managing and implementing Azure storage
What are the Types of Azure Storage account?
What are the Azure Storage blob types?
What are the Blob storage resources?
What are the Azure Storage Firewall and Access Key?
What is the Azure Storage Explorer?
What are the Azure Storage Replication?
What is the Azure blob Pricing?
What are the Solutions for moving data to Microsoft Azure Blob storage?
What is Azure Data Box?
What is Azure CDN?
What is the Azure backup Types?
What is the SQL Server Backup limitation on Microsoft Azure Blob storage?
What is the Azure Backup Monitoring ?
References and another Important articles
IF you are planning for (Az-103) Microsoft Azure Administrator Exam or new update from this certification (AZ-104) Microsoft Azure Administrator (beta) that will be available on or around April 2, 2020, you should take in your consideration implement and manage storage this part is (10-15%) from the exam . in this post I will share some important key note that can-do refresh for you in this part.
IF you are interested to read more articles in the same subject here is below :
There are four layers in Azure SQL Architecture most quite similar to on-premises
Client Layer: Is the interface for applications to access a SQL database the client layer includes SQL Server tools, (ODBC), ADO.NET, (PHP) and Tabular Data Stream (TDS) is used to transfer data between a SQL database and applications. SQL Server also uses TDS to communicate with applications. This allows applications such as .NET, ODBC, ADO.NET, and Java to easily connect to Azure SQL Database without any additional requirements
Service Layer: acts as a gateway between the client and platform layers
Platform Layer: (SQL Server, Azure Fabric, Management Services) Azure Services Fabric is Responsible for Load Balancing , Automatic Failover and Automatic replication of the SQL Databases between Physical Servers https://docs.microsoft.com/en-us/azure/service-fabric/service-fabric-overview and Management Services takes care of an individual server’s health monitoring and patch updates.
Infrastructure Layer: Layer responsible for administrating the OS and physical hardware
#PowerShell, #Bash and #CLI, and the most common language now for how to manage #Azure are very helpful when you need to automate the tasks did you imagine that you can know provision new VM or new SQL on Azure using your mobile though azure mobile app with Powershell. 💥⛈
If you asked me what is the best? I will be told you nothing all of them are very powerful language and choosing which one of them depends on your past experiences If you are coming from background windows so the PowerShell is the most compatibles with you and if you are coming from Linux background Bash will be the most compatible with you.
How I prepare my local PC to be able to manage azure using theses language?
This is our post of today, all of us know that we have the availability to write PowerShell or Bach code fromAzure portalitself😲 . But if we need to manage the portal from our PC, we need to install some tools and import some modules, at the end of the post you will find most of the tools and requirements and advanced tools you can use it on your local PC or on-Premises server to manage Azure.🦾
All of the markets now taking about azure and the new technologies in azure this means that all of us should learn what Is azure and what is the services related to our work in azure for example if you are SQL Server database administrator like Me, you should have fundamental information about azure services, and what is SQL Server types on azure and how we can manage azure SQL .
One of the most interesting topics for me How to migrate your SQL Server DB from on-premises to Azure, If you do search on google on this topic you will find many of articles talking about this subject, and based on my study I listed some notes that can help you on this subject
All of the information here depend on my study and search if something wrong I will be happy to correct me , and if there is extra information you know it related to this subject it will be great to share it on the comment for sharing the knowledge wit all of us I am highly recommend you to read the post first (https://www.thomasmaurer.ch/2019/01/azure-cloud-shell/)