By: Aaron Bertrand | Updated: 2021-06-16 | Comments | Related: > Dates
Problem
In two previous articles (part 1 and part 2), I showed some practical ways to use a SQL Server calendar table to solve business day and date range problems. Next, I'll demonstrate how you can use this table to solve scheduling problems, like environment-wide server patch management.
When you have a handful of servers, managing patching is straightforward. When you have hundreds, it gets more complicated to balance keeping everything in sync and not spending all of your time organizing and performing patching.
Solution
Last year, we made a concerted effort to combine Windows and SQL Server patching (instead of treating them as independent tasks). The primary goals of this change were to reduce downtime, minimize labor, and simplify logistics. Combining them, however, did not simplify scheduling, as there are many business rules we still need to follow. These rules range from how many servers our teams can patch in a day to how long a server can go without patching. Some fictitious examples for the purposes of this article:
- Patching teams can only patch five servers per day
- No more than two servers from the same server "pool" can be patched
on the same day
- A "pool" is a group of related servers, such as a set of cache warmers for a distributed application
- No patching on Fridays, weekends, holidays, or during deployment freezes
- Servers and server pools have different tolerance levels for going unpatched – 30, 45, 60, or 90 days
I could put 400+ servers in a spreadsheet and manually work through all of those rules, but that would take a long time – and could turn into an endlessly complicated and annoying game of whack-a-mole. A calendar table is a better solution for this problem.
Let's say we store metadata about our servers and server pools in a set of tables:
CREATE TABLE dbo.ServerPools
(
ServerPoolID tinyint NOT NULL,
Name nvarchar(128) NOT NULL,
CONSTRAINT UQ_ServerPools_Name UNIQUE(Name),
CONSTRAINT PK_ServerPools_ID PRIMARY KEY(ServerPoolID)
); INSERT dbo.ServerPools(ServerPoolID, Name)
VALUES (1, N'Server Pool for AppA'),
(2, N'Server Pool for AppB'),
(3, N'FCI for AppC'),
(4, N'AG for AppD'),
(5, N'Secure Server Pool'); CREATE TABLE dbo.Servers
(
ServerID smallint IDENTITY(1,1) NOT NULL,
ServerPoolID tinyint NOT NULL,
Name nvarchar(128) NOT NULL,
CONSTRAINT UQ_Servers_Name UNIQUE(Name),
CONSTRAINT PK_Servers_ID PRIMARY KEY(ServerID),
CONSTRAINT FK_ServerPools FOREIGN KEY (ServerPoolID)
REFERENCES dbo.ServerPools(ServerPoolID)
); INSERT dbo.Servers(ServerPoolID, Name)
VALUES (1, N'AppA_01'),
(1, N'AppA_02'),
(2, N'AppB_01'),
(2, N'AppB_02'),
(2, N'AppB_03'),
(2, N'AppB_04'),
(3, N'FCI_C_Node_01'),
(3, N'FCI_C_Node_02'),
(4, N'AG_D_Node_01'),
(4, N'AG_D_Node_02'),
(5, N'Secure_01'),
(5, N'Secure_02');
To establish some rules around how long we delay patching a server, we create a table called PatchingRules:
CREATE TABLE dbo.PatchingRules
(
ServerID smallint NULL,
ServerPoolID int NULL,
MaxGapInDays int NOT NULL DEFAULT (90)
); CREATE UNIQUE CLUSTERED INDEX UQ_PatchingRules
ON dbo.PatchingRules(ServerID, ServerPoolID); INSERT dbo.PatchingRules(ServerPoolID, ServerID, MaxGapInDays)
VALUES (NULL, NULL, 90), -- default
(1, NULL, 45),
(3, NULL, 60),
(4, NULL, 60),
(5, NULL, 30); -- secure pools every 30 days!
This data allows us to determine what is the absolute last calendar day we can patch a given server or pool (based on the last time it was patched), and still be in compliance with our rules. While we don't necessarily want to put off patching, we do want to optimize for patching a server as late into its schedule as possible. If we patch a server too soon, that just makes its next patch an earlier priority.
Whenever we patch a server, we record the action in a PatchingHistory table. I'll populate it now with some sample data that conveniently makes all the servers due for patching on the same day:
CREATE TABLE dbo.PatchingHistory
(
ServerID smallint,
Started datetime2(2),
Finished datetime2(2),
Details nvarchar(4000), -- we can leave this blank for now
CONSTRAINT FK_Servers_ID FOREIGN KEY (ServerID) REFERENCES dbo.Servers(ServerID),
INDEX CIX_DatesServerID CLUSTERED(Started, Finished, ServerID)
); INSERT dbo.PatchingHistory(ServerID, Started, Finished)
VALUES (1, '20210511','20210511'), (2, '20210511','20210511'),
(3, '20210327','20210327'), (4, '20210327','20210327'),
(5, '20210327','20210327'), (6, '20210327','20210327'),
(7, '20210426','20210426'), (8, '20210426','20210426'),
(9, '20210426','20210426'), (10,'20210426','20210426'),
(11,'20210526','20210526'), (12,'20210526','20210526');
To simplify queries, I've created a view to present the last and next patch dates for a given server:
CREATE OR ALTER VIEW dbo.ServerPatchDetails
AS
SELECT s.ServerPoolID,
s.ServerID,
s.Name,
PatchDelayInDays = MIN(pr.MaxGapInDays),
LastPatch = CONVERT(date, MAX(ph.Finished)),
NextPatchDeadline = CONVERT(date, DATEADD(DAY, MIN(pr.MaxGapInDays), MAX(ph.Finished)))
FROM dbo.Servers AS s
LEFT OUTER JOIN dbo.ServerPools AS sp
ON sp.ServerPoolID = s.ServerPoolID
LEFT OUTER JOIN dbo.PatchingRules AS pr
ON (sp.ServerPoolID = pr.ServerPoolID OR pr.ServerPoolID IS NULL)
AND (s.ServerID = pr.ServerID OR pr.ServerID IS NULL)
LEFT OUTER JOIN dbo.PatchingHistory AS ph
ON s.ServerID = ph.ServerID
GROUP BY
s.ServerPoolID,
s.ServerID;
GO
(To account for servers that have never been patched, you could use their creation date or some arbitrary date as the last patch, or just manually bump them to the top of the priority list.)
If we look at the output from the view, we see all 12 servers are represented, and all 12 have a June 25th deadline to be patched:
While you could use a separate table for this, I'm going to store information about deployment freezes in our OutageDates table (described in an earlier tip). The next deployment freeze conveniently falls on the same day all our servers are due for patching (and it also happens to be a Friday):
INSERT dbo.OutageDates(TheDate, Description)
VALUES('20210624', N'June Deployment Freeze'),
('20210625', N'June Deployment Freeze');
Next, I'll create a view called ValidPatchingDays which restricts our original Calendar table to the days where we can perform patching (not on Fridays, weekends, holidays, or other outages like deployment freezes):
CREATE OR ALTER VIEW dbo.ValidPatchingDays
AS
SELECT TheDate
FROM dbo.Calendar AS c
WHERE IsWeekend = 0
AND TheDayName <> N'Friday'
AND NOT EXISTS
(
SELECT 1 FROM dbo.OutageDates AS od
WHERE od.TheDate = c.TheDate
);
GO
When we combine the ServerPatchDetails view with the ValidPatchingDays view, we can determine the last possible day a server can be patched to stay within its rules, and be patched on a day when we can perform patches:
SELECT spd.ServerID, spd.ServerPoolID, spd.LastPatch,
spd.PatchDelayInDays, spd.NextPatchDeadline, x.TheDate
FROM dbo.ServerPatchDetails AS spd
CROSS APPLY
(
SELECT TOP (1) TheDate
FROM dbo.ValidPatchingDays
WHERE TheDate <= spd.NextPatchDeadline
ORDER BY TheDate DESC
) AS x;
Output:
You can see that the last possible day to patch any server is June 22nd. The problem is, we can't patch all of those servers on June 22nd, both because we're limited to patching 5 servers a day, and we also can't patch all four servers in pool 2 on a single day. We need to introduce some ranking to bump servers back to a previous (also valid) day when necessary. Again, the calendar table can help us here, because we can get all valid patching days, as far back as we need. But how can we apply multiple business rules simultaneously?
Originally, I tried to solve this in a single, set-based query, using a variety of windowing functions. I was able to avoid violating the rules for certain input parameters, but the schedule wasn't optimized. For example, additional days were added to the schedule just because ranking wasn't optimal. And for some combinations of parameters, the business rule validation broke down. After some discussions with good friend and T-SQL guru Itzik Ben-Gan, the most sensible approach to applying the business rules properly – while also optimizing for populating the end of the schedule – is to use an iterative approach. Here is what we came up with:
CREATE OR ALTER PROCEDURE dbo.GeneratePatchSchedule
@MaxServersPerDay tinyint,
@MaxPerPoolPerDay tinyint
AS
BEGIN
SET NOCOUNT ON; CREATE TABLE #CandidateDays
(
TheDate date PRIMARY KEY
); DECLARE @MinDate date,
@MaxDeadline date,
@LoopDate date,
@NumServers int; SELECT @NumServers = COUNT(*),
@MaxDeadline = MAX(NextPatchDeadline)
FROM dbo.ServerPatchDetails; INSERT #CandidateDays(TheDate)
SELECT
-- worst case scenario:
TOP (@MaxServersPerDay * @MaxPerPoolPerDay * @NumServers) TheDate
FROM dbo.ValidPatchingDays
WHERE TheDate <= @MaxDeadline
ORDER BY TheDate DESC; CREATE TABLE #Result
(
ServerID int NOT NULL PRIMARY KEY,
TheDate date NULL -- NULL in TheDate means no available date
); SELECT @LoopDate = MAX(TheDate)
FROM #CandidateDays; WHILE @LoopDate IS NOT NULL AND @@ROWCOUNT > 0
BEGIN ;WITH date_cte AS
(
SELECT s.ServerID,
s.ServerPoolID,
s.NextPatchDeadline,
PoolRank = (ROW_NUMBER() OVER
(PARTITION BY s.ServerPoolID ORDER BY s.ServerID) - 1)
/ @MaxPerPoolPerDay + 1,
NumberPerPool = COUNT(*) OVER (PARTITION BY s.ServerPoolID)
FROM dbo.ServerPatchDetails AS s
WHERE @LoopDate <= s.NextPatchDeadline
AND NOT EXISTS
(
SELECT 1 FROM #Result AS r WHERE r.ServerID = s.ServerID
)
)
INSERT INTO #Result(ServerID, TheDate)
SELECT TOP(@MaxServersPerDay) ServerID, @LoopDate
FROM date_cte
WHERE PoolRank = 1
AND @LoopDate <= NextPatchDeadline
ORDER BY NumberPerPool DESC, NextPatchDeadline DESC; SELECT @LoopDate = MAX(TheDate)
FROM #CandidateDays
WHERE TheDate < @LoopDate; END; -- catch any servers that may have been missed
INSERT INTO #Result(ServerID) -- NULL will be used for TheDate
SELECT ServerID FROM dbo.ServerPatchDetails
EXCEPT
SELECT ServerID FROM #Result; SELECT
r.ServerID,
s.ServerPoolID,
s.NextPatchDeadline,
PatchDate = r.TheDate,
Details = CASE
WHEN r.TheDate < GETDATE() THEN 'Overdue!'
WHEN r.TheDate IS NULL THEN 'Problem - server is not scheduled!'
ELSE CONVERT(varchar(5), DATEDIFF(DAY, GETDATE(), TheDate)) + ' days from now.'
END
FROM #Result AS r
INNER JOIN dbo.ServerPatchDetails AS s
ON r.ServerID = s.ServerID
ORDER BY r.TheDate, s.ServerPoolID, r.ServerID; DROP TABLE #Result; END
GO
Yes, that is certainly a mouthful, but I'll explain the steps:
- Determine the number of servers and the last possible patching deadline
- Insert possible candidate days into a temp table, accounting for the most days we could ever possibly need
- Create a temp table to hold the results
- Starting with the last possible day, loop through all of the days to determine
which servers can be patched on a given day
- The PoolRank expression tells us whether a server should be included for the current day, based on how many other servers are present from the same pool
- The NumberPerPool expression helps us process the largest pools first, so that we don't end up with a pool of servers on the last day that then have to be stretched out over multiple additional days
- After the loop, we clean up any servers that may have been missed due to an error in logic or invalid input parameters
- Finally, we output the results, giving warning messages if a server has been skipped or is past due.
Here are some example executions and their output (note that the "days from now" numbers are based on an execution date of June 3rd, 2021):
Max servers per day = 5; max per pool = 2
EXEC dbo.GeneratePatchSchedule @MaxServersPerDay = 5, @MaxPerPoolPerDay = 2;
In this case, the last two valid days in the patching window get 5 servers each, and no pool has more than two servers on any given day.
Max servers per day = 6; max per pool = 2
EXEC dbo.GeneratePatchSchedule @MaxServersPerDay = 6, @MaxPerPoolPerDay = 2;
If we change the max servers per day to 6, then all of the patching can be accomplished in two days.
Max servers per day = 4; max per pool = 3
EXEC dbo.GeneratePatchSchedule @MaxServersPerDay = 4, @MaxPerPoolPerDay = 3;
If we allow three servers from a pool on a given day, you can see that the logic maximizes the number from that pool "first" (working backward).
Max servers per day = 6; max per pool = 3
EXEC dbo.GeneratePatchSchedule @MaxServersPerDay = 6, @MaxPerPoolPerDay = 3;
If we keep 3 per pool and go back up to 6 per day, we see a similar effect: most of pool 2 is scheduled for patching on the last possible day.
Max servers per day = 8; max per pool = 2
EXEC dbo.GeneratePatchSchedule @MaxServersPerDay = 8, @MaxPerPoolPerDay = 2;
If we increase our number of servers we can patch in a day, we once again can finish in two days.
Max servers per day = 2; max per pool = 2
EXEC dbo.GeneratePatchSchedule @MaxServersPerDay = 2, @MaxPerPoolPerDay = 2;
Down at two servers per day, we don't hit our other business rule at all, so we get a consistent two servers per day all the way through. It takes longer to complete all of the patching, of course.
Max servers per day = 12; max per pool = 2
EXEC dbo.GeneratePatchSchedule @MaxServersPerDay = 12, @MaxPerPoolPerDay = 2;
Finally, if we allow for 12 servers per day, we see that most servers can be patched on the last day, but not all of them. The two additional servers from pool 2 have to be bumped to an earlier day.
What else?
We could add more optimization rules later, such as handling FCIs and AGs differently (patching secondaries first, then after failover being sure we patch former secondaries within x days), better distributing servers from a pool across the entire patching period, and implementing settings based on other priorities such as server function, criticality, or relationships with other servers.
Any method of scheduling many events can get complex rather quickly, and I wasn't trying to solve every aspect of patch scheduling in this post. I hope I've demonstrated how the calendar table can take a whole chunk of complexity out of the equation from the start.
Next Steps
For more info on calendar tables and general date handling in SQL Server, see these tips and videos:
- Using a calendar table in SQL Server – Part 1
- Calendar Table in SQL Server to Identify Date Gaps and Islands
- Creating a date dimension or calendar table in SQL Server
- SQL Server DateTime Best Practices
- Recommended SQL Server Date Formats
- SQL Server Date Time Shorthand and Other Tricks
- SQL Server Date and Time Data Type Comparison
- All SQL Server date tips
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-06-16
