Moving from LiteDB to SQL

thefox91 · June 15, 2022, 11:04am

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

Thanks

adam · June 15, 2022, 12:26pm

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.

onehit · July 14, 2022, 3:41pm

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

muravsky · July 15, 2022, 12:22pm

@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

adam · July 15, 2022, 3:18pm

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.

adam · July 15, 2022, 6:12pm

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.

onehit · July 15, 2022, 7:26pm

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

adam · July 15, 2022, 7:28pm

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

onehit · July 15, 2022, 7:30pm

You’re rockstars!

dkkazak · July 18, 2022, 4:06am

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”

jkarlan · August 29, 2022, 2:22pm

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

adam · August 29, 2022, 5:50pm

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?

jkarlan · August 29, 2022, 6:44pm

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)

jkarlan · August 30, 2022, 2:57pm

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

dknodel-appriver · September 15, 2022, 12:45am

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:

Stopped PSU service
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;"

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;"
  },

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

onehit · September 15, 2022, 1:00pm

Hi @dknodel-appriver

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

stop services
point PSU to AutomationSys (an empty dbase)
start services
data migration tool to AutomationSysMig
check what data exists in the AutomationSysMig tables that you want to keep/migrate
stop services
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)
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

dknodel-appriver · September 15, 2022, 6:52pm

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!

mikedhanson · January 17, 2023, 2:54pm

Trying to migrate one of our PSU instances over to sql and i keep getting the following error.

commandline

PS C:\Program Files (x86)\Universal> .\DataMigration.exe -l C:\path\to\database.db -s "Server=localhost\\SQLEXPRESS;Database=migrated;Trusted_Connection=True;TrustServerCertificate=True;"

Error

Cannot use file stream for [C:\Program Files (x86)\Universal\DataMigration.deps.json]: No such file or directory
Creating database
Unhandled exception. System.InvalidOperationException: Instance failure.
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext c, TState s)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass31_0`2.<Execute>b__0(DbContext context, TState state)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementation[TState,TResult](Func`3 operation, Func`3 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
   at DataMigration.Program.<>c.<Main>b__2_0(Options o)
   at CommandLine.ParserResultExtensions.WithParsed[T](ParserResult`1 result, Action`1 action)
   at DataMigration.Program.Main(String[] args)
PS C:\Program Files (x86)\Universal>

adam · January 18, 2023, 9:57am

What happens if you change: localhost\\SQLEXPRESS to localhost\SQLEXPRESS?

mikedhanson · January 18, 2023, 2:15pm

Funny you ask I tried that and it seemed to work better but ran into the following error.

PS C:\Program Files (x86)\Universal> .\DataMigration.exe -l C:\Work\database.db -s "Server=PSU1\SQLEXPRESS;Database=migrated;Trusted_Connection=True;TrustServerCertificate=True;"

Migrating GitStatus 98817
Migrating GitStatus 98818
Migrating GitStatus 98819
Migrating GitStatus 98820
Migrating GitStatus 98821
Migrating TerminalInstance 1
Migrating Job 15202
Migrating Job 15203
Migrating Job 15206
Migrating Job 15211
Unhandled exception. System.InvalidOperationException: Sequence contains no elements
   at System.Linq.ThrowHelper.ThrowNoElementsException()
   at lambda_method1352(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](IQueryable`1 source)
   at DataMigration.Program.<>c.<Main>b__2_5(Job item, PsuDbContext dbContext)
   at DataMigration.Program.TransferItems[T](String connectionString, String tableName, IEnumerable`1 items, Func`3 preProcess, Boolean setIdentity)
   at DataMigration.Program.<>c.<Main>b__2_0(Options o)
   at CommandLine.ParserResultExtensions.WithParsed[T](ParserResult`1 result, Action`1 action)
   at DataMigration.Program.Main(String[] args)

Topic		Replies	Views
Transition from sqlite to ms sql PowerShell Universal	1	326	June 14, 2022
Migrating LiteDB to PostgreSQL after Universal V5 Upgrade Universal Dashboard	4	60	October 17, 2024
DB-Migration from LiteDB to SQLite possible? PowerShell Universal	2	102	August 12, 2024
SQLLite to SQL Migration PowerShell Universal	4	385	March 10, 2024
Upgrade LiteDB to V5 didnt work as per PowerShell Universal Docs	7	807	February 22, 2024

Moving from LiteDB to SQL

Related topics