It is not always easy to divide Low-level Design and High-Level Design. How to differentiate between high-level and low-level design depends on the entity that we refer to. When we need to design our cloud then high-level decision could be choice of the technology that makes our SQL Servers highly-available. But when we talk about our business or company (the entity) then SQL Servers are in Low-level Design category. This means that following division to High-level Design and Low-level Design are only within our entity.

AlwaysOn Failover Cluster Instances (FCI) is the standard Failover Clustering role of the SQL Server instance with a name.

Low-level Design

Questions you need to ask

ReFS vs. NTFS

  • ReFS does not support named streams and sparse files so we do not want to use it now and we need to stay with NTFS.

Allocation unit size

  • For performance reasons it is recommended to you use a 64 kB allocation unit size for DBs (data, logs, tempdb) .
  • Use default 4 kB for the volume with appplication data (installed SQL binaries and shared resources).

FILESTREAM

  • Consider if you are able to use it.

Virtual machines

  • contsqlfcic0
    • Name of the cluster (Access Point for Administering the Cluster).
    • Nodes
      • contsqlfcic0n0
      • contsqlfcic0n1
        • Microsoft SQL Server 2012 SP1
          • AlwaysOn Failover Cluster Instances
          • Features
            • Required
              • [x] Database Engine Services
            • Optional - Management Tools (I recommended to install them on management server only)
              • [x] Management Tools - Basic
                • [x] Management Tools - Complete
          • Named instance: SCCM0
          • Collation: SQL_Latin1_General_CP1_CI_AS
        • .NET Framework 3.5
        • Firewall
          • Open
            • 1433 (SQL Server)
    • Roles
      • SQL Server (SCCM0)
        • SQL Server Cluster Resource Group
        • contsqlfcic0cm0
          • SQL Server Failover Cluster

Service accounts, user accounts and security groups

SQL DB Servers - Service accounts

  • sercontsqlfcic0cm0a
    • Service account: SQL Server Agent
    • Member Of
      • Domain Users
  • sercontsqlfcic0cm0d
    • Service account: SQL Server Database Services (Database Engine)
    • Member Of
      • Domain Users
  • sercontsqlfcic0cm0sr
    • Service account: Reporting Services
    • Member Of
      • Domain Users

SQL DB Servers - Security groups

  • dbadmins0
    • Security group: DB Administrators
    • Members
      • You and other DB admins that are allowed to work on the SQL Server.
« Previous Next »

Part of the series

  1. Building guest (virtual) Failover Cluster on Hyper-V host with Windows Server 2012 R2 - Part 1 - Create virtual machines
  2. Building guest (virtual) Failover Cluster on Hyper-V host with Windows Server 2012 R2 - Part 2 - Install and configure cluster
  3. Building Microsoft SQL Server AlwaysOn Failover Cluster Instances - Part 2 - Distributed Transaction Coordinator (MSDTC)
  4. Building Microsoft SQL Server AlwaysOn Failover Cluster Instances - Part 3 - Configure storage and mount points
  5. Building Microsoft SQL Server AlwaysOn Failover Cluster Instances - Part 4 - Install first node
  6. Building Microsoft SQL Server AlwaysOn Failover Cluster Instances - Part 5 - Install second node
  7. Building Microsoft SQL Server AlwaysOn Failover Cluster Instances - Part 6 - Install another instance
  8. Building Microsoft SQL Server AlwaysOn Failover Cluster Instances - Part 7 - Manually configure Reporting Services