AWS Cloud Operations Blog

Use AWS License Manager and AWS Systems Manager to discover SQL Server BYOL instances

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.

If your enterprise uses AWS, you can address this challenge in two ways:

  • Using license-included instances allows you access to fully compliant licenses, where AWS handles the tracking and management for you. With this option, you pay as you go, with no upfront costs or long-term investment.
  • AWS License Manager makes it easy for you to set rules to manage, discover, and report software license usage. When you use AWS License Manager to associate an Amazon Machine Image (AMI) with a licensing configuration, you can track the use of licenses in AWS or your on-premises environment. You can also set rules in AWS License Manager to prevent licensing violations to help you stay compliant.

It’s still possible that your teams might provision software products not governed by these two options, which means you could receive an unwanted surprise in the next audit. In this blog post, we show you how to build a solution that discovers and tracks your Microsoft SQL Server instances. You can enhance this approach to target other commercial software such as Oracle, SAP, or IBM.

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, Microsoft 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.

In addition to AWS License Manager, the solution uses the following AWS Systems Manager features:

  • Automation orchestrates the entire workflow.
  • State Manager invokes the Automation document on a user-defined frequency.
  • Inventory maintains all the information collected about the instances and the Microsoft SQL Server editions running on them.

Figure 1 shows the solution architecture.

Step 1 is to invoke the primary Automation document, which in step 2 removes old custom Inventory data. Step 3 is to invoke the secondary Automation document. Step 4 removes old AWS License Manager data. Step 5 is discovery of the instances. Step 6 is to update the inventory. Step 7 is to update AWS License Manager.

Figure 1: Solution architecture

Walkthrough

To implement this solution, complete the following steps:

  1. Create the IAM service role and policy that allow AWS Systems Manager to call other AWS services on your behalf.
  2. Create the Automation documents.
  3. Create a State Manager association, which invokes the Automation document.
  4. Test the solution.

If you prefer to use CloudFormation to create these resources, then download this template and launch it in your AWS Account.

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

Create an IAM service role and policy

Create an IAM policy granting permissions for AWS Systems Manager, Amazon EC2 and AWS License Manager to perform the steps in the Automation document.

  1. In the AWS Identity and Access Management console, choose Policies, and then choose Create policy.
  2. On the JSON tab, enter the following code. Supply values for REGION, AWS-ACCOUNT-ID, and ARN OF YOUR LICENSE CONFIGURATION
{
  "Version":"2012-10-17",
  "Statement":[
    {
      "Sid":"sid0",
      "Effect":"Allow",
      "Action":[
        "license-manager:UpdateLicenseSpecificationsForResource"
      ],
      "Resource":[
        "(ARN OF YOUR LICENSE CONFIGURATIONS)"
      ]
    },
    {
      "Sid":"sid1",
      "Effect":"Allow",
      "Action":[
        "ssm:SendCommand"
      ],
      "Resource":[
        "arn:aws:ec2:*:(AWS-ACCOUNT-ID):instance/*",
        "arn:aws:ssm:(REGION):(AWS-ACCOUNT-ID):automation-definition/Primary-SQLServerLicenseTrackingSolution-Document:VersionId}",
        "arn:aws:ssm:(REGION):(AWS-ACCOUNT-ID):document/Primary-SQLServerLicenseTrackingSolution-Document",
        "arn:aws:ssm:(REGION):(AWS-ACCOUNT-ID):automation-definition/Secondary-SQLServerLicenseTrackingSolution-Document:VersionId}",
        "arn:aws:ssm:(REGION):(AWS-ACCOUNT-ID):document/Secondary-SQLServerLicenseTrackingSolution-Document",
        "arn:aws:ssm:(REGION):*:document/AWS-RunPowerShellScript"
      ]
    },
    {
      "Sid":"sid2",
      "Effect":"Allow",
      "Action":"iam:PassRole",
      "Resource":"arn:aws:iam::(AWS-ACCOUNT-ID):role/SQLServerLicenseTrackingSolution-Role"
    },
    {
      "Sid":"sid3",
      "Effect":"Allow",
      "Action":[
        "ssm:DeleteInventory",
        "ssm:PutInventory",
        "ssm:StartAutomationExecution",
        "ssm:ListCommands",
        "ssm:DescribeInstanceInformation",
        "ssm:ListCommandInvocations",
        "ssm:UpdateInstanceAssociationStatus",
        "ec2:DescribeTags",
        "ec2:DescribeInstances",
		"ec2messages:GetEndpoint",
		"ec2messages:FailMessage",
		"ec2messages:AcknowledgeMessage",
		"ec2messages:SendReply",
		"ec2messages:GetMessages",
		"tag:GetResources",
        "license-manager:ListLicenseSpecificationsForResource"
      ],
      "Resource":"*"
    }
  ]
}
  1. Choose Review policy.
  2. For Name, enter SQLServerLicenseTracking-Policy.
  3. For Description, enter Policy used by Systems Manager SQLServerLicenseTracking-Role to track SQL Server licenses using AWS License Manager.
  4. Choose Create policy.
  5. Next, you need to create an IAM role. In the IAM console, choose Roles, and then choose Create role.
  6. On the Select role type page, choose AWS service, and then choose Systems Manager.
  7. Under Select your use case, choose Systems Manager.
  8. Choose Next: Permissions.
  9. For Filter policies, enter SQLServerLicenseTrackingSolution-Policy.
  10. Choose Next: Tags. You can add a tag to this role based on your corporate tagging strategy.
  11. Choose Next: Review.
  12. For Role name, enter SQLServerLicenseTrackingSolution-Role.
  13. For Description, enter Role used by Systems Manager and AWS License Manager to track SQL Server licenses using AWS License Manager.
  14. Choose Create role.

Now that you have created an IAM role and policy granting the permissions required for the execution of the automation documents, we are going to use AWS Systems Manager to create the automation documents.

Create the Automation documents

  1. In the AWS Systems Manager console, under Actions & Change, choose Automation.
  2. Choose Execute automation, choose Create document, and then choose Editor.
  3. For Name, enter  Primary-SQLServerLicenseTrackingSolution-Document.
  4. In the editor, choose Edit, and then choose OK to confirm.
  5. Delete the existing content and paste the contents of Primary-SQLServerLicenseTrackingSolution-Document.yaml.
  6. Choose Create automation, and then verify that the newly created document is displayed under Owned by me.
  7. Follow these steps to create the second Automation document, Secondary-SQLServerLicenseTrackingSolution-Document,using the contents of Secondary-SQLServerLicenseTrackingSolution-Document.yaml.

Nine-step sequence is described in the body of the blog post.]

Figure 2: Execution sequence of Automation document

The steps in the primary document are executed in the following order.

  1. Remove old custom inventory (deleteCustomInventory): This cleanup task deletes all the metadata associated with the custom inventory type created for MSSQL (Custom:SQLServer) on all the EC2 instances.
  2. Invoke secondary document (invokeSecondarySQLServerLicenseTrackingSolutionDocument): This step invokes the Secondary-SQLServerLicenseTrackingSolution-Document

The steps in the secondary document are executed in the following order.

  1. Remove old AWS License Manager data (removeLicenseConfigDataForInstance): Performs a cleanup task, removing any association to the license configuration, if one exists. This gives us an opportunity to refresh the latest data to AWS License Manager in case changes have been made.
  2. Assert instance eligibility (assertInstanceEligibility): Checks if the EC2 instance is eligible for this document. The two criteria are:
    • The instance is managed by Systems Manager and currently online.
    • The instance is running on a Windows operating system.
  1. Is BYOL SQL installed (isBYOLSQLServerInstalled): Checks if Microsoft SQL Server is installed. If so, checks if it is not a SQL Server license-included instance. If so, retrieves the SQL Server details on the EC2 instance using Windows Registry. The output captures the name, edition, and version of SQL Server.
  2. Conditional logic (foundSQLServerInstalledBranch): Performs a branch action based on the evaluation of the previous step. Defaults to the next step if MSSQL exists on the EC2 instance. Exits if unavailable.
  3. Update Inventory (updateInventory): Uses the output of step 3 (metadata) to update Inventory with a custom inventory of type Custom:SQLServer for the EC2 instance.
  4. Update AWS License Manager (updateLicenseManager): Determines the most recent edition of SQL Server installed and updates the AWS License Manager configuration associated with the EC2 instance accordingly.
  5. End (exitIfNoSqlServerFound):Is triggered if no SQL Server instances are found.

Create a State Manager association

  1. In the AWS Systems Manager console, under Instances & Nodes, choose State Manager, and then chooseCreate association.
  2. For Name, enter  SQLServerLicenseTrackingSolution-Association.
  3. In the search field, enter Primary-SQLServerLicenseTrackingSolution-Document,and then choose it in the results.
  4. For Document version, choose Default at runtime.
  5. Choose Simple execution.
  6. Under Input parameters, enter the following:
    • InstanceId: Use * to target all instances in this account or you can specify an individual instance ID, the default is *
    • TagKey: Specify a Tag name that will be used to filter the managed instances for this solution, the default value is LicenseTrackingSolution-ManagedInstance. Ensure the same key is used to tag your EC2 instances or virtual machines
    • TagValue: Specify the Tag value corresponding with the TagKey, the default value is true. Ensure the same value is used to tag your EC2 instances or virtual machines
    • Region: Region where you are deploying this document or the Region in which this document is executed
    • AccountId: Account ID where you are deploying this document or the account in which this document is executed
    • LicenseConfiguration(s): AWS License Manager configuration ARN associated with the editions of SQL Server running on instances
    • AutomationAssumeRole: SQLServerLicenseTrackingSolution-Role
  7. For Specify schedule, you can either choose CRON schedule builder to run at your preferred time or No schedule to run the association once. We configured the association to run once.
  8. Choose Create Association. After a State Manager Association has been created, it will trigger the first run of the document.

Test the solution

To ensure the solution has been deployed correctly, perform the following checks.

  1. In the AWS Systems Manager console, under Instances & Nodes, choose State Manager.
  2. Search for SQLServerLicenseTrackingSolution-Association,and then choose the association ID.
  3. Choose Execution history, choose the latest execution ID, and then choose Outputs.

Figure 3 shows a successful execution of the primary document.

On the Outputs section, the result is available for the execution along with the executed steps.

Figure 3: Primary document execution

  1. Under Automation executions, enter the Automation execution ID displayed in the Outputs section, as shown in Figure 4.

The execution ID is displayed in a table along with details like document name (Secondary-SQLLicenseTrackingSolution-Document), status (Success), start time, end time, and executed by.

Figure 4: Automation execution search

  1. Choose the execution ID to see the instances the secondary document has been targeted against, as shown in Figure 5. For more details about individual instances, choose the step ID.

On the details page for the execution ID, there is a list of step ID, step #, step name, actions, status, start time, and end time.

Figure 5: State Manager associations

  1. Under Node Management, choose Inventory. In Top 10 Custom inventory types, you should see SQL Server, as shown in Figure 6.

On the Dashboard tab, there are graphs that show managed instances with Inventory enabled, Inventory coverage per type, and the top 10 custom Inventory types.

Figure 6: Dashboard tab showing managed instances with Inventory enabled

  1. For filter type, use Custom : Custom:SQLServer.Edition : Standard Edition in the search bar. At the bottom of the Inventory dashboard, you should see a list of managed instances. If no EC2 instances are available, change the edition value to one of the other editions.
  2. Choose one of the instance IDs, and then choose the Inventory
  3. Under Inventory type, choose Custom:SQLServer. The SQL Server version, edition, and name is displayed, as shown in Figure 7.

On the Inventory tab, Custom:SQLServer is selected from the Inventory type list. The list includes columns for version, edition, and name.

Figure 7: Custom:SQLServer Inventory type

  1. Open the AWS License Manager console to confirm that the license configuration has been updated by the solution.
  2. In Customer managed licenses, review the configurations to see if they have been updated, as shown in Figure 8.

There are three license configurations displayed in the list, all with a status of Active and all with a license type of vCPU. In addition to these details, there are columns for the license configuration name, licenses consumed, and account ID.

Figure 8: Customer-managed license configurations

  1. Choose any of the license configurations that has more than one license consumed. All the associated instances are displayed under Tracked resources, as shown in Figure 9.

In the Summary section, the status of SQLServerSTDLicenseConfiguration is Active. There are three entries in the Tracked resources list.

Figure 9: Associated resources for the customer managed license configuration

Conclusion

In this post, we showed how you can use AWS License Manager and AWS Systems Manager to automate the process of tracking your Microsoft SQL Server licenses within an account. For more information on tracking SQL Server licenses across mulitple accounts and/or Regions withing an AWS Organization, refer to Centrally track Microsoft SQL Server licenses in AWS Organizations using AWS License Manager and AWS Systems Manager.

About the Authors

Photo of Praveen Bhat

Praveen Bhat is an Enterprise Solutions Architect with several years of experience in the technology industry. Using his passion to bridge the gap between technology and business, he has helped organizations across a variety of industry verticals including banking, insurance, manufacturing, government, and wagering and media to achieve their business outcomes.

Photo of Brett Spedding

Brett Spedding is an AWS Senior Solutions Architect who specializes in Microsoft workloads on AWS. Passionate about working on complex business problems, Brett uses his more than 15 years of enterprise experience to help customers navigate their cloud transformation journey.