Microsoft SQL Server

Microsoft SQL Server 2014 Express

https://msdn.microsoft.com/en-us/sqlserver2014express.aspx

Microsoft offers the following Microsoft SQL Server 2014 Express components for download:

  • LocalDB (SqlLocalDB)
    • LocalDB is a lightweight version of Express that has all its programmability features, yet runs in user mode and has a fast, zero-configuration installation and short list of pre-requisites. Use this if you need a simple way to create and work with databases from code. It can be bundled with Application and Database Development tools like Visual Studio or embedded with an application that needs local databases.

Benefits

Small installer (20MB+)
Simplified. It does do not require configuration or administration.
Run as a low privileged user.
Simple installation.
Offers the same T-SQL language as SQL Server Express. It supports stored procedures,  geometry and geography data types, triggers, views.
LocalDB uses the same sqlservr.exe as other editions of SQL Server and the same client-side providers.
LocalDB doesn’t create any database services; LocalDB processes are started and stopped automatically when needed.
Unlimited local connections.
It is the default development database for SQL Server Data Tools (SSDT).
You can create instances of LocalDB programmatically using LocalDBCreateInstance or using system.data.localdb.
LocalDB supports named instances.

Limitations

Allows only local connections(!)
Only Named Pipes connections(!)
Limited to use one CPU.
It supports ADO.NET but does not support ADO.
The system databases, SQL Server error logs,  are stored in the AppData folder in the user profile.

  • Express (SQLEXPR)
    • Express edition includes the SQL Server database engine only. Best suited to accept remote connections or administer remotely.
  • Express with Tools (SQLEXPRWT)
    • This package contains everything needed to install and configure SQL Server as a database server including the full version of SQL Server 2014 Management Studio. Choose either LocalDB or Express depending on your needs above.
  • SQL Server Management Studio Express (SQLManagementStudio)
    • This does not contain the database, but only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure, full version of SQL Server 2014 Management Studio, etc. Use this if you already have the database and only need the management tools.
  • Express with Advanced Services (SQLEXPRADV)
    • This package contains all the components of SQL Server Express including the full version of SQL Server 2014 Management Studio. This is a larger download than “with Tools,” as it also includes both Full Text Search and Reporting Services.

Microsoft SQL Server comparison matrix

Comparison matrix

Microsoft SQL Server 2016 Express with Advanced Services is only available for download for MSDN subscribers.  Other options are to use LocalDB, which only allows local connections.

Microsoft SQL Server 2014 Express

  • Download Microsoft SQL Server 2014 Express directly from Microsoft: http://www.microsoft.com/en-us/download/details.aspx?id=42299
  • Go to Start–Administrative Tools–Computer Management
  • Click on Local Users and Groups
  • Double-Click on Users
  • Right click on the screen and select “New User”
  • Enter a user name for the database engine to use (your choice) and fill in a strong password(at least 8 characters, capital, lower-case, number, and a symbol)
  • Un-check user must change password at login. Check User cannot change password and password never expires and then click create
  • For security, right click on the user and select “Properties”
  • Click on the “Member Of” tab and remove the user from the Users group by selecting Users and clicking remove
  • Click on the “Remote Desktop Services Profile”. Check the box for “Deny this user permission”. Click apply then OK.
  • Run the install file you downloaded
  • Select New Installation
  • Click next through the prompts to install “Setup Support Files”
  • For the Microsoft SQL Feature selection, you will need “Database Engine Services”, “Full-Text Search”, “Client Tools Connectivity” and “Management Tools Basic and Complete”. Other options are at your discretion i.e. Management Studio.
  • In the instance configuration screen, leave at the Default Instance or MSSQL Express and click next
  • In the service accounts screen, enter in the account you created above and the password
  • On the next screen, select Mixed Authentication mode and enter in a strong password for the SA user. Save this password
  • Click on “Add current user” at the bottom to add the Administrator if it is not already there to allowed users
  • Leave “Install the native mode default configuration” checked and click next through the remaining screens
  • Enable TCP/IP connections: Start SQL Server Configuration Manager. Click Start, point to All Programs, and click Microsoft SQL Server. Click Configuration Tools, and then click SQL Server Configuration Manager.

 

Microsoft SQL Server was last modified: January 10th, 2017 by tabcom