• Ms Sql Server Express
• Ms Sql Server Management Studio

Bring Microsoft SQL Server 2017 to the platform of your choice. Use SQL Server 2017 on Windows, Linux, and Docker containers. SQL Server 2019 introduces support for the widely used UTF-8 character encoding. This has been a longtime requested feature and can be set as a database-level or column-level default encoding for Unicode string data. Why did we need UTF-8 support? This is an asset for companies extending their bu. SQL Server works in client-server architecture, hence it supports two types of components − (a) Workstation and (b) Server. Workstation components are installed in every device/SQL Server operator’s machine. These are just interfaces to interact with Server components. Example: SSMS, SSCM, Profiler, BIDS, SQLEM etc.

With the recent releases of both VMware vSphere 7 and SQL Server 2019 as well as changes announced to SQL Server licensing, this is a good time to discuss licensing for SQL Server when virtualized using VMs with VMware vSphere. This blog post is part one of two. In this first installment, I’ll cover the basics of licensing SQL Server as well as per-VM licensing.

First Things First

All production deployments of SQL Server must be licensed properly according to the Microsoft SQL Server 2019 Licensing Guide. Production implies the use of Enterprise, Standard, or Web editions of SQL Server. Developer Edition cannot be used for production. Express Edition has no licensing requirement but is limited in scalability and features. To see all the differences between the editions of SQL Server, read the official Microsoft documentation topic “Editions and supported features of SQL Server 2019 (15.x)”.

If applicable, you must also account for licensing the operating system. A license for Windows Server must be purchased whereas some distributions of Linux that are supported for SQL Server may not need an OS license but require support agreements. An example is Red Hat Enterprise Linux. This blog will not cover the licensing/support scenarios for the underlying operating system. If you are reading Microsoft’s documentation, they refer to an operating system environment (OSE).

If you are deploying an older version of SQL Server for some reason such as an older application, you will be purchasing SQL Server 2019 licenses with downgrade rights. You can no longer buy licenses for SQL Server 2017 and earlier.

SQL Server Instance

An installation of SQL Server is an instance. On Linux, you can only have one instance of SQL Server per VM and on Windows Server you can have up to the supported maximum. For Always On Failover Cluster Instances (FCIs), that limit is up to 25 and for standalone, 50. Most customers do not, and should not, deploy that many instances in a single VM.

Each instance has its own system (and user) databases and some dedicated binaries. There are some shared components that would be common to all instances as well. This concept of an instance not only plays into how SQL Server works, but also how it is licensed. You do not pay per instance of SQL Server; you can install as many instances as you would want inside a given VM which hosts an OSE (Linux or Windows Server), up to the supported maximum if SQL Server is licensed properly.

When deploying with Windows Server, use the right installation ISO from Microsoft. For example, downloading the wrong ISO of Enterprise Edition could limit you to only 20 cores, or you could install Standard instead of Enterprise. Using the wrong ISO not only has licensing implications but can also impact performance.

SQL Server Licensing Models

There are two licensing models for SQL Server depending on the edition. Core-based, which can be used by either Enterprise and Standard, and server + client access license (CAL) which only is applicable for Standard.

Core-based licensing means that you are accounting for all the cores used in each virtual OSE (VM). Using a server license along with CALs is exactly what it sounds like: you license the VM for SQL Server and each device (Device CAL) and/or user (User CAL) accessing SQL Server requires a license. Today, most customers using Standard or Enterprise editions of SQL Server use core-based licensing. Server + CAL is a bit outdated and only applicable to Standard Edition. Server + CAL allows you to license only the server running SQL Server but you need to account for any device or user (the CALs) that will connect to SQL Server.

SQL Server is more than a relational database engine. There are other components such as Reporting Services, Analysis Services, Integration Services, and so on. If these components are installed, location matters. If the non-engine components are installed elsewhere, such as another VM which may be better from a performance or application standpoint, they are licensed separately. For example, if one VM has SQL Server 2019 but another has Reporting Services, both VMs will need to be accounted for when it comes to licensing.

Another fundamental concept for licensing Microsoft products is Software Assurance (SA). SA adds additional benefits to SQL Server that affect availability, upgrade rights, and virtualization. SA is an additional cost, but compared to not having it, is often more cost effective than licensing without it. SA will be covered where applicable. SA cannot be added later; it must be included when licensing is purchased.

The biggest question I often get when it comes to licensing SQL Server for virtualized environments is this: should VMs be licensed individually or should entire virtualization hosts be licensed? It depends.

The next two sections will cover licensing considerations for both per-VM and per-host, focusing on the Core-based model.

Licensing Individual Virtual Machines

Licensing SQL Server per-VM is straightforward: every VM that has SQL Server or any of its server components such as Analysis Services and Reporting Services must be licensed. Some tools, such as SQL Server Management Studio, do not incur any licensing cost. Licensing per-VM is common when your company does not have a large SQL Server footprint and you only need a handful of VMs. In most scenarios, the per-VM model assumes that the VM running SQL Server is part of a mixed vSphere deployment that is running various VMs hosting different workloads besides SQL Server, and thus, is not a host or cluster dedicated to SQL Server.

For example, if you have 20 production VMs with SQL Server installed, you’ll need to account for licenses for every VM individually. Licensing per VM also means that you can technically overcommit resources since the unit of licensing is the VM and that VM and its licenses can be associated with another host if it is moved. I do not recommend overcommitting resources as it can have a performance impact on SQL Server.

Microsoft requires a minimum of four cores to be licensed for SQL Server. After that, they sell SQL Server licenses in packs of two cores. For example, if you have a VM with only one or two vCPUs, you need to purchase four cores of SQL Server licensing for that VM. However, if you resize the VM to four cores, no additional cost is incurred.

For purposes of this blog post and the way VMware works, a vCPU represents the total amount of CPU resources assigned to a VM. This could be comprised of vSockets as well as vCores. As shown below, the VM pictures has eight vCPUs – two vSockets each of which has four cores. In this example, you would need to purchase eight cores of licensing.

For more information, see page 24 of the Architecting Microsoft SQL Server on VMware vSphere Best Practices Guide.

Summary

Now that you understand the basics and what it means to license per-VM, Part Two will cover per-host licensing, the impact of availability architectures, as well as Software Assurance in more depth.

This is a guest post by Allan Hirt. Consultant, trainer, author, business continuity expert, and SQLHA, LLC founder Allan Hirt has been working with both SQL Server since 1992 when it was still a Sybase product as well as clustering in Windows Server since the late 1990s when it was called Wolfpack. He has been using VMware’s products for virtualizing workloads for nearly 20 years. Currently a dual Microsoft MVP (Data Platform; Cloud and Datacenter Management) as well as a VMware vExpert, Allan works with customers of all sizes around the world to deliver mission critical projects whether they are on premises, in the cloud, or hybrid. You can follow him on Twitter at @SQLHA, read his latest blog posts and see upcoming speaking engagements and classes at sqlha.com, and get his free video content on the SQLHA YouTube Channel.

Learn how to simulate the FOR LOOP in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server, there is no FOR LOOP. However, you simulate the FOR LOOP using the WHILE LOOP.

Syntax

The syntax to simulate the FOR Loop in SQL Server (Transact-SQL) is:

Parameters or Arguments

cnt_total

The number of times that you want the simulated FOR LOOP (ie: WHILE LOOP) to execute.

statements

The statements of code to execute each pass through the loop.

Note

• You can simulate the FOR LOOP in SQL Server (Transact-SQL) using the WHILE LOOP.

Example

Let's look at an example that shows how to simulate the FOR LOOP in SQL Server (Transact-SQL) using the WHILE LOOP.

Ms Sql Server Express

For example:

Ms Sql Server Management Studio

In this WHILE LOOP example, the loop would terminate once @cnt reaches 10.