A database is a persistent, organized place for storing data when a spreadsheet is no longer an appropriate tool. SQL Server provides structured operations for adding, deleting, updating, and querying records, while PowerShell can automate those operations. section 26.0 section 26.1
A practical workflow starts by connecting to a server and a specific database through a connection string. A database contains tables whose columns match the data produced by an automation command; rows can also record when each observation was added. section 26.1 section 26.3
A maintainable database-backed automation solution typically:
- Uses SQL Server or another relational database management system instead of forcing Excel to act as a database. section 26.0 section 26.1
- Creates a database by connecting to master, then changes the connection string to the working database. section 26.1 section 26.2
- Models command output explicitly, such as computer name, disk size, drive type, free space, drive ID, and collection date. section 26.3
- Keeps reusable connection information at module scope and exports only commands intended for users. section 26.3
Disk-inventory database
A PowerShell module gathers disk information from computers, creates a diskinfo table if it does not exist, and stores typed values plus DateAdded. Scheduled collection and SSRS can turn those records into recurring trend reports. section 26.3 section 26.6
Listing 26.1 Set up a new database on SQL Server Express instance
$conn_string = "Server=localhost\SQLEXPRESS;Database=master;Trusted_Connection=True;" #1 $conn = New-Object System.Data.SqlClient.SqlConnection #2 $conn.ConnectionString = $conn_string #3 $conn.Open() $sql = @" CREATE DATABASE Scripting; "@ #4 $cmd = New-Object System.Data.SqlClient.SqlCommand #5 $cmd.CommandText = $sql #6 $cmd.Connection = $conn #7 $cmd.ExecuteNonQuery() #8 $conn.close() #9
Follow the setup listing to connect to a local SQL Server Express instance, open master, execute CREATE DATABASE Scripting, and close the connection. section 26.2
