Category: 2014

Today I like to write about a problem that excists in a lot of environments. The file sizes and file growth of the databases are default configured with 1MB growth and 10% growth. I see a lot of environments and most of them do not have adjusted values for these settings. The default setting for database files is:

  • Data: initial size 2MB; Autogrowth: by 1MB, unrestricted growth
  • Log: initial size 1MB; Autogrowth: by 10 percent, unrestricted growth

Good reading about the default values is this article [Choosing Default Sizes for Your Data and Log Files]. So after reading that excellent blog article by Paul S. Randal you should be convinced that the default settings are not the best for your environment. But what should they be?

Of course the well known answer is: “It depends…”

In an ideal situation you know what the expected growth in data for your database is and you pre-size your database files accordingly. However I rarely see an environment where the DBA and/or the Developers have any clue about the growth in data for the next few months, years of even a longer time frame.

After changing file sizes too long by hand I started thinking about creating a script for automating this based on the file sizes the databases have when you run the script.

I started with defining 4 possible sizes for database file growth settings:

  • Databases that grow per 100MB
  • Databases that grow per 250MB
  • Databases that grow per 1.000MB
  • Databases that grow per 2.500MB

The next thing is to define what file sizes are the limits for these file growth settings:

  • Databases from 1MB to 249MB
  • Databases from 250MB to 2.499MB
  • Databases from 2.500MB to 4.999MB
  • Databases from 5.000MB to ∞

Next I started writing a script to programmatically create the T-SQL statement to change the settings for the databases.

First I check what the result will be for each database with an overview for every new file size and file growth setting:

SELECT [SMF].[database_id] AS [Database ID]

, [SMF].[name] AS [File name]

, [SMF].[size] * 8 / 1024 AS [Current size]

, ROUND ((([SMF].[size]) * 8 / 1024 / 100) , 2 , 0) * 100 + 100 AS [New size in growth per 100]

, ROUND ((([SMF].[size]) * 8 / 1024 / 250) , 2 , 0) * 250 + 250 AS [New size in growth per 250]

, ROUND ((([SMF].[size]) * 8 / 1024 / 1000) , 2 , 0) * 1000 + 1000 AS [New size in growth per 1000]

, ROUND ((([SMF].[size]) * 8 / 1024 / 2500) , 2 , 0) * 2500 + 2500 AS [New size in growth per 2500]

FROM [master].[sys].[master_files] [SMF]

The result from the script above will be something like this (results will vary per environment):

 

 

 

 

 

Next I created the script that creates a result based on the current file sizes:

SELECT [SMF].[database_id] AS [Database ID]

, [SMF].[file_id] AS [File ID]

, [SMF].[name] AS [File name]

, [SMF].[size] * 8 / 1024 AS [Current size]

, [SMF].[growth] * 8 / 1024 AS [Current growth]

, [SMF].[is_percent_growth] AS [Is current percentage growth]

, CASE

WHEN [SMF].[size] * 8 / 1024 < 100 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 100) , 2 , 0) * 100 + 100)

WHEN [SMF].[size] * 8 / 1024 < 2500 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 250) , 2 , 0) * 250 + 250)

WHEN [SMF].[size] * 8 / 1024 < 5000 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 1000) , 2 , 0) * 1000 + 1000)

WHEN [SMF].[size] * 8 / 1024 >= 5000 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 2500) , 2 , 0) * 2500 + 2500)

END AS [New file size]

, CASE

WHEN [SMF].[size] * 8 / 1024 < 100 THEN '100'

WHEN [SMF].[size] * 8 / 1024 < 2499 THEN '250'

WHEN [SMF].[size] * 8 / 1024 < 4999 THEN '1000'

WHEN [SMF].[size] * 8 / 1024 >= 5000 THEN '2500'

ELSE '1024'

END AS [New file growth]

FROM [master].[sys].[master_files] [SMF]

In the results from that script you will see what the current sizes and growth settings are and the proposed new sizes and growth settings (results will vary per environment):


 

 

 

 

 

So with this logic in place I could create the final script that creates the T-SQL code for changing the database file sizes for each database in the SQL Server instance based on the current file sizes and defenitions made earlier. In scripts like these I never make them that the T-SQL code will automatically run. The scripts create T-SQL code that you need to copy paste into a new query window so that you can check if the script accomplishes what you want. Before running the script below make sure your results are displayed in Text (CTRL + T)

In this script I use the SQLCMD mode option :CONNECT + Servername to be sure the changes will be done on the correct SQL Server instance.

SELECT ':CONNECT ' + @@SERVERNAME + '

ALTER DATABASE [' + CONVERT(nvarchar(1000), DB_NAME(SMF.database_id)) + '] MODIFY FILE ( NAME = ''' + RTRIM(SMF.name) + ''', SIZE = ' +

CASE

WHEN [SMF].[size] * 8 / 1024 < 100 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 250) , 2 , 0) * 100 + 100)

WHEN [SMF].[size] * 8 / 1024 < 2500 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size])* 8 / 1024 / 250) , 2 , 0) * 250 + 250)

WHEN [SMF].[size] * 8 / 1024 < 5000 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 1000) , 2 , 0) * 1000 + 1000)

WHEN [SMF].[size] * 8 / 1024 >= 5000 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 /1024 / 2500) , 2 , 0) * 2500 + 2500)

END + ', FILEGROWTH = ' + (

CASE

WHEN [SMF].[size] * 8 / 1024 < 100 THEN '100'

WHEN [SMF].[size] * 8 / 1024 < 2499 THEN '250'

WHEN [SMF].[size] * 8 / 1024 < 4999 THEN '1000'

WHEN [SMF].[size] * 8 / 1024 >= 5000 THEN '2500'

ELSE '1024'

END + 'MB )' )

FROM [master].[sys].[master_files] [SMF]

The results from this script are the T-SQL commands for changing the file sizes and file growth settings (results will vary per environment):


 

 

 

 

 

 

 

 

 

When you used CTRL + T to get the results to text the result will look like above. Select only the commands and copy them into a new Query windows:


 

 

 

 

 

 

 

Default you will see the :CONNECT + Server lines not in grey, to get your script to run correctly you first need to enable SQLCMD mode via the menu option [Query] and [SQLCMD Mode]

Now you can run the query created by the script to change your database file sizes and file growth settings.

Remember that every time you run the query the sizes will change. This script is mainly intended for first time running after installing a new server with databases or when you need to change the default settings for the first time.

Of course you could run this script every now and then. My advise in this case is to change only databases that needs to change and leave the already changed databases as they are.

There is impact when running the results from the script because databases are grown to new sizes and that takes disk I/O. So in heavy used environments I advise to run the results from the script planned in a time windows where there is lower usage or better in a maintenance window.

On the 17th of August Cumulative Update 2 for Microsoft SQL Server 2014 SP1 ​has been released​.

On the 17th of August Cumulative Update 9 for Microsoft SQL Server 2014 RTM has been released​.

SQL Server Support Information: http://support.microsoft.com/ph/2855

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