Step 1: Retrieve the SQL Server Images
Microsoft does not allow third parties to ship their container images. Thus, the onus is on the DSM Admin to retrieve the images. There are two ways to do this. The first is to connect DSM to the external Microsoft Container Registry (mcr) and pull down the images. The second is to upload an image bundle to DSM. The latter would be most likely used for air-gapped environments where there is no internet access. In this post, I will show how to configure DSM to connect to the external Microsoft registry and retrieve the SQL Server images. Simply login to the DSM UI/Portal as a DSM Admin. Then navigate to Version & Upgrade > Image Registries. This provides an option to add a new Image Registry to DSM:
Use the following settings for the Microsoft Registry:
- Name: You can choose anything, but keep an eye on the naming convention. I chose mssql
- Endpoint: mcr.microsoft.com
- Repository: mssql
- Auth type: You can leave at None
- Data Services: Select SQL Server
At this point, the relevant image(s) will begin to synchronise from the external registry to DSM. After a few moments, you should see the image status by navigating to Version & Upgrade > SQL Server. Click on the >> symbol next to the Version field to see the status. If the status is showing synchronising, wait until it changes to the Ready state, as shown below. Once it the ready state, the image is ready to be used.
The next step is to enable this particular database engine for provisioning. By default, database engine provisioning is CreateDisabled until the DSM Admin enables it. To enable the database, click on the checkbox in the first field, and then click on the Enable link, as shown here:
An Enable Service dialog box will now appear. This dialog box also allows you to mark a database as Preview. This adds a banner to end-users that caution should be exercised when provisioning this database engine type, and that this database may not be intended for production use. This is exactly what a technical preview is meant for. Thus, at this point, good practice would be to mark this data service release as a preview as shown below.
Step 2: Provision an MS SQL Server Instance
With the MS SQL Server images now available, we can go ahead and provision our first SQL Server instance. For anyone who is familiar with DSM, this will be very similar to the Postgres databases or MySQL databases workflows, which have been supported since the early versions of DSM. Navigate to Databases > SQL Server (Preview), and under SQL Servers, click on Create SQL Server.
This will launch the wizard to create an MS SQL Server database. First section is Basic Information. In this tech preview there is only a single version (16.0.x) which corresponds to MS SQL Server 2022. Next, provide a name for the instance, and then select the Edition. There are certain editions such as Developer and Express which I believe may be used without the need for a license. There are of course other editions which can be selected such as Standard and Enterprise which of course do require licensing. These license come from Microsoft and are not provided by DSM. Finally, you can also add an full qualified domain name (FDQN) for the database in the DNS section, as well as provide your own custom certificates for secure communication to the database.
The next step is to provide Data Availability and Protection options. In this technical preview, we are only supporting the provisioning of single node, standalone MS SQL Server instances. Clustering solutions are planned in upcoming releases. However, automated backups are available, but disabled by default. To enable automated backups, ensure that there is an S3 bucket configured for database backups. Select the backup location and backup retention period as shown below. You can also setup your own backup schedule if you wish.
The final section in deploying an MS SQL Server instance is Infrastructure. Here you get to choose the appropriate infrastructure policy. Again, for those of you familiar with DSM, this is identical to how thing are done for other database engines. After selecting the infrastructure policy to identify which vSphere resources to use for the database, additional granularity is offered around which storage policy to use, which VM class to use for defining the resources (CPU/Memory) for the backing virtual machine running the database, and finally setting the actual size of the disk to create for the database.
Now click next, review your choices and submit the request to create an MS SQL Server instance. If everything is working as expected, then the database instance should be created momentarily.
DSM provides a complete view of the database once it has been provisioned. This includes various details about configuration as well as the protection and infrastructure which was selected when the database was deployed.
One of the items shown in the summary view above is the connection string. This can be used with standard Microsoft tooling such as MS SQL Server Studio, or for non-Windows based Operating Systems, Azure Data Studio can be used to connect to the databases created as part of the MS SQL Server instance deployment (e.g., master, model, etc). End users and developers can continue to use the same tooling that they are familiar with today to connect to, and work with, MS SQL Server databases provisioned via DSM 9.0.
Step 3: Provision a SQL Server database on an existing instance
Now that we have successfully created an instance, we can use that instance to provide true DBaaS to our end users. To provision an MS SQL Server database from an existing MS SQL Server instance, navigate back to Databases > SQL Server (Preview), and this time select the Databases tab. Click the Create Database button. When there is only a single MS SQL Server instance in DSM, it is automatically selected as the instance on which the new database is created. Give the new database a name, and select the user type, either SQL User or Windows Principal. As I have not enabled Active Directory / Windows Authentication on this instance, I will create a SQL User called “cormac” for this database and provide a password.
Next, we move to the Data Availability and Protection section. Since we are limited to single node topologies in the tech preview, we don’t have any choice over clustering. However, you can setup backups if you so wish. Note that we put a restriction in place here so that new databases deployed to an existing MS SQL Server instance may use only the trusted root Certificate Authorities which are universally trusted by any Server. If a trusted CA does not exist, “No Backup Location Found” is reported. This avoids unnecessary restarts of the engine each time a new database is created.
Complete the database creation, and shortly the new database should become Ready. Click on the database to get summary details, including the specific connection string for this user and database.
This connection string can now be used to connect to the database, again using whatever MS SQL Server tooling your end users are familiar with.
Summary
That completes the introduction to MS SQL Server as a new tech preview data service in DSM version 9.0. Hopefully this will have given you a good idea on how to get started with MS SQL Server on DSM, both from an engine/instance perspective but also from a database perspective. I haven’t shown you how to configure Active Directory integration for the MS SQL Server instance, but we will have those instruction in our official documentation. With the instance integrated into AD, you will be able to use Windows Authentication to access the databases should you wish to do so. I also have not yet shown the API aspects of this integration, which can be used to create, delete and clone existing MS SQL Server instances. However, as I mentioned, I hope there is enough detail here to get you started. Thanks for reading this far.