Download the SQL Server Management Pack

In VCF Operations, select Administration > Integrations > Solutions Catalog. The database management packs are not available by default. You will need to download them from the VCF Solutions Catalog.  You can navigate to the VCF Solutions Catalog directly from VCF operations, or you can get to it from https://vcf.broadcom.com/vsc/services. Browse the Management Packs. Here you will find the Management Pack for SQL Sever. You will need to sign in to download it. You will also have to create a “Signing Key” and then provide this when you do the install on the Management Pack into VCF Operations. The latest version of the SQL Server Management Pack at the time of writing is 9.0.0.0100.

Install SQL Server Management Pack

In VCF Operations, select Operate / Integrations > Solutions Catalog. You can see the downloaded management packs from there, as shown below. Once the management pack is downloaded into the catalog, install it. You will be prompted for the “Signing Key” created earlier as part of the install.

Create SQL Server Account

Microsoft SQL Server now appears as an available account type after the management pack has been successfully installed in your VCF Operations.

Add a SQL Server account. Provide the necessary information to connect to the SQL Server instance in the account. I am using the mssql-admin credentials that DSM created for the instance.

Validate the Connection as a test:

Soon after adding the account, you should see data getting collected from the SQL Server.

Examine the SQL Server Dashboards

The SQL Server Management Pack includes some very useful dashboards ‘out of the box’. Navigate to dashboards in VCF Operations where you should see MS SQL Server metrics now displayed. This first one details the Query metrics.

You can also drill down into database specific resource consumption:

Note that on the initial install of the management pack, and the initial view of the dashboards, the Microsoft SQL Server dashboard was empty. It did not show any SQL Server instances. I therefore edited the dashboard to see what the inputs were. The inventory tree was correctly set to MS SQL Server Environment but there were no Objects. After adding the SQL Server instances to the Input data Objects as shown here, and saving the changes to the dashboard, everything started to work:

Now the SQL Server instances are visible and can be selected to get more details on the deployment.

This is a really nice dashboard to have because once you have selected the instance and database, you can begin to examine metrics like Longest Wait Types, which are essentially the internal indicators that record why a query thread has paused execution and what specific resource it is waiting for.

One final note – there does appear to be some discrepancy in building relationships between the VMs and the database nodes. If the VM name does not match the SQL Server node name, then the relationships are not built in this version of the management pack 9.0.0.0100.  In the adapter logs, located on the VCF Ops 9.1 appliance in /storage/log/vcops/log/adapters/MicrosoftSQLServerAdapter, the logs will show the following:

Failed to add VirtualMachine resource with hostname "msql-vcfa-instance1-2" as parent of SqlAvailabilityGroup 
resource with name "DSMAG": Failed to create relationship with VM names ("msql-vcfa-instance1-2")

The reason is that the SQL Server hostname msql-vcfa-instance-2 does not match the name of the virtual machine. msql-vcfa-instance1-398432-4z2fl-czs9p in the vCenter inventory. For vanilla SQL Server deployments, you might be able to change the VM name to match. However since Data Services Manager integrated with VCF Automation uses the Supervisor and VKS (vSphere Kubernetes Service) to host the database , this is not possible. This means that you won’t be able to see the VM <-> SQL Server relationship in the dashboards unfortunately. However, it is a minor nit compared to all the other goodness that the management pack brings to you. If a work-around appears in the near future, I will be sure to update this blog post.

For further information on the SQL Server Management Pack, check out some of the official documentation here and here.

The post Using SQL Server Management Pack in VCF Operations with DSM provisioned SQL Server appeared first on CormacHogan.com.

First, you will need some simple scripts to get you started. If you are unfamiliar with blueprints, feel free to download some of my simple examples from my GitHub repo here. The blueprints that I will use in this post are the “create-mysql-snpshot.blp” and “create-mysql-from-snap.blp”.

Next, we should discuss the personas that can be granted to VCF Automation Organization users. We have Project Admins, Project Advanced Users and Project Users. Only the Project Admins can do Blueprint designs. Project Advanced Users and Project Users do not have access to blueprint designs, but both can consume request blueprints from the catalog. These are the users in my organization:

Create MySQL Snapshot

Let’s start by creating the snapshot of an existing MySQL database. I will login to the organization as user ‘cormac’ who is a project admin. Under Build & Deploy, select Content Hub > Blueprint Design. From the Blueprints > “New From” dropdown, we can begin to import the blueprints downloaded from the GitHub repo mentioned earlier.

Next, provide general information about the blueprint. Select the name, optional description, project, whether to share the blueprint with other projects in the organization, upload the downloaded blueprint, and optionally select an icon for the catalog item.

The blueprint should now be visible.

You can now begin to modify the blueprint to match your own environment. Click on the name of the blueprint to enter ‘edit’ mode:

There are two sections to the blueprint code. First, you are setting up your inputs which will be used to prompt the user to provide information about the MySQL database to snapshot. The second is actually the piece of YAML code which matches the object your are trying to create. For us, this is a MySQL snapshot as defined in the DSM API. You can now fine-tune the code, correcting namespace names and other default values. When you are happy with the code, click on the ‘TEST’ button in the lower left hand corner of the screen. This will launch the blueprint but will only check syntax – it does not do an actual test.

If the test is successful, it will report back something similar to the following:

To add the blueprint to the catalog, click on the VERSION button, and add a description, a change log and check the box to publish the blueprint to the catalog. Now, navigate to the Catalog in the left hand navigation menu, and the item should be available.

Now you can request a deployment of the catalog item. A deployment name is needed so the request can be identified in the instances view. The other items relate to the Supervisor namespace where the MySQL database is deployed from, the name of the database and the name of the snapshot that you wish to create:

Click Submit, and the name of the deployment will be displayed as in progress. If you then click on the name of the deployment (demo), and then the History view, you should be able to observer various events occurring as the snapshot is getting created. This is also a good place to check if the snapshot creation fails as it should report which part of the job the snapshot failed at.

Tasks will also be visible in the vSphere client as the snapshot is getting provisioned.

Eventually, you should be able to observe that the request completed successfully and that the blueprint has been able to take a snapshot of an existing MySQL database.

It is also possible to check on the snapshot through the API or by logging into the DSM appliance as the root user and running the following command (kg is an alias for kubectl which points to the gateway KUBECONFIG):

# kg get mysqlsnapshots -n b03cde-tenant-02-ns-01-5wtsk 
NAME                 CLUSTERNAME    STATUS   CREATED 
mysql-db01-snap-01   mysql-db01     Ready    24m

Clone MySQL Snapshot to new database

We can now start to look at the second part of the post, which is how to clone a snapshot to create a new MySQL database. Once more, we can begin by importing an existing blueprint to do the clone operation. This can once again be retrieved from my GitHub repo, and the process is identical to before.

Fine tune any code, and the default values to match your own environment. Again, items such as namespaces will need to be changed to reflect your setup. the only inputs this time are the name of the new database and the name of the snapshot. Many of the other parameters simply relate to the information that one would need to provide when creating a MySQL database. But you can tune the code for all of these entries. Use the TEST button once again to make sure that the fields are populating as expected.

Use the VERSION button to upload a copy of the blueprint to the catalog when you are happy with the code and the tests are successful.

Verify the item appears in the catalog alongside our snapshot entry:

Once added to the catalog, you can make a request to clone a new MySQL database from an existing snapshot.

Again, you can monitor the clone request in the same way as you monitored the snapshot request. In fact, this can also be monitored from the DSM UI if you wish.

And if everything has gone smoothly, you can click on the deployment (clone-demo) to get further details, similar to what we saw with the snapshot. The new database should also be Ready under the Databases view in the tenant’s namespace, as shown below. The original database which the snapshot was taken from is also visible in this view.

Summary

In Data Services Manager version 9.1, MySQL databases can now leverage the the new snapshot capability in vSAN ESA. Although not available for selection direction in the VCF Automation UI, the blueprint feature allows project admin users to create catalog items which can then be requested by other project users.

Now, you might be thinking that this is all well and good, but it needs vSAN. You would be correct. But have you considered using your existing certified ESX hosts as vSAN ESA nodes by adding NVMe devices? Were you aware that you could do this? If so, and you were planning to do so, we would like to hear from you. Even if you’re not, we would still like to hear from you. You will find a short survey link here.

The post Using VCF Automation 9.1 Blueprints to snapshot and clone DSM provisioned MySQL databases appeared first on CormacHogan.com.

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.

The post Configuring SQL Server with VCF Automation & Data Services Manager versions 9.1 appeared first on CormacHogan.com.

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

1. In the DSM 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
5. Enable SQL Server Agent (optional)
6. Configure SQL Server (optional)
7. Test connectivity to SQL Server Instance/Engine

As a DSM Admin, setup access controls (part 2):

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

As a DSM User, deploy a SQL Server database (part 3):

1. In the Data Services Manager UI, create SQL Server Database
2. Chose the SQL Server Instance (as per 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

This is a lot to get through, and there are a number of different personas to discuss as well, especially if you want a full automated Active Directory integration experience. So let’s begin:

Personas

Here is a screenshot taken from a deck that I use when speaking to customers about SQL Server / Active Directory personas and Data Services Manager. The one that most of you may be unfamiliar with is the Privileged User. In a nutshell, if this user is configured, it has the ability to generate the Kerberos keytab file required for SQL Server AD authentication, i.e., it has the privileges to write Service Principal Names. This means that integrated authentication using AD username can be used to access SQL Server. Without this privileged user, enabling AD integration on every SQL Server Instance is a very manual process. The manual approach is still available however, which is why the privileged user is marked as optional. The other personas are explained in the accompanying text.

As we go through the deployment, I will highlight the role that each of these personas play in the setup. In my example, I already have a number of running databases, including a SQL Server Instance and Database. However, we will go through the process of creating a new SQL Server Instance and Database in this post.

Create the SQL Server Instance/Engine

Step 1: Setup image registry

As highlighted in the introduction, there are a number of steps involved in deploying the SQL Service Engine. Since these are container images provided by Microsoft, and are not open source, we cannot embed these images in Data Services Manager. Instead, the images must be retrieved from the Microsoft Container Registry (mcr). Fortunately, Data Services Manager makes this very easy. Under Version & Upgrade, select Image Registries and Add one for the SQL Server data services that points to the “mcr.microsoft.com” endpoint and the “mssql” repository, as shown below.

Step 2: Enable SQL Server version

Once the image registry is configured, the image(s) will begin to sync to Data Services Manager. Once synchronised, the image(s) need to be enabled for use. To do this , remain in the Version & Upgrade section, and select SQL Server. Choose the version, which for this initial 9.1 release is 2022 CU22 (CU: Cumulative Update). Click Enable and the status should change to Enabled, as shown below. We can now provision SQL Server instances using this image.

Step 3: Configure Active Directory Domain

Let’s integrate SQL Server with Active Directory. This allows us to use integrated authentication with Active Directory, allowing AD users to access the databases. Select the options to automatically write the Service Principal Names, and automatically update DNS. Setup an Active Directory account with the necessary permissions to do these tasks (write DNS, write SPN) – in this example, the AD user is called sqlprivadmin. When configuring Active Directory Domain, provide the Domain, the DNS server to write the FQDN of the SQL Servers, enable DSM Privileged Account, and set the privileged account username and password. You will find the option to setup an Active Directory Domain location under Databases > SQL Server, and under the Active Directory Domains tab. Then select the Create Active Directory Domain tab. Here is an example of my configuration for reference. The DNS Servers are taken from Data Services Manager, but as you can see, an alternative DNS server can be chosen if it is different to the ones configured on the DSM appliance.

With Active Directory Domain configured, proceed to the creating of the SQL Server instances.

Step 4: Create SQL Server Instance

In the initial ‘Basic Information’ screen during the creation of a SQL Service Instance, information such as version, instance name, edition as well as administrator username and password are requested. As there is only a single 2022 CU22 version in this initial 9.1 release, that is the only version that can be selected. Proceed to provide a name for the instance along with the edition of SQL Server. Note that some of the editions do not require a license, but are limited in the functionality that they provide, e.g. Developer edition, Express edition. However other editions do require a license from Microsoft. There is also an option in the editions field to provide a license key, and I am reliably informed that this will result in the associated SQL Server edition getting selected. The other section in the basic section is integration with Active Directory, where you select the Domain created in the previous step. If AD integration is selected, you are then prompted to optionally select the privileged user for writing SPNs and writing DNS. You will also need to provide the Group Membership AD user which we mentioned in the personas section above. Note that the Group membership AD user does not have the domain qualification added – simply add the username and password.

Finally, add the FQDN of the instance and whether you wish to use your own custom certificates for communication with the database or if you wish to use DSM-Managed certificates (the default). This then completes the basic information section.

The next section covers the deployment topology. For SQL Server instances, Data Services Manager can provision single node or clustered deployments. The three node SQL Server cluster uses an Always On availability group with two synchronous replicas and one configuration-only replica. In this example, a highly available instance is selected. Setting up SQL Server clustering is just a single click.

This now brings us to the Infrastructure section. This is where the Infrastructure Policy and its associated resources are selected. We have chosen a DSM-Managed infrastructure policy which references vSphere resources directly. The other option is to use an infrastructure policy which uses resources defined in a vSphere (Supervisor) Namespace. The vSphere Namespace option will be examined in more detail when VCF Automation integration is discussed in a future post. Note that for SQL Server, the pre-defined VM Class size of small is unsupported and is not available for selection.

Step 5: Additional SQL Server Settings

We now get to the point where you can configure the SQL Server to meet many different requirements. For example, you can enable SQL Server Agent for running TSQL-based jobs on the database. These jobs can range from index optimisations to the running of database integrity checks. The SQL Server Agent and associated jobs are a feature commonly used by SQL Server DBAs. There are also a host of configuration options that can be set on the database. In the example below, I have a single configuration setting called errorLog.numErrorLogs set to a value of 25. Many other configuration options are available. Another option that can be enabled in Auditing. I have set the audit feature to log failed logins only, and to keep this information available for 30 days.

Review the Summary and then click the create button to commence the creation of the SQL Server instance. After a few minutes, the SQL Server Instance should now appear Online. Click into the instance to examine the settings. This will allow the DSM Admin to retrieve the connection string for the SQL Server instance. The instance can also be modified from here if necessary.

Connect to the SQL Server Instance/Engine

With the connection string retrieved, standard Microsoft tools such as SQL Server Management Studio can now be used to connect to the instance. The connection string includes the admin credentials, server FQDN and the database to connect to (master). The connection string can be passed directly into the connection parameters section as shown below:

All going well, this will open a connection to the instance, and all of the system databases should now be visible:

Success! The SQL Server engine has been deployed and we can connect to it using the mssql_admin credentials put in place by DSM. We can also se that the SQL Server Agent is present, and if we examined the database in more detail, we would find our configuration options and auditing settings also in place. Now we need to verify that we can use this instance to offer a true DBaaS (Database as a Service) to our end users. By that I mean, can our DSM Users now provision databases on this instance for their own purposes? Let’s find out.

Use Permissions and Namespaces to give users access

We are now in the DSM Admin (part 2) section highlighted in the introduction. The DSM Admin must give DSM Users access to the SQL Server instance so that they can provision their SQL Server databases on the instance. I will skip the step of creating namespaces and permissions, but will make the assumption that some users have already been added to my new “internal-ns” namespace.  With that in mind, I can proceed to the creation of the Data Service Policy which forms the relationship between the namespace and the data service and thus, by extension, the relationship between the users in the namespace and the data service.

With the namespace chosen, you can now proceed to select the SQL Servers that these users are allows to provision databases onto. You can also select which backup locations they are allowed to use. A note here about backup locations – the Object Store hosting the S3 bucket for SQL Server backups must be provisioned with a valid certificate. A self-signed certificate won’t work here. There is also a requirement to have backups configured for databases deployed on clustered SQL Server (AlwaysOn) instances, FYI. Here is an example from my setup:

Complete the configuration by selecting the “Allowed User Types” for the databases that these users are allowed to provision on the instance. You can choose between Windows Principal and/or SQL User. In this example, because I have AD integration, I am allowing both.

Complete the setup of the Data Service Policy. We are now ready to check if our DSM User can successfully provision a SQL Server Database on this SQL Server Instance.

Provision a SQL Server Database

Log out of the Data Services Manager portal as the DSM Admin and log back in as a DSM User. Navigate to Databases > SQL Server. From the available tabs, select Databases, then click on Create Database. From here, the selectable options are completely controlled by the Data Service Policies. The user can select the namespace from the drop-down list if they are a member of multiple namespaces. Next, they select which SQL Server instance they wish to provision the database on. Again, they may have been granted access to multiple instances by the Data Service Policy. Finally, they give the database a name:

The next step is to define the db_owner. Again, since we granted the user to have either an AD User (Windows Principal) or a SQL User, both are offered as options. If the Data Service Policy stated that it was one user type or the other, then that is what would appear here. Since we have fully integrated with Active Directory and have a privileged user for Write SPNs, we can set an AD user as the db_owner. Note that this user requires the domain name to be included. One other important point – this user must also support Kerberos AES 128 bit encryption and Kerberos AES 256 bit encryption. This is a property of the User account configured in Active Directory.

To complete the database deployment, you can fine-tune the backup requirements, assuming you have control to do so from the Data Service Policy. Review the summary and create the database. Soon the database should become ready and accessible.

Connect to the SQL Server Database

When the database is Ready, the status should be reflected in the SQL Server Database view.

Click on the Database to get more details, including the Connection String.

Now, logon to the desktop of the user who wishes to use their AD account to access the SQL Server database, in this case “arkham\george”. Launch the SQL Server Management Studio once more (in this case it is version 2022). Paste the connection string into the appropriate field and connect:

Note that you may need to add “TrustServerCertificate=True;” if you get a certificate error when trying to connect. However, assuming everything is working, the user “arkham\george” should be able to connect to the database that he just provisioned via Data Services Manager:

Success! The AD user “arkham\george” has been able to successfully access their database, and note that they can only see their own database. They do not have access to any of the system databases. This implies that all of our configuration, from the privileged user to Write SPN and Write DNS, to the Group Membership AD user and eventually our db_owner AD user is working as expected. Data Services Manager can now offer a true DBaaS to our SQL Server customers.

Summary

Data Services Manager now offers a new data service, Microsoft SQL Server. In the above how-to, we saw how to provision SQL Server Instances and automatically integrate with Active Directory and DNS for a seamless deployment experience. This data service also offers a true Database as a Service (DBaaS) to customers, allowing DSM Admins controls over resources and engines, while still allowing consumers to provision their own SQL Server databases onto existing Instances. Of course, you could also give the full SQL Server Instance to an end-user if you wish – there is nothing to stop you from taking that approach if that is what you wish to offer your users. Or perhaps it is a combination of both – some users get the full instance, and other users get one or more databases. Data Services Manager can offer both. This guide has focused on how to achieve this with standalone Data Services Manager consuming vSphere resources. In a future post, we will look at how this data services in integrated into VCF Automation version 9.1 for a true multi-tenanted experience. Stay tuned for that, and thank you for reading to this point.

The post Configuring Microsoft SQL Server in Data Services Manager version 9.1 appeared first on CormacHogan.com.

Introducing a new data service – Microsoft SQL Server

With Data Services Manager version 9.1, we can now offer Microsoft SQL Server as a service, alongside our existing PostgreSQL & MySQL services. Without going into too much detail, the following functionality is now available with this new service:

• Fleet Management for SQL Server Instances/Engines and SQL Server Databases
• AlwaysOn Availability Groups (AAG) for high availability
• Configurable Custom Certificates for secure communication
• Automated Backup Schedules with Point In Time (PIT) Restores
• Configurable Maintenance Windows for staged (out of hours) updates
• Automated Active Directory integration for DSM provisioned SQL Server Instances
• Automated DNS updates with FDQN of DSM provisioned SQL Server Instances
• Windows Authentication and SQL Server Authentication supported
• Support for Advanced Configuration Settings, Collation & Auditing
• SQL Server Agent support with job scheduling
• Easy Clone and Restore of databases using UI and/or API with YAML manifests
• Easy Connect to the Database with Connection String
• Configurable metrics forwarding to VCF Operations and Prometheus
• Configurable log shipping to VCF Operations for Logs and other endpoints
• Fully integrated with VCF Automation providing multi-tenancy controls

And, in case you missed it, this data service runs within containers SQL Server on Linux to provide a standardised database-as-a-service environment. It has no dependency on an underlying Windows OS or associated Windows OS licensing which will be a significant saving to many of our customers. A special word of thanks must go to our design partners who helped us to get this service enterprise-ready.

Improvements to the existing PostgreSQL data service

Data Services Manager version 9.1 introduces a new availability enhancement for PostgreSQL databases. The 9.1 version now supports restoring PostgreSQL clusters from a backup that was generated by a different Data Services Manager instance. In other words, Data Services Manager can now do cross-instance S3 restores, which is extremely useful should a Disaster Recovery (DR) type event scenario occur in your organization.

Improvements to the existing MySQL data service

Most of you will be familiar with vSAN, and more recently, the greatly improved vSAN Express Storage Architecture (ESA). One of the relatively recent enhancements to vSAN ESA is the introduction of enhanced linked clones and snapshots technologies. This creates storage-efficient thin volume copies on vSAN ESA datastores. In Data Services Manager version 9.1, MySQL databases now support snapshot creation on vSAN ESA, and can then use fast database cloning functionality with zero downtime to create copies of the existing database.

This snapshot functionality requires that the MySQL databases use a Supervisor Namespace Infra Policy. The snapshot controls are not yet plumbed up into the data services section of the VCF Automation UI for end-users to consume directly. However it is possible to leverage the API to create snapshots, and clone databases from the resulting snapshots. Since these functions are available in the API, they can be built into VCF Automation Blueprints and hosted in a catalog. This allows your users to leverage the functionality with a few clicks. Here is an example of a snapshot taken of a running MySQL database:

And from this snapshot, a linked clone can be made to create a brand new database. Note the new Linked Clone type shown in the Data Services Manager portal for such a deployment:

This is a nice VCF 9.1 addition for both vSAN ESA and Data Services Manager customers.

VCF Automation integration

DSM 9.1 brings a lot of “fit & finish” to the VCF Automation integration. All three data services – PostgreSQL, MySQL and SQL Server – are now plumbed up for selection in the Org portal for tenants who have been granted permission to provision databases. Data Service Policies have also been improved so that Provider Administrators can now have more granular control over how organisations provision databases. Again, this is an area I will visit in greater detail in future blog posts, especially the integration with SQL Server, but for now I just wanted to highlight that all data services are available in VCF Automation, as shown below.

Onboarding existing databases into DSM

This is a feature that many customers have been asking for some time. The Data Services Manager 9.1 release includes documented, supported workflows for onboarding existing unmanaged databases into Data Services Manager. This is great news for customers who are already suffering from data sprawl, but also customers who want to have a more holistic way of managing their databases. Now they can just bring them into Data Services Manager and manage them as part of their overall fleet of databases.

Summary

All in all, this is a great release for Data Services Manager customers. The long awaited SQL Server data services is now generally available, and can be consumed through both the DSM appliance portal or consumed via VCF Automation. And alongside SQL Server integration with VCF Automation, there is also full integration for both PostgreSQL and MySQL. Data Services Policies in VCF Automation have also been greatly extended, giving the Provider Administrators much more control over the data services offered to their tenants.

The post Announcing Data Services Manager 9.1 appeared first on CormacHogan.com.

The post VMware TAM Services – Data Services Manager 9.0.2 Update (Video) appeared first on CormacHogan.com.

Prepping DSM for Aria Automation integration

Let’s say, for example, I want my user dragomir to deploy certain versions of Postgres databases to the engineering-ns namespace, my user christos deploy certain versions of Postgres databases in the sales-ns namespace and my user paudie to deploy certain versions of Postgres databases to the marketing-ns. I want to achieve this via requests to provision a DBaaS catalog item in my Aria Automation Service Broker. The steps to implement this in DSM by a DSM administrator would be:

1. Assuming that these are Active Directory users, enable Directory Services in DSM & import the different AD groups (engineering, marketing, sales) containing the users into DSM as Directory Service Groups under Permissions. Alternatively, you could use local users in DSM if you wish.
2. Create different namespaces for the different groups of users (engineering, marketing, sales) and, if using Active Directory, add the appropriate Directory Service Group to the namespace.
3. Create Data Service Policies which associates the different namespaces with the permissions to provision data services. Choose the data service, which versions of service the users/groups are allowed to provision, select the appropriate infrastructure policies, select appropriate backup locations, etc.

For the purposes of this post, I have the following AD users configured in DSM:

Checking Active Directory – Users and Computers for dragomir, I can see here is in the correct AD group, DSMUsers-Engineering:

By checking the DSM Portal as a DSM Admin, I can also verify that the DSMUsers-Engineering AD Group has been added to the engineering-ns namespace in DSM:

Lastly, we can check the Data Service Policy that engineering-ns has been added to. This makes the relationship between the users/namespace and the data services and versions that they are allowed to provision. Here is a snippet of the DSP, showing that we are granting the members of the engineering-ns the ability to provision versions 16 & 17 of Postgres.

The relationship between Data Service Policy and Namespace can be shown as the following:

At this point the DSM side of things has been setup. Later on, we will see how to add these users to Aria Automation,  give them Service Broker user roles, and assign them as members of a Projects. These steps will allow them to provision databases to their own DSM namespaces through Aria Automation.

Aria Automation config.yaml changes

The config.yaml, which contains the configuration setting to integrate DSM with Aria Automation, now contains additional fields to map DSM Namespaces to Aria Automation Projects in version 9.0.2. For my particular setup, I have 3 users in different DSM namespaces that I want to be able to provision databases from Aria Automation. So I will have to create 3 different Projects in Aria Automation to achieve this:

Here is an example of the config.yaml which has the new project_to_namespace entries defined. Note that the DSM plugin for Aria Automation will only create the SVC_DSM_PROJECT referenced in the config.yaml manifest. It does not create the additional projects listed in the project_to_namespace in Aria Automation. These will need to be done manually by an Aria Automation admin after the plugin installer has been run.

---
dsm_hostname: 192.168.0.1
dsm_user_id: 'dsmadmin@ams.local'
dsm_password: 'VMware123!'
aria_base_url: https://vra.rainpole.com
orchestrator_base_url: https://vra.rainpole.com
aria_username: 'cormac'
aria_password: 'VMware123!'
org_id: 08969e76-5e6c-4581-9cf6-19c06384a9b2
blue_print_name: SVC DSM DBaaS
abx_action_name: SVC-DSM-DB-crud
cr_name: SVC_DSMDB
cr_type_name: Custom.DSMDB
env_name: SVC_DSM_ENV
project_name: SVC_DSM_PROJECT
default_namespace: marketing-ns
project_to_namespace:
  SVC_DSM_PROJECT: default
  SALES_DSM_PROJECT: sales-ns
  ENG_DSM_PROJECT: engineering-ns
  MK_DSM_PROJECT: marketing-ns
skip_certificate_check: 'False'
dsm_root_ca: |
-----BEGIN CERTIFICATE-----
MIIDcDCCAligAwIBAgIGAZrZgFtWMA0GCSqGSIb3DQEBCwUAMFkxCzAJBgNVBAYT
AlVTMQwwCgYDVQQKDANEU00xFTATBgNVBAsMDERTTSBQcm92aWRlcjElMCMGCSqG
SIb3DQEJARYWZHNtX3N1cHBvcnRAdm13YXJlLmNvbTAgFw0yNTExMjgxMDQwNTla
<--snip-->
GO10PoLXP5MZObWGarfkC/+IgY8=
-----END CERTIFICATE-----

After the deployment (running the command “python3 aria.py enable-blueprint-version-2“), the Aria Automation administrator will have to do the following:

1. Add users to Aria Automation, noting which namespace they have access to in DSM.
2. Assign these users the service role of Service Broker User
3. Create projects to match the project_to_namespace
4. Assign these users as member of their appropriate projects
5. Create new content sharing policies which grants the projects access to the DSM Content Source

1. Add users to Aria Automation with Service Broker User Role

In Aria Automation Identity and Access Management, assign the users the role of Service Broker User. This way, they will be able to trigger requests from the Service Broker DBaaS catalog item to provision databases from their own Project. If they have the role of Service Broker Admin, they would be able to see all projects, and would be able to request databases on namespaces which we do not want.  A Service Broker User role means that they only have access to their own project.

Note that there is no automatic correlation here between the DSM users and the Aria Automation users, other than the fact that, in this post, they are the same user in Active Directory (and both DSM and Aria automation support integration with AD). It is up to the Aria and DSM administrators to coordinate the correlation of the user in DSM and the user in Aria Automation to ensure that these users have access to the correct DSM resources.

2. Create Projects as per project_to_namespace in config.yaml

With the users configured, we can now login into Aria Automation as admin user, navigate to the Assembler and build the necessary projects that map to the config.yaml entries in project_to_namespace. With each project, assign one of the Service Broker users to the appropriate project:

It should look something like this. Of course, this is a simple example. If you have multiple users in a DSM namespace, these additional users would be added to the same project. To keep things simple, I am using a single user for each.

3. Create Content Sharing Policy Definitions

The final step for the administrator is to create new content sharing policies. These grant the projects access to the DSM Content Source so that they can ask DSM to provision databases. Navigate to the Service Broker, and under the Content & Policies tab, select Definitions (under Policies). In this example, I have created 3 policies, one per project. It should also be possible to create a single share and assign it to all projects.

And that completes the setup in Aria Automation. We can now go ahead and check if it possible to deploy a Postgres database as user dragomir from Aria Automation, and verify that it lands in the engineering-ns namespace in DSM.

Deploy a database via the Service Broker catalog

Begin by logging is to Aria Automation as the user ‘dragomir‘. As per the assigned role of Service Broker user, dragomir can only see the Service Broker service.

Clicking on the Service Broker, and then navigating to the Catalog, dragomir should only see a single item in the catalog, and that item should show that it is associated with only a single project, the ENG_DSM_PROJECT. So far, this looks like it is achieving our goal and ‘guard-railing’ the user dragomir into using the correct vSphere resources for data services, as defined by the Data Service Policy that is associated with the namespace where user dragomir resides on DSM.

Let’s validate this by making a request to provision a database. The only project available for selection in ENG_DSM_PROJECT, which is correct. Also, the list of available database versions matches those in the Data Service Policy to which the engineering-ns has been assigned in DSM. So far, so good. Continue with the deployment of the database.

Assuming that the deployment of the database is successful, dragomir can drill into the details of the database, and check which namespace was used for this deployment. And it does indeed look like that the deployment has been placed in the correct engineering-ns namespace as defined in the project_to_namespace directive in the config.yaml.

We can verify the same from the DSM Portal. Login as user dragomir, who we have seen has been added to the engineering-ns and check if the database is present. Since dragomir is a DSM user, a result of adding the DSMUsers-Engineering Directory Service Group in the Permissions section of DSM, he can access the UI.

Success. We have achieved our goal of integrating Aria Automation with the new RBAC features of DSM, and the relevant Data Service Policy controlling which users can provision which data services into which namespaces on DSM.

The post DSM 9.0.2 – Aria Automation plugin changes to support new RBAC features appeared first on CormacHogan.com.

The story so far …

By way of a recap, here is what we have delivered so far for the beta versions of MS SQL Server DSM data service:

• Click here for an overview of the original MS SQL Server tech preview release back in DSM 9.0.0.
• Click here and here for an overview of the MS SQL Server enhancements made in DSM 9.0.1, which include automated Active Directory integration and automatic DNS updates for MS SQL Server instances. This release also introduced an overview of the new RBAC mechanism based on Data Services Policies. These policies control user access to data services and resources.

Deploy MS SQL Server instance and user database

Let’s now take a look at the new SQL Server Agent support. I have already configured an Active Directory Domain for DSM. This allows me to create a privileged AD user account which can write SPNs (servicePrincipalNames). This in turn allows DSM to enable Windows Authentication for MS SQL Server database users. See the DSM 9.0.1 blog post mentioned previously for further details on this cool feature.

Next, we create an MS SQL Server instance or engine. I won’t repeat the steps here as they are very similar to how it is done in DSM version 9.0.1. However, there is a new section to enable SQL Server Agent. It also includes some configuration parameter around Job History when enabled. Later, we will create a job to see this in action.

Wait for the SQL Server instance to come online, verifying that Active Directory integration and automatic DNS updates are working correctly. This creates 4 System Databases on the MS SQL Server instance; master, model, msdb and tempdb. These are only accessible with administrator privileges. The details are available to the DSM admin when they view the summary page of the MS SQL Server instance, including a full connection string.

With the instance successfully deployed, we can create a user database on the instance. I am going to include a Windows Principal so that the user can use Windows Authentication to access the database. the format is ‘domain-name\user-name’.

After a few moment, the database will be online.

Now we can begin to look at the SQL Server Agent configuration and how we can use it to run some jobs.

Verify SQL Server Agent functionality

As part of this post, I am going to use some of the very popular scripts created by Ola Hallengren. The scripts are available here: https://ola.hallengren.com/downloads.html. In particular, I will look at the stored procedures that does database integrity checks. This is a stored procedure which we will install on the user database. First, we must installed another required stored procedure called CommandExecute.sql. We also need to run a script called CommandLog.sql to create a required table within the user database. Now we can run the DatabaseIntegrityCheck.sql stored procedure. Once this is completed, we can run the database integrity check.

After confirming that the stored procedure is working, we can then build a SQL Server Agent job to run the database integrity check, and modify the job to include a daily schedule to run it on a regular basis.

I am first going to login to the SQL Server as an administrator. The Admin login and password can be retrieved from the DSM UI, in the Summary view of the database instance.

Use this to login to the MS SQL Server Management Studio (SSMS) using SQL Server Authentication:

Let’s first check that the SQL Server Agent is running by opening a new query and running the following:

SELECT status_desc FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server Agent%'; 

It appears to be running as expected. We can also check that the configuration values provided via the DSM UI for the SQL Server Agent have been implemented via the following command:

EXEC msdb.dbo.sp_get_sqlagent_properties; 

The max and min values of 1500 and 150 match the values that I provided during the deployment. So far, so good. Let’s now add the stored procedures on the user database for the integrity check.

Create Stored Procedure to do Database Integrity Checks

To add a stored procedure, select the user database (cormacdb01), and under Programmability, click on Stored Procedure > New > Stored Procedure, as shown below.

This will create a stored procedure template:

Replace the content of the stored procedure template with the Ola Hallengren scripts mentioned earlier, and execute them one at a time on the user database. Start with the CommandExecute script to build the CommandExecute procedure.

Next, open execute the CommandLog script to create the CommandLog table.

And finally execute the DatabaseIntegrityCheck script to create the Database Integrity Check procedure.

You can now run a command to verify that the database integrity check procedure is working. You can do this as the database owner, rather than the database admin. Logout from SSMS as the admin. Re-connect to the SSMS using the Windows Credentials of the owner, in this case ‘rainpole\cormac’. This is possible since we have given DSM the ability to automatically enable these logins through a privileged user account (see here) that we referenced when we created the database instance.

This user does not have access to any of the System database. The user only has access to its own user database. Since the SMSS connection always defaults to the ‘master’ database, we will need to change the database for this user to the user database (cormacdb01).

Once connected to the database, execute the following query on the user database, which is essentially running the procedure that we added earlier:

EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y'; 

You should observe the results of the integrity database check appear, reporting any issues with the database integrity:

Create a SQL Server Agent Job to do Database Integrity Checks

We will now create a SQL Server Agent job to run the database integrity checks. We can then add a schedule to it so that it checks the database integrity on a daily basis. Log out of the MS SQL Server Management Studio as the current Windows user and back in as the database admin using SQL Auth. Then run the following query to create the job. In the second EXEC, change the database_name to the name of the user database.

In a nutshell, we are creating a job called indexOptimize, and adding a step to the job to run the database integrity check on the user database. As this is run on the msdb, a system database, it can only be executed by the database admin.

USE msdb; 
    EXEC msdb.dbo.sp_add_job @job_name = indexOptimize; 
    EXEC msdb.dbo.sp_add_jobstep @job_name = IndexOptimize, @step_name = IndexOptimize, @database_name = <YOUR_DB_NAME>, @subsystem = N'TSQL', @command = 'EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = ''USER_DATABASES'', @LogToTable = ''Y'';'; 
    EXEC msdb.dbo.sp_add_jobserver @job_name = IndexOptimize, @server_name = N'(local)';

After running the above command, we should now see the job appear in the SQL Server Agent inventory view on the left hand view of the MS SQL Server Management Studio, as shown below.

Click on the job name (indexOptimize in this example) to see the job properties.

If you select the Steps page on the left hand side of the job properties, you can see a T-SQL type job step.

Click on the Edit underneath the Start Step in the job properties to verify that it is indeed a request to run the integrity check:

The job appears to have been successfully created.

Schedule a SQL Server Agent Job

Let’s now schedule this job to run regularly and report on the database integrity. Cancel the previous ‘Edit’ window to return back to the Job Properties. One of the pages listed in the left hand side is ‘Schedules’. Click on this to create a schedule for the job using the ‘New…’ button. In this case, I created a daily schedule for the integrity check.

With a schedule in place, we can check the job history and its runs using below command. You obviously won’t see any job runs reported until the job has run at least once.

EXEC msdb.dbo.sp_help_jobhistory @mode = 'FULL';

This is a sample of the output that the above command generates, showing multiple scheduled runs of the job:

Summary

And that completes the post demonstrating how SQL Server Agent support is now included in DSM 9.0.2 provisioned MS SQL Server instances. We continue to add features for MS SQL Server in each release, and we hope to make it generally available very soon indeed. If there are any questions around our Data Services Manager offering, or MS SQL Server integration, please reach out or leave a comment on the post.

The post DSM 9.0.2 – New Microsoft SQL Server Enhancements – SQL Server Agent Support appeared first on CormacHogan.com.

Deploy the Velero Client/CLI

Step 1 in the process is to deploy the Velero Client. This involves downloading a zipped up Velero binary to your desktop, and then placing it somewhere in your execution path (full instructions here). I downloaded it to an Ubuntu VM that I have in my environment. Once the Velero Client/CLI has been installed, you can check what the version is by using the command ‘velero version’. Ignore the server error. This is simply because we have not yet installed the server part of Velero onto the VKS cluster. However, there client version is 1.17.0. We should match this with the server version on the VKS cluster.

$ velero version 
Client:
         Version: v1.17.0_vmware.1
         Git commit: 3172d9f99c3d501aad9ddfac8176d783f7692dce-modified
 <error getting server version: unable to retrieve the complete list of server APIs: velero.io/v1: no matches for velero.io/v1, Resource=>

Deploy the Velero Server onto VKS

To being with, ensure that your KUBECONFIG is set up correctly, and that you are pointing to the correct VKS cluster, i.e., the one where you wish to install the Velero server. I usually check by running a kubectl get nodes to verify that I have the correct cluster context.

$ kubectl get nodes 
NAME                                                              STATUS   ROLES           AGE   VERSION 
kubernetes-cluster-dkpp-hkpfp-r6j66                               Ready    control-plane   41m   v1.33.3+vmware.1-fips 
kubernetes-cluster-dkpp-kubernetes-cluster-dkpp-np-pk3w-smkvs46   Ready    <none>          36m   v1.33.3+vmware.1-fips

Create a velero-data-values.yaml file

A yaml file is required to describe the details of the backup storage location (bsl). In this example, I am once again using MinIO to provide me with an S3 compatible bucket but any S3 compatible bucket will suffice. I have create a bucket called “velero-backups”. Since I have TLS enabled, I am using an https URL. This means I need to provider a Certificate Authority in the manifest, as shown below. This provides trust between Velero and the object store provider. I also need to provide credentials to access the object store.

$ cat velero-data-values.yaml
backupStorageLocation:
  bucket: velero-backups
  config:
    region: "minio"
    s3ForcePathStyle: "true"
    s3Url: "https://minio.rainpole.io:9000"
  caCert: |
    -----BEGIN CERTIFICATE-----
    MIIGFzCCA/+gAwIBAgICEBkwDQYJKoZIhvcNAQELBQAwgY0xEDAOBgNVBAMMB01J
    Tk9fQ0ExCzAJBgNVBAYTAklFMQ0wCwYDVQQIDARDb3JrMQ0wCwYDVQQHDARDb3Jr
    MRUwEwYDVQQKDAxWQ0YgRGl2aXNpb24xDTALBgNVBAsMBE9DVE8xKDAmBgkqhkiG
---> snip
    G+aB3AHlbfw45mkMlsk9jXl1sj21UYw/ZHJN
    -----END CERTIFICATE-----
  credential: |
    [default]
    aws_access_key_id=admin
    aws_secret_access_key=password

Use the VCF CLI to add a package repo to the cluster

Our next step is to use the VCF CLI (my binary is called vcf-cli) to create a package repo. The VCF CLI can be downloaded from any Supervisor Namespace Summary page. The repo, once created, will contain our Velero package which we can install once downloaded. The packages are retrieved from the VKS Standard Packages Repository. I mentioned the VCF CLI a few times recently, such as using it to troubleshoot DSM deployed on VKS clusters. It is a very useful tool for VCF administrators as it allow command line interaction with the Sueprvisor and VCF Automation. Before we create the repo, let’s first ensure that we are pulling down the latest packages. This URL for the VKS Release Notes will tell you which path to use for the packages. At the time of writing, VKS 3.5 was the latest release, so the RN reports that the VKS Standard Packages v3.5.0+20251218 repository is available here:

projects.packages.broadcom.com/vsphere/supervisor/vks-standard-packages/3.5.0-20251218/vks-standard-packages:3.5.0-20251218

Always check the Release Notes for the latest path information. We will use this path when we create the repo. Note that I am still in the KUBECONFIG context. Let’s add the repository. I am placing it in the namespace tkg-system.

$  vcf-cli package repository add standard-package-repo \
--url projects.packages.broadcom.com/vsphere/supervisor/packages/2025.10.22/vks-standard-packages:3.5.0-20251022 \
-n tkg-system
1:26:48PM: Updating package repository resource 'standard-package-repo' in namespace 'tkg-system'
1:26:48PM: Waiting for package repository reconciliation for 'standard-package-repo'
1:26:50PM: Fetching
            | apiVersion: vendir.k14s.io/v1alpha1
            | directories:
            | - contents:
            |   - imgpkgBundle:
            |       image: projects.packages.broadcom.com/vsphere/supervisor/packages/2025.10.22/vks-standard-packages@sha256:36b48ba005e884586512c2fda8c4598f426c6f78efa7f84f5b24087b49a6b52d
            |       tag: 3.5.0-20251022
            |     path: .
            |   path: "0"
            | kind: LockConfig
            |
1:26:50PM: Fetch succeeded
1:26:52PM: Template succeeded
1:26:52PM: Deploy started (3s ago)
1:26:56PM: Deploying
            | Target cluster 'https://10.96.0.1:443'
            | Changes
            | Namespace   Name                                                                     Kind             Age  Op      Op st.                      Wait to  Rs  Ri
            | tkg-system  ako.kubernetes.vmware.com                                                PackageMetadata  -    create  fallback on update or noop  -        -   -
            | ^           ako.kubernetes.vmware.com.1.13.4+vmware.1-vks.1                          Package          -    create  fallback on update or noop  -        -   -
            | ^           autoscaler.kubernetes.vmware.com                                         PackageMetadata  9m   delete  -                           -        ok  -
            | ^           cert-manager.kubernetes.vmware.com.1.17.2+vmware.1-vks.1                 Package          9m   delete  -                           -        ok  -
            | ^           cert-manager.kubernetes.vmware.com.1.17.2+vmware.2-vks.1                 Package          -    create  fallback on update or noop  -        -   -
            | ^           cert-manager.kubernetes.vmware.com.1.18.2+vmware.1-vks.1                 Package          9m   delete  -                           -        ok  -
            | ^           cert-manager.kubernetes.vmware.com.1.18.2+vmware.2-vks.2                 Package          -    create  fallback on update or noop  -        -   -
.
.
.

Let’s check if the Velero package is now included in the repository, and if so, what versions are available.

$ vcf-cli package available list -n tkg-system | grep velero
  velero.kubernetes.vmware.com                    velero


$ vcf-cli package available get velero.kubernetes.vmware.com
  NAME:                   velero.kubernetes.vmware.com
  DISPLAY-NAME:           velero
  CATEGORIES:             - data protection
  SHORT-DESCRIPTION:      Velero is an open source tool to safely backup and restore, perform disaster
recovery, and migrate Kubernetes cluster resources and persistent volumes.
  LONG-DESCRIPTION:       Velero is an open source tool to safely backup and restore, perform disaster
recovery, and migrate Kubernetes cluster resources and persistent volumes.
  PROVIDER:               VMware
  MAINTAINERS:            - name: Wenkai Yin
  SUPPORT-DESCRIPTION:    https://github.com/vmware-tanzu/velero
  VERSION                RELEASED-AT
  1.16.1+vmware.1-vks.1  2025-05-19 12:30:00 +0000 UTC
  1.16.2+vmware.1-vks.1  2025-08-05 12:30:00 +0000 UTC
  1.17.0+vmware.1-vks.1  2025-09-16 11:30:00 +0000 UTC

Looks good. Velero version 1.17.0 is an available package version, which will match the client version we are using.

Install the Velero Server Package

Let’s proceed with the install of the Velero server package. Specify the namespace of the repo where the package is stored, the name of the package itself, the version you wish to install and the path to the velero-data-values.yaml created earlier. The actual Velero server components will be installed from the package into a namespace called velero.

$ vcf-cli package install velero --namespace tkg-system \
--package velero.kubernetes.vmware.com \
--version 1.17.0+vmware.1-vks.1 \
--values-file velero-data-values.yaml

1:30:07PM: Pausing reconciliation for package installation 'velero' in namespace 'tkg-system'
1:30:08PM: Updating secret 'velero-tkg-system-values'
1:30:08PM: Resuming reconciliation for package installation 'velero' in namespace 'tkg-system'
1:30:08PM: Waiting for PackageInstall reconciliation for 'velero'
1:30:08PM: Waiting for generation 3 to be observed
1:30:09PM: Fetch started
1:30:09PM: Fetching
            | apiVersion: vendir.k14s.io/v1alpha1
            | directories:
            | - contents:
            |   - imgpkgBundle:
            |       image: projects.packages.broadcom.com/vsphere/supervisor/packages/2025.10.22/vks-standard-packages@sha256:5dd00ce6284efa836cae4abb351ab8987cf118f79d355c84ce2ba0a5ac5fbd29
            |     path: .
            |   path: "0"
            | kind: LockConfig
            |
1:30:09PM: Fetch succeeded
1:30:09PM: Template succeeded
1:30:10PM: Deploy started
1:30:10PM: Deploying
            | Target cluster 'https://10.96.0.1:443' (nodes: kubernetes-cluster-dkpp-hkpfp-r6j66, 1+)
            | Changes
            | Namespace  Name  Kind  Age  Op  Op st.  Wait to  Rs  Ri
            | Op:      0 create, 0 delete, 0 update, 0 noop, 0 exists
            | Wait to: 0 reconcile, 0 delete, 0 noop
            | Succeeded
1:30:10PM: Deploy succeeded

Check the Velero install status

We can now check if the package installed successfully. We can use kubectl to check on various objects in the velero namespace, such as deployments, replicaSets and Pods. We can check that the BackupStorageLocation (bsl) is available and by using the velero version command, we can verify that the server portion is now reporting correctly.

$ kubectl get deploy -n velero
NAME     READY   UP-TO-DATE   AVAILABLE   AGE
velero   1/1     1            1           4m38s


$ kubectl get rs -n velero
NAME                DESIRED   CURRENT   READY   AGE
velero-845bfc6654   1         1         1       4m59s


$ kubectl get pods -n velero
NAME                      READY   STATUS    RESTARTS   AGE
node-agent-f8qdp          1/1     Running   0          5m5s
velero-845bfc6654-69vqs   1/1     Running   0          5m5s


$ kubectl get bsl -n velero
NAME      PHASE       LAST VALIDATED   AGE     DEFAULT
default   Available   49s              5m22s   true


$ velero version
Client:
        Version: v1.17.0_vmware.1
        Git commit: 3172d9f99c3d501aad9ddfac8176d783f7692dce-modified
Server:
        Version: v1.17.0_vmware.1

Create a sample workload to backup and restore

To test out Velero backup and restore, I am going to use a simple Pod and PersistentVolumeClaim (pvc) combination. The PVC will have to use one of the existing StorageClasses (sc) in the VKS cluster. The Pod will also mount the volume onto the /demo folder in the busybox container.

As this is VKS on vSphere, the vSphere CSI driver is a necessary component for creating persistent volumes on vSphere storage. This will also mean that during the backup, Velero will request the creation of a CSI snapshot of the volume to backup the volume.  VKS clusters on vSphere have all of the necessary vSphere CSI components to achieve this.

Here are the manifests and steps to create the simple app. First we create a namespace called cormac-ns, which is where our app will live. Then we get the StorageClasses, and then build the appropriate PVC manifest using one of the storage classes. Once the PVC is created, we can reference it as a Volume in the Pod.

$ kubectl create ns cormac-ns
namespace/cormac-ns created 


$ kubectl get sc
NAME                                                              PROVISIONER              RECLAIMPOLICY   VOLUMEBINDINGMODE      ALLOWVOLUMEEXPANSION   AGE
sfo-w01-cl01-optimal-datastore-default-policy-raid1               csi.vsphere.vmware.com   Delete          Immediate              true                   25h
sfo-w01-cl01-optimal-datastore-default-policy-raid1-latebinding   csi.vsphere.vmware.com   Delete          WaitForFirstConsumer   true                   25h


$ cat example-pvc-cormac-ns.yaml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: example-cormac-block-pvc
  namespace: cormac-ns
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 5Gi
  storageClassName: sfo-w01-cl01-optimal-datastore-default-policy-raid1


$ kubectl apply -f example-pvc-cormac-ns.yaml
persistentvolumeclaim/example-cormac-block-pvc created


$ kubectl get pvc -n cormac-ns -w
NAME                       STATUS    VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS                                          VOLUMEATTRIBUTESCLASS   AGE
example-cormac-block-pvc   Pending                                                                        sfo-w01-cl01-optimal-datastore-default-policy-raid1   <unset>                 4s
example-cormac-block-pvc   Pending   pvc-85cf01bc-52fb-4bc1-a74f-58990b2988bb   0                         sfo-w01-cl01-optimal-datastore-default-policy-raid1   <unset>                 4s
example-cormac-block-pvc   Bound     pvc-85cf01bc-52fb-4bc1-a74f-58990b2988bb   5Gi        RWO            sfo-w01-cl01-optimal-datastore-default-policy-raid1   <unset>                 5s


$ cat cormac-pod-with-volume.yaml
apiVersion: v1
kind: Pod
metadata:
  name: cormac-pod-1
  namespace: cormac-ns
spec:
  securityContext:
    runAsUser: 1000
    runAsGroup: 3000
    fsGroup: 2000
    supplementalGroups: [4000]
  containers:
  - name: busybox
    image: "dockerhub.packages.vcfd.broadcom.net/busybox:latest"
    command: [ "sleep", "1000000" ]
    securityContext:
      allowPrivilegeEscalation: false
      capabilities:
        drop: ["ALL"]
      runAsNonRoot: true
      runAsUser: 1000
      seccompProfile:
        type: "RuntimeDefault"
    volumeMounts:
      - mountPath: "/demo"
        name: demo-vol
  volumes:
    - name: demo-vol
      persistentVolumeClaim:
        claimName: example-cormac-block-pvc


$ kubectl apply -f cormac-pod-with-volume.yaml
pod/cormac-pod-1 created


$ kubectl get pods -n cormac-ns -w
NAME           READY   STATUS              RESTARTS   AGE
cormac-pod-1   0/1     ContainerCreating   0          8s
cormac-pod-1   1/1     Running             0          14s


$ kubectl get pod,pvc -n cormac-ns
NAME               READY   STATUS    RESTARTS   AGE
pod/cormac-pod-1   1/1     Running   0          33s

NAME                                             STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS                                          VOLUMEATTRIBUTESCLASS   AGE
persistentvolumeclaim/example-cormac-block-pvc   Bound    pvc-85cf01bc-52fb-4bc1-a74f-58990b2988bb   5Gi        RWO            sfo-w01-cl01-optimal-datastore-default-policy-raid1   <unset>                 2m15s

We now have an application. If you so wish, you can use kubectl exec to access the Pod, and create files and directories in the /demo directory. You can then check if the data is recovered after a backup and restore. Let’s now see if we can backup and restore this ‘critical’ application.

Backup using Velero CLI

I will now initiate a backup. I do not want to backup the full VKS cluster, only  the namespace cormac-ns as this is where my app is running. After the backup has completed, I can use the suggested ‘velero backup describe‘ command to look at it in more detail.

$ velero backup create cormac-backup-1 --include-namespaces cormac-ns --wait
Backup request "cormac-backup-1" submitted successfully.
Waiting for backup to complete. You may safely press ctrl-c to stop waiting - your backup 
will continue in the background.
......................................................................
Backup completed with status: Completed. You may check for more information using the 
commands `velero backup describe cormac-backup-1` and `velero backup logs cormac-backup-1`.


$ velero backup describe cormac-backup-1
Name:         cormac-backup-1
Namespace:    velero
Labels:       velero.io/storage-location=default
Annotations:  velero.io/resource-timeout=10m0s
              velero.io/source-cluster-k8s-gitversion=v1.33.3+vmware.1-fips
              velero.io/source-cluster-k8s-major-version=1
              velero.io/source-cluster-k8s-minor-version=33
Phase:  Completed
Namespaces:
  Included:  cormac-ns
  Excluded:  <none>
Resources:
  Included cluster-scoped:    <none>
  Excluded cluster-scoped:    volumesnapshotcontents.snapshot.storage.k8s.io
  Included namespace-scoped:  *
  Excluded namespace-scoped:  volumesnapshots.snapshot.storage.k8s.io
Label selector:  <none>
Or label selector:  <none>
Storage Location:  default
Velero-Native Snapshot PVs:    auto
File System Backup (Default):  false
Snapshot Move Data:            true
Data Mover:                    velero
TTL:  720h0m0s
CSISnapshotTimeout:    10m0s
ItemOperationTimeout:  4h0m0s
Hooks:  <none>
Backup Format Version:  1.1.0
Started:    2026-01-14 13:42:45 +0000 UTC
Completed:  2026-01-14 13:43:55 +0000 UTC
Expiration:  2026-02-13 13:42:44 +0000 UTC
Total items to be backed up:  65
Items backed up:              65
Backup Item Operations:  1 of 1 completed successfully, 0 failed (specify --details for more information)
Backup Volumes:
  Velero-Native Snapshots: <none included>
  CSI Snapshots:
    cormac-ns/example-cormac-block-pvc:
      Data Movement: included, specify --details for more information
  Pod Volume Backups: <none included>
HooksAttempted:  0
HooksFailed:     0

Firstly, it looks like the backup has completed successfully. Secondly, it has indeed used CSI Snapshots, and moved the snapshot data to my backup storage location (S3 bucket). Note that this section of the output recommends using a –details options to see more information above the data movement. Let’s run that now (note that I have snipped some of the output for conciseness).

$ velero backup describe cormac-backup-1 --details
Name:         cormac-backup-1
Namespace:    velero
Labels:       velero.io/storage-location=default
Annotations:  velero.io/resource-timeout=10m0s
              velero.io/source-cluster-k8s-gitversion=v1.33.3+vmware.1-fips
              velero.io/source-cluster-k8s-major-version=1
              velero.io/source-cluster-k8s-minor-version=33
Phase:  Completed
Namespaces:
  Included:  cormac-ns
  Excluded:  <none>
Resources:
  Included cluster-scoped:    <none>
  Excluded cluster-scoped:    volumesnapshotcontents.snapshot.storage.k8s.io
  Included namespace-scoped:  *
  Excluded namespace-scoped:  volumesnapshots.snapshot.storage.k8s.io
Label selector:  <none>
Or label selector:  <none>
Storage Location:  default
Velero-Native Snapshot PVs:    auto
File System Backup (Default):  false
Snapshot Move Data:            true
Data Mover:                    velero
TTL:  720h0m0s
CSISnapshotTimeout:    10m0s
ItemOperationTimeout:  4h0m0s
Hooks:  <none>
Backup Format Version:  1.1.0
Started:    2026-01-14 13:42:45 +0000 UTC
Completed:  2026-01-14 13:43:55 +0000 UTC
Expiration:  2026-02-13 13:42:44 +0000 UTC
Total items to be backed up:  65
Items backed up:              65
Backup Item Operations:
  Operation for persistentvolumeclaims cormac-ns/example-cormac-block-pvc:
    Backup Item Action Plugin:  velero.io/csi-pvc-backupper
    Operation ID:               du-f577e717-58bf-453b-8de8-c5f3b866855e.85cf01bc-52fb-4bcd93615
    Items to Update:
                           datauploads.velero.io velero/cormac-backup-1-mswhk
    Phase:                 Completed
    Progress description:  Completed
    Created:               2026-01-14 13:43:01 +0000 UTC
    Started:               2026-01-14 13:43:20 +0000 UTC
    Updated:               2026-01-14 13:43:49 +0000 UTC
Resource List:
  data.packaging.carvel.dev/v1alpha1/Package:
    - cormac-ns/ako.kubernetes.vmware.com.1.13.4+vmware.1-vks.1
    - cormac-ns/cert-manager.kubernetes.vmware.com.1.17.2+vmware.2-vks.1
    - cormac-ns/cert-manager.kubernetes.vmware.com.1.18.2+vmware.2-vks.2
<--snip-->
  data.packaging.carvel.dev/v1alpha1/PackageMetadata:
    - cormac-ns/ako.kubernetes.vmware.com
    - cormac-ns/cert-manager.kubernetes.vmware.com
    - cormac-ns/cluster-autoscaler.kubernetes.vmware.com
<--snip-->
  v1/ConfigMap:
    - cormac-ns/kube-root-ca.crt
  v1/Event:
    - cormac-ns/cormac-pod-1.188a9ca69877b51d
    - cormac-ns/cormac-pod-1.188a9ca70666357e
    - cormac-ns/cormac-pod-1.188a9ca9254c4de3
    - cormac-ns/cormac-pod-1.188a9ca963e97572
    - cormac-ns/cormac-pod-1.188a9ca96cda3faf
    - cormac-ns/cormac-pod-1.188a9ca974af3290
    - cormac-ns/example-cormac-block-pvc.188a9c7c780cab0a
    - cormac-ns/example-cormac-block-pvc.188a9c8eb1bdf906
    - cormac-ns/example-cormac-block-pvc.188a9c8eb1e499a8
    - cormac-ns/example-cormac-block-pvc.188a9c8fc1b87d38
  v1/Namespace:
    - cormac-ns
  v1/PersistentVolume:
    - pvc-85cf01bc-52fb-4bc1-a74f-58990b2988bb
  v1/PersistentVolumeClaim:
    - cormac-ns/example-cormac-block-pvc
  v1/Pod:
    - cormac-ns/cormac-pod-1
  v1/ServiceAccount:
    - cormac-ns/default
Backup Volumes:
  Velero-Native Snapshots: <none included>
  CSI Snapshots:
    cormac-ns/example-cormac-block-pvc:
      Data Movement:
        Operation ID: du-f577e717-58bf-453b-8de8-c5f3b866855e.85cf01bc-52fb-4bcd93615
        Data Mover: velero
        Uploader Type: kopia
        Moved data Size (bytes): 0
        Result: succeeded
  Pod Volume Backups: <none included>
HooksAttempted:  0
HooksFailed:     0

Now we can see even more details about the data movement. We can see that it is using the in-built velero data mover which is using Kopia. And we can in fact see more details about the snapshot and data movement by querying some other custom resources, as we will see next.

Backup: Under the covers

The following commands will examine some of the snapshot details, such as the snapshot location and class. You can add a “-o yaml” to the kubectl get commands, or use kubectl describe, to see more details. But it is the “dataupload” Custom Resource (CR) that is most useful. This is what ties the volume and resulting CSI snapshot tot he data mover and backup storage location, and if there are any issues with that operation, this is the CR to check.

$ kubectl get volumesnapshotlocations -n velero
NAME      AGE
default   22m


$ kubectl get volumesnapshotclasses
NAME                         DRIVER                   DELETIONPOLICY   AGE
volumesnapshotclass-delete   csi.vsphere.vmware.com   Delete           80m


$ kubectl describe datauploads cormac-backup-1-mswhk -n velero
Name:         cormac-backup-1-mswhk
Namespace:    velero
Labels:       velero.io/async-operation-id=du-f577e717-58bf-453b-8de8-c5f3b866855e.85cf01bc-52fb-4bcd93615
              velero.io/backup-name=cormac-backup-1
              velero.io/backup-uid=f577e717-58bf-453b-8de8-c5f3b866855e
              velero.io/pvc-uid=85cf01bc-52fb-4bc1-a74f-58990b2988bb
Annotations:  <none>
API Version:  velero.io/v2alpha1
Kind:         DataUpload
Metadata:
  Creation Timestamp:  2026-01-14T13:43:01Z
  Generate Name:       cormac-backup-1-
  Generation:          5
  Owner References:
    API Version:     velero.io/v1
    Controller:      true
    Kind:            Backup
    Name:            cormac-backup-1
    UID:             f577e717-58bf-453b-8de8-c5f3b866855e
  Resource Version:  12767
  UID:               f4da9b85-db30-4df7-9ebe-f61148c71c7c
Spec:
  Backup Storage Location:  default
  Csi Snapshot:
    Driver:           csi.vsphere.vmware.com
    Snapshot Class:   volumesnapshotclass-delete
    Storage Class:    sfo-w01-cl01-optimal-datastore-default-policy-raid1
    Volume Snapshot:  velero-example-cormac-block-pvc-f968m
  Operation Timeout:  10m0s
  Snapshot Type:      CSI
  Source Namespace:   cormac-ns
  Source PVC:         example-cormac-block-pvc
Status:
  Accepted By Node:      kubernetes-cluster-dkpp-kubernetes-cluster-dkpp-np-pk3w-smkvs46
  Accepted Timestamp:    2026-01-14T13:43:01Z
  Completion Timestamp:  2026-01-14T13:43:49Z
  Node:                  kubernetes-cluster-dkpp-kubernetes-cluster-dkpp-np-pk3w-smkvs46
  Node OS:               linux
  Path:                  /f4da9b85-db30-4df7-9ebe-f61148c71c7c
  Phase:                 Completed
  Progress:
  Snapshot ID:      4ff960c3f4d2f55253926f36a66d5e50
  Start Timestamp:  2026-01-14T13:43:20Z
Events:
  Type    Reason               Age                  From                   Message
  ----    ------               ----                 ----                   -------
  Normal  Data-Path-Started    4m2s                 cormac-backup-1-mswhk  Data path for cormac-backup-1-mswhk started
  Normal  Data-Path-Progress   4m2s (x2 over 4m2s)  cormac-backup-1-mswhk  {}
  Normal  Data-Path-Completed  4m1s                 cormac-backup-1-mswhk  {"snapshotID":"4ff960c3f4d2f55253926f36a66d5e50","emptySnapshot":false,"source":{"byPath":"/f4da9b85-db30-4df7-9ebe-f61148c71c7c","volumeMode":"Filesystem"}}
  Normal  Data-Path-Stopped    4m1s                 cormac-backup-1-mswhk  Data path for cormac-backup-1-mswhk stopped

This looks to have been successful. The final step of this post is to ensure we can do a successful restore of the application that we have just backed up. Let’s now do that.

Restore using Velero CLI

Start by deleting the “cormac-ns” namespace that was just backed up. It also deletes the Pod, PVC and resulting PV.

$ kubectl delete ns cormac-ns
namespace "cormac-ns" deleted


$ kubectl get pv
No resources found

Begin the velero restore, using the –backup command to identify which backup to restore from. Again, we can use the describe command to check the status of the restore.

$ velero restore create cormac-restore-260114 --from-backup cormac-backup-1
Restore request "cormac-restore-260114" submitted successfully.
Run `velero restore describe cormac-restore-260114` or `velero restore logs cormac-restore-260114` 
for more details.


$ velero restore describe cormac-restore-260114
Name:         cormac-restore-260114
Namespace:    velero
Labels:       <none>
Annotations:  <none>
Phase:                       Completed
Total items to be restored:  56
Items restored:              56
Started:    2026-01-14 13:57:37 +0000 UTC
Completed:  2026-01-14 13:58:06 +0000 UTC
Warnings:
  Velero:     <none>
  Cluster:    <none>
  Namespaces:
    cormac-ns:  could not restore, ConfigMap:kube-root-ca.crt already exists. Warning: the in-cluster version is different than the backed-up version
Backup:  cormac-backup-1
Namespaces:
  Included:  all namespaces found in the backup
  Excluded:  <none>
Resources:
  Included:        *
  Excluded:        nodes, events, events.events.k8s.io, backups.velero.io, restores.velero.io, resticrepositories.velero.io, csinodes.storage.k8s.io, volumeattachments.storage.k8s.io, backuprepositories.velero.io
  Cluster-scoped:  auto
Namespace mappings:  <none>
Label selector:  <none>
Or label selector:  <none>
Restore PVs:  auto
CSI Snapshot Restores:
  cormac-ns/example-cormac-block-pvc:
    Data Movement: specify --details for more information
Existing Resource Policy:   <none>
ItemOperationTimeout:       4h0m0s
Preserve Service NodePorts:  auto
Uploader config:
Restore Item Operations:  1 of 1 completed successfully, 0 failed (specify --details for more information)
HooksAttempted:   0
HooksFailed:      0

And just like we saw with the backup, the Data Movement details can be observed if you use the –details options with this command. This will include the following additional details in the output, as well as the list of resources that were restored:

Restore PVs:  auto
CSI Snapshot Restores:
  cormac-ns/example-cormac-block-pvc:
    Data Movement:
      Operation ID: dd-90ca852c-7601-4c70-bd8e-8df9e07c4a31.85cf01bc-52fb-4bcba3ea6
      Data Mover: velero
      Uploader Type: kopia
Existing Resource Policy:   <none>
ItemOperationTimeout:       4h0m0s
Preserve Service NodePorts:  auto
Uploader config:
Restore Item Operations:
  Operation for persistentvolumeclaims cormac-ns/example-cormac-block-pvc:
    Restore Item Action Plugin:  velero.io/csi-pvc-restorer
    Operation ID:                dd-90ca852c-7601-4c70-bd8e-8df9e07c4a31.85cf01bc-52fb-4bcba3ea6
    Phase:                       Completed
    Progress description:        Completed
    Created:                     2026-01-14 13:57:38 +0000 UTC
    Started:                     2026-01-14 13:57:46 +0000 UTC
    Updated:                     2026-01-14 13:58:01 +0000 UTC

The restore appears to have been successful. But lets verify using some kubectl commands.

$ kubectl get ns cormac-ns
NAME        STATUS   AGE
cormac-ns   Active   2m22s


$ kubectl get pod,pvc -n cormac-ns
NAME               READY   STATUS    RESTARTS   AGE
pod/cormac-pod-1   1/1     Running   0          2m32s

NAME                                             STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS                                          VOLUMEATTRIBUTESCLASS   AGE
persistentvolumeclaim/example-cormac-block-pvc   Bound    pvc-cf66b8fc-adc1-43e5-8a81-1fbac9e21e9a   5Gi        RWO            sfo-w01-cl01-optimal-datastore-default-policy-raid1   <unset>                 2m32s


$ kubectl get pv
NAME                                       CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS   CLAIM                                STORAGECLASS                                          VOLUMEATTRIBUTESCLASS   REASON   AGE
pvc-cf66b8fc-adc1-43e5-8a81-1fbac9e21e9a   5Gi        RWO            Delete           Bound    cormac-ns/example-cormac-block-pvc   sfo-w01-cl01-optimal-datastore-default-policy-raid1   <unset>                          2m37s

Success. It would appear as if everything has restored correctly. If there are issues with a restore, there are some useful CRs available once again.

Restore: Under the covers

Just like we saw with the backup, the following CRs, specifically downloadrequests and datadownlaods can be useful to query if there are some issue with a Velero restore operation on a VKS cluster using CSI snapshots.

$ kubectl get downloadrequests -n velero
NAME                                                         AGE
cormac-restore-260114-01fc60e9-d8c6-4efc-81f2-06f3e545272d   4m10s
cormac-restore-260114-05bd4b4e-5ad6-4e52-a261-f4b89fb0bae3   4m10s
cormac-restore-260114-2eba4517-ca71-4152-bb2a-fda141de8ed7   5m48s
cormac-restore-260114-7bbf1ae8-5e65-4a5b-86e7-54334b3ddd0a   4m45s
cormac-restore-260114-b926fbd2-8234-45c9-af1e-cd229d937504   4m10s
cormac-restore-260114-debbab74-3e50-449e-95b1-ccbe4415f8c9   5m48s
cormac-restore-260114-e133ec6f-6c8e-4951-8611-ffce0ee8ba8f   4m45s
cormac-restore-260114-fd6ed61e-d99e-43e1-a3db-4e2c8644242f   4m11s


$ kubectl describe downloadrequests cormac-restore-260114-01fc60e9-d8c6-4efc-81f2-06f3e545272d  -n velero
Name:         cormac-restore-260114-01fc60e9-d8c6-4efc-81f2-06f3e545272d
Namespace:    velero
Labels:       <none>
Annotations:  <none>
API Version:  velero.io/v1
Kind:         DownloadRequest
Metadata:
  Creation Timestamp:  2026-01-14T13:59:30Z
  Generation:          2
  Resource Version:    15170
  UID:                 db0c147b-7b67-4e0d-92f7-6ff12105b992
Spec:
  Target:
    Kind:  RestoreVolumeInfo
    Name:  cormac-restore-260114
Status:
  Download URL:  https://minio.rainpole.io:9000/velero-backups/restores/cormac-restore-260114/cormac-restore-260114-volumeinfo.json.gz?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=admin%2F20260114%2Fminio%2Fs3%2Faws4_request&X-Amz-Date=20260114T135930Z&X-Amz-Expires=600&X-Amz-SignedHeaders=host&x-id=GetObject&X-Amz-Signature=ddba7b633a6362e9ca648c301b7c5bb22fbf774230836c9ebe146f6c63a56ff5
  Expiration:    2026-01-14T14:09:30Z
  Phase:         Processed
Events:          <none>


$ kubectl get datadownloads  -n velero
NAME                          STATUS      STARTED   BYTES DONE   TOTAL BYTES   STORAGE LOCATION   AGE     NODE
cormac-restore-260114-v65f8   Completed   6m18s                                default            6m26s   kubernetes-cluster-dkpp-kubernetes-cluster-dkpp-np-pk3w-smkvs46


$ kubectl describe datadownloads cormac-restore-260114-v65f8  -n velero
Name:         cormac-restore-260114-v65f8
Namespace:    velero
Labels:       velero.io/async-operation-id=dd-90ca852c-7601-4c70-bd8e-8df9e07c4a31.85cf01bc-52fb-4bcba3ea6
              velero.io/restore-name=cormac-restore-260114
              velero.io/restore-uid=90ca852c-7601-4c70-bd8e-8df9e07c4a31
Annotations:  <none>
API Version:  velero.io/v2alpha1
Kind:         DataDownload
Metadata:
  Creation Timestamp:  2026-01-14T13:57:38Z
  Generate Name:       cormac-restore-260114-
  Generation:          5
  Owner References:
    API Version:     velero.io/v1
    Controller:      true
    Kind:            Restore
    Name:            cormac-restore-260114
    UID:             90ca852c-7601-4c70-bd8e-8df9e07c4a31
  Resource Version:  14953
  UID:               1c8f7949-b20e-4878-96b8-46a7859423cf
Spec:
  Backup Storage Location:  default
  Data Mover Config:
    Write Sparse Files:  false
  Node OS:               linux
  Operation Timeout:     10m0s
  Snapshot ID:           4ff960c3f4d2f55253926f36a66d5e50
  Source Namespace:      cormac-ns
  Target Volume:
    Namespace:  cormac-ns
    Pv:
    Pvc:        example-cormac-block-pvc
Status:
  Accepted By Node:      kubernetes-cluster-dkpp-kubernetes-cluster-dkpp-np-pk3w-smkvs46
  Accepted Timestamp:    2026-01-14T13:57:38Z
  Completion Timestamp:  2026-01-14T13:58:01Z
  Node:                  kubernetes-cluster-dkpp-kubernetes-cluster-dkpp-np-pk3w-smkvs46
  Phase:                 Completed
  Progress:
  Start Timestamp:  2026-01-14T13:57:46Z
Events:
  Type    Reason               Age              From                         Message
  ----    ------               ----             ----                         -------
  Normal  Data-Path-Started    7m               cormac-restore-260114-v65f8  Data path for cormac-restore-260114-v65f8 started
  Normal  Data-Path-Progress   7m (x2 over 7m)  cormac-restore-260114-v65f8  {}
  Normal  Data-Path-Completed  7m               cormac-restore-260114-v65f8  {"target":{"byPath":"/1c8f7949-b20e-4878-96b8-46a7859423cf","volumeMode":"Filesystem"}}
  Normal  Data-Path-Stopped    7m               cormac-restore-260114-v65f8  Data path for cormac-restore-260114-v65f8 stopped

And that completes the post. Hopefully this has demonstrated that Velero continues to be a very powerful command line tool for backing up and restoring Kubernetes workloads, not just vSphere Kubernetes Service (VKS) clusters. And if you have VCF Automation, remember that you do not need to do this manual approach, but instead you can use the VKS Management Data Protection tool via the UI.

The post Velero Revisited – Manually backing up VKS clusters using Velero appeared first on CormacHogan.com.

First of all, to get access to the new capabilities, you need to be logged in to the VCF Automation Organization Portal. From there, navigate to the Manage & Govern section. Select Kubernetes Management under VCF Services in the left hand navigation pane, then select clusters and this will reveal which VKS clusters are deployed. In this example, there is only one. The fact that it appears here suggests that VKSM has already discovered this cluster and has added the appropriate add-ons to allow it to be managed from VCF Automation. It is in a Healthy and Ready state, which is obviously good. This view alone, when you have many, many VKS clusters, can really help determine the status of your VKS estate at a glance.

Initial Setup

Initially you will find that Data Protection is not enabled on your VKS cluster. To enable Data Protection, you will need to have access to an S3 compatible object store bucket as this is where Data Protection sends the backup data. To enable Data Protection, click on the link highlighted below.

When you enable data protection, this popup appears. VKSM DP uses Velero to provide backup and restore functionality. Backups can use both File System Backup (FSB) and CSI snapshots. Later, we will see how we can choose between the different backup methods for volumes. Click on Enable to continue.

Once Data Protection has been enabled, you will see a new Data Protection tab for the cluster. You will also see a new data-protection agent/extension in the health view. Lastly, you will notice a warning about a missing backup target. This is the S3 bucket mentioned earlier. We will need to create a set of credentials for this target, as well as configure the target.

A similar, related message about missing credentials is seen if you try to configure the target location without first creating some credentials. Create the credentials by navigating to Configurations page, shown below. There are two configurations here – Credentials and Target Locations. Start with creating a new set of credentials.

The account credentials should contain an access key and secret key to allow Velero to access the S3 bucket. This is so that it can send the file backup data and potentially the CSI snapshot data, if this method is chosen for volume backups. More on this when we take a backup.

With the credential created, we can now begin to create the backup target. First step is to provide the credentials for the backup target which we just created in the previous step.

We then begin providing details about the storage provider and bucket. In this case, I am using a MinIO object store, but any compatible S3 API bucket should suffice.  Because I have TLS enabled on MinIO, I am providing a Certificate Authority from the MinIO server to that trust can be established to it by VKSM DP / Velero .

Next, from my list of VKS clusters, I select which ones are allowed to use this backup target. As I only have a single VKS, I only choose one in this example. Note that as you create further VKS clusters, you will also need to navigate to this point to assign your new cluster to the existing backup target location to allow it to use VKSM DP.

After giving the backup target a name, I save it. The backup target is now successfully configured.

Data Protection is now available on my VKS cluster.

This completes the setup. Let’s now try some backup and restore operations.

Create a stateful app on the VKS cluster

To provide something useful for the backup and restore, I made a very simple Pod and PVC using the below manifests, and deployed them into a new namespace in the VKS cluster called cormac-ns. Obviously this is a very simple example, as you can use VKSM DP to backup the whole of the VKS cluster. However, this simple app is just to show the backup and restore functionality in a way that is easy to follow. I won’t delve into describing the contents of each manifest. There is plenty of K8s documentation online that describe this already.

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: cormac-block-pvc
  namespace: cormac-ns
spec:
accessModes:
    – ReadWriteOnce
  resources:
    requests:
      storage: 5Gi
  storageClassName: vsan-default-storage-policy
—
apiVersion: v1
kind: Pod
metadata:
  name: cormac-pod
  namespace: cormac-ns
spec:
  securityContext:
    runAsUser: 1000
    runAsGroup: 3000
    fsGroup: 2000
    supplementalGroups: [4000]
  containers:
  – name: busybox
    image: “busybox:latest”
    command: [ “sleep”, “1000000” ]
    securityContext:
      allowPrivilegeEscalation: false
      capabilities:
        drop: [“ALL”]
      runAsNonRoot: true
      runAsUser: 1000
      seccompProfile:
        type: “RuntimeDefault”
    volumeMounts:
      – mountPath: “/demo”
        name: demo-vol
  volumes:
    – name: demo-vol
      persistentVolumeClaim:
        claimName: cormac-block-pvc

Apply these manifests, and if you wish, use the kubectl exec onto the pod and create some files in the /demo folder which is where the volume has been mounted to.

VKSM DP Backup

Using KSM Data Protection, I can now initiate a backup. The first question that you are prompted with is to select the object or objects that you wish to backup. This could be the whole VKS cluster, a namespace, or a set of objects identified by a selector. In this case, I went with a single namespace backup, cormac-ns.

Now comes the interesting part which is determining “how” to do the backup. There are two options for the File Share Backup (FSB), opt-in and opt-out. If you choose FSB Opt-in, then you have to add some special annotation to the volumes (i.e., Opt-in) if you WANT them to be backed up by FSB. If you choose FSB Opt-out, again, you have add special annotations (i.e., Opt-out) if you DO NOT WANT the volumes to be backed up by FSB. Thus, if you want all volumes to be backed up using the CSI snapshot method, select both “Use FSB Opt-in approach” and “Use CSI Snapshot backup” as shown below. If no FSB annotations are added to any volumes, then CSI will be used for all volumes. See https://velero.io/docs/v1.10/file-system-backup/#using-the-opt-out-approach for more information on annotations. Note that you are annotating with the name of the volume as seen by the Pod, not the actual PVC name. The “do not meet prerequisites for CSI snapshot” is a warning and can be ignored in as VKS Clusters deployed via VCF Automation should automatically have all of the necessary components in place. However, there is a link provided to the documentation where you can verify that the pre-requisites are indeed being met.

Once you have decided on the volume backup method, click next. Now select the backup location that we created previously.

Set the backup schedule. You can create a schedule to meet your requirements, and take backup son a regular basic. Or, do what I am doing here and take a one-off backup right now.

Select a retention policy for the backup (how long to keep it). By default the retention is set to 30 days.

Give the backup a name, and hit the create button.

Backup will enter in progress state, and all going well, should soon report completion.

After completion, click on the backup to see the backup details. It should report that the backup method was CSI snapshot and not FSB in the Persistent Volumes section. If it reports FSB, check that you made the correct backup selection, and that the Pod does not have any FSB annotations if FSB Opt-in was chosen.

VKSM DP Restore

Let’s start a restore by first deleting the cormac-ns namespace. This will also delete the pod and pvc in the namespace.

$ kubectl delete ns cormac-ns

Now let’s use VKMS DP to restore the namespace and its contents from our previous backup. There is a restore backup button in the top right hand corner of the backup details screen which we click to begin the operation.

You are now prompted to select the scope of the restore. Is it the full backup you wish to restore, or perhaps just a single namespace or a set of objects identified by a label. I’ve selected a single namespace just to show you an example, but of course, we only backed up a single namespace anyway so we could have chosen to restore the entire backup. But hopefully you can see the flexibility available.

Next, select the volumes to restore (optional). As we have only backed up a single volume using CSI snapshots, then this is the only volume available to restore as well. However, if you did not want to restore the volume, then you could un-click this option and just restore the pod.

Finally, give the restore job a name, and click on the restore button.

Once the restore successfully completes, you should observe the namespace and its contents restored.

$ kubectl get pod,pvc -n cormac-ns
NAME            READY  STATUS   RESTARTS  AGE
pod/cormac-pod  1/1    Running  0         5m23s

NAME                                     STATUS  VOLUME                                    CAPACITY  ACCESS MODES  STORAGECLASS                 VOLUMEATTRIBUTESCLASS  AGE
persistentvolumeclaim/cormac-block-pvc   Bound   pvc-0f8fd0bc-c2c2-4a9e-89b9-b9e6f2b9d1f3  5Gi       RWO           vsan-default-storage-policy  <unset>                5m24s

Success. We have used VKSM Data Protection to backup and restore a namespace. That completes the post. I think this is a really nice feature which is now available as part of VKSM in VCFA 9.0.1 and later.

The post A first look at VKSM Data Protection (VKSM DP) appeared first on CormacHogan.com.