Installing and Configuring SQL Server Express

This topic will guide you through the process of installing and configuring SQL Server Express for use with Email Signature Manager.

Note This guide is based on SQL Server Express 2014 and assumes that it will be installed on to a clean machine where no other instance of SQL Server is installed.

Before You Start
 

1.Download the SQL Server Express 2014 with Tools installer from the Microsoft website. The main download page can found here:
 
https://www.microsoft.com/en-gb/download/details.aspx?id=42299
 
You should download either ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe (for 32-bit operating systems) or ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe (for 64-bit operating systems).
 

2.It is recommended that after installation, SQL Server Express 2014 is updated to Service Pack 1 or later. The main download page can be found here:
 
https://www.microsoft.com/en-us/download/details.aspx?id=46694
 
You should download either SQLServer2014SP1-KB3058865-x86-ENU.exe (for the 32-bit version) or SQLServer2014SP1-KB3058865-x64-ENU.exe (for the 64-bit version).
 

Install and Configure SQL Server Express 2014

1.Start the installer for the appropriate version of SQL Server Express (either SQLEXPRWT_x86_ENU.exe or SQLEXPRWT_x64_ENU.exe) and extract the installation files to a suitable location (this can take several minutes).
 

2.In the SQL Server Installation Center, select the new SQL Server stand-alone installation option.
 

3.Read the License Terms and then check the I accept the license terms check box. Click the Next button.
 

4.In the Feature Selection step, the following components are mandatory:
 

oDatabase Engine Services

oClient Tools Connectivity

oManagement Tools - Basic (Management Tools - Complete is recommended)
 
Check the installation folders are acceptable and click the Next button.
 

5.In the Feature Rules step, install any prerequisites and complete any other steps that are required to continue with the installation.
 

6.In the Instance Configuration step, select the Default Instance option (the instance will be named MSSQLSERVER) and click the Next button.
 

7.In the Server Configuration step, it is recommended to change the Startup Type of the SQL Server Browser service to Automatic (which allows the instance of SQL Server to be discoverable on the network). Otherwise, the default configuration is suitable. click the Next button to continue.
 

8.In the Database Engine Configuration step, select the Mixed Mode option (this is mandatory for Email Signature Manager) and enter a suitable password for the sa account. The password must meet the complexity requirements specified by the Local Security Policy of the computer. It is also recommended that the Domain Administrators group is added to the list of SQL Server Administrators. Otherwise, the default configuration is suitable. Click the Next button to continue.
 

9.The installation process will now start. This can take several minutes to complete.
 

10.Review the actions taken in the Completed step, click the Close button to dismiss the installation wizard, and then close the SQL Server Installation Center.
 

11.At this stage, it is recommended to either install the latest Service Pack and use Windows Update to check for the latest updates.
 

Enabling Network Protocols

By default, only the Shared Memory protocol is enabled, which means that it is not possible to connect to SQL Server Express across the network. To enable the other protocols, use the following steps:

1.Start SQL Server Configuration Manager.
 

2.Expand SQL Server Network Configuration and Protocols for MSSQLSERVER (where MSSQLSERVER is the default instance name).
 

3.In the list of protocols, right-click the disabled protocols and select Enable from the context menu. It is recommended that all protocols (Shared Memory, Named Pipes and TCP/IP) are enabled for best connectivity to the server.
 

4.Right-click the TCP/IP protocol and select Properties. Select the IP Addresses tab and scroll down to IPAll. Check that the TCP Port is set to 1433 (the standard default for SQL Server). Close the TCP/IP Properties dialog.
 

5.Select SQL Server Services, right-click SQL Server (MSSQLSERVER) (where MSSQLSERVER is the default instance name) and select Restart to restart the SQL Server Database Engine.
 

6.Ensure that the firewall on the machine hosting SQL Server Express has been configured to allow the appropriate inbound TCP/IP connections. These are TCP port 1433 for the Database Engine and UDP port 1434 for the Browser Service.

Backing Up a Database

Assuming that the Recovery model for the database is set to Simple, you can create a manual backup of a database using the following steps:

1.Start SQL Server Management Studio and connect to the appropriate instance of SQL Server Express.
 

2.Expand the Databases node, right-click the Email Signature Manager database and select Tasks > Back Up....
 

3.Ensure the Backup type is Full.
 

4.By default, a backup file matching the name of the database will be created (for example, C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\EmailSignatureManager.bak). SQL Server is able to store multiple backups in the same file. You can therefore either choose to use a single backup file or to use a distinct file for each backup that you take. To use a distinct backup (or to alter the location where the backup is stored), click the Remove button to remove the default backup and then click the Add button to a new destination backup file. Alternatively, if you wish to use the default backup file but only store the latest backup, select the Media Options page and check the Overwrite all existing backup sets option; this will erase any existing backup in the specified backup file.
 

5.Click the OK button to create the backup.

Important This is a description of the simplest method to backup your database. It is recommended that you read this Microsoft article that fully explains backup options for SQL Server.

Restoring a Database

Before restoring the database, the Email Signature Manager Service should be stopped and any instances of the main application closed. To perform a full restore of the database, use the following steps:

1.Start SQL Server Management Studio and connect to the appropriate instance of SQL Server Express.
 

2.Expand the Databases node, right-click the Email Signature Manager database and select Tasks > Restore > Database....
 

3.Select the Device option and click the ellipses ("...") button.
 

4.Check that the Backup media type is selected as File and then click the Add button. Select the backup file that you wish to use to restore the database and the click the OK button.
 

5.On the General page, verify that the correct database has been selected. If the backup file contains multiple backup sets, the latest one will be selected by default. If you wish to use an early backup set, click the Timeline button, select the Specific date and time option and choose the appropriate backup set to use.
 

6.On the Options page, select the Overwrite the existing database (WITH REPLACE) option.
 

7.Click the OK button to restore the database.