Installing SQL Server Express

This entry is part 2 of 6 in the series AutoCAD Plant 3D on SQL Express

In our initial post on using SQL Server Express with Plant 3D, we discussed some background on what SQL Express is and when you need to use it.  Today’s post is going to be about installing SQL Server Express.  Due to the complexity of setting up computer’s and such, I am only going to address a basic install.  Your setup will vary based on your server setup and hardware availability.

Here is an outline of the procedure this post will cover:

  • Download SQL Express 2008 R2
  • Install Computer
  • Install options
  • Firewall Steps

In the next post we will cover connecting to SQL Express from a remote computer.

Download SQL Express 2008 R2

Other versions of SQL Express will work; 2008 R2 is the latest available version.  SQL Express 2005 has a 4 GB database size limit.

http://www.microsoft.com/betaexperience/pd/SQLEXP08V2/enus/

Install Computer

The simplest practice is to install SQL Server Express on a computer that is not a domain controller.  The computer should be always accessible however.  On my setup, I have a virtual machine (vm) domain controller running Windows Server 2008 and my SQL Server is setup on another vm running Windows 7 64-bit.  The fact that I’m running virtual machines isn’t important right now.  Everything installs/interacts the same way as a regular computer; I don’t have to clutter up my desktop with more computers though.

The main reason not to have SQL Express (Ok, from now on SSE) on the domain controller is for security (http://usa.autodesk.com/adsk/servlet/ps/dl/item?siteID=123112&id=15066533&linkID=9261340, http://msdn.microsoft.com/en-us/library/ms143506.aspx).

Install Options

Once you have download and started the installer, click New Installation.

SNAGHTML10af146

Accept the license terms and click Next.

Check the features you want to install.  For the server, choose the Database Engine Services and the Management Tools – Basic at a minimum. The SDK isn’t really necessary, and you probably won’t be doing anything with replication.  You can always come back and add additional features. Click Next to proceed.

SNAGHTML10e0cd1

The next screen lets you choose the name for your SSE instance.  You may customize the name of your instance if you wish (just remember what it is called).  I use the default SQLEXPRESS name.

The next page is the Service Configuration page.  Services are basically programs that get run in the background whenever the computer starts up.  Because SSE has to handle users connecting at any time, it runs on a service.

We have two types of services that we are configure.  The Database Engine is the actual program that users are working with.  The Server Browser is a service that lets users connect without specifying port information.

SNAGHTML116ee2a

Ports

For those of you new to IT speak, we are going to take a brief moment to talk about ports. A port is basically a connection point to each computer.  By default Windows Firewall (or your other firewall products) control port access.  Think of ports like a telephone number.  Anybody can call the number.  In this example, Windows Firewall would be similar to a personal answering service.  If it recognizes the person (program) calling, the Firewall lets the call through to you (the database engine).

The Server Browser basically makes it easier for users to make connections by letting them use any phone number (port) to connect to the database.  So, it’s less secure, but easier to set up.

From my perspective, my SQL Server is running on a local network behind my main firewall, so I’m not concerned about port access for security.  If you are running a different setup and connecting to the outside world, you should not run the browser service and force specifying a port.  In those situations, your best option is to higher a networking professional examine your set up and make sure it is secure.

image

So enable the Server Browser and have the startup set to Automatic.  The browser can be configured as a local service.

Account Provisioning

The account provisioning page specifies login authentication options for SQL Server.  For testing you may choose mixed, but in a production environment Windows authentication is more secure.

Click Next through Error reporting and Install.

Managing

To manage SSE, go to Start > All Programs > Microsoft SQL Server 2008 R2 and select SQL Server Management Studio.  You should be able to login using COMPUTERNAME\INSTANCENAME and choosing Windows Credentials..  On my server the login is WIN-SC-SRV3\SQLEXPRESS.

SNAGHTML13d79b7

Your Object Explorer will open and provide information about the available databases.

image

 

By way of disclaimer, I’m not a network security expert or even a database expert.  The information presented is content that I’ve run across in reading articles and trying to figure out how to do this stuff. Please give feedback if you have other ideas of best practices.  Thanks!

Series NavigationSQL Express for AutoCAD Plant 3D (Part 1)Creating Backups for SQL Express