This article is the first installment of a series of tutorials/articles focusing on SQL Server 2008 R2 with developers as the target audience. Therefore, the whole series is about to work with SQL Server (especially SQL Server 2008 R2) from the perspective of developers. Do not expect discussion on administering SQL Server. However, in the second part of this series we will address the basics of administering SQL Server 2008 R2 installation, just so you can maintain local installation to test your code against.
SQL Server is the name of relational database server software family developed by Microsoft. Being a relational database server, SQL Server main business is to provide ways to store and retrieve data as requested by other software. To be able to access data managed by SQL Server, other software must use language known by SQL Server. The languange is named T-SQL, short for Transact-SQL.
From the name, it is easily deduced that T-SQL is a variant or a dialect of SQL (Structured Query Language). SQL itself is de facto the most important database language. We will discuss more about SQL and T-SQL in third instalment of this series.
SQL Server 2008 R2 Express Installation
Basic steps in installing SQL Server 2008 R2 are:
- Download SQL Server 2008 R2 Express Installer.
- Launch the installer
- If the installer fails, it usually means there is one or more requirement(s) that your system does not meet. Check if you have the following packages installed in your system. If not, download and install them before re-run the installer.
- Microsoft .Net Framework 3.5 SP1.
- Windows Installer 4.5.
- Windows PowerShell 1.0.
Now I would like to provide you detailed guidance on installing SQL Server 2008 R2. I am assuming you have installed all the requirements mentioned previously. Follow the links to get to the pages where you can download the required packages.
- Visit this page to download SQL Server 2008 R2 Express With Management Tools matches your platform. I emphasize the With Management Tools so we get installer package that can install both SQL Server engine and tools to manage it in one go.
Basically there are 3 kinds of SQL Server 2008 R2 Installer.
- SQL Server engine only
- Management Tools only
- SQL Server engine and Management Tools
SQL Server 2008 R2 Express supports 2 platforms, x86 and x64. If your operating system is Windows 32bit, choose installer for x86. If you have Windows x64, you can pick installer either for x86 or x64.
- Once the download is finished, run the installer. Initially you will get a window similar like below. It indicates the installer is decompressing and extracting its content to a temporary folder.
Wait until this process is finished, which is indicated by the display of SQL Server Installation Center form like shown in the next picture.
- Beside installing a new SQL Server instance, SQL Server Installation Center also has many other interesting and helpful features. For example, it provides links to online resources where you can get help to maintain or to work with your SQL Server, it also provides tools to analyze a system for the requirements or for existing installation(s) of SQL Server.
In this case we only interested with installing a new instance of SQL Server. So make sure you select Installation menu on the left (click on it if it's not active), then click on New installation or add feature to existing instalation like like shown below.
This will execute the real SQL Server 2008 R2 installer. There will be several dialog shown to show the progress under the hood. Just wait until you get a dialog similar like below. Give check mark to I accept the license terms checkbox. Of course do it only after have read the license carefully and find that the terms are acceptable.
- Click button Next to continue.
- This dialog allows you to select which feature of SQL Server to be installed. Just select all, since we actually need all, except maybe SQL Server Replication feature. But it does not hurt to have it installed, though. So select all is fine.
- Click button Next to continue. The installer will check your system for SQL Server requirements, if everything is okay, you will get something like this.
Note that you might not be able to see it, since it usually will immediately skipped to the next dialog. If you really need to see it, hit Back button.
- Instance Naming dialog. This dialog allows you to select the name of the new SQL Sever instance. Basically instance naming allows a machine to run several SQL Server engines concurently and independently while still using the same access media (e.g. the same TCP port number and the same named pipe).
If you elect to name your instance (i.e. by not making it the default instance), clients that connect to that instance must provide not only the hostname (address of where the SQL Server instance is running), but must also specify the name of the instance. Connecting to a default instance only requires the hostname.
In this tutorial, let's use named instance. Let's name the new instance CODECALL. The instance name actually case insensitive. So click on Named instance radio button, and type CODECALL in the edit box next to it.
Click Next button to go to next dialog.
- The next dialog would be the Disk Space Requirements dialog. But if you actually have enough disk space, this dialog usually will be skipped so you will directly taken to Server Configuration dialog. If you really want to see this dialog, just click Back button in
the Server Configuration dialog.
- Server Configuration dialog. Here you assign account to be used by SQL Server services to run. Here I suggest to use user account that has administrative privilege for SQL Server Database Engine service. To do that, enter the user name in Account Name cell and corresponding password in Password cell.
Also you might want to make sure the Startup Type set to Automatic. This option will make sure the SQL Server Database Engine service will be run automatically when the system starts. No matter whether if there is a logged in interactive user or not. If you don't want this, in case you will use SQL Server occasionally, you may want to choose Manual. With selecting Manual startup type, you have to manually start the service before you can access its service.
Click Next button to get to the next dialog (Database Engine Configuration).
- Database Engine Configuration dialog has three tabs: Account Provisioning, Data Directories, FILESTREAM. For us, developers, we usually only interested with the first tab (Account Provisioning). Because this is where we define the authentication mode to use to access the SQL Server. Leave the other tabs as is.
There are two kinds of authentication modes, windows authentication mode and mixed mode. In windows authentication mode, it is Windows that does the authentication. SQL Server just see if the windows account that accesses it has enough privileges to access it.
In mixed mode, beside windows authentication mode, SQL Server also manage its own user accounts and does the authentication. To use this mode, you have to activate sa user account by giving it proper password. I highly recommend this mode for us. So click on Mixed Mode (SQL Server Authentication and Windows Authentication) radio button, then enter your sa password.
Also don't forget to check the account inside Specify SQL Server administrators list. Usually the current user account is already in it. Add another one if you feel need to. Users or user group listed in this list will have administrator access to the SQL Server instance.
Click Next button to get to the next dialog (Error Reporting).
- Error Reporting dialog is where you decide whether you want to automatically send error report that might be occured during the actual installation process.
Click Next button to start the real installation process. You will be represented with the next dialog that shows installation progress.
- In Installation Progress dialog like shown below, you can do one of two things.
- Click Cancel button if you feel like to cancel the installation progress. Please don't chicken out...
- Wait until the installation process completed, which will take you to the last dialog.
- Once the installation process successfully completed, you will be taken to this dialog. Meaning everything is okay, and you can just click the Close button to close the installer.
- Close the SQL Server Installation Center.
Checking The Installation
To check if the SQL Server Database Engine has been installed, we can inspect if its service has been installed and running if previously you selected Automatic startup type. To check, execute compmgmt.msc in the Run[/I] dialog from the Start button. You will get the Computer Management Console.
In the Computer Management Console, expand Services and Applications node, then select Services node. List view in the right area will be populated with registered services in the computer. In the list, find service named SQL Server (CODECALL) (remember that CODECALL is our instance name). If you find it, just like shown below, means that our installation has been successful.
Time to browse the innerside of our new SQL Server Database.
Inspect The Installation
Since we did choose to install Management Tools, we can expect that we will have it in our computer and be able to run it. So inspect your Start menu for menu related with SQL Server. Find menu item to open the SQL Server Management Studio, and click it. Mine looked like this.
You will be presented with login dialog as shown below. Here you can choose to use Windows Authentication or SQL Server Authentication. If you select Windows Authentication, you don't have to enter any credentials since your current user information will be used to login to SQL Server. When you choose SQL Server Authentication, you have to enter user name and password that has been defined previously to have access to the SQL Server. In our case we can use sa user with whatever password you have defined previously in the installation process.
After you enter the correct login credentials (or simply choose to use Windows Authentication), click Connect button. If everything is fine, you will see that the treeview under Object Explorer will be filled. You can browsed through the nodes and get similar display like shown below. Congrats! You have just inspected your first SQL Server 2008 R2 Express installation.
The next installment of this tutorial series will prepare you with the most basics of administering SQL Server, in order to make you ready to manage your SQL Server installation for coding purpose.