SQL Server High Availability – DBCC 2021 – Part 1

Any one of my customers has asked, or should have asked, “do I need a certain level of high availability for my SQL server or one of my SQL servers?” Regrettably, these deliberations often come to a halt when it comes to the budget… Naturally, such HA-solutions are more expensive, and depending on the solution chosen and the environment’s configuration, appropriate licenses must be made available. However, if one calculates the costs that will occur in the “worst-case” scenario during or as a result of an outage, it should be clear that these costs are reversible.

I had the opportunity to give a speech about it last week at the Data Blaster Community Conference 2021 (thank you, SQLPASS Deutschland), and here is a summary of what I spoke about.

I’ve shown right from the start how high such costs can be, and I’ve done so with the help of a Percona online calculator. The SQL Server goes down for 1-2 hours, 5 employees are assigned to the Restore, each earning $100,000 per year, and 100 additional employees cannot work correctly, earning an average of $50,000 per year. In the following screenshot, you can see the numbers that can occur during an emergency, during recovery, or as a result of the trouble.

Why does SQLServer High Verfuegbarkeit matter? - Percona Calculator

With total costs of about 8.000.000 Euros for a 2 hour SQL Server outage, I’d consider spending an additional 250.000 Euros for the installation to add a second server and equip it with the required licenses. But, as I’ll explain in the following sections, such high availability can be seen in the SQL Server setup dialog, but first, let’s clear up the terminology… 😉

Disaster Recovery vs. High Availability

On wikipedia.de, the term “high availability” is described as follows:

High availability (HA) is a characteristic of a system that aims to ensure an agreed level of operational performance, usually uptime, for a higher than standard period.

https://en.wikipedia.org/wiki/High_availability

In comparison, the concept of disaster recovery.

Disaster Recovery assumes that the primary site is not recoverable (at least for some time) and represents a process of rebuilding data and services to a secondary survived site, which is opposite to restoring back to its original place.

https://en.wikipedia.org/wiki/Disaster_recovery

When it comes to high availability, it’s all about keeping the time of an outage or downtime as low as possible so that all connected systems can keep running as quickly as possible without data loss or manual intervention. Naturally, before the installation, you should think about the process design, the goals to be achieved, and the need.

  • What is the purpose of the business?
  • What would it cost the company if they are unable to work?
  • Is there, on the whole, a compromise between technology and business? Does it really have to be the minimum downtime (<10 seconds), or is it possible that maximum downtimes of less than a minute are sufficient?
  • What is it that technology can accomplish?
  • Knowledge, processes, and all those must all be followed to the letter of the law?

And only after these questions have been answered can you think about the actual solution for high availability and how it can/should be implemented.

The AlwaysOn Failover-Cluster (FCI)

High Availability - SQL Server - AlwaysOn Failover Cluster

We now come to one of the possible solutions for achieving high availability of SQL Servers inside your own datacenter, the Windows Failover Cluster (basically identical to a Linux Failover Cluster, but you should refer to the documentation of the respective distribution and cluster software).

Initially, a Windows Failover Cluster is created from two Windows Servers with the additional installed Failover-Cluster feature, or, to put it another way, both servers are logically (and in AD) connected. Both are familiar with one another and are now aware that they belong together, exchanging several relevant health-status information. This Windows Failover Cluster is given its own IP address and DNS name and a Cluster-Named-Object (CNO) in Active Directory, which later “manages” the cluster.

I can’t say anything about the required storage requirements because it varies depending on the environment… Whether it’s a SAN, NAS, NFS, HCI, or something else, it’s always about a storage system that can give both servers access. Microsoft adds the following:

Contrary to the availability group, an FCI must use shared storage between all nodes of the FCI for database and log storage. The shared storage can be in the form of WSFC cluster disks, disks on a SAN, Storage Spaces Direct (S2D), or file shares on an SMB. This way, all nodes in the FCI have the same view of instance data whenever a failover occurs. This does mean, however, that the shared storage has the potential of being the single point of failure, and FCI depends on the underlying storage solution to ensure data protection.

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver15&WT.mc_id=DP-MVP-5002576#FCIelements

Now you can start installing SQL Servers; on Node 1, you’ll set up the foundation for a SQL Server Failover Cluster (Installation of a Failover-Cluster), while on Node 2, you’ll only add a node to an existing SQL Server Cluster.

Depending on how this cluster is set up – whether it’s only one instance or many – SQL Server licenses must be acquired. You can choose between two operating modes: active/passive or active/active. When using active/passive, SQL Server instances can only run on one cluster node, but they can run on both nodes for a limited time in the event of a failure. When using active/active, all nodes can be used equally and for an unlimited amount of time, which is especially useful when dealing with several instances because it allows some kind of load-balancing.

If a node in the cluster breaks, the second node can no longer communicate with the other side and tries to get the broken services into his side as quickly as possible and start them there.

Unfortunately, a hybrid scenario is not possible due to the lack of scalable resources on-premise and in the cloud. However, a Failover-Cluster can be built in the Azure Cloud as an alternative. To do so, you’ll need a Proximity Placement Group, Managed Disks with the “Shared Storage” feature enabled, and the two virtual machines described above, whose installation and operation are essentially identical.

High Availability - Azure SQL Server Failover Cluster - DBCC2021

More on SQL Server high availability will be covered in a next blog post.

TempDB-Performance-Tuning out of the box – T-SQL Tuesday #87

TempDB-Performance-Tuning out of the box - T-SQL Tuesday #87

T-SQL Tuesday is a recurring blog series which was launched by Adam Machanic (b | t). Every month, a blogger is hosting a topic around the SQL Server and anyone can write a blog post on this particular topic.

This month, Matt Gordon (b | t) our host and the topic is about new features in the SQL Server (starting with the 2014 version) which fixes old issues, see more details in his announcement post.

My topic for this TSQL2sDay is the new possibility to configure the TempDB as part of the SQL Server installation.

much easier Implementation of the Best Practice for TempDB

Previously, after you’ve finished a SQL Server installation, you had to implement best practice recommendations from Microsoftthe best practice recommendations from Microsoft in a more or less complicated way. Either you approach the topic in a more conventional way and adjust the values manually via the SQL Server Management Studio or you had made it a little bit easier to automate this by a T-SQL or Powershell script. But still, you had to add these configuration adjustments every time to achieve an optimized TempDB performance.

Essentially the TempDB performance comes basically with the hardware configuration, in this case, the physical separation between user databases and TempDB by transferring the TempDB onto a high-performance storage. Microsoft recommends the strict separation of UserDB and TempDB, not only through separate folder structures or separate partitions but should by separated (fast) disks. There is no need for a great RAID configuration, a RAID 1 or RAID 10 would be great, but no real need, because the TempDB is always “re-created” when restarting SQL Server, so it would not be a “drama” when it is corrupt or the discs below would crash. (Ok, here I don’t care about the possible loss of data and time).

Overview of Best Practice recommendations

So many adjustments are not necessary to get a better performance of TempDB, I would like to briefly summarize this:

  • Physical separation of user databases and TempDB
  • Splitting the TempDB into individual data files according to the number of processors
  • Autogrowth parameter adjustments
  • Trace flag depending on usage / requirement

Once this physical optimization has been implemented in the storage area, you can make the logical adjustments in the configuration. Depending on the processor/core count, Microsoft also recommends splitting data files into several separate files instead of one large data file in order to optimize the performance of the IO streams (parallelization). Likewise, depending on the application or requirements, the data files can initially be set to a fixed size so that the internal structure of the data files are optimized and all files are the same size. (Similar to trace flag 1117/1118). Otherwise, the Autogrowth parameter should be set to 10% so that the data files in conjunction with Trace flags 1117 and 1118 contribute to an optimized, uniform structure of the TempDB.

SQLServer2016 - Configure TempDB during Installation

SQL Server 2016 brings integrated simplifications

The SQL Server 2016 now brings this whole logical optimization right to the installation process… now you can tell the SQL Server how to set the number and distribution of TempDB data files and their auto growth parameters within the installation steps. Microsoft has finally integrated the long-published Best Practices recommendations into the installation process. So you only have to create the hardware prerequisites for the TempDB and can start to install.
Ok, here, too, you have to worry about which values you want to set, but at least you do not have to worry about these configurations afterward!

THANKS to Microsoft for this improvement.

During the installation process, you can set how many data files you want to create, the initial size and the Autogrowth parameter. Theoretically, you are even able to name a separate path for each individual data file (division by the number of cores) or even a separate (SSD) disc. Everything is displayed on a clear screen and can be used also from an inexperienced DBA.

Through this new “Toy” some old “problems” could be reduced … 😉

SQL Server 2016 – IFI activation and TempDB optimization

Now that the SQL Server 2016 officially released, more and more details coming to light, so that you have to get slowly prepared for your first “real” installation. What has changed compared to SQL Server 2014 in part of installing everything? How should we adapt any existing documentation? When reading numerous tweets and blogs I stumbled across a Tweet to SQL Server 2016 by Thomas Larock, he has created a very nice overview of varieties of new features in SQL Server 2016, which can be used e.g. for increasing the performance of SQL Server. Previously you had a variety of parameters and settings to optimize performance before installation (for example, Instant File Initialization) or adjust the number of TempDB files after installation. I would like to respond to the changes in the context of the SQL Server 2016 installation, which I am personally very pleased to facilitate a part of my work.

Instant File Initialization

Previously you had always to configure some parameters BEFORE installation of SQL Server the local security policy with the system tool “secpol.msc” in order to grant permissions to SQL service user on “Perform Volume Maintenance Tasks / Perform volume maintenance tasks”. First, open the tool, then click on a tree and still find the right authority, and then locate the user in the user management … a really complicated way to the full write new files or file spaces to get around to so as the Autogrowth Event to accelerate …

Now with the new SQL Server 2016, it is much easier, because the installation process does the work for us now. Here it was Microsoft, which integrated such configuration optimally which was published through community over years as best practices, and applied in the installation process in order to create an easy opportunity… tada…

SQLServer2016 Instant File Initialization

Now during installation, you can simply tick and installation process takes care of the work for you. Many thanks for this to Microsoft!
If you install SQL Server 2016 from the command line, you also have the option specifying this parameter either in the ConfigurationFile.ini when calling the “Setup.exe” or directly as an option on the call

/SQLSVCINSTANTFILEINIT=”True”

Automatic TEMPDB Configuration

The configuration of the TempDB after the installation always took a little time of course, you could script it so far, but it was never as easy as it was before, it still meant a certain effort. Microsoft has also moved this configuration into the installation process so that you do not have to call another script after the installation.

SQLServer2016 Config TempDB

In this new tab under Database Engine Configuration, you can set the number of TempDB files, their initial size and auto-growth value. You can customize the paths of both file types individually, theoretically, you can also specify several paths for the TempDB data files. All these new configuration parameters could be passed to sql server installation via the ConfigurationFile.ini or command line.

/SQLTEMPDBFILECOUNT=”8″ /SQLTEMPDBFILESIZE=”16″ /SQLTEMPDBFILEGROWTH=”256″ /SQLTEMPDBDIR=”C:\tempdb” “D:\tempdb” /SQLTEMPDBLOGFILESIZE=”256″ /SQLTEMPDBLOGFILEGROWTH=”0″ /SQLTEMPDBLOGDIR=”E:\tempdblog”

The above optimization (or better simplifications) makes the installation and performance-oriented configuration of SQL Server much more comfortable. However, it also has a small drawback (*wave*), the DBAs lose some of their influence since now everyone has the opportunity to operate this performance screws.

TSQL2sDay #80 – my open point list for more SQL Server knowledge

For T-SQL Tuesday #80, Chris Yates (b|t) asks “Is there something you’ve been wanting to do for a long time in SQL, but haven’t done it yet?”.

Yes, of cause 😉
There are some many topics I want to learn, to talk and to present about in the (near) future. Just to give you a quick look onto my open point list (see blog post picture) for SQL server topics to learn and write about.
SQL Server 2016 is a really awesome kind of software which includes so much new and very exciting topics which I want to get in touch with… ok, not all my open points are new in SQL server 2016 but very interesting and from earlier versions but I wanna learn them and then share my new knowledge with my colleagues.

  • SQL Server 2014 / 2016 – ColumnStore
  • SQL Server 2016 – Encrypted / Always Encrypted
  • SQL Server 2016 – Temporal Tables
  • SQL Server 2016 – Analytics with R

Or just the new tools around SQL Server like Power BI (Desktop).

Actually, I’m working on my Azure skills … it’s awful… just finished an MVA-course which opened several new topics I had to read about and get deeper into it.
Last month I had visited SQLSaturday Rheinland #525 and learn from Andre Kamann (b|t) new stuff about Desired State Configuration (DSC) that was also very very interesting and might be helpful for my daily business… So you can see (read) I’ll need more than 24h a day 😉

Also, Andreas Wolter spoke about a topic which could help me in future at my job => the new SQL Server 2016 QueryStore…

So who can tell me where to start with all those hot things? How to start with all this?

Finally, I would like to thank the community that all always share their knowledge and new information with us.

THANK YOU !!!

TSQL Tuesday Logo

PS: It is the birthday of our actual Host Chris Yates… Happy Birthday to you, I wish you all the best.

TSQL – Getting important information about SQL Server instance

We all know it, we are to provide information on our SQL servers … either the customer or a colleague will quickly have an overview of the most important parameters of the SQL Server … What is closer than times since just quickly a TSQL statement on performing the requested server?

The last of such requests would have always found the same information grouped together or … when I’ve compiled using TSQL a “short” script and adapted to my needs. I or we use this script now also for our own documentation;-)

  • Server- / Instance name
  • last SQL Server Engine Start
  • which SQL Server Edition / Version
  • which values for CPU / Hyper thread / maxDOP are configured
  • Configuration of Memory Restriction/Usage
  • Count / Names / Sizes of User-Databases
SET NOCOUNT ON;
Go
-- Setting value of 'show advanced options' to 1
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- DECLARATION
SET NOCOUNT ON
DECLARE @date datetime, @start int, @ver varchar(13), @config_value varchar(20), @run_value varchar(20), @sqlstart datetime, @Mem int, @optimal_maxdop int, @cpu_count int, @scheduler_count int, @hyperthread_ratio int
DECLARE @CM Table ( [Index] int, Name nvarchar(1000) NOT NULL, Internal_Value int, Character_Value nvarchar(1000) )

-- SET Current Date/Time
SELECT @date = getdate()

-- Get SQL Server Version
SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2005',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,13))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,35,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008 R2',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,30,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2012',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2014',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2016',@@version)
if @start = 1 SELECT @ver = rtrim(substring(@@version,29,12))

-- Get Informations and Calculation on MaxDOP
CREATE TABLE #MDP (
name nvarchar(1000),
minimun int NOT NULL,
maximun int NOT NULL,
config_value int NOT NULL,
run_value int NOT NULL
)
Insert into #MDP exec sp_configure 'max degree of parallelism'
SELECT @config_value=rtrim(convert(varchar(8),config_value)) ,@run_value=rtrim(convert(varchar(8),run_value)) from #MDP
DROP TABLE #MDP

-- Last SQL Server Start Date/Time
select @sqlstart = create_date from sys.databases where name = 'Tempdb'

-- Get Informations on CPU, Schedulers and Memory
Insert into @CM exec xp_msver select @Mem = Internal_Value from @CM Where Name = 'PhysicalMemory'
select
@scheduler_count=(SELECT count(*) FROM sys.dm_os_schedulers WHERE scheduler_id < 255)
,@cpu_count=cpu_count
,@hyperthread_ratio=hyperthread_ratio
,@optimal_maxdop=case
when @scheduler_count >= 8 then 4
when @scheduler_count > 8 then 8
else CEILING(@scheduler_count*.5) end
from sys.dm_os_sys_info;

-- Main Statement
SELECT
CONVERT(CHAR(50), SERVERPROPERTY('MachineName')) AS [Hostname]
,isnull(CONVERT(CHAR(50), SERVERPROPERTY('InstanceName')), 'Default') [InstanceName]
,@@servername as [Servername]
,getdate() as [Current Date/Time]
,@sqlstart as [last SQL Srv Start]
,serverproperty('Edition') as [SQL Edition]
,@ver as [SQL Version]
,case serverproperty('IsClustered') when 0 THEN 'NO' when 1 THEN 'YES' end as [IsCluster]
,@cpu_count/@hyperthread_ratio as [CPU Count]
,@config_value as [MDP cfg]
,@run_value as [MDP run]
,(SELECT count(*) FROM sys.dm_os_schedulers WHERE scheduler_id < 255) as [No of Scheduler]
,@optimal_maxdop as [Optimal MDP]
,@Mem as [Memory (MB)]
,(SELECT value_in_use FROM sys.configurations WHERE name like 'min server memory (MB)') as [actual min memory]
,(SELECT value_in_use FROM sys.configurations WHERE name like 'max server memory (MB)') as [actual max memory]
,(select COUNT(name) AS MB from master..sysdatabases) AS [No Of DBs]
,(select SUM(size*8/1024) AS MB from master..sysaltfiles where fileid = 1 and dbid > 4) AS [Overall Database Size (MB)]
Go

-- Adding Informations for all User Databases
-- Declaration
DECLARE @SumDBSize VARCHAR(2)
DECLARE @temp INT
DECLARE @DBSize INT
DECLARE @Servername varchar(100)

-- Get/Set Servername
Set @Servername = ''
If @Servername = '' Set @Servername = @@SERVERNAME

-- Calculating DBSize
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[master].[dbo].[DatabaseFiles]'))
Set @temp = (SELECT round(SUM(db.size)/1024/1024,0) FROM [master].[dbo].[DatabaseFiles] as db where DATEDIFF(day, db.CreationDate, GETDATE()) = 0)
else Set @temp = (SELECT sum((size*8)/1024/1024 ) FROM sys.master_files)
Set @temp = (SELECT sum((size*8)/1024/1024 ) FROM sys.master_files)

Select
@Servername as 'Servername',
DB_NAME(sys.master_files.database_id) as 'DBName',
sum(sys.master_files.size * 8 / 1024 ) as 'DB-Size/MB',
sum(sys.master_files.size * 8 / 1024 / 1024 ) as 'DB-Size/GB'
from sys.master_files INNER JOIN sys.databases ON DB_NAME(sys.master_files.database_id) = DB_NAME(sys.databases.database_id) where DB_NAME(sys.master_files.database_id) not in ('master','model','msdb','tempdb') group by sys.master_files.database_id;
go

-- Reset value of 'show advanced options' to 0
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

Thanks to  for its contribution to “Best Practices Recommendation for MaxDOP

This TSQL, of course, can be used freely but should be tested in advance on a test system, hazard/risk borne by the performer.