Archive for the ‘SQL 2019’ Tag

Step by Step SQL Server 2019 FCI Cluster on Storage Spaces Direct (S2D) Windows Server 2019 #S2D #SQL #SQLserver2019 #BigData #WinServ #WSSD   Leave a comment

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/

SQL database engine

 

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.

 

 

https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation?view=sql-server-ver15

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 :

  1. Windows Server 2019
  2. Windows Server 2016
  3. Windows Server 2012 R2
  4. Windows Server 2012

The supported storage types for data files are:

As the SQL server 2019 is still in preview I download the evaluation version.  Get your SQL server here

SQL Server 2019 

Pick the version you want, in our case I select the download media.

SQL Server 2019

Pick the ISO format do download this.

SQL Server 2019

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

https://robertsmit.wordpress.com/2018/11/29/step-by-step-windows-server-2019-file-server-clustering-with-powershell-or-gui-cluster-ha-azure-windowsadmincenter-windowsserver2019/

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.

https://robertsmit.wordpress.com/2016/01/05/using-windows-storage-spaces-direct-with-hyper-converged-in-microsoft-azure-with-windows-server-2016/

https://robertsmit.wordpress.com/2017/11/09/azure-storage-spaces-direct-s2d-standard-storage-vs-premium-storage/

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.

image

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.

image

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.

 

image

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.

image

The choice is developer but can also be the one with a product key.

imageimage

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.

image

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

image

Selecting the features and the installation folders.

image

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.

image

My service account that has JEA Just enough Access

image

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

image

Ini file content.

image

image

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

image

But running the wizard if failed on me, as the error said the Cluster is not verified!

image

I just need to run the Cluster validation.

image

image

Now with the Passed Cluster validation we try again.

image

Now this looks good lets stat the installation of the SQL 2019

image

With the Named instance and usage of an Alias, I can easy change this or move the SQL to another cluster.

image

The installation will show me what cluster groups I can use and which are used at the moment.

image

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

image

A fixed IP is needed unless you use the DHCP checkbox.

image

Some applications needs special Collation, when changing this it is an Instance setting and can’t be changed again.

image

Normally you will place the SQL admins or some groups. this is a demo cluster so admin access only

image

Select the right disks and change this in the location fields.

imageimage

Selecting multiple files for the Temp DB and the DB location

image

Also in this step we get a Ini file as output this can be reused if needed.

 

image

imageimage

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.

image

The Step to add an extra cluster node with the SQL installation is an easy step.

image

Follow the Wizard and use the Same account that you used before.

 

image

As the SQL studio is no longer a default installation you need to download the bits from Microsoft.

image

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

image

As we are using the 2019 We need to have the latest version

imageimageimage

Now we have the studio ready and can access the SQL instance.

image

In the next blogs I’ll show you how to extend the SQL to Azure.

image

Below is an overview of the SQL disks based on storage spaces direct.

image

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

Posted January 15, 2019 by Robert Smit [MVP] in SQL, SQL Server 2019

Tagged with ,

  • Twitter

  • RSS Azure and Microsoft Windows Server Blog

  • %d bloggers like this: