Introduction to Microsoft SQL Server

Introduction

 
Microsoft SQL Server is an application used to create computer databases for the Microsoft Windows
 

Family of Server Operating Systems

 
It provides an environment used to generate databases that can be accessed from workstations, the web, or other media such as a personal digital assistant (PDA).
 
We will provide lessons and other topics on how to use Microsoft SQL Server 2008.
 
In order to follow the lessons on this site, you must have access to a computer in which Microsoft SQL Server 2008 is installed. To start, you must have a computer that runs an appropriate operating system. If you are just learning, which is assumed for all lessons on this site, you can use a Microsoft Windows XP Professional, Microsoft Windows Vista, or Microsoft Windows Server 2003.
 
To use Microsoft SQL Server, you must install it on a computer. You can install it on Microsoft Windows XP Professional, Microsoft Windows Vista, Microsoft Windows Server 2003, or Microsoft Windows Server 2008. This means that you should take care of the operating system first.
Before installing Microsoft SQL Server 2008, make sure you have installed all service packs on your operating system. If you plan to use Microsoft Visual Studio 2008 to create Microsoft SQL Server 2008 databases, you should install Microsoft SQL Server 2008 first, then install Microsoft Visual Studio 2008.
 

Introduction to Security

 
One of the main concerns of a database is security, and this is central to Microsoft SQL Server. Security is exercised at different levels: in the room where the computer(s) is(are) located (the computer(s) that hold the server application (Microsoft SQL Server) and the computer(s) that will use(s) the databases (clients)) and the people who will use all these resources.
 
When it comes to the room that houses the computer on which you will install Microsoft SQL Server, you should make sure the room can be locked at any time and only a few selected people have access to it. For maximum security, you should also put the computer in a locked closet.
 

Creating an Administrator Account

 
To actually install Microsoft SQL Server 2008, you must use a user account that has the ability to install it. If you are using Microsoft Windows XP or Vista, you should use the Administrator account or create a new account that has administrative rights. The account you will use must also use/have a password. In Microsoft Windows XP Professional or Vista, to use the Administrator account, assign a password. Alternatively, after logging in as Administrator, create an account that has administrative rights.
 
Practical
LearningPractical Learning: Creating an Account in MS Wnd XP or Vista
  1. Start Control Panel and click User Accounts:
     
    Control Panel
     
  2. Click Create New User: 
     
    User Accounts
     
     
  3. Type the name for the new account as pkatts:
     
    User Accounts
     
     
  4. Click Next.
     
    Make sure the Computer Administrator radio button is selected.
     
    User Accounts
     
  5. Click Create Account
     
  6. To assign a password to an account, in the User Account, click the platts account
     
  7. Click Create A Password
     
    User Accounts
     
  8. In the Type A New Password text box, type Password1 and press Tab
     
  9. Type Password1 to confirm and type anything in the last text box.
     
    User Accounts
     
  10. Click Create Password
     
  11. In the same way, create another account named gmonay that has administrative rights with the password Password1
Practical
LearningPractical Learning: Creating an Account in a Server
  1. Log in to Microsoft Windows Server 2003 or 3008 with an account that can create an administrative account.
     
  2. To create an account in Microsoft Windows Server 2003 or 2008, on the taskbar, you can click Start -> Administrative Tools -> Active Directory Users and Computers.
     
  3. In the left frame, right-click Users -> New User
     
    Active Directory Users and Computers
     
  4. Set the First Name as Patricia, the Initials as G, the Last Name as Katts, and the User Logon Name as pkatts (the domain name will be different from yours)
     
    New User
     
  5. Click Next
  6. Type the Password as Password1
  7. Type the Confirm Password as Password1 and accept the options in the checkboxes
     
    New User
     
  8. Click Next (in the summary, the domain name will be different from yours)
     
    New User
     
  9. Accept the options in the last page of the wizard and click Finish
  10. In the same way, create another account with the following information:
     
    First Name: Gertrude
     
    LastName: Monay
     
    User Logon Name: gmonay 
     
    Password: Password1

Security Principals

 
A security principal is a person or an object that needs access to a database. The security principal can be considered as one entity or as a group of entities:
 
Security Principals
 
This means that a security principal can be considered as a lone entity or as part of a group. For example, a person who accesses a database can be considered an entity. A group of people who exercise the same function and have been give the same function(s) on a database is considered a collection.
 
To create a group in Microsoft Windows Server 2003 or 2008, open the Active Directory Users and Computers. Right-click the Users node ->
 
New -> Group:
 
Active
Directory Users and Computers
 
In the New Object - Group dialog box, give a name to the new group. Make the necessary selections in the Group Scope and the Group Type:
 
New
Group
 
When you have finished, click OK. After creating a group, you can use it. To use a group, you can add one or more existing users to it. To do this, locate the group and double-click it or right-click the group and click Properties:
 
Active
Directory Uses and Computers
 
Add the necessary information.
 
To control the type of access that a security principal can exercise on a machine or a database, it works or acts on a level. There are three levels:
 
Windows, server, and database:
 
Security
 
Based on the functionality of a security principal, it can be considered as belonging to a collection.
 
To help the operating system identify them, each security principal uses a unique number called a security identifier (SID).
 
Practical
LearningPractical Learning: Adding an Account to a Group
  1. In the left frame, click Users if necessary.
     
    In the right-click frame, double-click Domain Admins
  2. In the Domain Admins Properties, click the Members tab (the domain name will be different from yours)
     
    Domain Admins Properties
     
  3. Click Add...
     
  4. In the Enter the Object Names to Select text box of the Select Users, Contacts, or Computers dialog box, type pkatts
     
    Select Users, Contacts, or Computers
     
  5. Click Check Names (in the result, the domain name will be different from yours)
     
    Select Users, Contacts, or Computers
     
  6. Click OK
  7. Click OK

Roles and Logins

 
A role is an action or a set of actions that are granted to a security principal. For example a person A can be allowed to create and use a database. The ability to perform such action is referred to as a role. Another person B can be allowed only to use an existing database without being able to create a new one. This is another type of role.
 
In order to access something (such as a computer or a database), the object must be authenticated. Such an object can use a username and a password and use the group it belongs to. For this reason, the ability to be authenticated in order to access the database or resource is called login.
 
We will see how to create a login in Lesson 3.
 

Getting Microsoft SQL Server

 
To make it possible for you and other students to study databases and SQL, Microsoft made available a trial version of Microsoft SQL Server 2008. This full version is available from the Microsoft web site. To get the trial edition of Microsoft SQL Server 2008, go to the Microsoft web site, do a search on "trial software", and follow the links to locate the the trial edition of Microsoft SQL Server 2008 Enterprise Edition (this is a very big file: 3GB).
 
You can download from any computer that has enough space and can allow you to create a DVD.
 
If you are downloading, the file will be saved in your computer. When the download is over,  you must create an image. After creating the DVD image, you can insert the DVD into the drive.
 
Practical
LearningPractical Learning: Installing MS SQL Server
  1. Start the computer. Using an account with administrative rights, login, providing a username and a password
  2. Insert the DVD in the drive. The installation will start. You may get a message that the .NET framework needs to be installed. On that message, click OK. You will be prompted to start the installation:
     
    Microsoft .NET Framework 3.5 SP1 Setup
     
    After clicking the ACCEPT button, you can click Install. The installation of the .NET Framework would start. When the installation of the .NET Framework is over, you would receive a message:
     
    .NET
     Framework
     
    From there, you can click Exit. You may be asked to install a hotfix:
     
    Hotfix
     
    Hotfix
     
    In this case, accept to install it by clicking Next and following the wizard. When the installation is over, you may be asked to restart:
     
    Hotfix
     
    Which you should do.
     
  3. To start the installation, put the Microsoft SQL Server 2008 DVD in the drive. A window would come up:
     
    Installation
     
  4. In the left section, click Installation
     
    Installation
     
  5. If you are only interested in studying database design and development in Microsoft SQL Server, on the right side, click New SQL Server Stand-Alone... The installation would start:
     
    Installation
     
  6. After the message box, click OK
     
    Setup
     
  7. Another message box would come up. When it finishes, the next page of the the wizard would come up. If you are using the evaluation version, read the message and click Next
     
    Product Key
     
  8. The License Terms page is presented to you. Read it. If you don't agree with the license, click Cancel. Otherwise, click the I Accept check box and click Next:
     
    License Agreement
     
  9. On the next page, read the messages and click Install:
     
    Microsoft SQL Server 2008 Setup
     
  10. On the next page, read the messages and click Next:
     
    Microsoft SQL Server 2008 Setup
     
  11. The next page allows you to specify what you want to install and what would be left out. After making your selections, click Next
     
    Feature Selection
     
  12. The next page allows you to name the instance and to specify where the the server would be installed. After making your selections, click Next:
     
    Instance
     
  13. The next page mostly allows you to check and confirm the disk space. After checking it, click Next
     
    Disk Space
     
  14. The next page allows you to configure the server. After making your selections, click Next
     
    Server Configuration
     
  15. On the next page, specify how authentication would be made. You should also, specify the account used as the administrator. To do this, you can click Add Current User. Then click Next
     
    Database Engine Configuration
     
  16. The next step allows you to specify an account for the analysis services. You can simply click the Add Current User and click Next
     
    Analysis Services Configuration
     
  17. The next page allows you to specify the reporting service to install. Accept the default or make the necessary change. Then click Next:
     
    Reporting Services Configuration
     
     
  18. The next page allows you to specify whether you want installation information to be collected and sent to Microsoft. After making your choice(s), click Next
     
    Error and Usage Reporting
    The pre-installation will proceed and let you know when it ends with the Installation Rules display:
     
    Installation Rules
     
  19. After reading, click Next.
     
    This would indicate that the installation is ready to proceed:
     
    Ready to Install
     
  20. To start the installation, you can click Install. The installation would start and the progress bar would indicate the evolution. This can take a while
     
    Installation Progress
     
    Installation Progress
     
  21. When this phase is over, a message box will let you know. After reading it, you can click Next. The last page of the wizard should announce that the installation was complete
     
    Installation
     
  22. After reading the message, you can click Close