Moving from LiteDB to SQL

Hi,

What is the process for moving from litedb to sql. Just run the msi and put in the new connection string in? will i lose anything stored in the litedb.

want to check before triggering the upgrade :slight_smile:

Thanks

Hi,

We currently don’t have a migration process for LiteDB data to SQL. We have had several people ask for this so we are looking to include some sort of process in v3.1.

You will not lose the data in the LiteDB database during upgrade but it won’t be migrated into the SQL database so it will appear empty. The database.db file for LiteDB will still exist on disk.

Were you able to include anything in 3.1 for app tokens, or have any plans? We just upgraded and noticed the app tokens don’t transfer. Tried recreating a new one to be sure it functioned as expected and got a success, then manually changed the value to the old one in the sql db and it failed.

Just trying to figure out if we need to issue new tokens to our clients and how best to roll that out

@adam Looking into updating to version 3.x and also stumble on this issue. Please update this thread if any kind of migration tool will be included in upcoming version. I have quite a lot data in LightDB and moving it manually is not really an option.

Thanks,
Igor

We are still working on this tool. It will be available in 3.2. We are working out some kinks because of data integrity issues in the LiteDB database require us to patch up what’s going into SQL since it has referential constraints.

You’ll be able to specify a LiteDB database and SQL connection string and it will create the database and populate it with your data.

DataMigration.exe -l "C:\ProgramData\UniversalAutomation\database.db" -s "Server=ADAMDESK;Database=Migrated;Trusted_Connection=Yes"
Creating database
Migrating Identity 1
Migrating Identity 2
Migrating Identity 3
Migrating Identity 4
Migrating AppToken 1
...

I’ll update this thread once we have a nightly build that works so you can give it a shot.

1 Like

We’ve successfully migrated our integration test data using this tool! I think we might still have some things to iron out but it will be available in the install folder starting in tonight’s nightly build.

1 Like

Awesome news! Would the tool be usable on a 3.1.1 server, or will it require 3.2?

It would work with v3.1.1. We haven’t made any schema changes in 3.2.

1 Like

You’re rockstars!

I attempted to do this tonight and recieved the following error:
Cannot use file stream for [C:\Program Files (x86)\Universal\DataMigration.deps.json]: No such file or directory
Unhandled exception. System.IO.FileLoadException: Could not load file or assembly ‘CommandLine, Version=2.9.1.0, Culture=neutral, PublicKeyToken=5a870481e358d379’. The located assembly’s manifest definition does not match the assembly reference. (0x80131040)
File name: ‘CommandLine, Version=2.9.1.0, Culture=neutral, PublicKeyToken=5a870481e358d379’
at DataMigration.Program.Main(String args)

This is using the nightly build 2687396605. Any ideas on what I did wrong?

This was my cmdline:
DataMigration.exe -l “C:\Temp\database.db” -s “Server=SQLServer1;Database=PUDAPI;Trusted_Connection=Yes”

I’m having some similar issues Server version 3.2.5.

PSU is creating the SQL Db w/ DBcreator role, and running properly. I stop the service and migrate from Lite DB:

PS C:\ProgramData\PowerShellUniversal\Server> DataMigration.exe -l C:\ProgramData\UniversalAutomation\database.db -s ‘Se
rver=ESGSQLAUTOMTEST.wintest.adtest.jhu.edu;Database=AutomationSys;Integrated Security=True;’
Cannot use file stream for [C:\ProgramData\PowerShellUniversal\Server\DataMigration.deps.json]: No such file or directory
Creating database
Migrating Identity 1

Migrating TerminalInstance 14
Unhandled exception. System.InvalidOperationException: Sequence contains no elements
at System.Linq.ThrowHelper.ThrowNoElementsException()
at lambda_method529(Closure , QueryContext )
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
at System.Linq.Queryable.First[TSource](IQueryable1 source) at DataMigration.Program.<>c.<Main>b__1_2(TerminalInstance item, PsuDbContext dbContext) at DataMigration.Program.TransferItems[T](String connectionString, String tableName, IEnumerable1 items, Func3 preProcess) at DataMigration.Program.<>c.<Main>b__1_0(Options o) at CommandLine.ParserResultExtensions.WithParsed[T](ParserResult1 result, Action`1 action)
at DataMigration.Program.Main(String args)
PS C:\ProgramData\PowerShellUniversal\Server>

Then starting the Powershell Universal Service it crashes with the following error:
Application: Universal.Server.exe
CoreCLR Version: 6.0.822.36306
.NET Version: 6.0.8
Description: The process was terminated due to an unhandled exception.
Exception Info: Microsoft.Data.SqlClient.SqlException (0x80131904): There is already an object named ‘Computer’ in the database.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade) at PowerShellUniversal.SQL.DatabaseFeature.InitializeDatabase(IConfiguration configuration) in C:\actions-runner\_work\universal\universal\src\PowerShellUniversal.SQL\Plugin.cs:line 53 at Universal.Server.Services.PluginService..ctor(IConfiguration configuration) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Services\PluginService.cs:line 73 at Universal.Server.Startup.ConfigureServices(IServiceCollection services) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Startup.cs:line 63 at System.RuntimeMethodHandle.InvokeMethod(Object target, Span1& arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture)
at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.InvokeCore(Object instance, IServiceCollection services)
at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.<>c__DisplayClass9_0.g__Startup|0(IServiceCollection serviceCollection)
at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.Invoke(Object instance, IServiceCollection services)
at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.<>c__DisplayClass8_0.b__0(IServiceCollection services)
at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.UseStartup(Type startupType, HostBuilderContext context, IServiceCollection services, Object instance)
at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass13_0.b__0(HostBuilderContext context, IServiceCollection services)
at Microsoft.Extensions.Hosting.HostBuilder.CreateServiceProvider()
at Microsoft.Extensions.Hosting.HostBuilder.Build()
at Universal.Server.Program.<>c__DisplayClass3_0.b__0(Options o) in C:\actions-runner_work\universal\universal\src\Universal.Server\Program.cs:line 71
at CommandLine.ParserResultExtensions.WithParsed[T](ParserResult1 result, Action1 action)
at Universal.Server.Program.Main(String args) in C:\actions-runner_work\universal\universal\src\Universal.Server\Program.cs:line 53
ClientConnectionId:a90d1063-4c11-490c-91fb-170b5523cb21
Error Number:2714,State:6,Class:16

It appears it’s attemptint to create the database again in the migrator. Is it possible to try dropping the sql db and running the migrator again?

Thanks for jumping in Adam! I love the service!

I stopped the Powershell Universal Service.
Deleted the Db
Ran datamigration.exe; same errors as previous

Checked that the Db had been created.
Started the Powershell Universal service; same crash:
Application: Universal.Server.exe
CoreCLR Version: 6.0.822.36306
.NET Version: 6.0.8
Description: The process was terminated due to an unhandled exception.
Exception Info: Microsoft.Data.SqlClient.SqlException (0x80131904): There is already an object named ‘Computer’ in the database.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

The migration appears to be failing on entry #14 which isn’t a user that we are aware of?

We are getting the error mentioned above (“There is already an object named ‘Computer’ in the database”), however for us it’s after DataMigration.exe having run successfully, and instead while running the service for the first time after migrating.

This is a relatively fresh deployment (test box with new (3.3.1) PSU version installed, migrated repo from 2.12). Everything seems to be working fine using LiteDB, but when I try to migrate to SQL, the service fails to start. If I change the appsettings.json back to LiteDB, it works again. I verified that it created all of the tables in the DB during the migration process.

Not likely related, but the user in the DataMigration process was a different user than the one in the appsettings.json. I needed to create a user specific for the PSUv3 DB since the creds are in plain text in appsettings.json. The second user has been mapped as db_owner for that DB, and just to be sure I granted all of the permissions to it as well. Changing it back to the creating user does not correct the issue.

Edit: I just renamed the Computer table to ‘Computerfake’, and got an error on a different table (There is already an object named ‘PK_Computer’ in the database).

Steps:

  1. Stopped PSU service
  2. ran migration tool successfully:
.\DataMigration.exe -l C:\ProgramData\UniversalAutomation\database.db -s "Data Source=XXXServer\XXXInstance; User ID=XXX; Password=XXX;Database=PSUv3;TrustServerCertificate=True;"
  1. Changed appsettings.json:
  "Plugins": [
    "SQL"
  ],
  "Data": {
    "RepositoryPath": "%ProgramData%\\UniversalAutomation\\Repository",
    "ConnectionString": "Data Source=XXXServer\\XXXInstance; User ID=XXX; Password=XXX;Initial Catalog=PSUv3;TrustServerCertificate=True;"
  },
  1. Start service. Service fails to start and generates this error in application event logs:
Application: Universal.Server.exe
CoreCLR Version: 6.0.922.41905
.NET Version: 6.0.9
Description: The process was terminated due to an unhandled exception.
Exception Info: Microsoft.Data.SqlClient.SqlException (0x80131904): There is already an object named 'Computer' in the database.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
   at PowerShellUniversal.SQL.DatabaseFeature.InitializeDatabase(IConfiguration configuration) in C:\actions-runner\_work\universal\universal\src\PowerShellUniversal.SQL\Plugin.cs:line 53
   at Universal.Server.Services.PluginService..ctor(IConfiguration configuration) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Services\PluginService.cs:line 73
   at Universal.Server.Startup.ConfigureServices(IServiceCollection services) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Startup.cs:line 64
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Span`1& arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.InvokeCore(Object instance, IServiceCollection services)
   at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.<>c__DisplayClass9_0.<Invoke>g__Startup|0(IServiceCollection serviceCollection)
   at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.Invoke(Object instance, IServiceCollection services)
   at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.<>c__DisplayClass8_0.<Build>b__0(IServiceCollection services)
   at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.UseStartup(Type startupType, HostBuilderContext context, IServiceCollection services, Object instance)
   at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass13_0.<UseStartup>b__0(HostBuilderContext context, IServiceCollection services)
   at Microsoft.Extensions.Hosting.HostBuilder.CreateServiceProvider()
   at Microsoft.Extensions.Hosting.HostBuilder.Build()
   at Universal.Server.Program.<>c__DisplayClass3_0.<Main>b__0(Options o) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Program.cs:line 71
   at CommandLine.ParserResultExtensions.WithParsed[T](ParserResult`1 result, Action`1 action)
   at Universal.Server.Program.Main(String[] args) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Program.cs:line 53
ClientConnectionId:ffd1d2c0-5a53-4283-aaf6-b8e883730672
Error Number:2714,State:6,Class:16

Hi @dknodel-appriver

We ended up getting around the errors by doing the following:

  1. stop services
  2. point PSU to AutomationSys (an empty dbase)
  3. start services
  4. data migration tool to AutomationSysMig
  5. check what data exists in the AutomationSysMig tables that you want to keep/migrate
  6. stop services
  7. select into from the AutomationSysMig to AutomationSys, excluding records that already exist from steps 2/3 (code we used below but you will want to verify it matches your environment)
  8. start services
SET IDENTITY_INSERT [AutomationSys].[dbo].[Identity] ON

INSERT INTO [AutomationSys].[dbo].[Identity] ([Id]
      ,[Name]
      ,[Source]
      ,[RoleName])
SELECT [Id]
      ,[Name]
      ,[Source]
      ,[RoleName]
  FROM [AutomationSysMig].[dbo].[Identity]
  WHERE [Id] != '1'


SET IDENTITY_INSERT [AutomationSys].[dbo].[Identity] OFF

SET IDENTITY_INSERT AutomationSys.dbo.AppToken ON

INSERT INTO [AutomationSys].[dbo].[AppToken] ([Id]
      ,[Token]
      ,[IdentityId]
      ,[Revoked]
      ,[Role]
      ,[Created]
      ,[Expiration]
      ,[RevokedDate]
      ,[CreatedById]
      ,[Description])
SELECT [Id]
      ,[Token]
      ,[IdentityId]
      ,[Revoked]
      ,[Role]
      ,[Created]
      ,[Expiration]
      ,[RevokedDate]
      ,[CreatedById]
      ,[Description]
  FROM [AutomationSysMig].[dbo].[AppToken]

SET IDENTITY_INSERT AutomationSys.dbo.AppToken OFF

SET IDENTITY_INSERT AutomationSys.dbo.Job ON
INSERT INTO [AutomationSys].[dbo].[Job] ([Id]
      ,[CreatedTime]
      ,[StartTime]
      ,[EndTime]
      ,[Status]
      ,[Output]
      ,[IsScriptDeleted]
      ,[ScriptFullPath]
      ,[ScriptCommitId]
      ,[AppTokenId]
      ,[IdentityId]
      ,[ParentJobId]
      ,[ParentLineNumber]
      ,[Debug]
      ,[ComputerName]
      ,[Port]
      ,[ProcessId]
      ,[MemoryBytes]
      ,[RunspaceId]
      ,[Activity]
      ,[CurrentOperation]
      ,[PercentComplete]
      ,[SecondsRemaining]
      ,[StatusDescription]
      ,[Environment]
      ,[AgentId]
      ,[ComputerId]
      ,[ErrorAction]
      ,[Notes]
      ,[Credential]
      ,[ScheduleId]
      ,[Triggered]
      ,[Trigger]
      ,[AccessToken]
      ,[IdToken]
      ,[RetryCount]
      ,[Tags])
SELECT [Id]
      ,[CreatedTime]
      ,[StartTime]
      ,[EndTime]
      ,[Status]
      ,[Output]
      ,[IsScriptDeleted]
      ,[ScriptFullPath]
      ,[ScriptCommitId]
      ,[AppTokenId]
      ,[IdentityId]
      ,[ParentJobId]
      ,[ParentLineNumber]
      ,[Debug]
      ,[ComputerName]
      ,[Port]
      ,[ProcessId]
      ,[MemoryBytes]
      ,[RunspaceId]
      ,[Activity]
      ,[CurrentOperation]
      ,[PercentComplete]
      ,[SecondsRemaining]
      ,[StatusDescription]
      ,[Environment]
      ,[AgentId]
      ,[ComputerId]
      ,[ErrorAction]
      ,[Notes]
      ,[Credential]
      ,[ScheduleId]
      ,[Triggered]
      ,[Trigger]
      ,[AccessToken]
      ,[IdToken]
      ,[RetryCount]
      ,[Tags]
  FROM [AutomationSysMig].[dbo].[Job]
  WHERE Job.Id NOT IN (Select Id from AutomationSys.dbo.Job)
SET IDENTITY_INSERT AutomationSys.dbo.Job OFF

INSERT INTO [AutomationSys].[dbo].[JobLog] ([Id]
      ,[Log])
SELECT [Id]
      ,[Log]
  FROM [AutomationSysMig].[dbo].[JobLog]
  WHERE JobLog.Id NOT IN (Select Id from AutomationSys.dbo.JobLog)


SET IDENTITY_INSERT AutomationSys.dbo.JobOutput ON
INSERT INTO [AutomationSys].[dbo].[JobOutput] ([Id]
      ,[Message]
      ,[Type]
      ,[Data]
      ,[JobId]
      ,[Timestamp])
SELECT [Id]
      ,[Message]
      ,[Type]
      ,[Data]
      ,[JobId]
      ,[Timestamp]
  FROM [AutomationSysMig].[dbo].[JobOutput]
  WHERE JobOutput.Id NOT IN (Select Id from AutomationSys.dbo.JobOutput)
SET IDENTITY_INSERT AutomationSys.dbo.JobOutput OFF
1 Like

Yooooooooo! That worked perfectly, thanks so much!

So once it creates the DBs itself, you can just re-migrate the data from the migrated DB to the initialized one. I guess the fix would probably be to have DataMigration.exe set a flag on the DB (or have something in appsettings.json) that tells PSU not to run the initialize DB step. Maybe this one here?

PowerShellUniversal.SQL.DatabaseFeature.InitializeDatabase(IConfiguration configuration) in C:\actions-runner_work\universal\universal\src\PowerShellUniversal.SQL\Plugin.cs:line 53

Anyway, thanks again @onehit ! Now I can proceed with our upgrade!

1 Like