Apply database updates without the data service

Your software vendor ships database changes as signed packages, but you do not run the data service. In that case, the “WKL Database Update” service applies the packages on the server: checked, backed up, in one transaction, and after your confirmation where you want one.

graph LR
    H[Vendor] -->|signed package| E[Inbox]
    E --> D[WKL Database Update service]
    K[Protected configuration] --> D
    D -->|check, back up, apply| B[(Database)]
    A[Administrator] -->|command line| D

You install the service, create a protected configuration with the vendor's keys for each database, and give the service account the permissions it needs in the database. From then on, the service takes every package from the inbox. You confirm what should wait with the command line on the server. For all details, see the reference.

1. Install the package as an administrator:

   msiexec /i Wkl.Data.Update-1.0.0.msi /qn /l*v install.log

   The Wkl.Data.Update service then runs as NT SERVICE\Wkl.Data.Update. For a group managed service account, add UPD_SERVICE_ACCOUNT=DOMAIN\name$.

2. Give the service account a login in SQL Server and, in the database, the permissions to change its structure and back it up, for example the db_owner role. If open sessions of older applications should hold a deadline back, the login also needs VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE from SQL Server 2022).

   CREATE LOGIN [NT SERVICE\Wkl.Data.Update] FROM WINDOWS;
   USE Kunden;
   CREATE USER [NT SERVICE\Wkl.Data.Update] FOR LOGIN [NT SERVICE\Wkl.Data.Update];
   ALTER ROLE db_owner ADD MEMBER [NT SERVICE\Wkl.Data.Update];

   If SQL Server runs on another computer, the virtual account appears there as the computer account (DOMAIN\SERVER$); use a group managed service account instead.

3. Create the inbox and the archive folder. Only Administrators, SYSTEM, and the service account may change them; the service creates the subfolders done and failed and archives packages:

   mkdir D:\Updates\Kunden D:\Updates\Kunden-Archiv
   icacls D:\Updates\Kunden /inheritance:r /grant:r *S-1-5-32-544:(OI)(CI)F *S-1-5-18:(OI)(CI)F "NT SERVICE\Wkl.Data.Update:(OI)(CI)M"
   icacls D:\Updates\Kunden-Archiv /inheritance:r /grant:r *S-1-5-32-544:(OI)(CI)F *S-1-5-18:(OI)(CI)F "NT SERVICE\Wkl.Data.Update:(OI)(CI)M"

4. As an administrator, create the protected configuration in C:\ProgramData\WKL\Database Update\databases\kunden.json:

   {
     "format": 1, "id": "kunden", "product": "Kunden", "dialect": "sqlserver",
     "connection": { "string": "Server=sql01;Database=Kunden;Integrated Security=True;Encrypt=True" },
     "keys": [ "MFkwEwYHKoZIzj0CAQYIKoZIzj0DAQcDQgAE…" ],
     "inbox": "D:\\Updates\\Kunden", "archive": "D:\\Updates\\Kunden-Archiv",
     "backup": { "mode": "native", "target": "D:\\Sicherung" }
   }

   keys is the public key your vendor gives you; product is the product name in the vendor's packages. backup.target is a folder on the database server.

5. From an elevated command prompt, check that the service accepts the configuration:

   "C:\Program Files\Wkl\Database Update\Wkl.Data.Update.exe" databases

   If the file is listed as left out (“nicht beachtet”), the line gives the reason.

6. Set what should wait, for example confirm every package and also report to a file:

   Wkl.Data.Update.exe settings kunden --expand confirm --contract confirm --channels eventlog,file

7. Put the vendor's packages into the inbox, or hand one over directly:

   Wkl.Data.Update.exe upload kunden D:\Lieferung\Kunden-schema-14.zip

8. See what is waiting, and confirm:

   Wkl.Data.Update.exe status kunden
   Wkl.Data.Update.exe confirm kunden 41

• The commands run only on the server itself and only from an elevated command prompt.
• On a confirmation, the service records your Windows account in the database as the one who confirmed.
• With contract = automatic, the service announces a breaking package and applies it at the deadline; move-deadline moves the deadline, cancel-notice withdraws the announcement.
• Reports go to the event log (source WKL Database Update) and, when switched on, to C:\ProgramData\WKL\Database Update\log. For mail, add the mail server as mail to the protected configuration and turn on the channel with recipients: settings kunden --channels eventlog,email --email-to "it@example.com".
• The command line and the service answer in German.
• If you run the data service, use its update module instead.

• WKL Database Update service (reference)
• Service configuration (reference)