3

I'm trying to setup an SQL database project (using Visual Studio 2017).

I want this to create a dacpac that could be run on either SQL Server or Azure SQL, would create a database from scratch with a single publishing.

However, I'm running into couple issues:

  • I can't create a memory optimised table if there are no existing database (and tables). When running this (SQL Server):

    CREATE TABLE [dbo].[TicketReservationDetail] (
    TicketReservationID BIGINT  NOT NULL,
    TicketReservationDetailID BIGINT IDENTITY   NOT NULL,
    Quantity    INT             NOT NULL,
    FlightID    INT             NOT NULL,
    Comment      NVARCHAR (1000),

CONSTRAINT [PK_TicketReservationDetail]  PRIMARY KEY NONCLUSTERED HASH
        ([TicketReservationDetailID]) WITH (BUCKET_COUNT=10000000)
) WITH (MEMORY_OPTIMIZED=ON);
GO
ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP [mod] CONTAINS MEMORY_OPTIMIZED_DATA

It gives me this:

(63,1): SQL72014: .Net SqlClient Data Provider: Msg 10794, Level 16, State 125, Line 1 The operation 'AUTO_CLOSE' is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup.
(63,0): SQL72045: Script execution error.  The executed script:
ALTER DATABASE [$(DatabaseName)]
     ADD FILEGROUP [mod] CONTAINS MEMORY_OPTIMIZED_DATA;

An error occurred while the batch was being executed.

However, if I publish a disk-table first then the SQL above works just fine.

I've tried having pre deployment script where I would create disk-table schema, It publishes the disk-table, and fails when it tries to publish the actual memory optimised table schema.

I've used sample database from: Ticket-reservations

Any ideas or suggestions would be appreciated.

Stenso
  • 31
  • 1
  • 1
    If you haven't already done so, make sure the `Auto close` database setting is unchecked under Project Settings-->Database Settings...-->Operational. – Dan Guzman Sep 03 '18 at 10:44
  • If the option is off in your project, check if the "auto close" option has been turned on for the `model` database on the server you are deploying it to, since it will be copied from that for new databases. (I'm not sure if this can even be changed in Azure SQL, and if the DACPAC deployment does not explicitly turn off this option after creating the database, that would arguably be a bug.) – Jeroen Mostert Sep 03 '18 at 10:47
  • AUTO_CLOSE is turned off (unchecked) under project settings, as for Azure SQL, you don't even need to be creating filegroup for it. Its only for local development that I would need to create filegroup. – Stenso Sep 03 '18 at 10:52
  • You could put the filegroup creation in a pre-deployment script and fence it off with a condition check for Azure. As long as it's never mentioned by name in objects, the project won't care. – Jeroen Mostert Sep 03 '18 at 10:58
  • I did try that. Putting in either Pre-Deployment or in Post-Deployment, still generates the same publish sql, and throw an exception. The operation 'AUTO_CLOSE' is not supported :/ – Stenso Sep 03 '18 at 12:05
  • Check what's actually in the publish script. Is `AUTO_CLOSE` in there anywhere? Are you publishing to a new database or to an existing one? Is the server you're publishing to Azure or not? If it's not Azure, did you verify the `model` database doesn't have `AUTO_CLOSE` set? – Jeroen Mostert Sep 03 '18 at 12:14
  • The publish script has AUTO_CLOSE, that's why it's giving me the exception. It's trying to set AUTO_CLOSE on a database I'm publishing after the filegroup is created (not sure why it decides to put filegroup creation above database creation regardless whether I put it as pre-deployment/post-deployment). I'm testing it against SQL Server. Publishing new database. And AUTO_CLOSE is set to false on model database. – Stenso Sep 03 '18 at 12:35
  • 1
    I can't reproduce this on SQL Server 2017 using an empty DB project and a pre-deployment script of the form `ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP XTP CONTAINS MEMORY_OPTIMIZED_DATA`; `ALTER DATABASE [$(DatabaseName)] ADD FILE (NAME=XTP,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\$(DatabaseName)_XTP') TO FILEGROUP XTP`. The generated script has only a `SET AUTO_CLOSE OFF`, the pre-deployment script is inserted just before the `CREATE TABLE`, and SQL Server creates the database successfully. – Jeroen Mostert Sep 03 '18 at 12:45
  • 1
    Are you using SQL Server Express, by any chance? It has a nasty "feature" whereby it automatically sets `AUTO_CLOSE` to `ON` for new databases, irrespective of `model`. However, an explicit `SET AUTO_CLOSE OFF` should still override that. The only other way I can think of to get `AUTO_CLOSE ON` even when you're not asking for it is if someone has put a trigger on the server that tweaks database settings in defiance of your wishes, but that's quite improbable. – Jeroen Mostert Sep 03 '18 at 12:48
  • Ahh I see. My bad I was using SQL Server Express rather than SQL Server 2017. Besides that I wasn't adding a file to a file group, once I have added that, it worked. Thank you for the help. Can you post last 2 of your comments as a answer so I can accept it? – Stenso Sep 03 '18 at 15:01

0 Answers0