Plan your SQLServer Flex instance

STACKIT offers maximum flexibility in conjunction with Microsoft SQL Server. However, technical limitations require certain restrictions. To avoid technical debt and therefore downtimes and unwanted future migration, it is best practice to plan your SQLServer Flex instance in advance.

Plan your needs

Before selecting your SQLServer Flex instance size, you need to analyze your data patterns and requirements:

Data volume analysis

Estimate the size of the data based on the average width of the rows, the number of rows and the index footprint. SQL Server stores data in 8 KB pages, to which additional overhead is added for row structure, LOB storage and non-clustered indexes. Consider the impact of compression (row/page or columnstore), transaction log requirements and TempDB usage. When planning capacity, allow for 2–2.5 times growth over 12–24 months to account for data expansion, new indexes and schema evolution.

Query Patterns

Examine how your application interacts with the database. Frequent complex queries, aggregations, and index-heavy operations require more CPU and memory resources. Applications with high read-to-write ratios may benefit from different sizing strategies than write-heavy workloads. Also consider if your application is more read or write heavy.

Limitations

You can consult Architecture of SQLServer Flex for the limitations in flexibility. For a quick reference you can consult this table:

Parameter	Downgrade possible	Upgrade possible	Downtime
type			-
plan	✓	✓	✓
performance-class
storage-size		✓
version		✓	✓

You can make changes to ACL and backup settings without any limitations and without downtime.

Best practice

You need to keep the limitations in mind. This paragraph helps you to make good assumptions and choices to set up an instance.

Instance type

If you plan to set up an instance for production use, you should use Business Critical instance type. For non-production use, you can use General Purpose instance type.

Plan

At first you need to distinguish if short downtimes are acceptable. If yes, you can start with a small plan and upgrade it, when you consistently reach 80% of either CPI or RAM consumption. If downtime isn’t acceptable, you need to estimate your needs.

As a rule of thumb you may use this ruleset:

CPU

• Read-heavy: 2-4 CPUs sufficient
• Write-heavy or aggregations: 8+ CPUs

RAM

• Minimum: 25% of data size + index size
• Optimal: 50-75% of active data size + index size
• Rule of thumb: 8GB RAM at minimum, better 32GB+ for production

You can assume the index to be as big as 30% of your data size.

Performance class

For the performance class you also need to distinguish if short downtimes are acceptable. If not, you need to estimate your needs.

As a rule of thumb you may use this ruleset:

IOPS:

• Read-heavy: 1000-3000 IOPS
• Write-heavy: 3000-10000+ IOPS
• Mixed workload: 2000-5000 IOPS

Storage size

As storage size can be enlarged anytime without downtime, it is safe to start with a smaller size. As a rule of thumb start with your data size multiplied by 1.3 for the index and multiplied by 3 again for the growth factor.

That’s equivalent to data size × 3.9.

Example

If your current data size is 500 GB:

• Index allowance: 500 GB × 1.3 = 650 GB
• Growth buffer: 650 GB × 3 = 1,950 GB
• Provision ~2 TB (round up to the next convenient size)

Quick reference

Data size today	Estimated provisioned size (×3.9)
100 GB	390 GB
250 GB	975 GB (~1 TB)
500 GB	1,950 GB (~2 TB)
1 TB	3,900 GB (~4 TB)