Skip to main content

Create a database server in BlueXP workload factory for Databases

Contributors netapp-rlithman
PDFs

Creating a new Microsoft SQL Server, or database host, in BlueXP workload factory for Databases requires an FSx for ONTAP file system deployment and resources for Active Directory.

About this task

Before creating a Microsoft SQL Server from workload factory, learn about the available storage deployment types for the database host configuration, Active Directory deployment, workload factory modes of operation, and the requirements to complete this operation.

After deployment, you'll need to enable remote connection on the Microsoft SQL Server.

FSx for ONTAP file system deployments

Creating a new Microsoft SQL Server requires an FSx for ONTAP file system as the storage backend. You can use an existing FSx for ONTAP file system or create a new file system. If you select an existing FSx for ONTAP file system as your database server storage backend, we create a new storage VM for the Microsoft SQL workloads.

FSx for ONTAP file systems have two Microsoft SQL Server deployment models: Failover Cluster Instance (FCI) or Standalone. Different resources are created for the FSx for ONTAP file system depending on the FSx for ONTAP deployment model you select.

  • Failover Cluster Instance (FCI) Microsoft SQL deployment: A Multiple Availability Zone FSx for NetApp ONTAP file system is deployed when a new FSx for ONTAP file system is selected for FCI deployment. Separate volumes and LUNs are created for data, log, and tempdb files for an FCI deployment. An additional volume and LUN are created for Quorum or witness disk for Windows cluster.

  • Standalone Microsoft SQL deployment: A Single Availability Zone FSx for ONTAP file system is created when a new Microsoft SQL Server is created. In addition, separate volumes and LUNs are created for data, log, and tempdb files.

Microsoft Multi-path I/O configuration

Microsoft SQL Server deployment models both require LUN creation using the iSCSI storage protocol. Workload factory configures Microsoft Multi-path I/O (MPIO) as part of configuring LUNs for SQL Server over FSx for ONTAP. MPIO is configured based on AWS and NetApp best practices.

For more information, refer to SQL Server High Availability Deployments Using Amazon FSx for NetApp ONTAP.

Active Directory

The following occurs for Active Directory (AD) during deployment:

  • A new Microsoft SQL service account is created in the domain if you don't provide an existing SQL service account.

  • The Windows cluster, node host names, and Microsoft SQL FCI name are added as managed computers to the Microsoft SQL service account.

  • The Windows cluster entry is assigned permissions to add computers to the domain.

User-managed Active Directory security groups

If you select “user-managed Active Directory” during Microsoft SQL Server deployment in workload factory, you must provide a security group that allows traffic between the EC2 instances to the directory service for deployment. Workload factory doesn't automatically attach the security group for user-managed Active Directory like it does for AWS Managed Microsoft AD.

Resource rollback

If you decide to rollback your Domain Name System (DNS) resources, the resource records in AD and DNS are not removed automatically. You can remove the records from the DNS server and AD as follows.

Workload factory operation modes

Workload factory offers three operational modes depending on how comfortable you are with letting workload factory manage your AWS resources.

Basic mode: in this mode of operation, you don't need to associate any AWS account credentials in workload factory. You can copy or download a partially filled YAML template from the Codebox to be completed outside workload factory.

Read mode: in this mode of operation, you provide AWS account credentials with read permissions which lets you complete the Quick create or Advanced create form and then copy or download it. You can also redirect to CloudFormation from workload factory with the completed form details. And you will be able to manage the deployed database server in workload factory.

Automate mode: in this mode of operation, you provide AWS account credentials with automate permissions which lets you create and manage AWS resources within workload factory.

Before you begin

Ensure you have the following prerequisites before you create a new database host.

Credentials and permissions

You must have AWS account credentials and automate mode permissions to create a new database host in workload factory.

Active Directory

When connecting to Active Directory, you must have administrative access with permissions to do the following:

  • Join the domain

  • Create Computer Objects

  • Create objects in the default Organization Unit (OU)

  • Read all properties

  • Make the domain user a local admin on the AD nodes

  • Create a Microsoft SQL Server service user in the AD, if it doesn't exist already

Step 1: Create a database server

You can use Quick create or Advanced create deployment modes to complete this task in workload factory with Automate mode permissions. You can also use the following tools available in the Codebox: REST API, AWS CLI, AWS CloudFormation, and Terraform. Learn how to use Codebox for automation.

Note When using Terraform from Codebox, the code you copy or download hides fsxadmin and vsadmin passwords. You'll need to re-enter the passwords when you run the code. You'll need to include the following permissions for the user account in addition to Automate mode permissions: iam:TagRole and iam:TagInstanceProfile. Learn how to use Terraform from Codebox.
Quick create
Note In Quick create, FCI is the default deployment model, Windows 2016 is the default Windows version, and SQL 2019 Standard Edition is the default SQL version.
Steps

  1. Log in using one of the console experiences.

  2. In the Databases tile, select Deploy database host and then select Microsoft SQL Server from the dropdown menu.

  3. Select Quick create.

  4. Under AWS settings, provide the following:

    1. AWS credentials: Select AWS credentials with automate permissions to deploy the new database host.

      AWS credentials with automate permissions let workload factory deploy and manage the new database host from your AWS account within workload factory.

      AWS credentials with read permissions let workload factory generate a CloudFormation template for you to use in the AWS CloudFormation console.

      If you don't have AWS credentials associated in workload factory and you want to create the new server in workload factory, follow Option 1 to go to the Credentials page. Manually add the required credentials and permissions for automate mode for Database workloads.

      If you want to complete the create new server form in workload factory so you can download a complete YAML file template for deployment in AWS CloudFormation, follow Option 2 to ensure you have the required permissions to create the new server within AWS CloudFormation. Manually add the required credentials and permissions for read mode for Database workloads.

      Optionally, you can download a partially completed YAML file template from the Codebox to create the stack outside workload factory without any credentials or permissions. Select CloudFormation from the dropdown in the Codebox to download the YAML file.

    2. Region & VPC: Select a Region and VPC network.

      Ensure security groups for an existing interface endpoint allow access to HTTPS (443) protocol to the selected subnets.

      AWS service interface endpoints (SQS, FSx, EC2, CloudWatch, CloudFormation, SSM) and the S3 gateway endpoint are created during deployment if not found.

      VPC DNS attributes EnableDnsSupport and EnableDnsHostnames are modified to enable endpoint address resolution if they aren't already set to true.

    3. Availability zones: Select availability zones and subnets according to the Failover Cluster Instance (FCI) deployment model.

      Note FCI deployments are only supported on Multiple Availability Zone (MAZ) FSx for ONTAP configurations.

      1. In the Cluster configuration - Node 1 field, select the primary availability zone for the MAZ FSx for ONTAP configuration from the Availability zone dropdown menu and a subnet from the primary availability zone from the Subnet dropdown menu.

      2. In the Cluster configuration - Node 2 field, select the secondary availability zone for the MAZ FSx for ONTAP configuration from the Availability zone dropdown menu and a subnet from the secondary availability zone from the Subnet dropdown menu.

  5. Under Application settings, enter a user name and password for Database credentials.

  6. Under Connectivity, provide the following:

    1. Key pair: Select a key pair.

    2. Active Directory:

      1. In the Domain name field, select or enter a name for the domain.

        1. For AWS-managed Active Directories, domain names appear in the dropdown menu.

        2. For a user-managed Active Directory, enter a name in the Search and Add field, and click Add.

      2. In the DNS address field, enter the DNS IP address for the domain. You can add up to 3 IP addresses.

        For AWS-managed Active Directories, the DNS IP address(es) appear in the dropdown menu.

      3. In the User name field, enter the user name for the Active Directory domain.

      4. In the Password field, enter a password for the Active Directory domain.

  7. Under Infrastructure settings, provide the following:

    1. FSx for ONTAP system: Create a new FSx for ONTAP file system or use an existing FSx for ONTAP file system.

      1. Create new FSx for ONTAP: Enter user name and password.

        A new FSx for ONTAP file system may add 30 minutes or more of installation time.

      2. Select an existing FSx for ONTAP: Select FSx for ONTAP name from the dropdown menu, and enter a user name and password for the file system.

        For existing FSx for ONTAP file systems, ensure the following:

        • The routing group attached to FSx for ONTAP allows routes to the subnets to be used for deployment.

        • The security group allows traffic from the subnets used for deployment, specifically HTTPS (443) and iSCSI (3260) TCP ports.

    2. Data drive size: Enter the data drive capacity and select the capacity unit.

  8. Summary:

    1. Preview default: Review the default configurations set by Quick create.

    2. Estimated cost: Provides an estimate of charges that you might incur if you deployed the resources shown.

  9. Click Create.

    Alternatively, if you want to change any of these default settings now, create the database server with Advanced create.

    You can also select Save configuration to deploy the host later.

Step 2: Enable remote connection on the Microsoft SQL Server

After the server deploys, workload factory does not enable remote connection on the Microsoft SQL Server. To enable the remote connection, complete the following steps.

Steps

  1. Use computer identity for NTLM by referring to Network security: Allow Local System to use computer identity for NTLM in Microsoft documentation.

  2. Check dynamic port configuration by referring to A network-related or instance-specific error occurred while establishing a connection to SQL Server in Microsoft documentation.

  3. Allow the required client IP or subnet in the security group.

What's next

Now you can create a database in BlueXP workload factory for Databases.