In my most recent post, we looked at the steps to deploy the new Data Services Manager 9.1 SQL Server data service. In that post, we created an infrastructure policy to allocate vSphere resources such as CPU, Memory, Networking and Storage to the SQL Server Engine and subsequent databases. We saw how this approach can offer a DBaaS (Database as a Service) to your end-users, allowing consumers to use the DSM portal to provision their own databases on an existing SQL Server instance. In this post, we want to take that a step further and show how Data Services Manager 9.1 can integrate with VCF Automation 9.1, allowing users in different VCF Organizations to provision their own SQL Server databases from an existing SQL Server Instance. This achieves a truly multi-tenanted DBaaS. The post will examine the advanced controls that a VCF Provider Admin has over which organization users are allowed to provision the data services provided by Data Services Manager. We will also look at how resources are managed differently, in so far as infrastructure policies now use vSphere Namespaces to guard-rail resources, as well as how this approach now uses vSphere Kubernetes Services (VKS) to provision the clusters to run the databases.

Let’s begin this blog post in the same way as the previous one by outlining the steps that need to be considered for deploying a SQL Server instance via Data Services Manager 9.1, and then allowing SQL Server to be consumed by VCF organization users for the provisioning of databases.

As a Provider Admin, prepare VCF Automation for Data Services Manager integration (part 1):

1. Connect VCF Automation to Data Services Manager
2. Create a dedicated Data Services Manager Organization for hosting data services.
3. Optionally create Projects for your different tenants/users
4. Create one or more Namespaces in the Projects of the DSM Organization – these vSphere namespaces hold the resources for the databases (VM Classes, Storage Classes, etc)

As a VI Admin, prepare Data Services Manager for VCF Automation integration (part 2):

1. install the Data Services Manager Consumption Operator service on the Supervisor – this extend the Supervisor’s APIs to include Data Services Manager APIs and enable data services
2. Add the Namespace(s) created in part 1 as “vSphere Namespace” infrastructure policies in DSM

As a DSM Admin, deploy the SQL Server instance (part 3):

1. In the Data Services Manager UI, link an image registry for the SQL Server images
2. Enable the SQL Server version
3. Setup Active Directory and a special DSM Privileged Account (optional)
4. Create a SQL Server Instance/Engine, selecting a vSphere Namespace Infrastructure Policy
5. Enable SQL Server Agent (optional)
6. Configure SQL Server (optional)
7. Test connectivity to SQL Server Instance/Engine

As a Provider Admin, define access controls and multi-tenancy for the data services (part 4):

1. Setup a Data Service Policy to determine which organizations have access to SQL Server by selecting namespaces for inclusion in the policy
2. Fine tune Data Service Policy to define Infra Policies and optional Backup controls available to DSM Users when provisioning SQL databases
3. Continue tuning Data Service Policy to determine which user types can connect to the database as  (AD user or SQL user)

As an Organization User, deploy a SQL Server database (part 5):

1. In the VCF Automation Organization UI, create SQL Server Database
2. Chose which SQL Server Instance (as defined in the Data Service Policy)
3. Select user who will be granted db_owner role (AD user or SQL user)
4. Configure backup settings
5. Test connectivity to SQL Server Database

Let’s go through these step by step.

Prepare VCF Automation for DSM integration

The Provider Admin handles this step. First and foremost the Provider Admin must login to the System (Provider Management) organization in VCF Automation, and establish a connection between VCF Automation and Data Services Manager. The Provider Admin will require a DSM Admin user and password to establish the connection. After providing the URL of the Data Services Manager and the DSM Admin login credentials, the remote connection can be tested, certificates accepted and trust established between the two entities. The connection settings are found under VCF Services > Data Services.

Once configured, the connection should appear similar to the following:

Optionally, the VI Admin can also verify this connectivity from the vSphere Client. Select vCenter in the inventory, then Configure > Data Services Manager > Permissions view. Under VCF Automation, the status should show the URL for VCF Automation and a status of Ready:

The next step in setting up VCF Automation for Data Services Manager is to create a dedicated Organization for hosting data services. The official documentation refers to this as the DataServiceProvider Organization. In my example, I simply called it ‘dsm org’. In the default project within this organization, two namespaces, “tenant-1-dsm-ns” and “tenant-2-dsm-ns”, have been created. As you might expect, I want to land databases created by users from tenant organization 1 onto one namespace and databases created by users from tenant-org-2 onto another namespace. However, you have the flexibility to setup your multi-tenancy on a per-project basis, or collapse everything into a single namespace for all your tenants. The choice is yours, as long as the infrastructure policies point to vSphere Namespaces that exist in this dedicated DataServiceProvider Organization.

That now finishes the initial setup steps which are required to integrate DSM & VCF Automation. The Provider Admin now hands the baton over to the VI Admin to complete the initial integration.

Prepare DSM for VCF Automation integration

This step is carried out by the VI Admin. From the vSphere Client, they need to add a Consumption Operator service to the Supervisor. This step extends the Supervisor’s API to include the DSM API, so that it understands what to do with data service requests to create databases, etc, which originate from users in VCF Automation organizations. The consumption operator comes in two parts; a ‘package.yaml’ and a ‘values.yaml’. The package.yaml is uploaded to the Supervisor when the “Add Service” button is selected. This does not need any modification from the downloaded version. Once the Consumption Operator service is added, you then need to configure it. Select the Actions box on the Consumption Operator Service, followed by the “Manage Service” link. Here is where the values.yaml is provided. The values.yaml must be modified to reflect details about your Data Services Manager, including URL, credentials and CA. Here is a snippet taken from one of my pre-GA environments (reason for obfuscating version numbers is to avoid any confusion with GA code).

Assuming the Consumption Operator successfully deployed, it should be visible, configured and trusted in the list of Supervisor Services. If not, re-examine the values.yaml and ensure that the correct URL, credentials and DSM Certificate Authority have been included correctly.

For more details about how to retrieve, install and manage Supervisor Services, please see this link.

Now we come to the second step that requires the VI Admin. This step is to mark the vSphere Namespace(s) in the DataServiceProvider Organization as Infrastructure Policies in Data Services Manager. This is again done via the vSphere Client. The VI Admin must navigate to vCenter, select Configure > Data Services Manager > Infrastructure Policies. Now click on the “Add vSphere Namespace” button and select the DataServiceProvider Organization namespace that you wish to use as Infrastructure policies for DSM. In this example, the “tenant-1-dsm-ns” and “tenant-2-dsm-ns” are both chosen. Note that all vSphere (Supervisor) namespaces are shown, but you must select the ones from the DataServiceProvider Organization only.

After a moment or two the new infrastructure policies should become visible. You can have both DSM-Managed infrastructure policies and vSphere Namespace infrastructure policies co-existing in DSM, as shown here.

That completes the role of the VI Admin in this configuration. We can now hand this over to the DSM Admin for the provisioning of the MS SQL Server Instance.

Deploy the SQL Server instance

Now it is the turn of the DSM Admin. This step is identical to the steps outlined in the previous post when we provisioned the SQL Server instance on a DSM-Managed infrastructure policy. The only difference this time is that the DSM Admin chooses a vSphere Namespace based infrastructure policy for the provisioning, so I won’t repeat all of the steps here. In fact, selecting a vSphere Namespace infrastructure policy is not completely necessary. An MS SQL Server provisioned onto a DSM-Managed infrastructure policy could also be used by VCF Automation organizations for the provisioning of databases. However, the purpose of this post is to show the full VCFA-DSM integration experience, so for that reason, the DSM Admin will provision a new MS SQL Server instance onto one of the vSphere Namespace infrastructure policies defined previously. We will use a cluster topology once again for this SQL Server, resulting in a 3 node cluster using Always On. The DSM Admin now selects the vSphere Namespace infra policy, rather than a DSM Manager one, during this deployment:

Other than choosing a different infrastructure policy, the rest of the SQL Server Instance deployment steps are identical to those seen before. Integration with Active Directory is added, including the privileged user to write both SPNs and do DNS updates. The big difference now of course is that rather than DSM spinning up its own Kubernetes cluster to host the data service, it will now request a VKS (vSphere Kubernetes Service) to be spun up in the DataServiceProvider Organization namespace referenced in the infrastructure policy.

Assuming there are no issues, and the different personas are correctly configured, e.g., the privileged user has Write SPN and Write DNS permissions, the group membership AD user has Kerberos encoding, and the DNS server has the correct Forward and Reverse Lookup Zones configured, the instance should come online and ready.

And if we take a closer look at the vSphere client inventory items, we can see that there is now a VKS cluster provisioned for this instance.

The work of the DSM Admin is now done. The baton can be passed back to the Provider Admin to manage multi-tenancy access to the data services from the different tenant organizations.

Setup multi-tenancy access control across organizations

In the previous post on SQL Server in Data Services Manager 9.1, we saw how access control was managed through permissions, namespaces and data service policies via the DSM UI. VCF Automation has similar controls, but access is managed on a per-organization basis. This is also achieved via a Data Service Policy as we will see. The Data Service Policies in VCF Automation are setup by the Provider Admin in the System organization. Under VCF Services, select Data Services followed by Data Service Policies. As you can see, this environment already has some policies for PostgreSQL and MySQL. However there are no Data Services Policies for SQL Server yet.

Let’s now create a new Data Service Policy for SQL Server, allowing users in tenant-org-1 and tenant-org-2 to provision databases to the newly create SQL Server instance. Click on the New Policy button and begin by providing basic information about the policy, such as name, description and which data service this policy is for – in this case, SQL Server Databases.

We now come to the assignment section. This is where the Provider Admin decides which organizations are allowed to use the data services defined in the policy. In this example, users from two different organizations will be allowed access, tenant-org-1 and tenant-org-2.

We now come to the resources section. This is where we choose which SQL Server instances are included in the policy, and also which backups locations are allowed to be used. In this case, the SQL Server instance is the one which is using the vSphere Namespace infrastructure policy, called tenant-1-dsm-ns. The backup location is an S3 bucket which has been previously defined in DSM as a backup endpoint for databases. A Data Service Policy can control which backup storage an organization should use for their backups, if there are multiple locations available for selection.

 

Because this is a clustered SQL Server, backups must be configured. If this was a single node deployment, the Provider Admin can have more controls over the backup settings, making them required, optional or disabling them for the organization users. But when the SQL Server is clustered, backups are required.

Now we come to the user types. Since the SQL Server instance has been integrated with Active Directory, the Provider Admin can choose to allow Windows Principal and/or SQL User access to the database. If SQL User is selected, when an organization user requests a new database, DSM will automatically create that SQL User on the database and grant it db_owner privileges. Here, the Provider Admin has decided to support both types of user.

Complete the setup, and verify that the Data Service Policy is reflecting what you planned, e.g., correct number of organizations, zero compliance issues.

Now our organization user(s) can login to their VCFA organization and try to deploy a SQL Server database.

Deploy a SQL Server database

Organization users can have different personas assigned to them. For example, Project Admins and Project Advanced Users will be able to navigate to “Build and Deploy” and select Data Services from the available list of services to provision a database. Project Users will not have this level of access, and will only be able to provision a database if someone with higher permissions creates a blueprint to deploy a database and publishes it to the organizations catalog. Something to keep in mind when you wish to offer a true DBaaS experience to your end users. In this example, organization user ‘george’ is an advanced user so he has the ability to provision his own databases directly. To begin, ‘george’ logs into the VCF Automation organization to which he has been granted access as an organization user. This access control is handled by the Organization Admin, and is beyond the scope of this blog. Suffice to say that ‘george’ is a member of this tenant-org-1.

As a Project Advanced User, ‘george’ may have access to a number of tenant namespaces to work from. This depends on how many namespaces are in their project(s). If they only have access to one namespace, then that is automatically chosen when ‘george’ clicks on the Build & Deploy view. However, it is important to keep in mind that when ‘george’ creates a database, he is not consuming any resources in his organizations namespace. Instead, the resources are being consumed in the DataServiceProvider Organization (dsm org) namespace which is defined in the infrastructure policy. So while the database will be visible to ‘george’ in his organization, he will not be able to see the VMs or the VKS cluster in his organization since these are instantiated in a completely different organization, project and namespace. This is the crux of the reasoning behind the DataServiceProvider Organization, keeping all data services in one place for the IT team to manage.

So, as user ‘george’, select Build & Deploy, and which namespace you wish to make the database request from.

Click on the Create Database button. Select the SQL Server instance that ‘george’, as an organization user, has been granted access to via the Data Service Policy. Then, give the database a name.

Decide who the database owner is going to be. Since ‘george’ is also an AD user, he can decide to give his AD user account (qualified with the domain) access to the SQL Server database that he is creating. So as you can see, users can be given self-service to provision their own databases through VCF Automation integrated with DSM.

Next up is the Data Availability and Protection section. You may remember from the Data Service Policy that backups are required on clustered SQL Server deployments. Thus, it is not possible to change this at the database level. However, you can customise the backup schedule should you wish to do so. The transaction log backup frequency can also be tuned at this point, but keep in mind that changing this could directly impact your Point In Time (PIT) restores of the backups, as well as the size that the transaction log file could grow to. The default value of 5 minutes seems a good fit in our opinion.

Review the settings and create the database. All going well, the database should enter a ready state after a moment or two.

Now all that is left to do is to make sure that our organization user ‘george’, who is also an AD user ‘arkham\george’ can successfully connect to the database. The first step is to copy the connection string from the basic information view above.

Next, from his desktop, ‘george’ can launch the SQL Server Management Studio and paste in the connection string. Note that if the certificate is signed by an unknown user, the ‘Security=TrustServerCertificate’ might also be required in the connection string.

If everything is working as expected, AD user ‘arkham\george’ should be able to connect to the database successfully. And it is important to note that ‘george’ can only see his newly created database. He cannot see any of the system databases, nor can he see the databases belonging to any other users of the SQL Server instance.

That now complete the installation and configuration of SQL Server as a DBaaS on Data Services Manager integrated with VCF Automation version 9.1.

Summary

Thank you for reading to the end. As you can see, this is a very powerful solution for customers who wish to offer data services to their users, all the while maintaining multi-tenancy controls through VCF Automation. We’ve seen how the VI Admin and Provider Admin (we can assume these being part of the IT team) have full control over the services and resources. They have the ability to choose which of their tenants/organizations have access to which data services and which underlying resources are used for provisioning these services. We can also see how an organization user can achieve full self-service, all the while being guard-railed by the Data Service Policy. And while this demo is focused on SQL Server, a similar approach is available for both PostgreSQL and MySQL data services as well.