This blog is about creating a new SQL cluster with a Failover instance based on a storage space direct configuration. There are a lot off opinions on do’s and don’t for on prem and in Azure with S2D. This is not the blog post on this discussion. This is just a post on SQL 2019 on Windows server 2019 storage space direct because I can!
And in a later blog post we are extending this to Azure. Still it always depends on running a Full SQL server or use a managed instance in Azure. But for now the new SQL server 2019 is the basic of our cluster running on a Windows Server 2019 cluster. As this time the installation will be GUI based as I got often the question on the screens. normally I run some PowerShell or a command line script that will do the job in several minutes
What are the new features in SQL Server 2019, the list below is just a short list for a full updated list go to the link below.
OR if you want to run a managed SQL server in Azure : https://azure.microsoft.com/en-us/pricing/details/sql-database/managed/
The Database Engine is the core service for storing, processing, and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise. The Database Engine also provides rich support for sustaining high availability.
What’s new in SQL Server 2019
- Installation of SQL Server is supported on x64 processors only. It is no longer supported on x86 processors.
- SysPrep is supported for all installations of SQL Server. SysPrep now supports failover cluster installations
- Always On Availability Groups – secondary replica connection redirection
- SQL Server Machine Learning Services failover clusters
- the operating system requirements for the principal editions of SQL Server
- SQL Server Management Studio (SSMS) 18.0 (preview)
- Azure Data Studio
- Azure Data Studio
Always On Availability Groups – more synchronous replicas (CTP 2.0)
-
Up to five synchronous replicas: SQL Server 2019 preview increases the maximum number of synchronous replicas to 5, up from 3 in SQL Server 2017 (14.x) . You can configure this group of 5 replicas to have automatic failover within the group. There is 1 primary replica, plus 4 synchronous secondary replicas.
-
Secondary-to-primary replica connection redirection: Allows client application connections to be directed to the primary replica regardless of the target server specified in the connection string. This capability allows connection redirection without a listener. Use secondary-to-primary replica connection redirection in the following cases:
- The cluster technology does not offer a listener capability.
- A multi subnet configuration where redirection becomes complex.
- Read scale-out or disaster recovery scenarios where cluster type is
NONE.
SQL Server Enterprise /SQL Server Standard :
- Windows Server 2019
- Windows Server 2016
- Windows Server 2012 R2
- Windows Server 2012
The supported storage types for data files are:
-
Local Disk
-
Shared Storage
-
SMB File Share
As the SQL server 2019 is still in preview I download the evaluation version. Get your SQL server here
Pick the version you want, in our case I select the download media.
Pick the ISO format do download this.
The SQL installation is done on my cluster. described in the blog post below
step by step Windows Server 2019 File Server clustering With powershell or GUI
In this Windows server 2019 cluster I created a S2D formatted with ReFS. There is plenty of info to find on my blog how to create the S2D in the cluster.
We have a Windows server 2019 Cluster in place with the Storagespace direct configuration.
The specific cluster configuration is debatable, in a typical SQL configuration you would have multiple disks.
Our Cluster with storage spaces. In storage spaces all disks come from the same disk pool if you create things default. then multiple disk has a different purpose. As you want to manage the SQL different than others.
Our SQL Installation is done this time by the GUI, show you the screens and options. you can always use the one liner to install the SQL server unattended.
And no I do not use the normal cluster installation but I use the advanced Cluster prep and completion. these two steps gives me a bit more freedom and flexibility it the installation fails.
The choice is developer but can also be the one with a product key.
Just do the updates direct as it is a new version and on a new OS, just to make sure that I run the latest bits.
I got a warning on the Firewall as I turned this off during the installation. This is configured by a GPO when I place the Cluster in the Right AD OU
Selecting the features and the installation folders.
Do I go for a default instance or a named instance. I always go for a named instance. Its easy and better management. In the old days some applications just want to run on a default instance.
My service account that has JEA Just enough Access
C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\20181217_034408\ConfigurationFile.ini
If you want to use a unattended install you can use this ini file to get the same or adjusted values during the install
Ini file content.
Now that the first step is completed we can do the completion step.
Now the second step is needed to finish the SQL 2019 installation on the first node
But running the wizard if failed on me, as the error said the Cluster is not verified!
I just need to run the Cluster validation.
Now with the Passed Cluster validation we try again.
Now this looks good lets stat the installation of the SQL 2019
With the Named instance and usage of an Alias, I can easy change this or move the SQL to another cluster.
The installation will show me what cluster groups I can use and which are used at the moment.
Selecting the right disks for the installation remember These are the Storage space direct disks that are created with the cluster installation. See my other blog post about creating S2D on Windows Server 2019
A fixed IP is needed unless you use the DHCP checkbox.
Some applications needs special Collation, when changing this it is an Instance setting and can’t be changed again.
Normally you will place the SQL admins or some groups. this is a demo cluster so admin access only
Select the right disks and change this in the location fields.
Selecting multiple files for the Temp DB and the DB location
Also in this step we get a Ini file as output this can be reused if needed.
Now that the installation on one node is ready you can see the SQL resources in the cluster. As I did only the install on one node we need to do the Add node installation to get the installation done for a full FCI.
The Step to add an extra cluster node with the SQL installation is an easy step.
Follow the Wizard and use the Same account that you used before.
As the SQL studio is no longer a default installation you need to download the bits from Microsoft.
SSMS 18.0 Public Preview 6 is now available, and is the latest generation of SQL Server Management Studio that provides support for SQL Server 2019 preview!
SSMS 17.9.1 is the current General Availability (GA) version of SSMS
Download SQL Server Management Studio 17.9.1
Download SQL Server Management Studio 17.9.1 Upgrade Package (upgrades 17.x to 17.9.1)
Version Information
- Release number: 17.9.1
- Build number: 14.0.17289.0
- Release date: November 21, 2018
As we are using the 2019 We need to have the latest version
Now we have the studio ready and can access the SQL instance.
In the next blogs I’ll show you how to extend the SQL to Azure.
Below is an overview of the SQL disks based on storage spaces direct.
With All these steps I hope you can build your own cluster and play with this. As for production never use a next next Finish installation there is always some custom tweaks needed to get the best performance.
Follow Me on Twitter @ClusterMVP
Follow My blog https://robertsmit.wordpress.com
Linkedin Profile Robert Smit MVP Linkedin profile
Google : Robert Smit MVP profile
Share this:
Robert Smit MVP Blog 
Robert, great post as usual. Did you ever post the follow-up post where you extended this to Azure? I have my doubts that you could extend this to a single node in Azure, I think you would have to have at least 2-nodes so S2D could be configured on that end as well? What do you think?
Hi David, that is a good question I know with some software tools this is done easily. But this post is still in progress the machines are done but the configuration needs to be done and the whole blog post. But time is limited and lately I’m stranded in a whole different topic as Windows 10 with autopilot is eating some time away. but this post is still coming. and In fact I played with a useless config but fun to do S2D on a ramdrive.
Hey Robert,
I’m interested in your follow up post regarding a S2D SQL FCI to Azure as well. I’ve set one up with a cloud witness according to https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-create-failover-cluster, but it keeps losing the path to one of the data folders in c:\clusterstorage when I shut down one node. Wonder if you had a similar experience.
HI, Still working on this but during lack of time..Will come soon. The Idea was guest clustering in Azure or s2d with some tricks but you can always use datakeeper https://us.sios.com/products/windows/cluster/ See also the comment from David.
Thanks for reading my blog!