How to Install, Setup and Configure MS SQL Server 2017 Express Edition A quick tutorial explaining all the required steps to deploy an instance of MS SQL Server 2017 Express Edition on a Development, Testing or Production machine

How to Install, Setup and Configure MS SQL Server 2017 Express Edition

If you’re a .NET developer working with Visual Studio, you’ll most likely know SQL Server LocalDB: a specialized edition of the well-known Microsoft SQL Server engine – firstly introduced in SQL Server Express 2012 – acting as a minimal, on-demand, version of SQL Server designed for application developers. Since its first introduction LocalDB has proved itself to be a great development tool, especially when used as an “embedded database” to quickly test our Data Model without having to setup  a whole DBMS service. We briefly talked about it in a couple posts some months ago, such as when we compared the Entity Framework Data Modeling patterns (Code-first, Model-first and Database-first) and in our ASP.NET MVC 5 Web Application Project Setup Guide.

Although LocalDB proved itself to be a great development choice, it’s not a good idea to use it in a production environment of any kind. The reasons for that are fairly simple to explain and understand:

  • Despite having the same performances of a regular SQL Server Express instance – since they share the same database engine – the LocalDb underlying process will shut down itself if it doesn’t have any request for a while. When this happens, the next request(s) will most likely have poor performance and could even get a timeout.
  • There are some (minor and fixable) issues with the LocalDB default user profile and permissions that you’ll need to fix to use it with an IIS-based production website: for further info about these problems – and their workarounds – I strongly suggest to take a look to this great two-part MSDN blog post (part 1part 2).

The “LocalDB in production” debate is also a recurrent topic on tech sites such as StackOverflow, which hosts some really interesting threads providing a great analysis of the pros and cons, such as:

Having read and said all that, here come my 2 cents: although LocalDB is great, for a production machine I would strongly recommend using a standard, full-fledged and service-based instance of SQL Server: as for the chosen edition, we can either go for Express, Web, Standard, or Enterprisedepending on what we need and/or can afford.

In this post we’ll see how to properly setup and configure an instance of  Microsoft SQL Server 2017 Express Edition, aka MSSQL2017, which can be downloaded for free from this Microsoft page. Needless to say, we need to install it on a machine that is reachable from our web server via a Local Area Network (LAN) or within the web server itself, although this is definitely not a recommended choice: both IIS and SQL Server are resource-intensive, hence it could be advisable to keep them in two separate environments.

In the following tutorial we’ll take for granted that we’ll have a Windows Server available and accessible either phisically or via Remote Desktop, which would be the case for a server hosted, housed, virtualized or even available as a VPS or within a public, private or hybrid cloud farm.

Installing SQL Server 2017 Express

The installation process is pretty straightforward. Unless we don’t need anything specific, we can just go for the basic type:

How to Install, Setup and Configure MS SQL Server 2017 Express Edition

Eventually, we’ll be prompted with the Installation Complete window, which will also give us some useful info, including the database instance name and a default connection string ready for a connection test:

How to Install, Setup and Configure MS SQL Server 2017 Express Edition

Installing SQL Server Management Studio

From here, we can click the Install SSMS button and download SQL Server Management Studio, a tool that we can use to create our fist sample database and also a dedicated user that can access it.

SQL Server Management Studio is a separate product and can also be retrieved for free download at the following URL.

Configuring the MSSQL Service

Once we’ve downloaded and installed it, we can launch SQL Server Management Studio and start the required steps to create our first database. We will be prompted by a Connect to Server modal window that will allow us to connect to our local SQL Server instance.

To do this, select the Database Engine server type and then, from the Server name combo box, choose <Browse for more…>. Another pop-up window will appear, from which we’ll be able to select the database engine we just installed on our server:

How to Install, Setup and Configure MS SQL Server 2017 Express Edition

As for the Authentication part, we can leave Windows Authentication for now, being it the default SQL Server authentication mode: however, we’re going to change it soon enough.

When we’re done, click on the Connect button and a Server Explorer window will appear, containing a tree view representing the structure of your SQL Server instance. This is the interface we’ll use to create our database and also the user/password that our application will use to access it.

Changing the authentication mode

The first thing we need to do is to change the default SQL Server authentication mode, so we won’t be forced to use an existing Windows account. To do so, right-click on the root tree view node, which represents our SQL Server instance, and select Properties from the contextual menu. From the modal window that appears, select the Security page, then switch from Windows Authentication mode to SQL Server and Windows Authentication mode:

How to Install, Setup and Configure MS SQL Server 2017 Express Edition

Adding the TestMakerFree database

Now we can create the database that will host our application’s tables. Right-click on the Databases folder and choose Add Database from the contextual menu. Give it the TestMakerFree name and click on OK.

Adding the TestMakerFree login

Go back to the root Databases folders, then expand the Security folder, which should be just below it. From there, right-click on the Logins subfolder and choose New Login. Again, give it the TestMakerFree name. From the radio button list below, select SQL Server Authentication and set a suitable password – for example, SamplePassword123$ – and click on OK. If you want a simpler password you might have to also disable the enforce password policy option. However, we advise against doing that: choosing a weak password is never a wise choice, expecially if we do that in a production-ready environment. Instead, replace the sample password we used above with a custom one and store it carefully: we’re going to need it later on.

Mapping the login to the database

The next thing we need to do is to properly map this login to the TestMakerFree database we added earlier. From the navigation menu to the left, switch to the User Mapping tab. Click on the checkbox right to the TestMakerFree database, then write TestMakerFree in the User cell and assign the db_owner membership role:

How to Install, Setup and Configure MS SQL Server 2017 Express Edition

As soon as we click on the OK button, a new TestMakerFree user will be added to the TestMakerFree database with full administrative rights.

We can easily confirm that by going back to the root Databases folder and expanding it to TestMakerFree Security Users:

How to Install, Setup and Configure MS SQL Server 2017 Express Edition

That’s it! Now we’ll be able to access our brand new TestMakerFree database with a standard connection string using the credentials we just created.

This article is part of the ASP.NET Core 2 and Angular 5 book, available as paperback, e-book and as a 26-lessons video-course. Promo Code: ASPCA50 to get it with a 50% discount!

 

RELATED POSTS

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies.

View all posts by Ryan