How to manage Azure using Windows PowerShell

Introduction

#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 from Azure portal itself😲 . 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.🦾

Note🚦: If you didn’t used Cloud shell before on Azure portal, when you start it portal will ask you to create Storage account for more information check this postStep by Step How to Enable Azure Cloud Shell in Microsoft Azure“🚀

Azure Cloud Shell

AS i explained above ⬆ How we can enable cloud shell on azure portal i need to share with you YouTube channel you can learn from it more information about how to use cloud shell

How to Enable Azure AZ module

  • Open PowerShell on your Local PC AS administrator
  • Install Az module using this first command
  • Check the Az module after installation using last two command
Install-Module -Name Az -AllowClobber -Force -Verbose
Get-InstalledModule -Name Az -AllVersions | select Name,Version
Get-Module -ListAvailable *Az*

Introducing the new Azure PowerShell Az module

Install Azure PowerShell

Enable Azure CLI on Windows PowerShell

  • Download and install MSI installer using this link (Install Azure CLI on Windows)
  • Open Windows PowerShell As Administrator and Write Az
  • If you need to enable the interactive mode only write Az interactive

Get started with Azure CLI

Azure CLI Interactive Mode

Enable Azure Module

  • Open Windows PowerShell As Administrator
  • Execute below two PowerShell Command
Install-Module Azure -AllowClobber -Force -Verbose
Import-Module Azure -Force -Verbose

Enable AzureRM Module

  • Open Windows PowerShell As Administrator
  • Execute below two PowerShell Command
  • Check the Module after installation using second command
Install-Module AzureRM -AllowClobber -force -Verbose
Get-Module -ListAvailable *Azure*

Check Azure Module Versions

Get-InstalledModule Azure -AllVersions | Select-Object Name,Version,Path
Get-InstalledModule AzureRM -AllVersions | Select-Object Name,Version,Path
Get-InstalledModule Az -AllVersions | Select-Object Name,Version,Path
Check Azure Module

Azure Support

In case if you have any issue on these steps you can contact me or leave your comment by your issue and i will do my best to support you otherwise you can communicate with Microsoft support team they are very supportive and they will fix to you all of your issues

Most used tools for Managing azure from on-Premises

Tool NameSite Link
Azure Portalhttps://portal.azure.com/
Azure accounthttps://account.azure.com/Profile
.Net Framework 4.7https://docs.microsoft.com/en-us/dotnet/framework/install/
Windows Powershellhttps://github.com/powershell/powershell
Azure Studiohttps://docs.microsoft.com/en-us/sql/azure-data-studio/what-is?view=sql-server-ver15
Install Azure CLIhttps://docs.microsoft.com/en-us/cli/azure/install-azure-cli-windows?view=azure-cli-latest
Install Azure PowerShell Modulehttps://docs.microsoft.com/en-us/powershell/azure/install-az-ps?view=azps-3.6.1&viewFallbackFrom=azps-1.2.0#install-the-azure-powershell-module
Install AzCopy Command-Line Tool for Azure Storagehttps://aka.ms/downloadazcopy
Install Azure Storage Emulatorhttps://go.microsoft.com/fwlink/?LinkId=717179&clcid=0x409
Azure Storage Explorehttps://azure.microsoft.com/en-us/features/storage-explorer
Install PowerShell Module Manger 2020https://www.sapien.com/software/powershell_modulemanager

Conclusion

It is very important now to know how to use these languages or at least one of them to be able to automate and custom your azure tasks from your azure portal, PC or even your mobile phone using an azure mobile application.

Backup Tips on SQL Azure

Based on my Study i will list some tips and notes related to the backup on SQL Azure, I will be Happy if you ٍshared your knowledge, experiences and your review in the comment.

SQL AZURE BACKUP

  • Backup Concept: Backup on SQL Azure DB is taken Automatically and the first backup is taken after the DB Provisioning, however, you can’t download this backup or doing a manual restore from it.
  • Backup Storage: Microsoft support us with free storage based on the DB size on Services Tier selected, and it gives us 2 MAX size of the DB size, Azure SQL Database backups are stored in geo-replicated blob storage (RA-GRS storage type)
  • Reaching Backup Storage Limit: at this time, we have two solutions (1) Reduce the retention period by Contacting the Support team and this time you will not pay an extra charge (2) Pay for an extra backup build at the stander Read Access Geographically redundant storage rate 
  • Archiving data:In case if we have data archiving, we have two solutions to archive it on azure (1) Export the DB as BACPAC file and save it on Azure blob storage (2) Use Long-Term-backup-retention (LTR)to the Azure backup vault and you can keep it to 10 years
  • Backup retention period: It is depending on services tier model (basic = 7 days, stander and premium = 35 days) however we have “Long Term backup retention” for 10 years to an azure backup vault
  • : this means the Azure replicated the backup cross regions, and in case of the region down you can restore the backup in another region, but this feature replicated the FULL and DIFF backup only. This meaning the RPO will be one hour because the DIFF taking every one Hour
  • Azure backup RTO and RPO: Microsoft Support Point in Time Recovery with 12 Hours as RTO and 5 minutes as RPO for the in-Region backups
  • Disaster: If you delete the DB you can restore it on the retention period time based on your Services tier For Example if your DB in basic service tier and you delete the DB you can restore the DB in 7 days after this you will not find your backup , If you delete the server all backup will be deleted and you will not have any option to restore your DB
  • Manual backup:  There is no Manual backup on Azure SQL Database but we can take backup from the data and Structure database using another way called BACPAC and we can do Export for the BACPAC file on azure storage account using Azure portal, PowerShell and we can do the same to on-Premises system using sqlpackage.exe , also we can export BACPAC file using SSMS SQL Server Management studio
  • When using the Azure Backup service to store backups of virtual machines, which container is required? Recovery Services Vault
  • What is Azure Backup Explorer Today, we are pleased to share the preview of Backup Explorer. Backup Explorer is a built-in Azure Monitor Workbook enabling you to have a single pane of glass for performing real-time monitoring across your entire backup estate on Azure. It comes completely out-of-the-box, with no additional costs, via native integration with : T Azure Resource Graph and Azure Workbooks. https://azure.microsoft.com/en-us/blog/backup-explorer-now-available-in-preview/?WT.mc_id=linkedin-social-thmaure

What are the benefits from Azure backup Explorer

  •  At-scale views: With Backup Explorer, monitoring is no longer limited to a Recovery Services vault. You can get an aggregated view of your entire estate from a backup perspective. This includes not only information on your backup items, but also resources that are not configured for backup
  • Deep drill-downs – You can quickly switch between aggregated views and highly granular data for any of your backup-related artifacts, be it backup items, jobs, alerts or policies
  • Quick troubleshooting and actionability – The at-scale views and deep drill-downs are designed to aid you in getting to the root cause of a backup-related issue. Once you identify an issue, you can act on it by seamlessly navigating to the backup item or the Azure resource, right from Backup Explorer.

How to Migrate Database to 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/)


Azure Data Migration tools

Below a list of all tools, you can use it on this project some of them can be acting the assessment tool and some of them acting the migration tool offline or online migration and some of them doing both jobs. The first 4 tools are the most commonly used.

  • Azure Data Migration Services (DMS):t is an Online migration using Azure DMS services it can be used to migrate multiple databases from multiple sources to multiple targets, DMS services used the DMA (Data Migration Assistant) services to generate assessment tool, and DMS services it can be used for automated migration for more information about how to use this services check this Microsoft video https://azure.microsoft.com/en-us/resources/videos/online-migrations-using-azure-dms/
Data Migration Assistant | Data Exposed

Migrating other databases to Azure Database:

If you target to migrate any other DB like Oracle, MongoDB, MySQL, DB2, PostgreSQL, Cassandra, MariaDB, access, SAP you should look into this site https://datamigration.microsoft.com/ 

References and interesting articles :

Azure Learning Resources

Why I should upgrade to SQL Server 2019 even if I will not use the big features on it (Big Data Cluster)

AS all of us know SQL Server 2019 is the latest SQL Server version and it come up with very sophisticated and impressed enhancements.

The most interested features is Big Data Clusters and How SQL Server now are able to integrated with other database and open sources and How SQL Server are now working and supporting all of the new technologies in the market like ( Spark, Hadoop, HDFS, Python, Docker, Kubernetes, machine learning, Kafka, NoSQL  ..etc)

shall this means if i will not use all of this features no need to upgrade to SQL Server 2019 ?

My answer NOOOOO SQL Server 2019 come up with many other features that’s Urges to upgrade to it and here i will list the most important features

  1. Engine of SQL Server 2019 more powerful in Performance with Intelligent Database Feature from Intelligent Query Processing To support for persistent memory devices, the SQL Server Intelligent Database features improve performance and scalability of all your database workloads without any changes to your application or database design. https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver15
  2. Engine of SQL Server 2019 more powerful in Performance with Insert with SQL 2019 more faster: Using Feature Called optimize_for_sequential_key. SQL Server has long suffered from a bottleneck when trying to insert sequential records into a table at very high volume. This is because of contention in memory, SQL Server 2019 introduces an optional feature called “optimize_for_sequential_key” that reduces the effect of these bottlenecks. Microsoft quotes up to 40 percent performance gains with this feature https://techcommunity.microsoft.com/t5/sql-server/behind-the-scenes-on-optimize-for-sequential-key/ba-p/806888
  3. High availability: Up to five synchronous replicas, Secondary-to-primary replica connection redirection
  4. Recovery: Accelerated database recovery Reduce the time to recover after a restart or a long-running transaction rollback with accelerated database recovery (ADR). See Accelerated database recovery. https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-and-recovery-overview-sql-server?view=sql-server-ver15#adr
  5. Mission-critical security enhancement: Audit, Always Encrypted with secure enclaves, Data Discovery & Classification, Certificate management in SQL Server Configuration Manager https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sql-server-ver15#mission-critical-security
  6. Monitoring: New DMV for Monitoring DB https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sql-server-ver15#monitoring
  7. Resumable online rowstore index build: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/perform-index-operations-online?view=sql-server-ver15
  8. And so many other features https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sql-server-ver15

Finally, Microsoft also announced an update for SQL Server 2019, so if your organization typically waits for the first patch of a newly released product, you can install it today.  https://support.microsoft.com/en-us/help/4517790/servicing-update-for-sql-server-2019-rtm

How to Upgrade to SQL Server 2017

One of the tasks that any DBA now can be asked to upgrade the production environment to the latest SQL Server version . and this task is more critical you can find below some tips and resources that can be helpful on this tasks

  1. How to decide on an upgrade
  • SQL Server 2000 and SQL Server 2005 are no longer supported by Microsoft and no updates are published. This lack of support means your databases may be vulnerable to security attacks that have been addressed by recent upgrades. Upgrade these database servers unless you can afford to lose them or allow the data they contain to fall into malicious hands.
  • Because these databases are no longer supported, it is highly likely that you are not in compliance with the data protection laws in your region.
  • Your company already uses Azure for other systems and may wish to migrate databases into Azure as well. You cannot migrate a database into Azure SQL Database from SQL Server 2000 or 2005.
  • You can upgrade a SQL Server 2005 database server to SQL Server 2017 directly. However, to upgrade from SQL Server 2000, you must first upgrade to SQL Server 2008.
  1. Discover your DB and Document anything (versions, features, editions , DB Size , DB Count , Configuration component, ..etc ) you can use (Microsoft Assessment and Planning (MAP) toolkit) in this part
  2. Data Migration Assistant (DMA) : supports versions of SQL Server from 2005 upwards. Using the assistant, you connect to the instance and select the database you plan to upgrade. You tell the assistant the target version of SQL Server and it will analyze the size and schema of the database. It will also make recommendations for your upgrade. After the analysis, DMA provides a compatibility report, which highlights breaking changes, behavioral changes, and deprecated features so these problems can be fixed.
  3. Database Experimentation Assistant (DEA) : To test your migration and upgrade process to test the DB performance and workload if we upgraded it You can also test performance on different hardware or with new features added

Introduction to upgrading SQL Server

Microsoft Assessment and Planning Toolkit MAP :

Microsoft® Data Migration Assistant DMA :

Microsoft® Database Experimentation Assistant DEA :

Learn about the Misconceptions around SQL Database Corruptions

SQL database corruption is the issue every database admin has to face at some point in their lifetime. But do you know what can cause you more trouble than a corrupt database? It is the misconceptions around SQL database corruption. These myths can actually worsen the situation for SQL database and its users. Therefore, it is crucial that users have the full knowledge of SQL Database corruptions and the myths and reality surrounding it.

Some Common Myths Regarding Database Corruptions

Let’s explore which ideas about database corruption are basically misconceptions.

  • Server Restart/ Reboot is the Quickest Method to Fix Corruption: This is one of the most prevailing misconceptions about SQL database corruption. SQL Server reboot or Windows Server restart will not fix the corruption, rather, it will possibly make the situation worse by putting it in Suspect mode. Detaching a database that is already in Suspect mode will make it impossible to fix the corruption ins future. A corrupt database page requires repairing or restoration, not reattaching.
  • Detaching and Re-attaching the Database will Resolve Corruption: This is another malpractice performed by many database admins. Instead of repairing, it will make the database recovery more difficult.
  • Repetitive Running of DBCC CHECKDB will Do Away With All Corruptions: In some extremely rare cases, re-running the DBCC command may repair one or two minor level corruption from SQL database. But this is not a recommended solution as the chance of success is very rare.
  • Applying Repair Command is harmless: If you believe that running commands to repair will not cause any harm to your database, you are wrong. If you are running DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS, it is possible that your database may face some significant amount of data loss. So, always keep a backup of your database to avoid this sort of situation.
  • To Execute Repair Commands, DBCC CHECKDB is Not Required: No, DBCC CHECKDB commands are used for the purpose of checking physical and logical integrity complete set of objects of any particular database. Before and after running the database repairing command, users have to run DBCC CHECKDB. It is not possible to repair database using manual command without running DBCC CHECKDB.
  • Running Repair Command Alone Assures Corruption Removal: Wrong! The success of Repair command in order to corruption removal depends on many factors. For this reason, its result will vary depending on the severity of the damages done to the database. So, it is mandatory to run DBCC CHECKDB command after repairing. It will help users to find out more critical corruptions and confirm the success of Repair command.
  • Incomplete Lengthy Operations Lead to Corruption: Many of us believe that if an operation is going on for a long period of time, interrupting it halfway will cause damage to the database. This is not true at all. Database corruption occurs only when there is a physical level corruption (e.g. bug, virus) in the Server.
  • DBCC CHECKDB is All-In-One Solution for All Corruptions: This is not quite the scene. The Repairing command of DBCC CHECKDB has only the limited capability when it comes to fixing corruption from SQL database. There are many corruptions that repair command is unable to fix, like PFS Page header corruption, Metadata corruption, Common value corruption, etc. If used in case of these corruptions, it will end up extract data in a lengthy process with no result. Moreover, REPAIR_ALLOW_DATA_LOSS command often leads to data loss. Restoration from last good backup is the most useful method in such cases.

Conclusion

There are numerous misconceptions around SQL database corruption in the industry. Here, we have focused on some of the most common myths regarding database corruption in SQL Server. We expect that the users will gain knowledge from this post and be able to take the right decision once the database becomes corrupt. SQL Server experts recommend having a regular backup of the entire database to avoid inconvenience during database corruption. In case any corruption persists in the database, then one can use SysTools MDF Recovery tool to repair corrupted MDF/NDF File of SQL version 2017, 2016 & all below versions.

Method to Backup and Restore SQL Database

If you are an SQL user, you must back up your SQL database to save your business-critical data from getting lost/ deleted permanently. Only regular data backup can safeguard your data from various mishaps like end-user error, hardware issues, etc. Just by knowing the importance of backup is not suffice: users need to know the procedure in detail. This post will tell you some of the trusted methods to backup and restore database in SQL Server.

How to Backup and Restore Database in SQL Server

We will be providing you with a step-by-step guide to both the processes: SQL database backup and restore. Read the following segments to know more about it.

  • How to Backup SQL Database

Using this method of SQL Server Management Studio (SSMS), users can back up the database that can be used at any crucial moment.

a. Open SSMS and go to Object Explorer. Select the Servername and expand it.

b. Expand Databases to choose a user database. Or you can expand System Databases to choose any system database.

c. Right-click and hover your cursor on Tasks>> click Back up to get the Back Up Database dialog box.

d. Confirm the name of the database from the drop-down list of Database. If you want, choose another database from the list.

e. Choose Full from the drop-down list of Backup type. (You can also choose SIMPLE or BULK_LOGGED)

f. To create the copy-only backup, select the checkbox beside the same option.

g. Select the radio button beside Database under Backup component.

h. Under Destination section, choose your destination for backup from the Back up to drop-down list. Click Add to include extra backup objects and/or their destinations. You can also remove a mistakenly added backup destination by selecting it and clicking on Remove.

Note: To back up a transaction log, run this script. In this example,l get created in AllSalesWorks_FullRM_log1.

  • How to Restore SQL Server Database from Backup File

If the SQL database encounters some issues that cannot be resolved, the last good backup of the database can be restored to fix the problem. In this section, we will describe the process to restore SQL database from the backup file.

For Full Database Restoration

a. To execute this process, start SQL Server Management Studio (SSMS), select the option Databases.

b. Right-click on it and choose Restore Database to open a window.

c. Click on the radio button select From Device under Source for restore section and then click on the Browse icon.

d. Specify Backup window will open. Click on Add and go to the location where the flat files are stored. Select the first backup file from the list as it is the Full backup file.

e. Click on OK. The file will get added to the Specify Backup window. Click on OK again.

f. Choose the target database where you want to restore the backup file by going to the section “Destination for restore.” Under “Select the backup sets to restore” section, check mark the database file chosen earlier.

g. Select Options from the left pane and then do these steps:

  • Check the box beside Overwrite the existing database (WITH REPLACE) under ‘Restore potions’ section.
  • Under ‘Recovery state’ section, choose Leave the database non-operational, and do not roll back uncommited transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)” radio button.

h. Click OK and the restoration will be done.

I. Perform these steps for every incremental backup file, which includes the .tm file. When you reach a point when you have to restore the point-in-time file, follow this second method after “Restoring” message appears.

For Point-in-Time File Restoration

These steps need to be performed for the restoration of last incremental file that contains the point-in-time:

a. Start SQL Server Management Studio to right-click on Databases>> click Restore Database.

b. Choose From Device option under Source for restore section and click Browse.

c. On the next window, click on Add to go to the location of the incremental backup file with the point-in-time that you want to restore. Select that file and click OK.

d. Click on OK again. Select the added backup file in Select the backup sets to restore segment.

e. Choose the destination database in Destination for restore section. Click on Browse button next to “To a point in time” to make ‘Point in time restore’ window appear.

f. Enable ‘a specific date and time’ option and select the date and time.

g. Click on OK>> Options (left panel)>> Overwrite the existing database>> Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH RECOVERY)

h. Click OK to perform the process. In the restored database, you will only view the changes done to a particular point-in-time.

SQL Backup Recovery: The Ultimate Way to Restore Healthy and Corrupt BAK File

As we can see, the manual restoration process is a long one. Moreover, this process does not support restoring data from corrupt BAK file. To avoid these problems, SQL bak File Repair is a reliable solution. In some easy steps, users can restore their corrupt or healthy .bak file to SQL database.

a. Launch the tool and Add BAK file.

b. The software will resolve all the issues and offer full Preview. Click on Export.

c. You can export as SQL Server Database or as .csv script.

Concluding Words

In case you are wondering about the process to backup and restore SQL database, here are some of the approaches you can follow. All the methods mentioned here have been tested and users can execute them without any worry. If your backup file is damaged or you prefer an easy way of restoration, you must go for SQL Backup Recovery Tool.