What is the best way to handle quick running jobs that have “big” modules like dbatools? For instance, we have some vendor apps that we need to sync data but not all of them have webhooks so sometimes we use the last modified field so we have to keep checking. It takes longer to load the dbatools module than to run the actual script part. I know it is best practice not to use the integrated environment. When using integrated env it is fast because the module is already loaded but when using ps7 it has to import the module every run. When we have a lot of these little jobs some get queued up. Any thoughts or ideas would be helpful.
1 Like
Have you considered persistent runspaces for an API that does your change lookup?
I ended up doing that for now
1 Like
The persistent runspaces thing is another good idea, but for me I just use Invoke-SQLCmd2 which is a function that I dot source into my scripts, it requires no other dependencies and it’s super lightweight. Granted, it’s just for straight forward SQL queries and doesnt do everything that dbatools does, but depending on use case it can be beneficial. I run my instance as a service with an account that has permissions to the database that way it just passes through authentication, all I need to do is splat a server and database onto my sql commands and i’m good to go, I have these defined as variables, which are different in dev and prod so the code stays the same.
1 Like
How did you install dbatools? I use PSU, dbatools, and use PowerShell 7. I installed it using the scope of AllUsers. Its just available, no need to load it. Also… when you setup an environment, on the Resources tab you can specify what modules to import but I’m not sure if that is pre-loaded or not. I don’t use the Integrated environment.
In addition, you can use ChatGPT. Copy your code using dbatools and ask it to use standard .net, it’ll just convert it for you. You might need to tweak a couple of things. For example, regular .Net needs a function for DB-Null that dbatools handles for you. In dbatools, a $null value is auto-converted to DB-Null.
Example:
function To-DbNull($value) {
if ($null -eq $value) { return \[DBNull\]::Value }
if ($value -is \[string\] -and \[string\]::IsNullOrWhiteSpace($value)) { return \[DBNull\]::Value }
return $value
}
Then properties are assigned like this:
$r[“ManagementHost”] = To-DbNull $vm.ManagementHost
$r[“VMUniqueId”] = To-DbNull $vm.VMUniqueId
$r[“Name”] = To-DbNull $vm.Name
$r[“IPAddress”] = To-DbNull $vm.IPAddress
Lastly, if you have large amounts of data: thousands of records with thousands of one-to-many tables of data, then you want to use TVPs (table value properties) in SQL server. Data can take 10+ hours to insert record by record. When using TVPs… seconds. ChatGPT also does a great job of creating everything: SQL tables, stored procedures, TVPs, and all the PowerShell code to make it work. Its great at saving sessions too. It’ll remember your code, your questions, and if you get an error, just post the error and it’ll fix it.
Personally, after using it, its insane (IMHO) not to use AI.