Configmgr Sizing Worksheets

Hierarchy Planning Worksheet v2.2

DB Sizing Worksheet v2.2

Several years ago Kent A. and someone else put out a couple spreadsheets on database sizing and server sizing for Configmgr.  I used them off and on for a few years and over time I have revised them based on the newer SQL server best practices (SQL 2012 R2+).

In the days of SQL 2008 and earlier, the common db practice was to have multiple files for the configmgr database files (MDF & NDF files), while the TempDB was configured to use a single file (MDF file).  And all databse transaction log files (LDF Files) were configured to use single files alongside each database.

TempDB or tempdb database: Is a globally used file available and is used for holding temporary objects such as local and global tables, stored procedures, table variables, row versions, and query results. The tempdb is temporary, it gets recreated at each reboot or SQL service start.  It is literally empty at each start, all data is purged and the db cannot be backed up.  The tempdb.mdf is the data file, templog.ldf is the log file for the tempdb.  The tempdb autogrowth setting can cause serious performance issues if the size of the db is too small and it is constantly growing the db.  A common practice when running a SQL server in an Azure VM is to put the temdb files on the D drive since the drive is non-persistent during reboots.

The best practice for SQL 2012 R2 and beyond is to use a single database file for the Configmgr database and multiple files for the TempDB files (MDF & NDF files).  The transaction log files (LDF files) are still configured for a single file as before.  My buddy Steve Thompson points this out in a recent post where he discusses the proper tempdb creation practices.  Steve is also a former SQL MVP, now a Configmgr MVP so he knows both well.

The topic of using a CAS or multiple primaries for a customer came up on a discussion list today and when I replied I had screen shots of my sql sizing spreadsheet and my site sizing spreadsheet and a few people asked me for copies of them.  They can be downloaded now and as they get updated I will do my best to make the new versions available.

Any questions please let me know.

P.S. SQL should always be local.

Azure Stack Home Lab Build

In the beginning there was…nothing

Microsoft Azure Stack is not something I could build and run on my desktop like my Configmgr lab, which runs on my W10 desktop while I use it for work as well.  I had to decide if I wanted to spend the money on a server or wait for the hardware to fall into my hands.  Because I saw this a real opportunity to learn something that would be valuable to my customers and myself, and because my wife…I mean CFO, approved the request I purchased the individual parts to build my own server for my home lab.

Having had a server in my home lab previously I knew of the drawbacks.  Noise and heat.  Lots of both.  Doing a customer demo when you are sweating and they cannot hear you makes doing a demo difficult.  That meant I had to meet the hardware requirements of Azure Stack, and it had to be quiet and cool.

And then there was…

After lots of research, I ordered all of my hardware.  Things have not changed much since I got my A+ back in 1995, thankfully.  And yes it did boot the first time I powered it on.  I won’t get into the hardware details but you can take a stab at it based on the images.

Empty Case
The case is a little larger than I expected. It has plenty of room to add additional drives later on though. It is behind my desk between the desk and the haze-grey wall you see here. It would actually fit under my desk but until I decide to move my Dell desktop it will stay back there.


MSI motherboard. While a gaming motherboard it supports my needs, which don’t include gaming.
Motherboard installed, i7 CPU installed, H20 powered CPU cooler installed and connected. The memory is installed but I later moved it for quad channel
Hard Drives
This shows the five drives mounted. It is a bit blurry but my Pearl Izumu shoes are in focus.
First boot
This shows the system running. Memory moved for quad channel, video card mounted, power supply, so on.
CPU Temp
Closer shot of the system running. You can see the “40” indicating the CPU temp.
If I were in charge of the world
And now Windows Server 2016 TP4 installing from USB. With a shot of my whiteboard cleaned off for Azure Stack testing. And also my daughters “If I Were In Charge Of The World” worksheet from first grade.
MAS Extracting
Azure Stack extracting in Server 2016 TP4


You can download the bits for Azure Stack here, and this is a link to the instructions on how to setup Azure Stack.  The instrcutions assume you have met the list of hardware requirements listed here and other requirements listed here.

Since I had a few hours while it installed, I went for a run out at Goldmine trail, which is about 20 minutes from me.


The install threw an error and I had to go visit a customer to upgrade them to Configmgr current branch, so it would have to wait until I got back.  At least, my server was nice and warm at home.


Back at home.  My install on the base OS did not work.  It complained that my time was off.  There is no UTC/local time setting in my BIOS and here in AZ we don’t do DST, so today we are equal to MST.  All times and time zones matched so after talking to a couple Azure TSP’s I decided to install using the VHD method, which worked, not sure why but it did.  It only took about two hours to install instead of the estimated four.

After returning from the northwest…

I completed the setup in my lab and it was pretty painless to get running.  I have since reinstalled it on the same system, starting from scratch.

When setup is complete and you run Failover Cluster Manager, or Hyper-V Manager, you will see the list of VM’s running.  If the PowerShell script throws an error during setup, fix the issue and then run the script again and it will pick up where it left off.  At the end you should have something like this.


Azure Stack VMs

Here is a breakdown of each VM you have running once the installation of Azure Stack has completed:

ADVM Virtual machine that hosts Active Directory, DNS, and DHCP services for Microsoft Azure Stack. These infrastructure foundational services are required to bring up the Azure Stack as well as the ongoing maintenance.

ACSVM Virtual machine that hosts the Azure Consistent Storage services. These services run on the Service Fabric on a dedicated virtual machine.

MuxVM Virtual machine that hosts the Microsoft software load balancer component and network multiplexing services.

NCVM Virtual machine that hosts the Microsoft network controller component, which is a key component of the Microsoft software-defined networking technology. These services run on the Service Fabric on this dedicated virtual machine.

NATVM Virtual machine that hosts the Microsoft network address translation component. This enables outbound network connectivity from Microsoft Azure Stack.

xRPVM Virtual machine that hosts the core resource providers of Microsoft Azure Stack, including the Compute, Network, and Storage resource providers.

SQLVM Virtual machine that hosts SQL Servers which is used by various fabric services (ACS and xRP services).

PortalVM Virtual machine that hosts the Control Plane (Azure Resource Manager) and Azure portal services and various experiences (including services supporting admin experiences and tenant experiences).

ClientVM Virtual machine that is available to developers for installing PowerShell, Visuall Studio, and other tools.

And…Storage services in the operating system on the physical host include:

ACS Blob Service Azure Consistent Storage Blob service, which provides blob and table storage services. SoFS Scale-out File Server. ReFS CSV Resilient File System Cluster Shared Volume.Virtual Disk, Storage Space, and Storage Spaces Direct are the respective underlying storage technology in Windows Server to enable the Microsoft Azure Stack core storage resource provider.


Here is a graphic representation of the VM’s and services.

Azure Stack TP1 POC logical architecture

 Manually Shutting Down Azure Stack

If you need to manually shutdown and startup the VM’s this is the recommended order:


Hyper-V will shut down the other three for you as you shutdown your server.  The start order is reverse.  If you need to manually shut down, after you start the VM’s ensure that all the services on xRPVM are running and happy, if it isn’t you will have issues since it hosts the core of the Azure Stack resources.

Azure Stack Portal

This is a set of images on the ClientVM.  Showing the portal view, then the ARM templates resources imported from GitHub, then as it appears in the marketplace.  Then a custom built VM template imported into the marketplace, using PowerShell to add a SQL 2014 custom VM into the platform image repository (PIR), and finally the custom SQL 2014 VM listed in the marketplace.

Image 001

Image 002

Image 003

Image 004

Image 005

Image 006

Image 008