search

erikbra / grate

grate - the SQL scripts migration runner
MIT License
209 stars 40 forks source link

Azure SQL server databases with elastic pool #581

Closed Tsingis closed 1 month ago

Tsingis commented 2 months ago

We were attempting to use Azure SQL server with databases using SQL elastic pool. However grate seems to be unable to create database in such environment resulting into error before attempting to run any migration scripts.

ALTER DATABASE statement failed.
Changed database context to 'master'.
Microsoft.Data.SqlClient.SqlException (0x80131904): ODBC error: State: 42000: Error: 1468 Message:'[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The operation cannot be performed on database "MyDB" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.'.
ALTER DATABASE statement failed.
Changed database context to 'master'.
   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, SqlCommand command, 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.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<InternalExecuteNonQueryAsync>b__193_1(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at grate.Migration.AnsiSqlDatabase.ExecuteNonQuery(DbConnection conn, String sql, Nullable`1 timeout) in /home/runner/work/grate/grate/src/grate.core/Migration/AnsiSqlDatabase.cs:line 667
   at grate.Migration.AnsiSqlDatabase.ExecuteNonQuery(DbConnection conn, String sql, Nullable`1 timeout) in /home/runner/work/grate/grate/src/grate.core/Migration/AnsiSqlDatabase.cs:line 667
   at grate.Migration.AnsiSqlDatabase.DropDatabase() in /home/runner/work/grate/grate/src/grate.core/Migration/AnsiSqlDatabase.cs:line 218
   at grate.Migration.GrateMigrator.Migrate() in /home/runner/work/grate/grate/src/grate.core/Migration/GrateMigrator.cs:line 110
   at grate.Commands.MigrateCommand.<>c__DisplayClass0_0.<<-ctor>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.NamingConventionBinder.CommandHandler.GetExitCodeAsync(Object returnValue, InvocationContext context)
   at System.CommandLine.NamingConventionBinder.ModelBindingCommandHandler.InvokeAsync(InvocationContext context)
   at System.CommandLine.Invocation.InvocationPipeline.<>c__DisplayClass4_0.<<BuildInvocationChain>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass17_0.<<UseParseErrorReporting>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass12_0.<<UseHelp>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass19_0.<<UseTypoCorrections>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseSuggestDirective>b__18_0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass16_0.<<UseParseDirective>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<RegisterWithDotnetSuggest>b__5_0>d.MoveNext()
--- End of stack trace from previous location ---
   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass8_0.<<UseExceptionHandler>b__0>d.MoveNext()

Workaround was to create (drop first if required) the databases with other tools first, to allow grate to run migrations as part of our pipelines.

This probably is not a bug but something that just cannot be done via grate only.

Full command was grate -cs="***;Database=MyDB" -csa="***;Database=master" --verbosity Debug --silent --drop

Where connection string is Server=<SERVER>;Integrated Security=false;User ID=<USER>;Password=<PASSWORD>;TrustServerCertificate=true;Connection Timeout=30;