simpcode: SQL Server 2008 R2 Express Installation And Setup

This post provides a basic guideline about installing SQL Server 2008 R2 Express together with the SQL Server Management Studio.

1. If you do not have Windows Power Shell installed, please install it from here.

On that page, find the Download information > Windows Management Framework Core (WinRM 2.0 and Windows PowerShell 2.0), then select Download the Windows Management Framework Core for Windows XXX package now, whereby XXX is your Windows edition (thanks to this site for the link).
Note: Power Shell is only needed by SQL Server Management Studio. You may skip this step if you do not want to install the studio.

2. Download and install "SQL Server 2008 R2 Express With Tools" from here.

Note: To do a clean install, you may first want to remove any existing SQL Server 2008 (or R2) database and utilities from the Control Panel > Add/Remove Programs.

3. When prompted, select "New installation or add features to an existing installation". Follow the instructions.

Instance Configuration > Named instance: You may want to rename it to "SQLEXPRESS2008R2" or just use the default.
Server Configuration > Service Accounts: Enter any existing Windows account name and password which has sufficient rights (e.g. the admin account).
Database Engine Configuration > Authentication mode: Select "Windows authentication mode" (this can be changed later).
Restart the computer if needed.

4. Go to Start > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager:

SQL Server Services > SQL Server: Make sure it is running.
SQL Server Network Configuration > Protocols for xxxx: You can enable and set the remote network connection settings here.

5. Go to Start > Microsoft SQL Server 2008 R2 > SQL Server Management Studio > Connect to server:

Server name: serverName\instanceName (e.g. myserver\SQLEXPRESS2008R2)
Authentication: Windows Authentication

If everything is correct, you should be able to connect to the database now.

Setup Security and Authentication using SQL Server Management Studio

1. Enable "SQL Server Authentication Mode":

In Object Explorer > Right-click the server to Properties > Security > Server authentication: Select "SQL Server and Windows Authentication mode".
Once set, you may need to restart the service from "SQL Server Configuration Manager".

2. Enable "sa" login:

By default the "sa" account is disabled. To enable:
In Object Explorer > Security > Logins > sa > Right-click to Properties:
- General: Set the password.
- Status > Login: Select "Enabled".

3. Add new SQL Server authentication:

In Object Explorer > Security > Logins > Right-click to New Login > Select "SQL Server authentication": From here, you can set the login name, password and default database.

4.Set database owner: