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.
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.
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.
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.
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.
Your Object Explorer will open and provide information about the available databases.
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!
