Migrating to MS SQL?

PowerShell Universal
1 
Product: PowerShell Universal
Version: 5.6.5

Hi.

We are migrating all our scripts to new servers running 5.6. Now some schedules starts failing with “Error executing job: SQLite Error 5: ‘database is locked’.” and I realize that it would maybe have been a good idea to put this database on a real SQL server instead. Can’t seem to find any good guides on how to do this though, is it doable on an already running environment?

2

It should be as easy as just updating your appsettings file:

Persistence | PowerShell Universal

I run PSU as a service with a service account that has access to the SQL db in question, so no credentials etc required.

Note that somethings that were code driven will now be stored in the database (such as schedules) though from experience, if you leave your schedules.ps1 in place, when you start up it will generate the table and entries in SQL and migrate everything for you, but obviously worth doing in dev/test first and confirming everything is working as expected.

1 Like
3

Great! What about secret variables? We have quite a lot of those saved in the database, I guess I will have to recreate them in the new database?

4

The definitions will still be in your secrets.ps1 I believe but yeah you’ll need to re-set the values for them.

1 Like
5

What connection string do you use for SQL? We keep hitting an issue where we are hosting the PSU in IIS and the certificate is not trusted. It is ok though when used with the SQL Studio manager. Thank you for any examples.

6

Oh. By pure coincidence, I hit that exact same wall. We don’t use IIS though. The cert used by our SQL server is signed by our internal PKI, I guess we need to make PSU trust that, question is exactly how to do that?

edit: Worked this around by adding TrustServerCertificate=true; to the connection string, just to get things going. Guess I’ll need a proper fix though.

Also noticed that of course our job history is gone. We can do without it, just worth noticing!

7

Server=Servername;Database=DatabaseName;Trusted_Connection=True;;Encrypt=True;TrustServerCertificate=True;

1 Like
8

This worked for us, thank you so much!