Building a SharePoint 2019 MinRole Environment in Microsoft Azure – Deploy SQL Server

Once the domain controller is configured and active directory for the farm is created it is time to deploy SQL Server.  Because this will be an “on-prem” farm I will not be utilizing an Azure SQL Database but instead will deploy an actual SQL Server VM.  This is made pretty straight forward as Azure provides a template for this.

This is a multi-part series.  You can see what is coming and review other posts in the series by clicking one of the following links:

• Microsoft Azure – Prepping the Azure Environment for SharePoint 2019
• Microsoft Azure – Configure Azure Network Resources for SharePoint 2019
• Microsoft Azure – Build Storage Resources for Azure SharePoint 2019
• Microsoft Azure – Creating the Domain Controller
• Microsoft Azure – Configuring the Domain Controller Network
• Microsoft Azure – Configuring DNS and Active Directory
• Microsoft Azure – Build SharePoint Server Virtual Machine
• Microsoft Azure – Deploy SQL Server (this post)
• Microsoft Azure – Build SharePoint 2019 Template with AutoSPInstaller
• Microsoft Azure – Prepping SharePoint Servers
• Microsoft Azure – Installing SharePoint 2019
• Microsoft Azure – Add a Load Balancer for External Access

Deploy SQL Server

To get started we will be utilizing an Azure provided SQL Server template.  We will be using a free version of SQL Server for this template.  As long as we aren’t using this environment for production a development license can be used.  If you are following this blog post to implement a production environment, you cannot use this license.

1. Click on Create a Resource
2. Perform a search for “SQL Server 2017 on Windows”
3. Click on the Tile: “SQL Server 2017 on Windows Server 2016“

4. At the software plan screen, select the free SQL Server 2017 Developer on Windows Server 2016 plan.

5. Then click on “Start with a pre-set configuration“.
6. The next page allows you to make general selections based on the workload environment and type the database server will be utilizing.  Select Dev/Test for the workload environment and General Purpose (D-Series) for the workload type.

7. Click “Continue to create a VM“

Basics Screen

1. Select the subscription and resource group you wish to use (ensure the resource group is the SharePoint one you created for this purpose).
2. Give the server a meaningful name
3. Select the region you wish to place the VM in.
4. At the Availability Options, select the option you want, but for a Dev environment I suggest “No infrastructure redundancy required“
5. Make sure your image is the free SQL Server on Windows Server 2016.
6. You can change the VM size, but I would leave it at the default.  If you are concerned about cost, remember the intent is only to have the environment running when you need it.
7. Enter in your admin ID and password
8. If you wish to open a port for RDP you can do that at the Inbound Port Rules section
9. Click Next: Disks.

Disks Screen

1. Nothing to change here.  Just ensure the disk type is Standard SSD.
2. Ensure Managed disks is Yes.
3. Click Next: Networking.

Networking Screen

1. Nothing to change here either.  Ensure the Virtual Network selected is the one created for the SP environment
2. Click Next: Management.

Management Screen

1. Set the Diagnostics storage account to the SQL storage account created earlier.
2. Ensure auto shutdown is enabled and set it for a time that works for you.
3. Click Next: Advanced

Advanced Screen

There won’t be anything to change on the Advanced Screen.  Click Next: SQL Server settings.

SQL Server Settings Screen

1. The only change I suggest you make for this particular VM is to enable SQL Authentication.  This is not mandatory, but something I feel is worth as a backup in case something happens with the domain integration.  When you do this, the wizard will import the admin username and password you set in the Basics screen.
2. Click Next: Tags.

Skip through to the Review screen and ensure the settings (and cost) are what you were expecting.

Click Create.

The VM should be finished in about 10 minutes.

Configure SQL Server

We aren’t quite done yet.  The next step is to configure the SQL Server to join the domain and prepare it to support the SharePoint environment.  First off we need to join the domain.

#This script provides the domain and username, but prompts for a password #when joining the domain 
$domainToJoin = "<Your Domain>"; 
$loginID = "<Your Admin Account>"; 
$domainUser = ("{0}\{1}" -f $domainToJoin,$loginID); 
$adminPWD = Read-Host -Prompt ("Please enter the password for account '{0}'" -f $domainUser) -AsSecureString; $joinCredential = New-Object System.Management.Automation.PSCredential($domainUser,$adminPWD); 

#Join to domain and reboot after. 
Add-Computer -DomainName $domainToJoin -Credential $joinCredential -Restart

Because the SQL server was created using an Azure template, it doesn’t see the domain accounts as a valid login at this point.  To correct this, you need to login to the server as the SQL_Admin account (after making it a domain admin account) or as the domain admin account if you chose to not make SQL_Admin a domain admin (you can remove it later if you choose).  Then Shift + Right Click on the Management Studio launcher and select “Run as a different user“.  Log in as the local admin account.  Next, you need to add the sql_admin domain account to the SQL login as server admin.

1. Right-click on the Security Folder and select New -> User

2. Add the domain SQL Admin account

3. Setup the account as a sysadmin

Now your server is ready for connecting to SharePoint.

Thanks for reading!