In the last exercise, you have seen how to enable security measures and monitoring for Arc-enabled servers. In this exercise, you will onboard SQL Server to Azure Arc using Azure Portal and PowerShell commands.
In this exercise, you will be performing the following tasks:
- Task 01: Login To Azure Portal
- Task 02: Register Azure Arc-enabled SQL Server.
- Task 03: Run on-demand SQL Assessment.
-
Navigate back to Azure Portal which you have already opened in the previous exercises.
-
Click on the search blade at the top and search for
SQL Server, select SQL Server - Azure Arc.
-
Click on the Add button to create the SQL Server- Azure Arc.
-
In the Adding existing SQL Servers instances page, Click on Connect Servers.
-
You will now see the prerequisite page. You can explore the page and then click on the Next: Server details option.
Note: We have already completed the prerequisite part for you.
-
On the Server Details blade, enter the below details.
-
Subscription: Leave default (1)
-
Resource group: Select azure-arc (2) from dropdown list.
-
Region: Select the same region as the Resource group. (3)
-
Operating Systems: Select Windows (4).
-
Server Name: Type sqlvm (5)
-
License Type: Select I have a production environment on this server with Enterprise or Standard edition covered by Software Assurance or SQL subscription ("Paid") (6).
Now, click on the Next: Tags (7) button.
-
-
Leave the default for tags blade and click on Next: Run Script button.
-
On the Script blade, explore the given script. We will be using this PowerShell script to Register Azure Arc enabled SQL Server later.
Note: Please skip the script download from here by clicking on
Xat the top right as we have already downloaded this script inside the Lab VM for you.
-
Minimize the Azure Portal Browser window.
-
From the desktop of your LabVM/ARCHost VM, double click on Windows PowerShell icon to open it.
-
Then, run the below command to change the directory to where the script gets downloaded.
cd C:\LabFiles -
After changing the directory to Lab files, run the command given below:
.\Execute-RegisterSqlServerArc.ps1Note : This will initiate the execution of RegisterSqlServerArc.ps1 script inside sqlvm that is deployed on Hyper-V.
-
After running the command, you will see that the script started running.
-
After some time, you will see that the script execution is completed. Make sure that you see the output as shown the image below.
-
Bring back the browser window where you had opened Azure Portal and search for SQL Server -Azure Arc. If you are already on that page, you will need to click on the Refresh button. On that page, you will see one resource SQLVM that we just created using the PowerShell script in the previous step.
NOTE: After sometime, if you don't see the Mode as Connected, then open a new Powershell window and run the below script, ensure to update the values in the
$blocksection to define your variables. You can fetch these values from the Environment > Service Pricipal Details tab.$block = { #Import Environment Credentials CD C:\LabFiles $AppID = "<Application ID>" $AppSecret = "<Secret Key>" $TenantID = "<Tenant ID (Directory ID)>" $SubscriptionId = "<Subscription ID>" $ResourseGroup = "azure-arc" $location = "<Resource group Region>" $passwd = ConvertTo-SecureString $AppSecret -AsPlainText -Force $pscredential = New-Object System.Management.Automation.PSCredential($AppID, $passwd) #Login to Azure Connect-AzAccount -ServicePrincipal -Credential $pscredential -Tenant $tenantId #Set ExecutionPolicy to Bypass so script can be executed from the terminal Set-ExecutionPolicy Bypass -Scope Process -Force #Run RegisterSqlServerArc.ps1 script to Register SQL VM Server and SQL Server on Azure Arc & '.\RegisterSqlServerArc.ps1' } $ap = "demo@pass123" $cred = New-Object -ArgumentList "Administrator",(ConvertTo-SecureString -AsPlainText -Force -String $ap) -TypeName System.Management.Automation.PSCredential # Install ARC agent set-item wsman:\localhost\Client\TrustedHosts -value 192.168.0.4 -Force Invoke-Command -ComputerName 192.168.0.4 -Credential $cred -ScriptBlock $block -
Select the SQLVM resource and now you can see the dashboard of SQLVM SQL Server -Azure Arc from Azure Portal.
-
Click on the search blade at the top and search for
Log Analytics workspace.
-
Select LogAnalyticsWS-.
-
Then select Agents (1) from the left side menu. Click on Log Analytics agent instructions (2) and copy the value of Workspace ID (3) and Primary Key (4) and save it into a Notepad or Notepad++ for later use.
-
Now, search for Machines - Azure Arc from search box and click on Machines - Azure Arc.
-
Select sqlvm from the list of Azure Arc servers.
-
Click on the Extension button from the left side menu.
-
Click on the + Add button to add a new extension.
-
Search and select the Log Analytics Agent - Azure Arc (1)(2) extension and click on the Next (3) button to continue.
-
At this step, you must enter the Log Analytics workspace ID and a key to install the MMA ( Microsoft Monitoring Agent ) in the sqlvm.
-
Now, enter the Workspace ID (1) and Key (2) that you copied from the previous step, click on Review + Create (3) button.
-
Review the configuration, and click on Create.
Note: The deployment will take around 5 to 10 minutes to complete. You have to wait for this deployment to be successful to proceed to the next step.
-
Open sqlvm from the Hyper-V Manager by double clicking on sqlvm.
-
On Connect to sqlvm box, scroll the bar towards Small to open the VM in the smallest window and then click on the Connect button.
-
Type password demo@pass123 and press Enter button to login. Then, you can resize the sqlvm window size at your convenience.
-
Click on Start Menu and search for Microsoft SQL Server Management Studio 18 and open it.
-
On Connect to server pop-up, select SQLVM as Server name from drop-down and click on Connect.
-
In the left pane, expand Security then Logins. In Logins, right-click on NT AUTHORITY\SYSTEM and click on Properties.
-
In Login Properties pane, click on Server Roles then enable the sysadmin role and click on Ok.
-
Then, Go to SQLVM SQL Server - Azure Arc resource select the Best practices assessment (1) under settings from the left pane and click on Change license type (2).
Note: Skip and continue from Step 20 if change license type option is not available.
-
Under SQL Server Configuration, scroll down and select license type as License with Software Assurance (1) and click on Save (2).
-
Select the log Analytics Workspace as LogAnalyticsWS- (1) from the drop-down and click on Enable assessment (2).
Note: After enabling the assessment, wait for a few minutes to get it complete.
-
Once the assessment is completed, click on it to see the results. The Assessment results will look like below:
Note: Now you can move to the next Exercise, you don't have to wait here for the Result to appear.
Congratulations on completing the task! Now, it's time to validate it. Here are the steps:
- Hit the Validate button for the corresponding task. If you receive a success message, you can proceed to the next task.
- If not, carefully read the error message and retry the step, following the instructions in the lab guide.
- If you need any assistance, please contact us at [email protected]. We are available 24/7 to help you out.
In this exercise, you registered an Azure Arc-enabled SQL Server, enabling centralized management and monitoring of SQL resources. You also performed an on-demand SQL Assessment to evaluate the server's configuration, identify potential issues, and receive recommendations for optimization and best practices.