SQL Server Bulk Object Creation for MDT
In Part 1, SQL Express 2019 was integrated with MDT.
In today's scenario, there's nearly 100 client Mac addresses and multiple models of Desktop and Laptop. Each model will be assigned a different Task Sequence with different types of configuration and software.
With a little Excel manipulation, the Mac Address for each client will be assigned a reserved DHCP IP, WDS Pre-staged Device and added to a specific Task Sequence.
Open MS Excel, create the heading 'Hostname','MacAddress','IPAddress'.
Populate the hostame and MacAddress.
The DHCP Scope starts at 10.1.1.150 up to 10.1.1.254.
SaveAs a CSV (Comma Delimited) and name 'PC-List-IP.csv'.
The following steps of reserving IP's in DHCP and Pre-Staging in WDS aren't strictly required, however, I wish to control every aspect of the deployment.
Download the following PowerShell script (here) and rename the extension from .txt to .ps1.
Within the script update the path of the .csv and IP of the DHCP Scope.
Execute the script.
Open DHCP Management console and confirm the reservations.
Open WDS and again confirm the reservations.
Open Deployment Workbench and navigate to 'Database' and then 'Computers'.
Right click and 'New' to create an MDT database object.
Complete both 'Description' and 'Mac Address' on the Identity tab, something made up is fine, this is a reference object used to assist with populating the database.
On the Details tab complete the following:
OSInstall = Y
OSDComputerName = PC01
AdminPassword = Password12345
TaskSequenceID = TEN-Client-v1.0
OSDAdapterCount = 0
OSDAdapter0EnableDHCP=TRUE
OSDAdapter0IPAddressList = 10.1.1.99
OSDAdapter0SubnetMask = 255.255.255.0
OSDAdapter0Gateways = 10.1.1.1
OSDAdapter0DNSServerListOSD = 10.1.1.1
Adapter0DNSSuffix = sh.loc
The values set in the Database will override 'CustomSettings'.
When 'OSDAdapterCount = 1' and 'OSDAdapter0EnableDHCP=FALSE' dynamically set IP's will become static IP's.
Individual administrator passwords can be set, ignoring the common password in CustomSettings.
Open SQL Management Studio and navigate to the MDT Database.
Open Tables and right click on 'dbo.ComputerIdentity' and 'Select Top 1000 Rows'.
Right click on the first row and 'Copy with Headers'.
Open Excel and paste in the row details.
Open PC-List-IP.csv, previously used to populate DHCP and WDS and copy the Hostname and Mac Address's in to the relevant rows. Delete row 1 then SaveAs ComputerID_Export.csv.
Opened in Notepad, ComputerID_Export.csv should look like this.
Now repeat the process with 'dbo.settings' table, Copy with Headers.
Create a new Worksheet in Excel and paste the row details.
Open PC-List-IP.csv, previously used to populate DHCP and WDS and copy the Hostname and IP Address in to the relevant rows.
Update all the other relevant rows, then SaveAs Settings_Export.csv
Opened in Notepad, Settings_Export.csv should look like this.
Open SQL Management Studio.
In the Toolbar click on 'New Query'.
Type the following or download (here) renaming from .txt to .sql.
Execute the query.
Re-run the 'Select Top 1000 Row' for dbo.ComputerIdentity and dbo.Settings
Confirm all the additional rows have been populated.
Open Deployment Workbench and refresh the Computers view, all clients should be present and ready for deployment.
Finally, power-on the clients, they will automatically get assigned the specified IP, Hostname and the correct Task Sequence. All prompts and wizards can be hidden with changes to either CustomSettings or the database objects making deployment virtually zero touch, except of powering on and pressing F12.