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.