Introduction
Customers evaluating or already using SQL Managed Instance (SQL MI) need to estimate how much time a database restore would take to complete. This estimation could be required when designing an SLA and/or SLO, or to assess whether a database with certain characteristics would be able to fit into predefined recovery time objectives (RTO).
This Point-In-Time Restore Service Level Expectation (SLE) solution was created to provide guidance for the expected time it would take to restore a database to a point in time, by providing a database restore time expectation based on input parameters.
Note that this SLE was built for PITR database restore operations, and not for Long Term Retention backups restore operations.
Reference documentation: Point-in-time restore - Azure SQL Managed Instance | Microsoft Learn
Factors that affect database restore time
The following list contains several factors that may affect the recovery time of a database using automated database backups:
- The size of the database being restored
- The compute size of the target database
- The number of transaction logs involved in the restore plan
- The amount of activity that needs to be replayed to recover to the restore point
- The network bandwidth if the restore is to a different region
- The number of concurrent restore requests that are processed in the target region
- Whether a system update is performed which will temporarily interrupt any database restore operation
Approach and modeling of the estimations
The approach chosen to generate a database restore time expectation is based on data from past restore operations that occurred across worldwide Azure regions. This data was collected from the backend telemetry of our SQL MI service to the end of 2023. It is assumed that future restore operations will take a similar amount of time as past restores with comparable database characteristics (such as backup size and number of files). However, there may be improvements in our service that could potentially reduce restoration times in the future, at which point this analysis will be revised to incorporate these advancements.
To model the estimations, 6 linear regressions were used with a series of independent variables, which were fed with restore operations data obtained from the Azure SQL MI backend telemetry.
To store the results of the regressions and to use them to make estimations, an Excel file called SQL MI Database Restore Service Level Expectation.xlsx can be used. The stored procedure at the bottom of this blog named usp_SQL_MI_Database_Restore_Service_Level_Expectation can be used to make estimations as well.
It is important to note that while regression analysis can be powerful, it assumes a linear relationship between the variables and makes certain assumptions about the data, such as normality and homoscedasticity. It Is important to recognize this method has its limitations, and that both the data used for the regressions as well as future database restores there will be outliers that will be very hard to predict. Even though that, these are mathematical models trying to provide an expectation based on actual database restores. In certain scenarios, such as with large databases in Business Critical SLO, the number of actual database restores may be relatively low due to the few large databases compared to medium and smaller ones. Consequently, this results in the generation of less accurate models. For the purpose of this work, a linear regression with an r2 greater than 0.65 was considered good enough from a statistics point of view.
Different models based on service tier and database size
Since database restores at different service tiers perform differently due to the number of resources available and in order to model estimations more accurately, six different estimation models were created. The table below shows the 2 service tiers and the 3 categories for database size for a total of 6 models:
|
Choosing the right estimation model |
General Purpose (GP Database Restore SLE tab) |
Business Critical (BC Database Restore SLE tab) |
|
Small Databases |
Smaller than 450 GB |
Smaller than 400 GB |
|
Medium Databases |
Between 450 GB and 1,500 GB |
Between 400 GB and 2,000 GB |
|
Large Databases |
Greater than 1,500 GB |
Larger than 2,000 GB |
For example, if you are trying to use the Excel file to estimate the restore time of a database that runs on Business Critical and the database is 1,800 GB in size, then use the BC Restore SLE tab and the second regression for Medium databases of the Excel file.
Reference documentation: Resource limits & purchasing model
Two options to obtain restore time estimations
This Database Restore Service Level Expectation (SLE) solution presents two options to obtain restore time estimations:
- An Excel file to manually enter values of a hypothetical database
- To obtain a copy of this file please email datasqlninja@microsoft.com.
- A stored procedure specifying an existing database and a point in time (PITR)
- The T-SQL code of this stored procedure called usp_SQL_MI_Database_Restore_Service_Level_Expectation is at the end of this blog.
Stored Procedure for existing databases
The stored procedure option provides estimations for an existing database in the SQL MI in which the stored procedure is first created and then executed. The stored procedure named usp_SQL_MI_Database_Restore_Service_Level_Expectation accompanying this solution does the calculations itself with only two input parameters. In order to use it, first create the stored procedure in any database of the SQL MI and second execute it as explained below.
Input Parameters
The only two parameters needed to run this stored procedure are shown below:
EXEC usp_SQL_MI_Database_Restore_Service_Level_Expectation @Database='AdventureWorks2019', @PITR_Time = '2024-01-26 09:21:00.000';
Running the above example estimates the restore time of the AdventureWorks2019 database to a point in time of 9:21 AM - January 26th 2024. Please enter the name of the existing database and the point in time you would like to obtain estimations for.
Results
The result of the execution of the above example looks like this:
Note that it provides estimations for restoring the existing database in both General Purpose and Business Critical in various vCore configurations, as well as information of the restore plan for the specified point in time restore (PITR).
Excel file for hypothetical databases
Please use the accompanying Excel file named SQL MI Database Restore Service Level Expectation.xlsx and choose the estimation model in the tab and section that corresponds to the service tier you want to use for the estimation (General Purpose or Business Critical) and the size of the database. Once you locate the appropriate model, you will enter the input parameters of a hypothetical database to perform restore estimations.
Input parameters
Inside the Excel file, the cells colored green are the cells where data must be entered to generate an estimation. vCore is the only cell that contains a drop box with the valid values for each model based on tier and database size. The file comes with sample values in the cells for illustrative purposes.
As you enter values one by one, a number of minutes and hours will start to be calculated but should be ignored until you have entered all the 5 values in all the corresponding green cells.
Note that for some of the input parameters you will have to be creative and make assumptions, i.e., how many log backups are needed in the restore plan for a given point in time? Or how big of a differential backup to I want to use for an estimation? Each green cell performs validations to make sure valid values for each cell are entered.
Once all input parameters are entered into the green cells, the expected database restore time is calculated underneath and displayed both in minutes with one decimal and in hours with two decimals. Both represent the same amount of time only differing in the time unit. i.e. 113.4 minutes equals 1.89 hours.
Number of vCores
This input parameter represents the number of vCores that are configured on the hypothetical SQL Managed Instance that will be the target of the database restore. In most cases the number of vCores changes the amount of resources available for the restore operation that translates to a slight time difference.
Please enter into the green cell next to vcore_count the number that you want to use to generate an estimation.
Note that the number of vCores that can be configured in SQL MI depends on the following:
- The service tier (General Purpose or Business Critical)
- The hardware series (Standard, Premium, Memory Optimized Premium)
- The maximum instance reserved storage size (1 TB, 2 TB, 4 TB, 5.5 TB and 16 TB)
|
Feature |
General Purpose |
Business Critical |
|
Number of vCores* |
4, 8, 16, 24, 32, 40, 64, 80 |
Standard-series (Gen5): 4, 8, 16, 24, 32, 40, 64, 80 |
|
Max instance reserved storage size |
- 2 TB for 4 vCores |
Standard-series (Gen5): |
The tables above were extracted from the following documentation:
Reference documentation: Resource limits - Azure SQL Managed Instance | Microsoft Learn
Full Backup Size in GB
This input parameter represents the size in gigabytes (GB) of the full database backup that is going to be used to restore the database to the point in time chosen. This is equal to the actual size of the database at the time the full backup was taken and is the most significant factor that affects estimations.
Please enter into the green cell next to full_backup_size_GB a number in GB that you want to use as the size of the database to generate an estimation.
Reference documentation: Full database backups (SQL Server) - SQL Server | Microsoft Learn
Differential Backup Size in GB
This input parameter represents the size of the differential backup that may need to be restored after the full backup to get to the point in time specified in the operation. The size of this differential backup depends on the number and size of changes made in the database since the last full backup was completed and the point in time chosen. Considering a full backup is normally taken once a week and a differential backup is normally taken every 12 hours, the differential backup could contain database changes accumulated for up to ~6.5 days. Note that if a point in time is chosen close enough to the last full backup, then no differential backup is needed for that restore plan.
Please enter into the green cell next to diff_backup_size_GB a number in GB if you want to use a differential backup in the estimation. The most typical value we observe in the telemetry is between 1% and 18% of the full backup size.
Reference documentation: Differential Backups (SQL Server) - SQL Server | Microsoft Learn
Log Backup Count
This input parameter represents the number of transaction log backups that need to be restored to bring the database to the point in time specified in the database restore operation.
Considering a log backup is normally as long as there are database changes and differential backups are taken normally every 12 hours, we could estimate that at the most 144 log backups could be needed in the restore plan to get to any point in time specified in the database restore operation.
Please enter into the green cell next to log_backup_count a number of log backups you want to use to generate an estimation. If you want to model a worst-case scenario, use 144 and for best case scenario use 0. The most typical value we observe in the telemetry is between 0 and 55.
Reference documentation: Transaction log backups - SQL Server | Microsoft Learn
Total Log Backup Size in GB
This input parameter represents the sum in GB of all the log backups needed in the restore plan after the full + potential differential backup restores to get to the point in time. This total size depends on the number of database changes that occurred since the last full or differential backup.
Please enter into the green cell next to total_log_backup_size_GB a number in GB that you want to use to generate an estimation. The most typical value we observe in the telemetry is between 0% and 5% of the full backup size.
Feedback and suggestions
We hope that this post has helped you. If you have feedback or suggestions for improving this asset, please contact the Azure Databases SQL Customer Success Engineering Team. Thanks for your support!
The following is the T-SQL to create the aforementioned stored procedure (usp_SQL_MI_Database_Restore_Service_Level_Expectation) to calculate the restore SLE:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================================================================================================================================================
-- Disclaimer: This stored procedure is provided “as-is”. Microsoft provided guidance in this stored procedure with the understanding that MICROSOFT MAKES NO WARRANTIES,
-- EXPRESS OR IMPLIED, WITH RESPECT TO THE INFORMATION CONTAINED HEREIN.
-- Authors: Raghavendra Srinivasan, Diego Caracciolo, Microsoft Corporation
-- Create date: 1/30/2024
-- Description: This stored procedure provides a SQL MI Database Restore Service Level Expectation (SLE) based on the backup history information of a database in the current SQL MI
-- This stored procedure is part of a solution called "Azure SQL MI Database Restore Service Level Expectation"
-- which main document is called Azure SQL MI Database Restore Service Level Expectation.docx
-- Parameters: @Database = The name of the database in the current SQL Managed Instance for which you want to obtain an SLE
-- @PITR_Time = The point in time at which you want to obtain a database restore SLE
-- Example: EXEC usp_SQL_MI_Database_Restore_Service_Level_Expectation @Database='MyDatabase1', @PITR_Time = '2024-01-26 09:21:00.000'
-- =================================================================================================================================================================================
CREATE PROCEDURE usp_SQL_MI_Database_Restore_Service_Level_Expectation
@Database NVARCHAR(255),
@PITR_Time DATETIME2
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
DECLARE @PITR_Checkpoint DATETIME2 = DATEADD(MINUTE, 30, @PITR_Time);
DECLARE @LogBackupFileCount INT = 0;
DECLARE @vores TABLE (Vcore INT)
DECLARE @FullBackup FLOAT, @DiffBackup FLOAT, @LogBackup FLOAT;
DECLARE @vcore_count FLOAT, @full_backup_size_GB FLOAT, @diff_backup_size_GB FLOAT, @total_log_backup_size_GB FLOAT, @log_backup_count FLOAT, @Intercept FLOAT;
-- Check if the temporary table exists, drop and create
IF OBJECT_ID('tempdb..#dataSET') IS NOT NULL
DROP TABLE #dataSET;
IF OBJECT_ID('tempdb..#FinalResult') IS NOT NULL
DROP TABLE #FinalResult;
CREATE TABLE #dataSET (
Id INT IDENTITY(1, 1),
database_name NVARCHAR(256),
backup_start_date DATETIME2,
backup_finish_date DATETIME2,
[type] NVARCHAR(64),
backupsize_GB FLOAT
);
CREATE TABLE #FinalResult (
DatabaseName NVARCHAR(256),
Backup_start_date DATETIME2,
Backup_finish_date DATETIME2,
[Type] NVARCHAR(64),
Backupsize_GB FLOAT,
BackupFileCount int
);
-- CTE to find the latest full backup before the specified PITR time
WITH LatestFullBackup AS (
SELECT TOP 1 database_name, checkpoint_lsn, backup_finish_date
FROM msdb.dbo.backupSET AS bs
WHERE database_name = @Database AND [type] = 'D' AND backup_finish_date < @PITR_Time
ORDER BY backup_start_date DESC
)
-- Populate the #dataSET temporary table
INSERT INTO #dataSET
SELECT DISTINCT
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
CASE bs.[type]
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Differential Backup'
WHEN 'L' THEN 'Log Backup'
ELSE 'Unknown'
END AS backup_type,
bs.backup_size / 1024 / 1024 / 1024 AS backup_size_GB
FROM
msdb.dbo.backupSET AS bs
JOIN msdb.dbo.backupmediafamily AS bmf ON bs.media_SET_id = bmf.media_SET_id
JOIN LatestFullBackup AS lfb ON bs.database_name = lfb.database_name AND (bs.database_backup_lsn = lfb.checkpoint_lsn OR lfb.checkpoint_lsn = bs.checkpoint_lsn)
WHERE
bs.database_name = @Database AND bs.backup_finish_date < @PITR_Checkpoint
ORDER BY
bs.backup_start_date DESC;
-- Get log backup count
IF EXISTS (SELECT TOP 1 id FROM #dataSET WHERE [type] = 'Differential Backup' and backup_finish_date <= @PITR_Time)
BEGIN
SELECT @LogBackupFileCount = COUNT(1)
FROM #dataSET
WHERE [type] = 'Log Backup' and
id <= (SELECT TOP 1 id FROM #dataSET WHERE [type] = 'Differential Backup' and backup_finish_date <= @PITR_Time) and
id >= (SELECT TOP 1 id FROM #dataSET WHERE [type] = 'Log Backup' and backup_finish_date >= @PITR_Time ORDER BY id DESC)
END
ELSE
BEGIN
SELECT @LogBackupFileCount = COUNT(1)
FROM #dataSET
WHERE [type] = 'Log Backup' and
id >= (SELECT TOP 1 id FROM #dataSET WHERE [type] = 'Log Backup' and backup_finish_date >= @PITR_Time ORDER BY id DESC)
END
-- Get full database backup
INSERT INTO #FinalResult
SELECT
database_name,
backup_start_date,
backup_finish_date,
[type],
ROUND(backupsize_GB, 2) AS backupsize_GB,
1
FROM #dataSET
WHERE [type] = 'Full Backup';
-- Get log backups
IF EXISTS (SELECT TOP 1 id FROM #dataSET WHERE [type] = 'Differential Backup' and backup_finish_date <= @PITR_Time)
BEGIN
INSERT INTO #FinalResult
SELECT
database_name,
MIN(backup_start_date) AS min_backup_start_date,
MAX(backup_finish_date) AS max_backup_finish_date,
'Log Backup',
ROUND(SUM(backupsize_GB), 2),
@LogBackupFileCount
FROM #dataSET
WHERE [type] = 'Log Backup' and
id <= (SELECT TOP 1 id FROM #dataSET WHERE [type] = 'Differential Backup' and backup_finish_date <= @PITR_Time) and
id >= (SELECT TOP 1 id FROM #dataSET WHERE [type] = 'Log Backup' and backup_finish_date >= @PITR_Time ORDER BY id DESC)
GROUP BY database_name;
END
ELSE
BEGIN
INSERT INTO #FinalResult
SELECT
database_name,
MIN(backup_start_date) AS min_backup_start_date,
MAX(backup_finish_date) AS max_backup_finish_date,
'Log Backup',
ROUND(SUM(backupsize_GB), 2),
@LogBackupFileCount
FROM #dataSET
WHERE [type] = 'Log Backup'
AND id >= (
SELECT TOP 1 id
FROM #dataSET
WHERE [type] = 'Log Backup'
AND backup_finish_date >= @PITR_Time
ORDER BY id DESC
)
GROUP BY database_name;
END
-- Get differential backups
INSERT INTO #FinalResult
SELECT TOP 1
database_name,
backup_start_date,
backup_finish_date,
[type],
ROUND(backupsize_GB, 2),
1
FROM #dataSET
WHERE [type] = 'Differential Backup'
AND backup_finish_date <= @PITR_Time
ORDER BY Id ASC;
SELECT 'Backups required for PITR restore' as Description, *
FROM #FinalResult
ORDER BY backup_finish_date;
SELECT @FullBackup = ISNULL(Backupsize_GB, 0)
FROM #FinalResult
WHERE Type = 'Full Backup';
SELECT @DiffBackup = ISNULL(Backupsize_GB, 10)
FROM #FinalResult
WHERE Type = 'Differential Backup';
SELECT @LogBackup = ISNULL(Backupsize_GB, 0)
FROM #FinalResult
WHERE Type = 'Log Backup';
IF @DiffBackup IS NULL
SET @DiffBackup = 0;
-- Calculate for General purpose SLO
IF (@FullBackup <= 450)
BEGIN
SET @Intercept = 4.25409547;
SET @vcore_count = -0.204980468;
SET @full_backup_size_GB = 0.135130245;
SET @diff_backup_size_GB = 0.163912245;
SET @log_backup_count = 0.081560044;
SET @total_log_backup_size_GB = 1.144489012;
SELECT
'SQL MI General Purpose Database Restore Service Level Expectation (Minutes)' AS SLO,
IIF(
ROUND(@Intercept + (4 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (4 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_4_vCores,
IIF(
ROUND(@Intercept + (8 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (8 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_8_vCores,
IIF(
ROUND(@Intercept + (16 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (16 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_16_vCores,
IIF(
ROUND(@Intercept + (24 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (24 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_24_vCores,
IIF(
ROUND(@Intercept + (32 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (32 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_32_vCores;
END
ELSE IF (@FullBackup > 450 AND @FullBackup <= 1500)
BEGIN
SET @Intercept = 34.85312089;
SET @vcore_count = -0.17126727;
SET @full_backup_size_GB = 0.035174866;
SET @diff_backup_size_GB = 0.296089387;
SET @log_backup_count = 0.141328736;
SET @total_log_backup_size_GB = 0.774604541;
SELECT
'SQL MI General Purpose Database Restore Service Level Expectation (Minutes)' AS SLO,
IIF(
ROUND(@Intercept + (4 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (4 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_4_vCores,
IIF(
ROUND(@Intercept + (8 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (8 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_8_vCores,
IIF(
ROUND(@Intercept + (16 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (16 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_16_vCores,
IIF(
ROUND(@Intercept + (24 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (24 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_24_vCores,
IIF(
ROUND(@Intercept + (32 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (32 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_32_vCores;
END
ELSE IF (@FullBackup > 1500)
BEGIN
SET @Intercept = 112.8998712;
SET @vcore_count = -2.211989797;
SET @full_backup_size_GB = 0.027989985;
SET @diff_backup_size_GB = 0.216673484;
SET @log_backup_count = 0.000731196;
SET @total_log_backup_size_GB = 0.688150443;
SELECT
'SQL MI General Purpose Database Restore Service Level Expectation (Minutes)' AS SLO,
IIF(
ROUND(@Intercept + (8 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (8 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_8_vCores,
IIF(
ROUND(@Intercept + (16 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (16 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_16_vCores,
IIF(
ROUND(@Intercept + (24 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (24 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_24_vCores,
IIF(
ROUND(@Intercept + (32 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (32 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS GP_32_vCores;
END
-- Calculate for Business Critical SLO
IF (@FullBackup <= 400)
BEGIN
SET @Intercept = 1.909336538;
SET @vcore_count = -0.041561702;
SET @full_backup_size_GB = 0.066450199;
SET @diff_backup_size_GB = 0.07559076;
SET @log_backup_count = 0.033185528;
SET @total_log_backup_size_GB = 0.297277091;
SELECT
'SQL MI Business Critical Database Restore Service Level Expectation (Minutes)' AS SLO,
IIF(
ROUND(@Intercept + (4 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (4 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_4_vCores,
IIF(
ROUND(@Intercept + (8 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (8 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_8_vCores,
IIF(
ROUND(@Intercept + (16 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (16 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_16_vCores,
IIF(
ROUND(@Intercept + (24 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (24 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_24_vCores,
IIF(
ROUND(@Intercept + (32 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (32 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_32_vCores;
END
ELSE IF (@FullBackup > 400 AND @FullBackup <= 2000)
BEGIN
SET @Intercept = 17.95688297;
SET @vcore_count = -0.556172229;
SET @full_backup_size_GB = 0.050444533;
SET @diff_backup_size_GB = 0.013286432;
SET @log_backup_count = 0.05186371;
SET @total_log_backup_size_GB = 0.180903906;
SELECT
'SQL MI Business Critical Database Restore Service Level Expectation (Minutes)' AS SLO,
IIF(
ROUND(@Intercept + (4 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (4 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_4_vCores,
IIF(
ROUND(@Intercept + (8 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (8 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_8_vCores,
IIF(
ROUND(@Intercept + (16 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (16 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_16_vCores,
IIF(
ROUND(@Intercept + (24 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (24 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_24_vCores,
IIF(
ROUND(@Intercept + (32 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (32 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_32_vCores;
END
ELSE IF (@FullBackup > 2000)
BEGIN
SET @Intercept = 177.1004847;
SET @vcore_count = -1.766472406;
SET @full_backup_size_GB = 0.017504939;
SET @diff_backup_size_GB = -0.044402262;
SET @log_backup_count = -0.164065766;
SET @total_log_backup_size_GB = 0.401938221;
SELECT
'SQL MI Business Critical Database Restore Service Level Expectation (Minutes)' AS SLO,
IIF(
ROUND(@Intercept + (16 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (16 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_16_vCores,
IIF(
ROUND(@Intercept + (20 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (20 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_20_vCores,
IIF(
ROUND(@Intercept + (24 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (24 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_24_vCores,
IIF(
ROUND(@Intercept + (32 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2) > 1,
ROUND(@Intercept + (32 * @vcore_count) + (@Fullbackup * @full_backup_size_GB) + (@Diffbackup * @diff_backup_size_GB) + (@LogBackupFileCount * @log_backup_count) + (@LogBackup * @total_log_backup_size_GB), 2),
1
) AS BC_32_vCores;
END
END TRY
BEGIN CATCH
-- Handle the exception
PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH
-- Drop the temporary tables outside the try-catch block
IF OBJECT_ID('tempdb..#dataSET') IS NOT NULL
DROP TABLE #dataSET;
IF OBJECT_ID('tempdb..#FinalResult') IS NOT NULL
DROP TABLE #FinalResult;
END
GO