0

Context

We have a series of development databases - Azure SQL - that we use for testing. These databases are frequently dropped and created again. We want to start using an Elastic Pool for a better managing of costs. We're using Entity Framework Core to create and upgrade the databases with a Code First strategy. There is the possibility of not wanting to use the pool for one or more of the environments, so this should be configurable, therefore, migrations do not seem like a viable choice.

Problem

So, i'm having trouble trying to provision an instance within an Elastic Pool, using EF Core. Based on the info on this link, I tried the following: modelBuilder.HasPerformanceLevelSql($"ELASTIC_POOL (name = [{_elasticPoolName}])");

This is being set inside OnModelCreating(ModelBuilder modelBuilder) of the DBContext.

Now, I would've expected the underlying T-SQL to be something like: CREATE DATABASE [DatabaseSomething] ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = ElasticPoolSomething ) ) ;

However, having enabled a logger, and looking at the actual underlying generated T-SQL, I only see: CREATE DATABASE [DatabaseSomething];

It's almost as if EF is ignoring the ELASTIC_POOL setting on the model builder.

Additional info

  • Having already failed a lot of tries against the actual Elastic Pool in our Azure, I am currently testing against a local SQL instance, which does not have an Elastic Pool, obviously, but i assume the generated T-SQL would still reflect the setting. Or am I wrong about this?
  • Also tried: modelBuilder.HasPerformanceLevelSql($"ELASTIC_POOL (name = {_elasticPoolName})"); (no square brackets)
  • Also tried: modelBuilder.HasPerformanceLevelSql($"SERVICE_OBJECTIVE = ELASTIC_POOL (name = [{_elasticPoolName}])");

Does anyone have an idea of something i might be missing? Or why this is happening?

Thanks in advance!

Adelmo Magalhães
  • 41
  • 3
  • I've had problems in the past when creating migrations, where if I changed database target (e.g. MSSql to MySql), then the migrations were incorrect as they were built for the other target. Try deleting all your migrations folder, and creating a new migration – Neil Aug 12 '20 at 13:15
  • Have you (re)generated the migration after specifying that option? There must be something like `migrationBuilder.AlterDatabase().Annotation("SqlServer:EditionOptions", "SERVICE_OBJECTIVE = ELASTIC_POOL ( name = myelasticpool )");` inside the migration class file. – Ivan Stoev Aug 12 '20 at 13:47
  • Thanks guys. But I do not want to "alter" an existing database. I want to be able to create a database within an Elastic Pool. These are development databases, so they are re-created frequently, and so I would want to be able to use an ElasticPool or not, based on configuration. It's always going to be an Azure SQL database. Because of these reasons, using the migrations would not be a solution for what i want. – Adelmo Magalhães Aug 12 '20 at 15:28

0 Answers0