SQL Server–Tiered Server Installs And Automated Deployments

An idea I’ve been playing with for a while and saw at the recent SQL Saturday Pass sessions is with creating Tiered SQL Server installs. For me this comes out of the increasing use of virtualization and the exponentially increasing number of SQL Servers this spawns. Without some sort of system in place it becomes incredibly difficult to standardize our SQL Server installs and we end up with a non-uniform environment.
The idea is relatively simple in theory – in order to standardize our SQL Server environments we define a number of tiers. The tiers should relate to how we use SQL Server in our environments but at the same time we want them to be standard across a tier. Each tier should add to the previous tier in terms of functionality and performance.
Furthermore by standardizing our installs into tiers we can also automate our SQL Server deployments. The approach I’ll be taking will be to develop a template that can be automatically deployed to a base tier. Ideally the server can easily be upgraded to higher tiers through upgrade wizards and by reconfiguring the resources in the hypervisor.
First I’m going to define the tiers I have in mind and how I want them to look.

Tier 3 – Entry-Level SQL Server Platform

The Tier 3 server is more designed with system administrators, developers, and non-DBAs in mind. It may also be necessary if the SQL Server is an application server which needs applications installed that are cannot run on Windows Core.

Operating System	Windows Server Standard Edition w/GUI
SQL Server Edition	2012 SP1 CU2 or greater, Standard Edition
Components Installed	SQL Engine Replication Full Text Indexing Integration Services Management Studio
Drive Layouts	C - Operating System E – Data L – Logs
Processors	Start at 1 vCPU. Can be customised higher.
Memory	Start at 2GB. Can be customised higher.

Tier 2 – The Mid Range SQL Server Platform

The Tier 2 standard is designed for non-critical business applications and is a compromise between performance and resources.
By using the core editions of SQL and Windows we save on resources. Windows Server 2008 R2 uses 2 to 4GB compared to around 600MB on Windows Server Core. Across many virtual machines this is a huge saving. The core editions also require far fewer patches (and hopefully restarts) compared to the editions with full GUIs.

Operating System	Windows Server Standard Edition (Core)
SQL Server Edition	2012 SP1 CU2 or greater, Standard Edition
Components Installed	SQL Engine Replication Full Text Indexing Integration Services
Drive Layouts	C - Operating System E – Data L – Logs T- TempDB
Processors	Start at 1 vCPU. Should be customised higher.
Memory	Start at 2GB. Should be customised higher.

Tier 1 –The High Performance SQL Server Platform

The high performance Tier is designed for high performance SQL applications but doesn’t provide for much in the way of high availability. I’ve deliberately left the operating system at Standard Edition as in many virtual environments the high availability is rightly or wrongly provided at the virtualisation layer.
The Enterprise edition of SQL Server provides much better performance over the Standard edition in a number of areas both in terms of internal engine performance and additional features to boost performance.
Of note is that both the processor and memory should be customised to suit the workloads on the server. The figures quoted below are simply a starting point and need to be revised accordingly.

Operating System	Windows Server Standard Edition (Core)
SQL Server Edition	2012 SP1 CU2 or greater, Enterprise Edition
Components Installed	SQL Engine Replication Full Text Indexing Integration Services
Drive Layouts	C - Operating System E – Data L – Logs T – TempDB
Processors	Start at 1 vCPU. Should be customised higher.
Memory	Start at 2GB. Should be customised higher.

Tier 0 – Ultra-High Performance SQL Server Platform

This tier is very similar to Tier 1 except it has additional features added for High Availability. The main difference is that we’re now using Windows Server Enterprise edition which is mandatory for clustering and AlwaysOn technologies.
Of note is that both the processor and memory should be customised to suit the workloads on the server. The figures quoted below are simply a starting point and need to be revised accordingly.

Operating System	Windows Server Enterprise Edition (Core)
SQL Server Edition	2012 SP1 CU2 or greater, Enterprise Edition
Components Installed	SQL Engine Replication Full Text Indexing Integration Services
Drive Layouts	C - Operating System E – Data L – Logs T- TempDB
Processors	Start at 1 vCPU. Should be customised higher.
Memory	Start at 2GB. Should be customised higher.

Using SQL Server Templates

Now that we’ve defined the tiers I want to build a SQL Serer template that can easily be deployed into these tiers. It’s a work in progress but I’m working on templates which fit into the Tier 2 and Tier 3 categories. The idea is to build fully sysprepped images(supported by SQL Server 2012 SP1 CU2 and onwards) which can easily and quickly be deployed to the standards.
To upgrade from Tier 2 to Tier 1 we would simply run the SQL Server Edition Upgrade to turn it into a SQL Enterprise server. We could also move the virtual disks to higher performing disk sets to upgrade performance.
Can we upgrade from Tier 1 to Tier 0? There is a tool to upgrade Windows editions by entering the new product key so this should be easy to accomplish.

Handy Date Functions

These date functions are especially useful for BI and reporting type scenarios. Used properly they can simply SQL code making it much, much easier to read. Used properly they can also make SQL queries perform better!!!

First let’s install the date functions by running the following code snippets:

1. The first day of the month

   1: SET ANSI_NULLS ON

   2: GO

   3: SET QUOTED_IDENTIFIER ON

   4: GO

   5: -- =============================================

   6: -- Author:        William Brown

   7: -- Create date: 2013-04-29

   8: -- Description:    Return the first day of the month

   9: -- For more information check out

  10: -- http://yetanothercomputingblog.blogspot.com.au/

  11: -- =============================================

  12: CREATE FUNCTION fn_FirstDateOfMonth(@date DATETIME)

  13: RETURNS DATE

  14: AS

  15: BEGIN

  16:     

  17:     DECLARE @first_of_month DATE

  18:  

  19:     SELECT @first_of_month = CONVERT(DATE,DATEADD(dd,-(DAY(@date)-1),@date))

  20:  

  21:     RETURN @first_of_month

  22:  

  23: END

  24: GO

  25:  

2. The first day of the next month

   1: SET ANSI_NULLS ON

   2: GO

   3: SET QUOTED_IDENTIFIER ON

   4: GO

   5: -- =============================================

   6: -- Author:            William Brown

   7: -- Create date:        2013-04-29

   8: -- Description:        Return the first day of the next month

   9: -- For more information check out

  10: -- http://yetanothercomputingblog.blogspot.com.au/

  11: -- =============================================

  12: CREATE FUNCTION fn_FirstDateOfNextMonth(@date DATETIME)

  13: RETURNS DATE

  14: AS

  15: BEGIN

  16:     

  17:     DECLARE @first_of_month DATE

  18:  

  19:     SELECT @first_of_month = CONVERT(DATE,DATEADD(dd,-(DAY(DATEADD(mm,1,@date))-1),DATEADD(mm,1,@date)))

  20:  

  21:  

  22:     RETURN @first_of_month

  23:  

  24: END

  25: GO

  26:  

3. The first day of the year

   1: SET ANSI_NULLS ON

   2: GO

   3: SET QUOTED_IDENTIFIER ON

   4: GO

   5: -- =============================================

   6: -- Author:            William Brown

   7: -- Create date:        2013-04-29

   8: -- Description:        Return the first day of the year

   9: -- For more information check out

  10: -- http://yetanothercomputingblog.blogspot.com.au/

  11: -- =============================================

  12: CREATE FUNCTION fn_FirstDateOfYear(@date DATETIME)

  13: RETURNS DATE

  14: AS

  15: BEGIN

  16:     

  17:     DECLARE @first_day_of_year DATE

  18:  

  19:     SELECT @first_day_of_year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), 0)

  20:  

  21:     RETURN @first_day_of_year

  22:  

  23: END

  24: GO

  25:  

4. The first day of the next year

   1: SET ANSI_NULLS ON

   2: GO

   3:  

   4: SET QUOTED_IDENTIFIER ON

   5: GO

   6:  

   7: -- =============================================

   8: -- Author:            William Brown

   9: -- Create date:        2013-04-29

  10: -- Description:        Return the first day of the next year

  11: -- For more information check out

  12: -- http://yetanothercomputingblog.blogspot.com.au/

  13: -- =============================================

  14: CREATE FUNCTION [dbo].[fn_FirstDateOfNextYear](@date DATETIME)

  15: RETURNS DATE

  16: AS

  17: BEGIN

  18:     

  19:     DECLARE @first_day_of_next_year DATE

  20:  

  21:     SELECT @first_day_of_next_year = DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR,1,@date)), 0)

  22:  

  23:     RETURN @first_day_of_next_year

  24:  

  25: END

  26:  

  27: GO

5. The first day of the Australian financial year (1 Jul XXXX)

   1: SET ANSI_NULLS ON

   2: GO

   3:  

   4: SET QUOTED_IDENTIFIER ON

   5: GO

   6:  

   7: -- =============================================

   8: -- Author:            William Brown

   9: -- Create date:        2013-04-29

  10: -- Description:        Return the first day of the next year

  11: -- For more information check out

  12: -- http://yetanothercomputingblog.blogspot.com.au/

  13: -- =============================================

  14: CREATE FUNCTION [dbo].[fn_FirstDateOfFinancialYear](@date DATETIME)

  15: RETURNS DATE

  16: AS

  17: BEGIN

  18:     

  19:     DECLARE @first_day_of_financial_year DATE

  20:  

  21:     SELECT @first_day_of_financial_year = DATEADD(MM,6,DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), 0))

  22:  

  23:     RETURN @first_day_of_financial_year

  24:  

  25: END

  26:  

  27: GO

6. The first day of the next Australian financial year (1 Jul XXXX)

   1: SET ANSI_NULLS ON

   2: GO

   3: SET QUOTED_IDENTIFIER ON

   4: GO

   5: -- =============================================

   6: -- Author:            William Brown

   7: -- Create date:        2013-04-29

   8: -- Description:        Return the first day of the next year

   9: -- For more information check out

  10: -- http://yetanothercomputingblog.blogspot.com.au/

  11: -- =============================================

  12: CREATE FUNCTION fn_FirstDateOfNextFinancialYear(@date DATETIME)

  13: RETURNS DATE

  14: AS

  15: BEGIN

  16:     

  17:     DECLARE @first_day_of_next_financial_year DATE

  18:  

  19:     SELECT @first_day_of_next_financial_year = DATEADD(MM,6,DATEADD(YEAR, DATEDIFF(YEAR, -1, @date), 0))

  20:  

  21:     RETURN @first_day_of_next_financial_year

  22:  

  23: END

  24: GO

  25:  

Running the following code:

   1: set nocount on

   2: select dbo.fn_FirstDateOfMonth(getdate()) AS FirstDateOfMonth

   3: select dbo.fn_FirstDateOfNextMonth(getdate()) AS FirstDateOfNextMonth

   4: select dbo.fn_FirstDateOfYear(getdate()) AS FirstDateOfYear

   5: select dbo.fn_FirstDateOfNextYear(getdate()) AS FirstDateOfNextYear

   6: select dbo.fn_FirstDateOfFinancialYear(getdate()) AS FirstDateOfFinancialYear

   7: select dbo.fn_FirstDateOfNextFinancialYear(getdate()) AS FirstDateOfNextFinancialYear

Gives the following results:

   1: FirstDateOfMonth

   2: ----------------

   3: 2013-04-01

   4:  

   5: FirstDateOfNextMonth

   6: --------------------

   7: 2013-05-01

   8:  

   9: FirstDateOfYear

  10: ---------------

  11: 2013-01-01

  12:  

  13: FirstDateOfNextYear

  14: -------------------

  15: 2014-01-01

  16:  

  17: FirstDateOfFinancialYear

  18: ------------------------

  19: 2013-07-01

  20:  

  21: FirstDateOfNextFinancialYear

  22: ----------------------------

  23: 2014-07-01

  24: