Installed SQL Server 2017 Enterprise Core but still the Database limit is 10 GB(Express Edition of SQL Server)

Andreas ss 726

Hello!

I am using Express Edition of SQL Server in my Visual Studio 2019 project which is limited to 10 GB. I will describe the scenario I have:

I have created a windows form .Net Core 3.1 Application. I use a Express Edition of SQL Server Database named "featuresDatabase1.mdf" in this project. I use Windows 10 Pro as operating system.
Now I need unlimited size of the database. So I have upgraded my operating system to: Windows Server 2019 DataCenter and I have used a "stand alone installation" of Microsoft SQL Server Enterprise Core 2017 (See below image)

When I run this query I get get below: SELECT AzureSQLDBVersion = @@VERSION

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Datacenter 6.3 <X64> (Build 17763:)

So I have installed the "SQL Server Enterprise Core 2017" on the computer. But when I open up my Visual Studio project. The "Express Edition of SQL Server" is still in use in my project and there is then still 10 GB limit.

To make the question simple. The above is now exactly the scenario. I simply don't know how my current database: "featuresDatabase1.mdf" will use the new "SQL Server Enterprise Core 2017" in Visual Studio 2019. What do I need to do from here to make this work?

Shashank Singh 6,251 Reputation points

2020-11-02T15:20:46.203+00:00

@Andreas ss
I do not see any image, can you add that again. Plus there is no version in SQL Server which is SQL Server 2017 enterprise core. Its simply SQL Server 2017. Also why have you tagged your question as Azure sql database ? Is this SQL Server on-premises or in Azure ?
Andreas ss 726 Reputation points

2020-11-02T15:31:16.287+00:00

I added the image again to the original post. I understand, so it is called only "SQL Server 2017" then. That must be true then as it shows that on the image. I am quite new to SQL so Azure must be wrong tag here?
Shashank Singh 6,251 Reputation points

2020-11-02T15:55:37.123+00:00

@Andreas ss I believe you have installed SQL Server 2017 but you are not connecting to it. You are connecting to express edition. Can you run Discovery report as shown by Tibor and add the output in the question

Accepted answer

Andreas ss 726 Reputation points

2020-11-03T17:01:18.387+00:00
Thank you!

I found a video that shows how it is done:
https://www.youtube.com/watch?v=swXmjWQTfq4

What must be done are:

DataSource: Microsoft SQL Server (SqlClient)

Connect to a database (Here choose the one that is attached in SQL Server Management Studio).

Then on properties for the database. Here you find the connection string that will be used.

Now it works in the C# code to write to the database!

Thank you for all help!
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

5 additional answers

tibor_karaszi@hotmail.com 4,311 Reputation points

2020-11-02T15:45:58.34+00:00

In the SQL Server Installation Center (the setup program), under Tools, there's a "Installed SQL Server Featured Discovery Report". That will show you what is installed. Something like below, executed on my machine:
Please sign in to rate this answer.
Andreas ss 726 Reputation points

2020-11-02T16:02:48.737+00:00

Yes interesting. I did that and it looks like this for me. However, I still have the same question. How my current Express Edition of SQL Server Database named "featuresDatabase1.mdf" will use the SQL Server 2017 ?

tibor_karaszi@hotmail.com 4,311 Reputation points

2020-11-02T17:42:09.973+00:00

Your report clearly states that you are have installed Enterprise Edition of SQL Server 2017, as the default instance. Also the report states that there are no other instances of the database engine.

The result from @@VERSION clearly states that the SQL Server you are connected to is an express edition, version 2016.

We have evidence that you are connected to something else, not the SQL Server that you have installed as the Enterprise 2017. You can for instance do SELECT @@SERVERNAME and see what result you get. Troubleshooting wh you connect to something different from what you have installed is what you need to do now. Again @@SERVERNAME is a good start.

Andreas ss 726 Reputation points

2020-11-02T20:47:12.31+00:00

When running: SELECT @@SERVERNAME, I get this result. I am not sure what this means?

WIN-S5P46PFR8ST\LOCALDB#4134DAC3
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Shashank Singh 6,251 Reputation points

2020-11-02T17:03:28.487+00:00
How my current Express Edition of SQL Server Database named "featuresDatabase1.mdf" will use the SQL Server 2017 ?

You have 2 options here

backup SQl Server express database, move backup to machine having sql server 2017 and restore it on SQL Server 2017.

Stop the SQL Server service, copy the mdf and ldf files to SQL Server 2017 machine and attach it.
Please sign in to rate this answer.
Andreas ss 726 Reputation points

2020-11-02T17:14:16.923+00:00

I looked that the URLs but I am really unsure what I need to do exactly. I have my Visual Studio 2019 project with my "featuresDatabase1.mdf" in it on the computer with SQL Server 2017 installed. (stand alone installation)

The connection string that I have which is used with SQL Server Express looks like this: (Which has a 10 GB limit)
"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Administrator\Desktop\App1\featuresDatabase1.mdf;Integrated Security=True;Connect Timeout=3600"

I have this information also for SQL Server 2017
Instance: MSSQLSERVER
Instance ID: MSSQL14.MSSQLSERVER

If I can ask again. What will I now do Exactly to use the SQL Enterprise Edition?
I am very new to SQL so I think I need the exact steps to understand what I need to do. Would it be possible to only change my connection string somehow with the above information and then it should work right away?

Shashank Singh 6,251 Reputation points

2020-11-02T17:47:02.207+00:00

So as per you the featuresDatabase1.mdf file is on same machine as that of SQL Server 2017 in that case again see my answer.

Shashank Singh 6,251 Reputation points

2020-11-02T18:00:37.063+00:00

Unfortunately from online it would be difficult to guide you but below link should help you. Follow step 2 and 4 to first connect to local DB, back it up and then restore it on SQl Server 2017. Before restoring in SSMS type hostname of machine this will connect to SQL Server 2017 as it is default instance, for local db connection string will be LocalDB)\\MSSQLLocalDB
localdb-to-sql-server

Andreas ss 726 Reputation points

2020-11-02T20:25:18.033+00:00

I am not sure if I can understand what to do at all if I am honest. I don't use SSMS. I use Visual Studio 2019.

I can only get back to my original question again because it is to many question marks for me. If there is an exact step by step list I can follow of how to do this. I am very new to the SQL server.

This is my setup now. Everything exists on the very same computer:

I have a SQL Server Express. I created a database 1 week ago which is named: "featuresDatabase1.mdf". This database is now 10 GB which is the limit.

My connection string is: Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Administrator\Desktop\App1\featuresDatabase1.mdf;Integrated Security=True;Connect Timeout=3600

I have installed "Microsoft SQL Server Enterprise Core 2017"

I beleive I must need an exact step by step process of how "featuresDatabase1.mdf" will use "SQL Server Enterprise Core 2017" in Visual Studio 2019?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
tibor_karaszi@hotmail.com 4,311 Reputation points

2020-11-02T17:44:44.577+00:00

Change the connections string so that you don't connect to this strange special version of SQL server that is called LocalDb (which is a non-installed version of SQL Server and that is why it doesn't show up in the discovery report).

Google connection strings for SQL Server and you will see what it should look like. You need of course to create the database etc as well, but that is general SQL Server stuff.
Please sign in to rate this answer.
Andreas ss 726 Reputation points

2020-11-02T19:44:30.997+00:00

I try with the below connection string:

"Data Source=MSSQLSERVER;AttachDbFilename=C:\Users\Administrator\Desktop\App1\featuresDatabase1.mdf;Integrated Security=True;Connect Timeout=30"

Then I get this error:
"A network related or instance specific error occurred while establishing a connection to sql server. The server was not found or was not accessible"

The instance name is: "MSSQLSERVER" in SQL Server 2017

I wonder what I am missing?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Jeffrey Williams 1,891 Reputation points

2020-11-02T22:27:06.423+00:00

In your VS project - you are using the LocalDB version of SQL which is installed with Visual Studio. The version that was installed is SQL Server 2016 Express...

You then installed an Enterprise Edition of SQL Server 2017 - which works quite a bit differently from the LocalDB version. The LocalDB version allows you to 'attach' the database when you connect, but for Enterprise you would not do that. In Enterprise Edition (Standard Edition also) - the database is always attached and available.

https://expressdb.io/sql-server-express-vs-localdb.html

Now - what you need to do is connect to the Enterprise Edition using SSMS. Once you are connected to that instance, you then need to attach your database to SQL Server 2017. Once the database is attached, you can then update/change the connection for your project to use the database in the new instance.

https://blogs.gre.ac.uk/cmssupport/application-development/programming/asp-net/connecting-to-sql-server-using-visual-studio/

You can also use VS to manage the database - but you need to install the components: https://learn.microsoft.com/en-us/sql/ssdt/sql-server-data-tools?view=sql-server-ver15.
Please sign in to rate this answer.
Andreas ss 726 Reputation points

2020-11-03T00:12:08.727+00:00

Thanks, I think I am one step closer now but still I am not entirely sure. Youtube videos doesn't give me the answer either since I am not entirely sure what I am looking for because it is so many different steps. (I will try and use SSMS to solve this then)

This is what I have done now:

I have installed SSMS.

Connect to Server using Windows Authentication (See Image)

I have "attached" my database that I previous used with SQL Express (Showed with the Red Arrow)

The other red arrow shows: SQL Server 14.0.1000.169 (This seems to be the Enterprise Edition of SQL)

This is the only thing I have done now and are not sure if I have done this correctly and what I am missing now. I beleive I am on the right track perheps?
(Image is not shown. Please right click the below link and open)

Andreas ss 726 Reputation points

2020-11-03T01:30:31.787+00:00

I like to add to my previous comment. --- Just Assuming I do the right things now?

I have restarted the computer

I then directly open Visual Studio 2019 without opening anything else.

I then now try to click the button(red ring) "Connect to Database". But I then get this message shown in the image below:
featuresDatabase1. This file is in use. Enter a new name or close the file thats open in another program
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.