Export (0) Print
Expand All

Phase 3: Configure SQL Server Infrastructure

Updated: November 11, 2014

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

This is the third phase of deploying SharePoint with SQL Server AlwaysOn in Azure, which includes configuring the two SQL Server computers. You must complete this phase before moving on to Phase 4.

For the previous phase, see Phase 2: Configure Domain Controllers.

For the next phase, see Phase 4: Configure SharePoint Servers.

For all of the phases of this deployment, see Deploying SharePoint 2013 with SQL Server AlwaysOn Availability Groups in Azure.

This deployment of SharePoint with SQL Server AlwaysOn is designed to accompany the SharePoint with SQL Server AlwaysOn Infographic and incorporate the latest recommendations.

The following figure shows the configuration resulting from the successful completion of this phase.

Phase 3: Adding SQL servers

Create the SQL Server virtual machines in Azure

There are two SQL server virtual machines. Th eprimary SQL server contains the primary database replica of an availability group. The secondary SQL server contains the secondary backup replica. The backup is provided to ensure high-availability.

Use the following procedure twice to create the two SQL Server virtual machines. Note that this procedure uses values from Table M in Phase 2: Configure Domain Controllers and Tables V (for your virtual network settings) and S (for your subnet) in Phase 1: Configure Azure.

Creating a VM from the gallery

  1. In the command bar of the Azure Management Portal, click New > Compute > Virtual Machine > From Gallery.

  2. On the Choose an image page, select Microsoft SQL Server 2012 SP1 Enterprise on Windows Server 2008 R2 Service Pack 1.

  3. Click the next arrow icon to continue.

  4. On the Virtual machine configuration page:

    • In Virtual Machine Name, type the name of this computer from Item 3 for the first SQL server and Item 4 for the second SQL server in Table M. This name will be displayed in the Azure Management Portal.

    • In Size, select the size for the virtual machine based on the Minimum size column of Table M. Virtual machines can be reconfigured for their size after creation. To do so, in the Azure Management Portal, click Virtual Machines, click the name of the virtual machine, and then click the Configure menu option.

    • In New User Name, type a user name for a local administrator account. You will use this name to log on to the machine when initially logging on to it through a Remote Desktop connection.

    • In New Password, enter a strong password for the local administrator account.

    • In Confirm Password, re-enter the strong password.

      Record the local administrator account name and password in a secure location for future reference.

  5. Click the next arrow to continue.

  6. On the Virtual machine configuration page:

    • In Cloud Service, select the name in the Cloud service name column of Table M for the virtual machine.

    • In Region/Affinity Group/Virtual Network, select the name corresponding to Item 1 in Table V, your newly-created virtual network.

    • In Virtual Network Subnets, select the name of your subnet corresponding to Item 1 in Table S.

    • In Storage Account, select the storage account that you created for this deployment.

    • For the first SQL server virtual machine, in Availability Set, click Create availability set and type the name from the Availability set column of Table M for Item 3. For the second SQL server, select the name of the availability set created for the first SQL server.

  7. Click the next arrow to continue.

  8. On the Virtual machine configuration page, in Endpoints, click the PowerShell row and delete 5986 from the Public Port column.

    This will tell Azure to generate a random public port for remote Windows PowerShell sessions for this virtual machine. To see this port number in the Azure Management Portal, click Virtual Machines, click the name of the virtual machine, and then click the Dashboard menu option.

  9. Click the check mark to create the machine. Virtual machine creation can take several minutes.

Use the following procedure twice to create the cluster majority node computer. Note that this procedure uses values from Table M in this topic and Tables V (for your virtual network settings) and S (for your subnet) that you defined in Phase 1: Configure Azure.

Creating a VM from the gallery

  1. In the command bar of the Azure Management Portal, click New > Compute > Virtual Machine > From Gallery.

  2. On the Choose an image page, select Windows Server R2 2012 Datacenter.

  3. Click the next arrow icon to continue.

  4. On the Virtual machine configuration page:

    • In Virtual Machine Name, type the name of this computer from the row for Item 5 in Table M. This name will be displayed in the Azure Management Portal.

    • In Size, select the size for the virtual machine based on the Minimum size column of Table M. Virtual machines can be reconfigured for their size after creation. To do so, in the Azure Management Portal, click Virtual Machines, click the name of the virtual machine, and then click the Configure menu option.

    • In New User Name, type a user name for a local administrator account. You will use this name to log on to the machine when initially logging on to it through a Remote Desktop connection.

    • In New Password, enter a strong password for the local administrator account.

    • In Confirm Password, re-enter the strong password.

      Record the local administrator account name and password in a secure location for future reference.

  5. Click the next arrow to continue.

  6. On the Virtual machine configuration page:

    • In Cloud Service, select the name in the Cloud service name column of Table M for the virtual machine.

    • In Region/Affinity Group/Virtual Network, select the name corresponding to Item 1 in Table V, your newly-created virtual network.

    • In Virtual Network Subnets, select the name of your subnet corresponding to Item 1 in Table S.

    • In Storage Account, select the storage account that you created for this deployment.

  7. Click the next arrow to continue.

  8. On the Virtual machine configuration page, in Endpoints, click the PowerShell row and delete 5986 from the Public Port column.

    This will tell Azure to generate a random public port for remote Windows PowerShell sessions for this virtual machine. To see this port number in the Azure Management Portal, click Virtual Machines, click the name of the virtual machine, and then click the Dashboard menu option.

  9. Click the check mark to create the machine. Virtual machine creation can take several minutes.

Next, you need to add an extra data disk to each SQL server virtual machine. Both SQL servers will use this extra disk to store their databases.

Use the following procedure twice, one for each SQL server, to attach an empty data disk.

Attaching an empty disk to an Azure VM

  1. In the Azure Management Portal, click Virtual Machines, and then select the virtual machine to which you want to attach the data disk. You must wait until the VM is provisioned and its status is Running before attaching the empty disk.

  2. On the command bar, click Attach, and then click Attach Empty Disk. The Attach Empty Disk dialog box appears. By default, the storage created earlier is entered into the Storage Location. The File Name box contains an automatically generated name that does not need to be altered.

  3. In Size, type 500.

  4. Leave the Host Cache Preference set to the default value None.

  5. Click the check mark to attach the empty data disk. Wait until the status is Running before continuing (a few minutes).

For additional guidance on creating data disks, see Disks.

Use the following procedure twice, one for each SQL server, to log on using the credentials of the local administrator account.

Logging on to a virtual machine with a Remote Desktop connection

  1. In the Azure Management Portal, in the left panel, click Virtual Machines.

  2. To connect to a VM, click Running in the Status column next to its name.

  3. In the command bar on the bottom of the page, click Connect.

  4. The Management Portal informs you the .rdp file is being retrieved. Click OK.

  5. The browser dialog appears asking, "Do you want to open or save ComputerName.rdp from manage.windowsazure.com." Click Open.

  6. In the Remote Desktop Connection dialog, click Connect.

  7. In the Windows Security dialog, click Use another account.

  8. In User name, type the name of the VM and user name of the local administrator account created with the VM (a local machine account). Use the following format:

    ComputerName\LocalAdministratorAccountName

  9. In Password, type the password for the local administrator account.

  10. Click OK.

  11. In the Remote Desktop Connection dialog, click Yes. The desktop of the new machine appears in a Remote Desktop session window.

Use the following procedure twice, one for each SQL server, to add the extra data disk and add folders.

To initialize an empty disk and add folders

  1. In Server Manager, in the left pane, click File and Storage Services.

  2. In the left pane, click Disks. In the list of disks, select the empty disk, which is identifiable by its capacity and its Partition set to Unknown.

  3. In the Volumes panel, click To create a volume, start the Volume Wizard. Follow the steps in the wizard to initialize the disk. Accept all defaults to create a GUID Partition Table (GPT) disk assigned to the drive letter "F". Give the volume a friendly name to distinguish it from other disks.

  4. Run an administrator-level Windows PowerShell command prompt.

  5. Run the following commands from the Windows PowerShell command prompt

    md f:\Data
    md f:\Log
    md f:\Backup
    
    

Use the following procedure twice, one for each SQL server, to test connectivity to locations on your organization network.

To test connectivity

  1. At the PowerShell command prompt, use the ping command to ping names and IP addresses of resources on your organization network.

  2. At the PowerShell command prompt, use the ping command to ping the name of both domain controllers in the virtual network.

This procedure ensures that DNS name resolution is working correctly (that the virtual machine is correctly configured with on-premises DNS servers) and that packets can be sent to and from the cross-premises virtual network.

Use the following procedure three times, one for each SQL server and one for the cluster majority node computer, to join the virtual machines to the appropriate Active Directory domain with the following Windows PowerShell commands (removing the text and the brackets for the $domname variable):


$domname="[DNS domain name of the AD domain for which this computer will be a member]"
Add-Computer -DomainName $domname
Restart-Computer

After restarting, connect to both SQL server virtual machines using the same steps as the previous Logging on to a virtual machine with a Remote Desktop connection procedure. Use the credentials (the name and password) in steps 8 and 9 corresponding to the local administrator account.

Next, use the following procedure twice, one for each SQL server, to configure the SQL server to use the F: drive for new databases and for accounts and permissions.

Configuring the SQL server virtual machine

  1. On the Start menu, click SQL Server 2012, and then click SQL Server Management Studio.

  2. Connect to the SQL Server default instance using the Windows account.

  3. Right-click the top node—the default instance named after the machine—and click Properties.

  4. In the Server Properties window, click Database Settings.

  5. Locate the Database default locations and set the following values:

    • For Data, set the path to f:\Data.

    • For Log, set the path to f:\Log.

    • For Backup, set the path to f:\Backup.

    Only new databases use these locations.

  6. Click the OK to close the window.

  7. In the left pane, expand the Security folder.

  8. Right-click Logins, then click New login.

  9. In the Login-New dialog, type [domain]\sp_farm_db (in which [domain] is the name of the AD domain in which the sp_farm_db account was created.

  10. After the name resolves, click OK to close the dialog.

  11. In the left pane, click Server Roles, and select the checkbox for Sysadmin role. Click OK to close the dialog.

  12. Right-click Logins and click New Login.

  13. In the Login name box, type NT Authority\System and click OK.

  14. In Object Explorer, right-click NT AUTHORITY\System and click Properties.

  15. In the Securables page, for the local server, select Grant for the following permissions and click OK.

    • Alter any availability group

    • Connect SQL

    • View server state

  16. Click OK and close the SQL Server Management Studio.

SQL Server requires a port that clients use to access the database server. It also needs a port to manage the high-availability group. By default, the client port number is 1433 and the high availability port is 5022. The Windows Firewall, however, blocks both ports by default. Next, use the following procedure twice, one for each SQL server, to add a firewall rule that allows inbound traffic to the SQL Server service.

Configure a Windows Firewall rule to allow incoming requests

  1. In the Start menu, type Windows firewall with advanced security, and then press ENTER.

  2. In the tree pane, click Inbound Rules.

  3. On the Action pane, click New Rule.

  4. On the Rule Type page, select Program, and click Next.

  5. In the Program page, select This program path and type C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe in the text box. Then, click Next.

  6. On the Action page, click Next (keep the default setting of Allow the connection).

  7. On the Profile page, accept the defaults, and click Next.

  8. On the Name page, type SQL Server ports 1433 and 5022, and click Finish.

For more information, see Configure a Windows Firewall for Database Engine Access.

For each of the SQL server virtual machines, sign out as the local administrator.

Next Steps

For the next phase of the deployment, see Phase 4: Configure SharePoint Servers.

For all of the phases of this deployment, see Deploying SharePoint 2013 with SQL Server AlwaysOn Availability Groups in Azure.

See Also

Community Additions

ADD
Show:
© 2014 Microsoft