1.1 INTRODUCTION
I’ve been building Standard Operating Environments (SOE) in one form or another for years starting with Windows NT 4 Workstation (around the year that Charles and Diana Spencer divorced), right up to Windows 10 and Windows Server 2016 (the year that Jonathan Francetic and Molly Duff from MAFS divorced [I swear had to Google that, I’ve never actually seen an episode!]).
I’ve built them using Visual Basic Scripting (VBS), Business Desktop Deployment (BDD), Microsoft Deployment Toolkit (MDT), System Centre Configuration Manager (SCCM) and even KixTart!
But for the first time ever, I was asked whether I could make MDT Highly Available (HA) including the MDT database.
1.2 WHY?
Having the ability to build a new Virtual Machine (VM) is not usually considered a HA activity. If a datacentre goes down and you desperately needed a new VM built, you can:
- Restore the MDT server
- Failover a replicated MDT VM using either Site Recovery Manager (SRM) or Azure Site Recovery (ASR)
- Build a VM manually and install mandatory applications
- Wait for a couple of days until the primary datacentre comes back online
But what happens when:
- You are providing a 24/7 operation to your clients
- When you guarantee uptime even if a datacentre goes offline
- You host VM’s on a mixture of Hyper-V and VMWare
You have so many systems to bring up in the second datacentre if the primary one fails, that frankly you want as many automatic failovers as possible
1.3 WHERE DO WE START?
There are four moving parts to consider:
- The MDT deployment share
- The linked deployment share on a server in the second datacentre
- The MDT database
- SQL Server
Should be a piece of cake, right? SQL Always-on availability cluster, linked deployment shares and use Active Directory (AD) Domain accounts for everything?
Yeah…Nah…
MDT tries to make life easy. It makes certain assumptions, forms certain concatenated strings to access the database and prefers Named Pipes access for SQL.
The last part is the tricky one. Almost every guide to successfully configuring MDT to talk to the database reliably stipulates the use of Named Pipes. To do this you would need to:
- Enable Remote Named Pipes on your SQL server
- Configure a share on your SQL server that MDT can use to establish an authenticated connection
- Configure the CustomSettings.ini file in MDT with a ‘SQLShare=’ parameter and to use the DBNMPNTW Network Library
So, what’s so hard about that?
- A SQL Listener for a SQL cluster only uses TCP/IP. Named Pipes are not available for a listener…
- MDT still wants to help by constructing a full path to the SQL share for you in the format \servernameSQLShareName, but as you can imagine, \MDTListenerSQLShareName isn’t a valid path!
1.4 THE SOLUTION
After a day of banging my head at each failed attempt to resolve this, error messages about Windows PE not being able to use AD authentication, the answer came courtesy of one of my Directors.
Mr. Albert Altit, please take a bow!
To fix this, Named Pipes and the SQL Share needed to be removed as dependencies. The steps to achieve this are:
- Create a login with SQL authentication on the first SQL server
- Create the same SQL login on the second server. Note that it is very important that the same SID is used for the account on both SQL servers
- Alter the customsettings.ini file in the MDT Workbench (or directly from the Control directory of the MDT share)
1.4.1 Creating the SQL account
No surprises here. Create a new SQL account (I’ve called mine ‘MDTConnect’).
Endure that the ‘User must change password at next login’ is cleared.
Grant the new account read rights to the MDT database.
1.4.2 Creating the SQL login on the second SQL cluster member
It’s really important to have the SID of the login account on the second server match the SID on the first server.
To obtain the SID, logon to the first SQL server and start a new query.
use MDTDB
go
select name,sid from sys.sysusers WHERE Name = ‘MDTConnect’
Copy the SID from the results of this query and use it to create the account on the second server.
Run the following code in a new query window on the second SQL Server. Obviously substitute the name of your MDT database, your SID and your password for the MDTConnect account.
USE [master]
GO
CREATE LOGIN [MDTConnect] WITH PASSWORD=‘MyCleverPassword’, SID=0x00E123D4ADD5A67891AB9876FE567810, DEFAULT_DATABASE=MDTDB, DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Finally, we can put the finishing touches together and remove any requirement on the SQL Share or on Named Pipes!
In Customsettings.ini, there are a number of database related sections. They are all very similar in purpose, structure and wording. For each database related section, change the following:
- Change DBNMPNTW to DBMSSOCN
- Remove the ‘SQLShare=’ line
- Create a new entry ‘DBID=MDTConnect’
- Create a new entry ‘DCPWD=YourPassword’
Each section should now look something like the following:
[CSettings]
SQLServer=MDTListener
Database=MDTDB
Netlib=DBMSSOCN
DBID=MDTConnect
DBPWD=<DB Password for MDTConnect>
Table=ComputerSettings
Parameters=UUID, AssetTag, SerialNumber, MacAddress
ParameterCondition=OR
And that’s that! Both MDT instances can access the same database via the listener.
Now I wonder what’s on TV?
