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.

High-level design

I cannot guide you how to do the high-level design of your SQL but I can pick a several questions that architect should ask.

Architectural design questions you need to ask

Use AlwaysOn Failover Cluster Instances or AlwaysOn Availability Groups

AlwaysOn Failover Cluster Instances (FCI) is the standard Failover Clustering for the SQL Server. AlwaysOn Availability Groups (AG) is the new technology but that does not mean that it is suitable for all environments.

Advantages

• AG do not require shared resources. When you have multiple datacenters in different locations then you do not need storage replication and it gives you large costs reductions and more flexibility for DB admins.
• You can easily have multiple copies (synchronous and asynchronous mirrors)
• AG gives you ability to have read only copy of you DB so you can run backup and reporting services on the servers that are not in the production.

Disadvantages

• Does your application support it?
• AG does not support cross-database transactions or distributed transactions.
• AG doubles (tripples, quadrupples) the size of data and usually doubles IOPS.
• AG requires Full Recovery mode.
• AlwaysOn Availability Groups are not for everyone however advantages are great so you should consider using it.

Is it possible to use AlwaysOn Availability Groups for old applications?

• You may want to have only one SQL cluster for all applications. For old applications that were designeed before Microsoft introduced AlwaysOn Availability Groups and for new applications that support it.
• It is possible to point old applications to new SQL cluster with AlwaysOn Availability Groups but of course you will lose new AlwaysOn Availability Groups features like reading from the secondary replicas.
• If you have some old application with the need for highly-available SQL and you do not want to build separate SQL Failover Cluster then you can target it to the SQL cluster with AlwaysOn Availability Groups. But your application have to follow a few rules:
  • Connection String
    • The application has to be able to set any connection string (you have to be able to modify the connection string).
  • Microsoft Distributed Transaction Coordinator (MSDTC)
    • If the application uses MS DTC then it is not possible to use AlwaysOn Availability Groups. In this case the application has to use the standard clustered SQL resources (AlwaysOn Failover Cluster Instances).

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 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 instances and shared resources).

FILESTREAM

• Consider if you are able to use it.

Drive letters and mount points

Please continue to the guide how to create and use the mount points.

• You can add a few cluster disks and assign driver letters for a small DB cluster.
• For the high-end DB cluster you usually need a lot of disks and you are limited with 26 drive letters (23 if you do not count A, B and C).
  • Create one small disk for every cluster resource group and name it for example as Mount Point and assign for example M drive letter.
  • Add mount point (mount a volume to a folder) for every cluster disk.

Data directories

• It is a good practice to install SQL Server on a different volume where the operation system is not located (C volume) or page file (C or some different volume). Partitions and volumes are important to guarantee that for example the D volume becomes full for any reason then the operating system will not be affected.
• It is a good practice to separate Temp DB database and log files because SQL Servers in production environment usually use it heavily.
• You may even want to separate separate one database data or log files into multiple LUNs because smaller LUNs usually have better performance but this is a complex topic and you ahould discuss these things with your DB architects.
• Example
  • Data root directory: D:\Program Files\Microsoft SQL Server\
  • User database directory: D:\Users\<Name of the instance>\Data
  • User database log directory: D:\Users\<Name of the instance>\Data
  • Temp DB directory: F:\Temp\Database\<Name of the instance>\Data
  • Temp DB log directory: G:\Temp\Log\<Name of the instance>\Data
  • Backup directory: E:\Backup\<Name of the instance>\Data

Environment

Virtual machines

• contdb1c0
  • Name of the cluster (Access Point for Administering the Cluster).
  • Nodes
    • contdb1c0n0
    • contdb1c0n1
    • contdb1c0n2
      • Microsoft SQL Server 2012 SP1
        • AlwaysOn Availability Groups
        • 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: scvmm0
        • Collation: SQL_Latin1_General_CP1_CI_AS
      • .NET Framework 3.5
      • Firewall
        • Open
          • 1433 (SQL Server)
          • 5022 (AlwaysOn DB mirroring)
  • Roles
    • agscvmm0
      • AlwaysOn Availability Group Listener
      • DNS: contdb1c0agvmm.ad.contoso.com

Service accounts, user accounts and security groups

SQL DB Servers – Service accounts

• serdb1scvmm0d
  • SQL Server Database Services (Database Engine)
• serdb1scvmm0a
  • SQL Server Agent

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.