Skip to content

Capacity Planning – an Introduction

February 27, 2012

Provide sufficient but not excessive capacity. No budget is too big anymore!

Serious consideration needs to be given to the probable final size of an application from the outset.

Sizing is always subject to continuous change, but the key issue is that it should be controlled change – to prevent unexpected system failure or degradation.  

1. Identify The Data Requirements

This task should be driven by the Users of the system, not IT.

The users of the system need to broadly define the boundaries. Only the actual users of the system can do so. Only they can include and exclude data. They cannot be expected to get it right first time though.

For example: The initial business requirement is:-

50 Actual & Forecast Measures for the last 10 and next 10 years on a Daily basis, for 200 products, for 50 major Customers in 500 locations – Roughly: 100 Measures x 20 Years x 365 days x 200 products x 50 accounts x 500 outlets = 3,650,000,000,000 data values.  = 3.65 trillion

If you used the default DECIMAL datatype:  Precision=38, which uses 17bytes, then 3.65 trillion cells equates to 3.65 x 17 = 62.05 TB. That is too much data for your average information system, implemented to an average budget!

Get the data boundaries correct. Manage expectations.

For example:- Explore whether it is acceptable to store data monthly (instead of daily):- 3.65 trillion then becomes 0.12 trillion. Next, is it possible to store data using the DECIMAL(19,6) datatype, which uses 9 bytes? This would give: 0.12 x 9 =  1.08 TB. Not every Account would have 500 outlets:- if the average was only 50, then this would give: 100GB. Still a lot of space, but a long way down from 62TB.

Keep narrowing the boundaries. Look at each item of data – what is its actual datatype?

Integer? Date? Every less byte helps.

Because it only gets worse from now on. Seriously!

2. Architecture – the broad components in an Information System

Data needs to be captured from source systems – the original copy should be stored in a Staging area.

Data needs to be validated, cleansed and transformed – the normalised copy should be stored in a separate area aka “Operational Data Store” (ODS).

Data may need to be “simplified” for reporting and analysis – the de-normalised copy should be stored in a separate area aka “Presentation layer”. This “simplification” may just be a collection of database Views, but it may need to be physically stored as well.

Data may need to be stored in a separate OLAP database, so that it can be accessed quickly and to reduce the load on the ODS. This depends on the end-users reporting requirement.

In short, that is (at least) 4 versions of the data! (You could also add another version for the reports.)

3. Infrastructure – the hardware/software required for an Information System

It is “best practice” to have a separate Server for the Relational Database, Integration Services and Analysis Services Database components. 64-bit v 32-bit?

This has a direct impact on licensing costs. Which Servers need to have the Enterprise Edition of SQL Server? Will the Standard Edition suffice?

Which is the most appropriate licensing model? CPU or CAL? The rules change again with SQL Server 2012, when it is done on a CPU-Core basis.

What should be the spec for the CPUs and RAM for each of the above Servers? Make sure that major changes to a Cube are fully stress-tested during UAT to ensure that the correct amount of RAM is made available in Production during the deployment of major changes.

Also, what impact might there be on the network infrastucture? Is there sufficient band-width to cope with regular “Cube Synchronisations”?

4. The Environments

For all of the above – how many “environments” are necessary? Ideally, you need separate:-

“Production”, “UAT”, “Development” and “DR” environments.

So, at least 4 then. That is a lot of Infrastructure! For small organisations, compromises may be necessary.

In summary, Capacity Planning needs to be done from the outset and re-assessed at each major deployment to ensure that the optimum infrastructure is made available. Technical Design techniques, such as Table Compression, exist which may help to reduce the amount of physical capacity actually required.

Sizing does matter. Especially for small to medium-sized companies. Sizing from the outset does make a difference. Make continuous efforts to reduce (“prune”) the size of an application. For example, it can make the difference between buying an expensive SAN or not. And money does matter.

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: