Tag: SQL Server

On the 12th of October 2015 Gartner has released the new “Magic Quadrant for Operational Database Management Systems”. Microsoft’s SQL Server is now the leader in the Magic Quadrant.

Quotes from the Gartner site:

  • Market vision: Microsoft’s market-leading vision consists of NoSQL (Azure Document DB and Azure Tables), cloud offerings (including hybrid cloud), the use of analytics in transactions (HTAP) and support for mobility. Its vision for in-memory computing across products, hybrid cloud implementations and a “cloud first” strategy is ahead of its competitors.
  • Strong execution: Microsoft SQL Server is an enterprise wide, mission-critical DBMS capable of competing with products from the other large DBMS vendors. Gartner’s 2014 market share data shows Microsoft as the No. 2 vendor in terms of total DBMS revenue.
  • Performance and support: Reference customers were very positive, with the performance of SQL Server, documentation, support, ease of installation, integration and operation all rated highly.

Of course there are some cautions too. The first is the perception that Microsoft SQL Server is not used in mission-critical enterprise wide applications.

  • Market image: Although SQL Server is an enterprise-class DBMS, Microsoft continues to struggle to dispel a perception of weakness in this area. Inquiries from Gartner clients demonstrate a continuing perception that SQL Server is not used for mission-critical enterprise wide applications — a view that inhibits wider use of SQL Server as a primary, enterprise-class DBMS.
  • Lack of an appliance: Microsoft still lacks an appliance for transactions (one comparable to its Microsoft Analytics Platform System, formerly Parallel Data Warehouse). By contrast, its major competitors (IBM, Oracle and SAP) all offer one, as does one new entrant to the Magic Quadrant (Fujitsu).
  • Pricing: Microsoft received below-average ratings for pricing suitability, a problem that stems from the pricing model changes implemented in SQL Server 2012. Microsoft’s cloud offerings appear to be partially mitigating this concern.

Today I got the question if it is possible to create a login trigger that only is triggered on SQL Server login’s (except SA of course)​ that are used from SQL Server Management Studio to connect to a SQL Server instance.

So the first thing you do in cases like this is to check if someone else has solved this already for you. I found the following post​ that had only part of the solution.​

​So I changed the code so that it does only block login attempts from SQL Server Management Studio with a SQL Server account. Domain accounts and domain groups keep working as requested and also SA keeps working.

/****** Object:  DdlTrigger [TR_Block_Logon_Via_Management_Studio_SQL_Login]    Script Date: 08/11/2015 16:47:21 ******/
IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'TR_Block_Logon_Via_Management_Studio_SQL_Login')
DROP TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login] ON ALL SERVER

/****** Object:  DdlTrigger [TR_Block_Logon_Via_Management_Studio_SQL_Login]    Script Date: 08/11/2015 16:47:21 ******/


CREATE TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login]


DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)

SELECT @program_name = program_name,
@host_name = host_name
FROM sys.dm_exec_sessions AS c
WHERE login_name <> 'sa'
AND nt_user_name = ''

IF @program_name LIKE '%Management%Studio%'
RAISERROR('This login is for application use only.',16,1)



ENABLE TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login] ON ALL SERVER

There is however a way to bypass this solution so it is not completely fool proof. It is meant as a first blocking.

SQL Server licensing is a beast of its own. There are quite a few options to choose from and it is difficult to determine the right choice for your organization. As a good habit as a consultant the answer is always: “It depends…”

If you end up having questions on the licensing always ask your license reseller for help. Every license reseller should have a license specialist who has been trained by Microsoft on licensing.

This information is from the Microsoft SQL Server 2014 Licensing Datasheet (Licensing Datasheet / original link) with a little bit less marketing blabla.

Editions Overview

First an editions overview on what editions of SQL Server 2014 are available:

  • Enterprise Edition for mission critical applications and large scale data warehousing
  • Business Intelligence Edition for premium corporate and self-service BI
  • Standard Edition for basic database, reporting and analytics capabilities

Enterprise Edition includes all product features available in SQL Server 2014, and the Business Intelligence (BI) Edition includes premium BI features in addition to Standard Edition database features.

Note: SQL Server 2014 is also available in Developer and Express editions. Web Edition is offered in the Services Provider License Agreement (SPLA) program only.

SQL Server 2014 Licensing Models

SQL Server 2014 offers customers a variety of licensing options aligned with how customers typically purchase specific workloads. There are two main licensing models that apply to SQL Server:

Server + CAL:

Provides the option to license users and/or devices, with low cost access to incremental SQL Server deployments.

  • Each server running SQL Server software requires a server license.
  • Each user and/or device accessing a licensed SQL Server requires a SQL Server CAL that is the same version or newer – for example, to access a SQL Server 2012 Standard Edition server, a user would need a SQL Server 2012 or 2014 CAL.
  • Each SQL Server CAL allows access to multiple licensed SQL Servers, including Business Intelligence Edition, Standard Edition and legacy Enterprise Edition Servers

Per Core:

Gives customers a more precise measure of computing power and a more consistent licensing metric, regardless of whether solutions are deployed on physical servers on-premises, or in virtual or cloud.

  • Core based licensing is appropriate when customers are unable to count users/devices, have Internet/Extranet workloads or systems that integrate with external facing workloads.
  • To license a physical server, customers must license all the cores in the server. Determining the number of licenses needed is done by multiplying the total number of physical cores by the appropriate core factor found in the core factor table.  A minimum of 4 core licenses is required for each physical processor on the server.

SQL Server 2014 Editions availability by licensing model:

Note: SQL Server 2014 Developer Edition is licensed under the Developer Tools model, which is ‘Per User’ based.

Special Note for Enterprise Edition Users:

With the introduction of SQL Server 2012, Enterprise Edition was removed from the Server + CAL model and new server licenses are no longer available.  However, customers with active Software Assurance (SA) coverage can continue to renew SA on Enterprise Edition servers and upgrade to SQL Server 2014 software.  Note: for customers who upgrade to SQL Server 2014, a 20 core limit applies to the software.

Licensing for Virtualization

SQL Server 2014 offers virtualization rights, options and benefits to provide flexibility for customers deploying in virtual environments. There are two primary virtualization licensing options in SQL Server 2014: the ability to license individual virtual machines and the ability to license for maximum virtualization in highly virtualized and private cloud environments.

Individual Virtual Machines

As hardware capabilities grow, it continues to be more common for each database to use a fraction of its server’s computing power. When deploying databases on Virtual Machines (VMs) that use just a fraction of a physical server, savings can be achieved by licensing individual VMs.

  • To license a VM with core licenses, purchase a core license for each virtual core (virtual thread) allocated to the VM (with a minimum of 4 core licenses per VM).
  • To license a single VM with a server license (for Business Intelligence or Standard Edition only), purchase a server license and matching SQL Server CALs for each user or device.
  • Each licensed VM covered with SA can be moved frequently within a server farm, or to a third-party hoster or cloud services provider, without the need to purchase additional SQL Server licenses.

Note: When licensing VMs under the Server + CAL model, the number of virtual cores does not affect the number of server licenses required.

High Density Virtualization

Further savings can be achieved by operating a SQL Server private cloud. This is a great option for customers who want to take advantage of the full computing power of their physical servers and have very dynamic provisioning and de-provisioning of virtual resources.

  • Customers can deploy an unlimited number of VM’s on the server and utilize the full capacity of the licensed hardware, by fully licensing the server (or server farm) with Enterprise Edition core licenses and SA coverage based on the total number of physical cores on the servers.
  • SA enables the ability to run an unlimited number of virtual machines to handle dynamic workloads and fully utilize the hardware’s computing power.

Licensing for High Availability

SQL Server software can be configured so that if one server fails, its processing will be picked up, recovered and continued by another server.  Beginning with SQL Server 2014, each active server licensed with SA coverage allows the installation of a single passive server used for fail-over support.

  • The passive secondary server used for failover support does not need to be separately licensed for SQL Server as long as it is truly passive. If it is serving data, such as reports to clients running active SQL Server workloads, or performing any “work” such as additional backups from secondary servers, then it must be licensed for SQL Server.

  • The active server license (s) must be covered with SA, and allow for one passive secondary SQL Server, with up to the same amount of compute as the licensed active server, only.

Business Intelligence Server Access

Similar to other SQL Server products offered under the Server + CAL licensing model, Business Intelligence (BI) Edition generally requires a SQL Server CAL for each user or device accessing the server software.  New with SQL Server 2014, use terms for BI Edition server software now allow batch processing of data without requiring CALs for those data sources supplying the data.

  • ‘Batch Processing’ is defined as an activity that allows a group of tasks occurring at different times to be processed together at the same time.
  • Non-batch processing access to BI Edition servers still requires CALs be assigned to those users and/or devices accessing the server software.
  • The general multiplexing policy still applies to SQL Server Standard and Enterprise Edition software licensed under the Server + CAL model.

©2014 Microsoft Corporation.  All rights reserved.  This document is for informational purposes only.  Microsoft makes no warranties, express or implied, in this summary.  For more information on how to buy SQL Server 2014, please visit http://www.microsoft.com/en-us/servercloud/products/sql-server/ .

Other links related to licensing SQL Server:

Core Factor Table

Introduction to Per Core Licensing and Basic Definitions

SQL Server 2012 Licensing Quick Reference Guide

SQL Server 2012 Licensing Datasheet

Microsoft Assessment and Planning (MAP) Toolkit link, to help with planning a SQL Server architecture and particularly useful for licensing calculations

During my first installation of Microsoft SQL Server 2016 CTP2 on a VM I created for this I got an error message stating that Java Runtime was not installed… (during an full install – selected all features)

Error message: “Oracle JRE 7 Update 51 (64bit) or higher is required”

And when you click on “Failed”:

Yes, of course, I do not want any dependencies on Java Runtime on my SQL Server boxes. So the Java Runtime is not installed on my default machines. The bigger question is of course:

“Why has SQL Server all of a sudden the requirement of Java Runtime since SQL Server 2016 CTP2?”

It turns out that a new feature of SQL Server 2016 CTP2 is PolyBase. “PolyBase is a T-SQL front end that allows customers to query data stored in HDFS”. With PolyBase you can query, using T-SQL, Hadoop or Azure Blob Storage and query it in an adhoc fashion. It also lets you query semi-structured data and join the results with relational data sets stored in SQL Server. PolyBase is optimized for data warehousing workloads and intended for analytical query scenarios.

So the new feature is a great addition in accessing data like Hadoop and Azure Blob Storage together with SQL Server.

So if you need PolyBase then you must install Java Runtime too. I really hope that the Java Runtime dependency will be rewritten to a .Net variant. Or I need to get used to install Java Runtime together with SQL Server. Of course it is better to install the PolyBase feature on a standalone VM in a larger environment.