DB Creation

[gbjbaanb]

I note the migration scripts do not contain any information on creating a DB. Which is unfortunate as it seems the settings required for it to work are not defined anywhere, and doing it manually (naively) myself caused errors to be thrown by the generated DB script that were not present when I create the DB using .Migrate() in code. (eg creating a spatial index failed, "the following SET options have incorrect settings 'Quoted Identitfier').

So can the SQL Scripts section be updated to include:

DB creation sql script that works for EF databases (even if only for SQL server)

Currently I'm trying to create a production DB that doesn't require running the app with a connection string that is the SA user. And it just doesn't work out of the box, and isn't as easy as it should be. The dotnet ef tooling seems to be entirely geared towards development, not production use.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 6e28b0a8-b7e4-4c07-9e60-0df403bbee28
• Version Independent ID: 12300b31-6f7b-b75c-970d-693365e77013
• Content: Migrations - EF Core
• Content Source: entity-framework/core/managing-schemas/migrations/index.md
• Product: entity-framework
• Technology: entity-framework-core
• GitHub Login: @bricelam
• Microsoft Alias: bricelam

[ajcvickers]

@gbjbaanb Creating a database in the script would require the script connect to the master database, since it can't connect to the database that doesn't yet exist. This involves many complications, and can often fail in databases with appropriate permissions set. It is recommended that you create databases as part of your deployment process, rather than doing it from code. For SQL Azure, the portal should be used to provision databases.

All that being said, we are working on improving this overall experience so that we can produce more than just scripts. See https://github.com/dotnet/efcore/issues/19693

[gbjbaanb]

If you're using scripts, then a DBA is going to be the one running them, and he will have access to the master DB (unless, of course you've configured SQL Server to use Contained Databases ;))

Now, I'm happy to create the DB independantly, and I tried, but when I did so, the script that was generated failed. Suggesting to me that what I did to create a DB wasnt suifficient or was missing configuration that the automatic migration process performed.

I'd like to know what that configuration is, so that I can manually create a DB correctly, that will work with the EF migration scripts in the future. I don't mind if this is documented, or the ability to generate a DB creation script is added to the tooling ("dotnet ef database create -script" for example)

At the moment, I am stuck with having to create the DB using the EF migrate code, and that's really not optimal for production.

[roji]

/cc @bricelam

We sometimes generate some options which are only valid on the CREATE DATABASE DDL, e.g. the database collation (can't be changed after creation in PG, also complications in SQL Server), possibly other stuff.

[ajcvickers]

@gbjbaanb Agreed that it would be good to document what EF does when it creates the database. For now, call Migrate or EnsureCreated with logging on to see what commands EF is sending. For example:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (355ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE DATABASE [Test];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (130ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [Test] SET READ_COMMITTED_SNAPSHOT ON;
      END;

[gbjbaanb]

Good idea - why didn't I think of profiling what it does, d'oh!

It does seem the committed snapshot is the only thing that is different to just running "create database".

However, probably the bit that matters is that before every EF operation, I see this in SQL Profiler:

-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

and I probably needed this before running the migration scripts after manually creating the DB. The "set quoted identifier on" is required to create a spatial index for example.

[ajcvickers]

@gbjbaanb Note that these are not things that EF is setting explicitly. This is just part of the way SqlClient sends commands to the database, which is below the EF level.

[gbjbaanb]

Sure, and thanks. My confidence in the system is restored - the problem lies in the SQL plumbing rather than the generated scripts. So my issue that a DB would be created by EF that wouldn't be created by running the script via sqlcmd (on linux BTW) is something I can deal with. But it was a big concern for something that works perfectly in dev not working properly when moved to production. If you could figure out how to ensure that doesn't happen, it'd be great.