Centrally track Microsoft SQL Server licenses in AWS Organizations using AWS License Manager and AWS Systems Manager

Update (March 30, 2023): The solution has been updated to support delegated administrator account in your AWS organization.

Most enterprises find it hard to maintain control of the commercial licensing of Microsoft, SAP, Oracle, and IBM products due to limited visibility. They wind up over-provisioning licenses to avoid the headache with third-party license providers or under-provisioning licenses, only to be faced with steep penalties.

To assist enterprises with the challenge of tracking licenses, AWS has built specific features and services to make this easier for customers. There are two main use-cases:

• Using license-included instances allows you access to fully compliant licenses, where AWS handles the tracking and management for you.
• AWS License Manager makes it easy for you to set rules to manage, discover, and report software license usage.

There are some scenarios or software products (for example, Microsoft SQL Server editions) that cannot be governed by these two options, which means you could receive an unwanted surprise in the next audit. In this first of a two-part post, I show you how to build a solution that centrally discovers and tracks your SQL Server instances across AWS accounts and Regions that are part of an organization in AWS Organizations. You can enhance this approach to target other commercial software such as Oracle, SAP, or IBM. For single account setups, see the Use AWS License Manager and AWS Systems Manager to discover SQL Server instances blog post.

In part 2 of this post, I’ll show you how to query and centralize your data so you have a unified view of your license utilization across AWS.

Prerequisites

To deploy this solution across multiple Regions and/or accounts in an organization, complete these steps.

• Enable trusted access with AWS Organizations for CloudFormation. Complete the following tasks as described in Enable trusted access with AWS Organizations
  • Enable all features in AWS Organizations. With only consolidated billing features enabled, you cannot create a stack set with service-managed permissions.
  • Enable trusted access with AWS Organizations. After trusted access is enabled, StackSets creates the necessary IAM roles in the organization’s management account and target (member) accounts when you create stack sets with service-managed permissions.
• Use a delegated administrator. This solution uses the management account within AWS Organizations, but you can also designate an account (delegated administrator) to manage this on behalf of the organization. If you intend to use a delegated account then you will need to register it as delegated administrator for CloudFormation stack set operations as described in Register a delegated administrator.
• Distribute license configurations with member accounts in all target regions.  To distribute self-managed licenses within your organization, from the License Manager console of the organization’s management account, choose Settings, and then select Link AWS Organizations accounts. When you select this option, we add a service-linked role to the management and member accounts. Repeat this step for all target regions. If you intend to use delegated administrator account, then from the License Manager console of the organization’s management account, choose Settings, and under Delegated administrator, choose Delegate administrator. Enter the account ID number for the AWS account that you want to assign, and then choose Delegate. You can’t use the ID for the management account. It must be a member account.

Once completed, under the Settings section you should see a link to the new Resource Share ARN (AWS Resource Access Manager (AWS RAM)), as shown in Figure 1.

Figure 1: License Manager Settings with resource share ARN 

• Create license configurations. In AWS License Manager, create license configurations for the SQL Server editions in each AWS Region where you will be deploying this solution. A license configuration represents the licensing terms in the agreement with your software vendor. Using AWS CloudShell, run the following command to create the following license configurations (case-sensitive), replace REGION with your target regions.

for r in REGION-1 REGION-2;\
do for i in SQLServerEELicenseConfigurationBYOL SQLServerEELicenseConfigurationLI SQLServerSTDLicenseConfigurationBYOL SQLServerSTDLicenseConfigurationLI SQLServerDEVLicenseConfigurationBYOL SQLServerWEBLicenseConfigurationBYOL SQLServerWEBLicenseConfigurationLI SQLServerEXPLicenseConfigurationBYOL;\
do aws license-manager create-license-configuration --name "$i" --license-counting-type vCPU --region $r; done; done

• • SQLServerEELicenseConfigurationBYOL for BYOL Enterprise Edition
  • SQLServerSTDLicenseConfigurationBYOL for BYOL Standard Edition
  • SQLServerWEBLicenseConfigurationBYOL for BYOL Web Edition
  • SQLServerDEVLicenseConfigurationBYOL for BYOL Developer Edition
  • SQLServerEXPLicenseConfigurationBYOL for BYOL Express Edition
  • SQLServerEELicenseConfigurationLI for License Included Enterprise Edition
  • SQLServerSTDLicenseConfigurationLI for License Included Standard Edition
  • SQLServerWEBLicenseConfigurationLI for License Included Web Edition
• Share license configurations. After you have defined your configurations, use AWS Organizations or AWS Resource Access Manager to share license configurations. For instructions, see the Tracking software usage across multiple AWS accounts using AWS License Manager blog post.

After you share your principals (accounts) and resources (license configurations), you should see them in the AWS Resource Access Manager console:

Figure 2: Shared principals and resources in the AWS Resource Access Manager console 

• Manage instances using Systems Manager. A managed instance is an Amazon EC2 instance that is configured for use with Systems Manager. Managed instances can use Systems Manager services such as Run Command, Patch Manager, and Session Manager. You must make sure that all instances targeted for this solution meet the prerequisites to become a managed instance including configuring instance permissions for Systems Manager as described in Setting up Systems Manager for EC2 instances.

Solution overview

AWS License Manager allows you to track your commercial license usage to stay compliant across your enterprise teams. It associates license definitions with AMIs from which instances are launched. AWS License Manager can also auto-discover licensed software (in this solution, SQL Server) that’s installed on instances after initial instance deployment. The solution described in this blog post enhances the auto-discovery capability and provides license edition details for instances deployed across AWS Regions and accounts in AWS Organizations.

Figure 3 shows the solution architecture. In addition to AWS License Manager, the solution uses the following Systems Manager features and capabilities:

• Automation to orchestrate the workflow.
• State Manager to invoke the Automation document on a user-defined frequency.
• Inventory to maintain the information collected about the instances and the SQL Server editions running on them.

Figure 3: Solution architecture 

1. Invoke the SQLServerLTS-Orchestrate Automation: State Manager invokes the SQLServerLTS-Orchestrate Automation and passes the required parameters using which the solution determines the target Organizational Unit IDs/AWS accounts and Regions where your SQL database instances are deployed.
2. Remove old custom Inventory data: In this step, the Orchestrate Automation first invokes the SQLServerLTS-DeleteInventory Automation in the target member account to remove the old custom Inventory schema in Systems Manager Inventory, making sure that Inventory data is current. Inventory data comprises of Oracle database editions and all the packs installed and/or used.
3. Invoke the SQLServerLTS-ManageLicenceUtilization Automation: Once the deletion has been completed, the SQLServerLTS-Orchestrate Automation invokes the SQLServerLTS-ManageLicenceUtilization Automation to initiate the discovery of Oracle databases in your account and track their utilization for license management.
4. Discovery: The Discover Automation then targets instances based on the State Manager association definition to determine the type of Oracle database running, and stores this data in the artifacts bucket under ssm-output. Instances can be targeted using ParameterValues, ResourceGroup or with tag: (default), AWS::EC2::Instance, InstanceIds, instanceids. Refer the API reference for Target for more details.
5. Remove old License Manager data: The Automation first disassociates the target instance from an existing License Configuration. This makes sure that the latest discovered licenses are available in License Manager for scenarios where changes have been made on the instance. For example, somebody deletes or installs a new edition of Oracle database on the target instance after the previous Automation run.
6. Update Inventory: The discovered data is used to update the Systems Manager Inventory. In this step, Automation creates two new custom schemas along with the metadata to store the Oracle edition details along with the management packs.
7. Update License Manager: Finally, the Automation updates the License Manager with the license utilization data and associates the target instance with the appropriate license specification that has been defined in License Manager. Discovered data under ssm-output is cleared for the next run.
8. Aggregate Inventory data using resource data sync: Systems Manager resource data sync sends the Inventory data collected from all your managed instances across the member accounts to a single Amazon Simple Storage Service (Amazon S3) bucket. Then, resource data sync automatically updates the centralized data when new Inventory data is collected.
9. Query the centralized Inventory data: You can use Amazon Athena which provides an interactive query service to analyze the Inventory data in Amazon S3 using standard SQL.
10. Visualize Inventory data: With Amazon QuickSight you can create and publish interactive BI dashboards with insights powered by machine learning (ML).

Walkthrough

To deploy the solution, download this CloudFormation template and deploy it in the management account of your organization.

This template deploys the following resources:

1. Systems Manager documents
   • SQLServerLTS-Orchestrate includes the logic to run step 1 and 3 of the walk-through.
   • SQLServerLTS-DeleteInventory includes the logic to run step 2 of the walk-through.
   • SQLServerLTS-ManageLicenceUtilization includes the logic to run steps 4-7 of the walk-through.

2. All the IAM roles required to deploy the solution
   • SQLServerLTS-SystemsManager-AutomationAdministrationRole, for the administration of the Automation documents.
   • SQLServerLTS-SystemsManager-AutomationExecutionRole, which is deployed using StackSets across all the target accounts and regions for the execution of the Automation documents
   • SQLServerLTS-CloudFormation-StackSetAdministrationRole, to deploy the solution across multiple accounts and Regions using CloudFormation Stacksets
   • SQLServerLTS-CloudFormation-StackSetExecutionRole, to deploy the solution across multiple accounts and Regions using CloudFormation Stacksets.

3. S3 bucket

• • This central bucket in the management account stores all the data from resource data syncs across the accounts, as shown in step 8 of Figure 3.

You can also visit this GitHub repository to access the individual resource templates.

Centralizing Systems Manager Inventory data using resource data sync

The resource data sync capability in AWS Systems Manager lets you sync inventory data from your managed instances into an Amazon Simple Storage Service (Amazon S3) bucket. The resource data sync then updates the S3 bucket whenever new Inventory data is collected. You can also sync Inventory data from multiple AWS accounts into a single S3 bucket, making the bucket an inventory data lake for multiple AWS accounts. You can then use the data lake for advanced queries and analysis of inventory data across multiple accounts. For more information, see Use resource data sync to aggregate inventory data in the AWS Systems Manager User Guide.

Refer to the instructions in Use resource data sync to aggregate inventory data to create resource data syncs for your member accounts.

Invoking the solution using a State Manager association

Use the following AWS Command Line Interface (AWS CLI) command to create an association. Update the highlighted parameters and then run this command in the management or root account of your organization.

• AutomationAssumeRole: Specify your management account ID for the AutomationAssumeRole ARN.
• DeploymentTargets: Enter the organizational unit IDs (for example, ou-abcd-1qwert43). Note: you need to specify all your target OUs explicitly, i.e. nested OUs need to be mentioned as inputs along with the parent OU.
• TargetRegions: Specify all of the AWS Regions (for example, us-east-1) where your Oracle databases are running.
• MaxConcurrency and MaxErrors: Specify these values based on the number of accounts and error thresholds described in StartAutomationExecution in the Systems Manager API Reference.

aws ssm create-association \
  --association-name "SQLServerLicenseTrackingSolutionAssociation" \
  --name "SQLServerLTS-Orchestrate" \
  --parameters '{"AutomationAssumeRole":["arn:aws:iam::ADMINISTRATOR-ACCOUNT-ID:role/SQLServerLTS-SystemsManager-AutomationAdministrationRole"],"DeploymentTargets":["OU1-ID LIKE ou-abcd-1qwert43","OU2-ID"],"TargetRegions":["REGION-1 like us-east-1","REGION-2"],"MaxConcurrency":["4"],"MaxErrors":["4"]}' \
  --no-apply-only-at-cron-interval

Note: This command will invoke the system to run it once immediately after it is created. To ensure that the solution can track licenses on an on-going schedule, update the above command using --schedule-expression, see create-association in the AWS CLI Command Reference.

Validating the execution ran successfully

After the association has triggered the automation, open the Systems Manager console and from the left navigation pane, choose Automation. In Automation executions, choose the most recent execution of the SetupSQLServerLicenseTrackingSolutionDocument, as shown in Figure 5.

Figure 4: Automation executions (management account) 

For more details on the status of individual instances, you can click on the Step ID of SQLServerLTS-ManageLicenceUtilization associated for the AWS account hosting the instance and navigate to the instance of interest, as shown in the figure below.

Figure 5: Automation execution detail (management account) 

To confirm that the license utilization data has been updated in AWS License Manager, using the management account and selected Region, open the License Manager console. Depending on the licenses consumed, the Customer managed licenses list will look something like Figure 9 in each region:

Figure 6: Customer managed licenses

Adding new accounts and Regions

If you add new OU’s, accounts outside of the currently targeted OU’s or Regions, then you must update both the CloudFormation template and association. However, if you are only adding accounts to the currently targeted OU’s then you must only update the association.

CloudFormation:

1. In the CloudFormation console, choose the original template you deployed and then choose Update.
2. Leave the Use the current template option selected.
3. Under Automation Documents, update the TargetRegions and TargetOUs parameters with the new values.

Association:

Update the association using the instructions provided in Editing and creating a new version of an association by updating the parameters with the new additions.

Resource data sync:

To update the resource data sync in the management account with new OUs/Regions refer to update-resource-data-sync

Customizing SSM Documents

To customize any of the SSM Documents including the logic or content, you will need to update the relevant CloudFormation template sections and deploy it as described above.

Cleaning up resources

If you would like to remove the resources and solution after testing you can clean up the resources deployed by the CloudFormation template using the following instructions:

1. Empty the inventory bucket
2. Use the AWS CloudFormation console or AWS CLI to delete the main CloudFormation stack. When you delete the CloudFormation stack, all the solution components will be deleted.
3. Use the Systems Manager console or AWS CLI to delete the association.
4. Use the Systems Manager console or AWS CLI to delete the resource data sync in all the member accounts.

Conclusion

In this post, I showed you how to use AWS License Manager, AWS Systems Manager, and AWS Organizations to automate the tracking of your SQL Server licenses across multiple accounts and Regions. This solution can be extended to govern other software licenses consumed in your organization to avoid any surprises during your next audit.

About the author