SQL server on EC2 setup

Prerequisites

For Clumio MSSQL on EC2 Installation, the following permissions and resources are required for suc-cessful installation and subsequent backups and restores.

IAM prerequisites

  • You must have permission to create and update CloudFormation Templates (CFT).
  • You must have Cloud Shell permissions to onboard hosts. Refer to AWS documentation for how to manage AWS CloudShell access and usage with IAM policies.
    • Alternatively, the generated script can be run on the AWS CLI. The script needs permissions to apply tags and modify the instance profile for an EC2 instance.

SSM agent prerequisites

The SSM Agent must be installed on the EC2 SQL host and updated to the latest version per AWS. For more details, see AWS documentation.

MS SQL prerequisites

  • SSM agent user should have sysadmin on the MSSQL server instance.
  • NT Authority/SQLwriter should have sysadmin privileges.
  • SQL Server VSS writer service should be running
  • Volume Shadow Copy service should be registered or present in services list.
  • Make sure the following VSS providers are running on the EC2 instance. You can verify by executing the vssadmin list providers command in Windows Powershell.
    • Microsoft File Share Shadow Copy provider
    • Microsoft Software Shadow Copy provider 1.0

Protect SQL databases on EC2 with Clumio

To set up Clumio protection for Microsoft SQL databases on EC2 instances, the following high-level steps must be completed:

  • Connect your AWS account to Clumio. Before you can activate protection for your MS SQL Server databases on EC2 with , you must first connect to the AWS environment through Clumio, see AWS account connection.
  • Connect EC2 MS SQL hosts using the IAM permissions and SSM to install Clumio binaries and executable files.

Add hosts

  1. On the left navigation pane, navigate to AWS > Protect and select the AWS account that contains the MS SQL EC2 instances you want to back up. 2.
  2. Click MS SQL on EC2 then click Add Hosts.
  3. Select the EC2 instances from the Add Microsoft SQL on EC2 Hosts wizard that contains the SQL databases you want to protect. If you select instances that do not contain SQL databases, the wizard fails at the next step.
  4. Follow the instructions in the widget to grant Clumio’s IAM policy permission to access the EC2 instances through the AWS Cloud Shell. Select the check box to indicate you have completed this step and proceed to the next step.
  5. After permission has been granted, install the AWS Systems Manager agent on each selected EC2 instance. If the Systems Manager is already installed, select the check box to confirm that it is already installed and click Next. Clumio then verifies that the required permissions to perform inventory search, backup, and restore operations on the MS SQL database have been granted.
  6. Next, install the executable files. This step includes multiple options:
    1. Use the same binary and temporary path for all hosts or clear the Use the same file patch for all these hosts check box to use different paths for each host.
    2. If required, select the option to create a directory if a given directory path is not available.
    3. Click Reset to default paths to undo any changes made to the paths and revert to the default original paths.
      The file path locations provided for the temporary files must be empty, otherwise the installation fails.

Clumio stores executable and temporary files required for backup and restore on each EC2 instance. Edit the file paths as needed. You must ensure that the temporary files path location has enough space to accommodate your database.

Add FCI hosts

If the hosts that you import are part of a Failover Cluster Instance (FCI), this is detected when the invent-ory sync runs. For a successful backup of an MS SQL database on EC2 instance that is part of an FCI, the active and passive nodes of that cluster must be added to the Clumio platform.

Clumio can identify whether the imported host is an active node or a passive node. Click the Failover clusters tab to view imported clusters. The Hosts column indicates if the host is an active or passive node. The Protection status column indicates if the cluster is protected or not.

  • If the imported host is a passive node, it cannot be protected and if you hover the status, a mes-sage telling you to import the active host displays.
  • If the imported host is the active node, but the passive nodes have not been imported, Clumio only provides partial protection. If you hover over the status, a message telling you to import the passive host displays.
  • If you imported all nodes of the FCI, Clumio provides complete protection for assets on those hosts.

Note:

  • Clumio does not support Availability Groups on FCI deployments.
  • Clumio does not support FCI instances that span across different AWS environments.
  • If you switch from using FCI to using Availability Groups or vice versa, Clumio automatically detects the change. However, any policies that applied to the earlier configuration are no longer valid and must be manually reapplied to the hosts or databases in the new configuration.