SQL Server for Beginners: Part II – Installing SQL Server Express

Just like with any software, SQL Server uses a standard installation program to install its components on your system. Depending on the edition that you’re using, you will be installing it from media that you purchased or from the free install packages that you can download from Microsoft’s website. Unlike other installs, the SQL Server installation can be a long process as there are a number of components, requirements and options to consider, many of which I’ll be detailing in this article.

Which version and which edition?

As of this writing, the latest version of SQL Server is SQL Server 2012. The previous versions, SQL Server 2008 and 2005, are still available for download on Microsoft.com. The 2008 version is also the last version to support Windows XP and it’s still being shipped as part of the Visual Studio 2010 product.

SQL Sever 2012 comes in a number of editions for different uses. The Standard and Enterprise editions cost thousands of dollars and are intended for full production use on a dedicated server which is why I’m not using either of them for this demonstration. There are two other editions that would be useful to someone just starting out with the product; the Developer and the Express editions.

The Developer edition has all the features of the full Enterprise edition except that it can only be used for testing and not on an actual production sever. It’s available for a very reasonable price online and is appropriate for developers testing large, high-demand applications.

The Express edition has a few limitations on it such as a maximum database size of 10 GB and no support for multiple processors but for the average home user trying to learn about SQL Server, it’s a free download that will handle almost any database a beginner might construct. Because of this, I’ve chosen to base this article around SQL Server 2012 Express. If you’re using another version or edition, you’ll still find much of this information helpful.

Requirements

For home machines, SQL Server 2012 Express requires Windows Vista Service Pack 2 or Windows 7, Service Pack 1 to be installed. 32- and 64-bit systems are supported with a 2 Ghz processor, 2 GB of RAM and 2.2 GB of hard disk space recommended. See the product download page for full requirements.

For my installation, I used a Microsoft Virtual PC machine with 2 GB of RAM allocated to it and Windows 7 (32-bit) installed. The host machine’s processor is 2.5 Ghz. I found out about the service pack requirement the hard way and that added a lot of time to the installation. The virtual machine’s original disk size was 16 GB but once the service pack, necessary updates and SQL Server were installed, I only had a few gigabytes left which was barely enough for a minimal installation of Visual Studio 2010 with one langauge. I found a way to expand the virtual machine’s disk to a 32 GB limit to give myself more room.

Even with the service pack pre-installed, depending on the options and the hardware, my installation took just over an hour to complete although that might have had something to do with the virtual environment. Still, set aside some time for this one.

Selecting and Downloading a Package

Even with the version and the edition decided, there are still more options. There are versions of SQL 2012 Express for both 32- and 64-bit systems so you will need to know what type of system you’re running before installing it. You should be able to see this in your computer’s System Properties.

The download page also offers different packages based on the components that need to be installed. If you have the resources, I recommend Express with Advanced Services which is the full package with the database engine itself and all the management, reporting and analysis tools. This package comes in both 32 and 64 bit versions so, again, you’ll need to be sure of what kind of system you’re running. The SQLEXPRADV_x64_ENU.exe (64-bit) and SQLEXPRADV_x86_ENU.exe (32-bit) packages on the download page are 1.3 GB each and contain the complete set of tools. This is the package that I use in this demonstration.

Microsoft’s download page for SQL Server 2012 Express offers a number of download options depending on the components you need.

The next best package, for my recommendation, is Express with Tools which contains the database engine and SQL Server Management Studio Express (often referred to as SSMS). This is a graphic interface that you will need in order to manage databases and issue queries to those databases. While it’s possible to do this through a command prompt, you’ll find life much easier and faster if you have the management studio and I recommend this package at a minimum.  The SQLEXPRWT_x64_ENU.exe (64-bit) and SQLEXPRWT_x86_ENU.exe (32-bit) packages on the download page will get you setup with this configuration and are 706 MB and 669 MB respectively.

A final note on installation packages – although SQL Server 2008 Express is included as part of the Visual Studio 2010 install, I actually recommend installing SQL Server first from one of the packages on the Microsoft site, getting it configured appropriately and then installing Visual Studio with the SQL Server option unchecked so that Visual Studio does not try to install it. I’ve had multiple bugs on a couple different systems when trying to add components to a SQL Server installation performed by Visual Studio, to the point where I had to remove both packages and start again. At this point I like to let each program take care of its own installation.

Installation
(Click on any image for full-size screenshot.)
The installation packages are EXE files which you can run by double-clicking on the file or right-clicking on the file and selecting Open from the context menu.The installation process may seem to pause or be inactive at times, this is because there are a lot of background inspections happening such as a verification that you have the correct operating system and updates installed. After a few moments, you should see the opening screen on the right where you have the choice of installing a new instance, adding features or upgrading a previous version.
Read over the license terms. After you indicate that you accept them, you can click Next to proceed.
The setup files themselves take a few moments to install. During this time, the installation is searching for updates to the installation.
You are able to choose which features are installed from the package. I recommend selecting all features, including LocalDB which is new to SQL Server 2012 and will be featured in an upcoming article.
The installation will perform a few more checks and alert you if it cannot continue because of missing updates or other issues.If all checks pass, the installation moves on to the next step which is to name the instance and specify its location. SQL Server Express can use either a single, default instance called SQLExpress or multiple, named instances on the same machine. This affects how the instances are referenced from outside SQL Server. In SQL Server Express, all it really means is that a default instance will be named SQLExpress. In the screenshot to the right, I decided to do a named instance called SQL2012.I also decided to put it on a second drive since I was running really low on the virtual machine’s C: drive space. The installation still created a folder under the Program Files directory with over 600 MB of application files but the other 1.24 GB of files specific to this instance went on the E: drive instead.

SQL Server runs a number of Windows services  that can be set to start automatically when Windows loads or to be started manually. The database engine itself is one of these services. Each service uses a Windows account to access the resources it needs. When installing SQL Server Express on a home system for training purposes like this, I’ve typically accepted the defaults presented by the installation program. As you get more involved with SQL Server, however, this becomes a more important part of the installation.  You can read more about SQL Server service accounts on Microsoft TechNet.The Collation tab affects the way in which the database engine puts information in order and can safely be left at the default.

One of SQL Server’s strengths is its ability to integrate its security with Windows so that Windows accounts can be used to authenticate users on SQL Server databases. The Server Configuration tab under Database Engine Configuration enables you to choose between straight Windows authentication and mixed mode which allows for SQL Server specific logins and passwords. I decided on mixed mode here and the installation then asks for a password for the server administrator (sa) account. Be sure to use a strong password. Additional users can also be added as administrators.The Data Directories tab enables you to specify directories for different types of data including log files and backups. It’s good to look these over but you’ll probably want to accept the defaults.The User Instances tab allows users with reduced privileges to run separate instances of SQL Server Express with limited permissions. These instances are generated automatically as needed. It can be left on the default setting.SQL Server 2012 uses the FILESTREAM feature to store certain data within the file system rather than directly in the database, providing for more efficient storage and searches. This applies to binary data such as documents and images that would normally take up large amounts of space in the database. If you will be storing large amounts of unstructured data within the database, it might be a good idea to activate this feature through the FILESTREAM tab.



If the install package you selected includes SQL Reporting Services, the install will give you the option of installing and configuring Reporting Services with the default settings or simply installing the files and letting you do the setup. Unless you’re familiar with Reporting Services, it’s probably a good idea to select Install and Configure as I did.  For more information, see the MSDN article.
After the Error Reporting screen, which asks your permission to send installation error reports to Microsoft, the install should finally begin. Again, depending on the package that you’re installing, this might take awhile. Once it’s finished, you’ll see the completion screen which lists the components and indicates if they were successfully installed.

The final screen of the installation presents options for additional documentation and support. I particularly recommend the first option; Books Online which, for past versions, is some of the best vendor produced documentation that I’ve seen. The screen also includes links to other web resources that you can use as you learn more about SQL Server.
After you close the installation program, check your program menu and verify that there is a SQL Server 2012 group with items for the installed components. One of them should be SQL Server Management Studio which will be the subject of another chapter. The advanced tools installation also includes a minimal installation of Visual Studio 2010 but if you want full programming capability, I recommend following up with an installation of either the express edition or professional edition of Visual Studio. If you do this, be sure to unselect the option to install SQL Server Express.

 

(This article is updated from an article series previously published by Comeau Software Solutions.)

Available on Amazon.com


Franklin Mint’s Official 50th Anniversary STAR TREK Tridimensional Chess Set
WE Games