______________

1. SQL Server – Overview M MS SQL Server by Thobius Joseph, 0717341960, UOI, 2020
Lecture One
Content:
0. Who is Database administrator (DBA)?
 DBA is a specialized computer systems administrator who:
 Unsure data integrity, by ensuring that data are secure from unauthorized access but is
available to users. How? By settings logins, users, permissions and other techniques
like firewall and Access List (ACL)
 Maintains a successful database environment by directing or performing all related
activities to keep the data secure. How? By configuring database program, services,
protocols, policy …
 Has knowledge and experience with a wide range of database management products
such as Oracle-based software, SAP and SQL.
 Performing risk management related to database. How? Database backup &
restoration, Power outages cases. How? By using management tools.
 Use, designing, implementing and maintaining the database system.
 Establishing policies and procedure related to the organization‟s data management
policy
 Perform database installation and upgrade

1. What is server?
 Refer to the types of computers
 Microcomputers e.g.: Laptops, tablets, mobile phones, desktop. notebook
 Minicomputers e.g. : Workstations
 Mainframe e.g. : Computers in data centres
 Super frame e.g. : International connecting computers
 A Server term sometimes is used to refer the computer which performing server
functionalities. Henceforth, it‟s important to determine which type of a computer you
are going to implement server functionalities, because these server functionalities
always demand some kind of computer specifications: The main specifications that
most of server functionality will demands are
 CPU
 RAM
 Storage capacity
Example: To run server functionality for Microsoft SQL server 2012 express edition
you will need a computer with at least 1 CPU, 1GB memory and 10 GB storage (for
your database). Hence from above types of computer you will determine which one is
suitable for your environment and demands.
 The term Server may also refer to :
- A server OS or
- A server program (application software) that is dedicated
to managing resources for server functionality. Servers are often referred to as
dedicated because they carry out hardly any other tasks apart from their server tasks.
 Most of time when we speak about server we are meaning combination of both
computer, OS and a program which in the end they provides server functionalities.
Examples are
 Web server
 Database server
 Media server
 Proxy server
 Server operating system (OS) is a type of operating system that is designed to be
installed and used on a server computer. From the types of operating systems. The
server OS falls on Network Operating System (NOS) type because their main function
is sharing of their resources such as database & webpages in a network. Some
common examples of server OSs include:
 Red Hat Enterprise Linux
 Windows Server: Example Microsoft windows server 2003, 2008, 2012.
 Mac OS X Server
 Server Programs are type of application software designed to provide server
functionalities. While most of server needs a dedicated computer plus its server OS,
these program can run in any computer with any OS.
 By installing these program to your computer which running any OS your turning
your computer into a server.
 Most of available server programs are developed to implement functionalities of
database servers, therefore are called Database Management Systems (DBMS).
Example: FTP server program, MySQL, PostgreSQL, Microsoft Access, SQL server,
FileMaker, Oracle and FoxPro.

2. Software layers
 You can divide your developed software in layers or partitions, whereby each
partition is developed to accomplish specific groups of task, and this is basic method
of developing server programs (DBMS). The followings are three layer (partition )
you can use divide your software:
 Presentation Layer ( client layer)
 Application Layer (Business logic layer)
 Data Layer
• Presentation Layer: It is also known as Client layer. Top most layer of an
application. This is the layer we see when we use software. By using this layer we can
access the webpages. The main functionality of this layer is to communicate with
Application layer. This layer passes the information which is given by the user in
terms of keyboard actions, mouse clicks to the Application Layer.
For example, login page of Gmail where an end user could see text boxes and buttons
to enter user id, password and to click on sign-in.
In a simple word, it is to view the application, like a GUI
• Application Layer: It is also known as Business Logic Layer which is also known as
logical layer. As per the Gmail login page example, once user clicks on the login
button, Application layer interacts with Database layer and sends required information
to the Presentation layer. It controls an application‟s functionality by performing
detailed processing. This layer acts as a mediator between the Presentation and the
Database layer. Complete business logic will be written in this layer.
In a simple words, it is to perform operations on the application, consist of IF else
statements, data conversions.
• Data Layer: The data is stored in this layer. Application layer communicates with
Database layer to retrieve the data. It contains methods that connects the database and
performs required action e.g.: insert, update, delete etc.
• In simple words, it is to share and retrieve the data.

NB: When we speak of layer we meaning of logical partition. When we speak of

tier we mean physical partition. But these two terms “layers” and “tier” most of
times are used interchangeably, as shown in figure below we use tier instead of
layer to demonstrate this.
3. (N-tier/Multi-Tier) Software architecture
 N-tier software architecture refer to the way the software is developed such that its
partition can be installed in different machines (client machine, business logic
machine, database machine, … ), whereby in the ends both partitions on those
machine are working together and accomplishing the intended tasks of the software.
 N-tier architecture is also called multi-tier architecture because the software is
engineered to have the processing, data management, and presentation functions
physically and logically separated. The “N” in the name n-tier architecture refers to
any number from 1.
 Not only does your software gain from being able to perform specific services at the
best possible rate, but it‟s also easier to manage. This is because when you work on
one section, the changes you make will not affect the other functions. And if there is
a problem, you can easily pinpoint where it originates.
 There are several benefits to using n-tier architecture for your software. These
are scalability, ease of management, flexibility, and security.
 Secure: You can secure each of the three tiers separately using different security
mechanism.
 Easy to manage: You can manage each tier separately, adding or modifying each tier
without affecting the other tiers.
 Scalable: If you need to add more resources, you can do it per tier, without affecting
the other tiers.
 Flexible: Apart from isolated scalability, you can also expand each tier in any manner
that your requirements dictate
 One Tier Architecture: One Tier application AKA Standalone application
 One-tier architecture has all the layers such as Presentation, Business, and Data
Access layers in a single software package. An application which handles all the three
tiers such as MP3 player, MS Office are come under one tier application. The data is
stored in the local system or a shared drive
 The Two-tier architecture is divided into two parts:
1. Client Application (Client Tier)
2. Database (Data Tier)
• Client system handles both Presentation and Application layers and Server system
handles Database layer. It is also known as client server application. The
communication takes place between the Client and the Server. Client system sends the
request to the Server system and the Server system processes the request and sends
back the data to the Client System.
• Most of systems we database systems we develop during our projects fall under this.
We had user interface with PHP or JAVA or VB.NET(as front end/client) and
database (Back end) MySQL or SQL server or SQLite

 Three-Tier Architecture: Three Tier application AKA Web Based application or

distributed application
QN: What are 5 differences between web applications and distributed applications?
 The Three-tier architecture is divided into three parts:
• 1. Presentation layer (Client Tier)
2. Application layer (Business Tier)
2. Database layer (Data Tier)
• Client system handles Presentation layer, Application server handles Application
layer and Server system handles Database layer.
• Note: Another tier is N-Tier architecture. Distributed applications are easy to be
viewed by using this type. It is similar to three tier architecture but number of
application servers are increased and represented in individual tiers in order to
distribute the business logic so that the logic will be distributed.
 4. RDBMS;SSMS logins
 RDBMS stands for “Relational Database Management System.” An RDBMS is a
DBMS designed specifically for relational databases. A relational database has
following major components:
 Table, Record (Tuple or Row), Field (Column name or Attribute), Instance, Schema
and Keys
 Database Management System (DBMS), is a database program or software system
that uses a standard method of cataloging, retrieving and running queries on data. The
DBMS manages incoming data, organizes it, and provides ways for the data to be
modified or extracted by users or other programs.
 Examples are MySQL, PostgreSQL, Microsoft Access, SQL server, FileMaker,
Oracle, dBASE, Clipper and FoxPro.
 Since there are so many database management systems available, it is important for
there to be a way for them to communicate with each other. For this reason, most
database software comes with an Open Database Connectivity (ODBC) driver (or
Providers) that allows the database created or managed by one DBMS to be able to
operated by another DBMS. For example, common SQL statements such as SELECT,
INSERT are translated from a program‟s proprietary syntax into a syntax other
databases can understand.

SQL Server 2012 installation, SQL server Management Studio installation and
logins (demo in class)

Lecture Two
 Data
 Data is unprocessed information. Some scholars go to fields to obtain a lot of data for
their topic of study. When these data are processed they will deliver some kind of
information. For example the table below represent list of student with smart phones.
1. When data in table are accessed electronically we will know that 3 students have
smart phones and 3 do not have, which is the information our data from the table
provide us. We can‟t use names to determine gender
2. From table we are missing some
Information such as how many
Women do have smart phones and
How many men do have smart phone.
This is due our data are not
Well organized.

3. The solution here is to reformulate our table so that at least it can provide us with
much information we can obtain.
  Database
 A database is an organized collection of data, stored and accessed electronically.
Formally, a "database" refers to a set of related data and the way it is organized.
Database designers typically organize the data in such that are easy to provide
necessary information. A Database is a collection of related data organized in a way
that data can be easily accessed, managed and updated. Database can be software
based or hardware based, with one sole purpose, storing data.
 Therefore from our previous table, the new table would look like shown below:
 From this table we need to increase one information; number of women and men with
smart phones.
 In order to access this data you need computer software. This software‟s developed
for the purposes of database access are called "database management system"
(DBMS). DBMS allows users to interact with one or more databases and provides
access to all of the data contained in the database (although restrictions may exist that
limit access to particular data).
 A database is generally stored in a DBMS-specific format which is not portable, but
different DBMSs can share data by using standards such as SQL and ODBC or JDBC.
Therefore Sometimes a DBMS is loosely referred to as a "database"
 Why Database Management System (DBMS)?
 During early computer days, data was collected and stored on tapes, which were
mostly write-only, which means once data is stored on it, it can never be read again.
They were slow and bulky, and soon computer scientists realized that they needed a
better solution to this problem.
 Larry Ellison, the co-founder of Oracle was amongst the first few, who realized the
need for a software based Database Management System.
 What is DBMS?
 A DBMS is software that allows creation, definition and manipulation of database,
allowing users to store, process and analyze data easily. DBMS provides us with an
interface or a tool, to perform various operations like creating database, storing data in
it, updating data, creating tables in the database and a lot more.
 DBMS also provides protection and security to the databases. It also maintains data
consistency in case of multiple users.
 Here are some examples of popular DBMS used these days:
 MySql, Oracle, SQL Server, IBM DB2, PostgreSQL and Amazon SimpleDB (cloud
based) etc.
 A database management system has following characteristics:
Data stored into Tables: Data is never directly stored into the database. Data is stored into
tables, created inside the database. DBMS also allows having relationships between tables
which makes the data more meaningful and connected. You can easily understand what type
of data is stored where by looking at all the tables created in a database.
Reduced Redundancy: In the modern world hard drives are very cheap, but earlier when
hard drives were too expensive, unnecessary repetition of data in database was a big problem.
But DBMS follows Normalization which divides the data in such a way that repetition is
minimum.
Data Consistency: On Live data, i.e. data that is being continuously updated and added,
maintaining the consistency of data can become a challenge. But DBMS handles it all by
itself.
Support multiple user and Concurrent Access: DBMS allows multiple users to work on it
(update, insert, and delete data) at the same time and still manages to maintain the data
consistency.
Query Language: DBMS provides users with a simple Query language, using which data
can be easily fetched, inserted, deleted and updated in a database.
Security: The DBMS also takes care of the security of data, protecting the data from un-
authorized access. In a typical DBMS, we can create user accounts with different access
permissions, using which we can easily secure our data by restricting user access.
DBMS supports transactions, which allows us to better handle and manage data integrity in
real world applications where multi-threading is extensively used.

 Advantages of DBMS
 Segregation of application program.
 Minimal data duplicity or data redundancy.
 Easy retrieval of data using the Query Language.
 Reduced development time and maintenances need.
 With Cloud Datacenters, we now have Database Management Systems capable of
storing almost infinite data.
 Seamless integration into the application programming languages which makes it very
easier to add a database to almost any application or website.
 Disadvantages of DBMS
 It's Complexity.
 Except MySQL, which is open source, licensed DBMSs are generally costly.
 They are large in size.
 The database management system can be divided into five major components, they
are :
 Hardware
 Software
 Data
 Procedures
 Database Access Language

 DBMS Components: Hardware

 When we say Hardware, we mean computer,
And hard disks for data storage, and any other physical component involved before
any data is successfully stored into the memory.
 When we run Oracle or MySQL on our personal computer, then our computer's Hard
Disk, our Keyboard using which we type in all the commands, our computer's RAM,
ROM all become a part of the DBMS hardware.
 DBMS Components: Software
This is the main component, as this is the program which controls everything. The DBMS
software provides us with an easy-to-use interface to store, access and update data.
The DBMS software is capable of understanding the Database Access Language and interpret
it into actual database commands to execute them on the DB.
 DBMS Components: Data
Data is that resource, for which DBMS was designed. The motive behind the creation of
DBMS was to store and utilize data. In a typical Database, the user saved Data is present
and metadata is stored. Metadata is data about the data. This is information stored by the
DBMS to better understand the data stored in it. For example: When I store my Name in a
database, the DBMS will store when the name was stored in the database, what is the size of
the name, is it stored as related data to some other data, or is it independent, all this
information is metadata.
 DBMS Components: Procedures
Procedures refer to general instructions to use a database management system. This includes
procedures to setup and install a DBMS, to login and logout of DBMS software, to manage
databases, to take backups, generating reports etc.
  DBMS Components: Database Access Language
Database Access Language is a simple language designed to write commands to access,
insert, update and delete data stored in any database.
A user can write commands in the Database Access Language and submit it to the DBMS for
execution, which is then translated and executed by the DBMS.
User can create new databases, tables, insert data, fetch stored data, update data and delete
the data using the access language.
 Users
Database Administrators: Database Administrator or DBA is the one who manages the
complete database management system. DBA takes care of the security of the DBMS, it's
availability, managing the license keys, managing user accounts and access etc.
Application Programmer or Software Developer: This user group is involved in
developing and designing the parts of DBMS.
End User: These days all the modern applications, web or mobile, store user data. How do
you think they do it? Yes, applications are programmed in such a way that they collect user
data and store the data on DBMS systems running on their server. End users are the one who
store, retrieve, update and delete data.

 Database model
 A database model is one that determines the logical structure of data in a database. It
also determines in which manner data can be stored, organized, and manipulated. The
most popular example of a database model is the relational model, which put data
inform of table. A table consist of columns and rows.
 Below figure shows some types of database models

 Hierarchical Model
This database model organizes data into a tree-like-structure, with a single root, to which all
the other data is linked. The hierarchy starts from the Root data, and expands like a tree,
adding child nodes to the parent nodes.
In this model, a child node will only have a single parent node.
This model efficiently describes many real-world relationships like index of a book, recipes
etc.
In hierarchical model, data is organized into tree-like structure with one one-to-many
relationship between two different types of data, for example, one department can have many
courses, many professors and of-course many students.
  Network Model
This is an extension of the Hierarchical model. In this model data is organized more like a
graph, and are allowed to have more than one parent node. In this database model data is
more related as more relationships are established in this database model. Also, as the data is
more related, hence accessing the data is also easier and fast. This database model was used
to map many-to-many data relationships. This was the most widely used database model,
before Relational Model was introduced.

 Entity-relationship Model
 In this database model, relationships are created by dividing object of interest into
entity and its characteristics into attributes.
 Different entities are related using relationships.
 E-R Models are defined to represent the relationships into pictorial form to make it
easier for different stakeholders to understand.
 This model is good to design a database, which can then be turned into tables in
relational model (see figure below).
 Let's take an example, If we have to design a School Database, then Student will be an
entity with attributes name, age, address etc. As Address is generally complex, it can
be another entity with attributes street name, pin code, city etc., and there will be a
relationship between them

 Relational Model
In this model, data is organized in two-dimensional tables and the relationship is maintained
by storing a common field. This model was introduced by E.F Codd in 1970, and since then it
has been the most widely used database model, in fact, we can say the only database model
used around the world. The basic structure of data in the relational model is tables. All the
information related to a particular type is stored in rows of that table.
Hence, tables are also known as relations in relational model.
  Database Objects
 A database object is any defined object in a database that is used to store or reference
data. Anything which we make from create command is known as Database Object.
A database (relational database) consisting of the following objects:
 Tables
 Queries
 Views
 Stored Procedures
 Functions
 Sequence – Generates primary key values
 Index – Improves the performance of some queries
If you do not have an index on the column, then a full table scan occurs.
 Synonym – Alternative name for an object
 Trigger
 Tables
 Tables are the main building blocks of any database. This is where all the information
will be stored. A table consists of rows (records) and columns (fields). A row or
record contains all the information pertaining to one topic. A column or field is once
piece of information. Above is a small example of records and fields.
 When you have many tables in your database and you need to connect them, you will
use technique called relationship. Consider figure below:
 All of these tables are connected by a term called relationships. If you look closely in
Figure, you will see that inside each table there is a reference to another table's field,
this means that in some table there‟s one or more columns which can be found in
another or others tables. This is what forms a relationship between tables.
 A relationship becomes possible when there is a
Primary key(PK) inside one table referencing
a Foreign key(FK).
 Basically primary key (in 1st table) and
foreign key (in 2nd table) will be the field with
same name. In 2nd table were we mark one of our
column as Foreign key, we should mark also another
column as Primary key. Consider table
Students and Courses as 1st Table and
StudentInfo as 2nd table; StudentNo column in
1st table is Primary key and in 2nd is foreign key,
also in 2nd we have CourseID as primary key.
  Queries
 Now that we know where and how to store data, we need to know how to access that
data. This is done through a database query. You usually have to write a query to
extract the needed information from the appropriate tables.
 In a well-designed database, the data that you want to present through a windows
form or report is usually located in multiple tables. A query can pull the information
from various tables and assemble it for display in the form or report.
 A query can either be a request for data results from your database or for action on the
data, or for both. A query can give you an answer to a simple question, perform
calculations, combine data from different tables, add, change, or delete data from a
database.
 Queries are developed using specific language such as SQL and T-SQL. Example of
query SELECT, DELETE, and UPDATE query.
 Views
 Is virtual table. This table is generated by query. This virtual table can contain some
column from one table (a subset of a table) or mixed column from multiple tables
(Views can join and simplify multiple tables into a single virtual table).
 Stored Procedures
 A Stored Procedure is a sequence of SQL code that can perform tedious tasks quickly.
By making use of Stored Procedures, you will save a lot of time and won't need to run
each query (that can reside in a Stored Procedure) manually.
 These procedures are stored in the database data dictionary. To save time and memory,
extensive or complex processing that requires execution of several queries statements
you can save them into stored procedures, and all applications call the procedures.
 Stored procedures may also contain declared variables for processing data and cursors
that allow it to loop through multiple rows in a table. Stored-procedure flow-control
statements typically include IF, WHILE, LOOP, REPEAT, and CASE statements, and
more. Stored procedures can receive variables, return results or modify variables and
return them, depending on how and where the variable is declared.
 Functions
 There are many functions inside the in any query language. These functions can help
with string manipulation, date manipulation as well as computing Averages, just to
name a few.
 Databases in MS SQL Server, two types of databases are available.
 System databases
 User Databases
 System Databases System databases are created automatically when we install MS
SQL Server. Following is a list of system databases:
 Master
 Model
 MSDB
 Tempdb
 Resource (Introduced in 2005 version)
 Distribution (It‟s for Replication feature only)

SQL Server 2012; Database creation, Table Creation,

Relation Creation. Database Creation by using Visual
Studio 2010, and development of server project by Visual
Studio 2010 (DEMO)
Lecture 3 SQL Server 2012

Contents
1. What is SQL Server?

2. SQL Server 2012 installation (Demo) and confirm for successfully installations

3. Ms SQL server management studio (Demo)

- For GUI operation instead of command line method

- Connecting to server by using SSMS

4. Ms SQL server configuration manager (Demo)

- SQL Server Services

- SQL Server Network Configuration

- Protocols for SQL SERVER instance; Shared Memory, Named Pipe and TCP/IP

- SQL Native Client Configuration

- Client Protocols; Shared Memory, Named Pipe and TCP/IP

Required Knowledge

This course is aimed at fostering the development of the concept and the skills of the students
that are required to perform the duties of Database Administrator (DBA) in organizations
using large relational databases. Students develop coherent plans for security, disaster
recovery, backup and restore, replication and other administrative tasks. Major topics will
include database security, authentication, granting permission, multiuser issues and
replication.

Prerequisite: DIT 108 Introduction to database designs.

 What is SQL Server?
 Is a database server by Microsoft
 Is a relational database management system.
 Is a Microsoft relational database management system or a software product which
primarily stores and retrieves data requested by other applications. These applications
may run on the same or a different computer.
 Example: Microsoft SQL Server 2005, Microsoft SQL Server 2008 and Microsoft
SQL Server 2012
Installation and confirmation (Demo)
 SQL Server 2012 installation (Demo)
 Confirm for successfully installations
1. Check if SQL server 2012 service has started.

2. Does Management studio work? Start it

3. Can you run sql query? Run query in management studio
4. Use osql -L on other server command promt
in the network to see if you can see the server name which
You installed

 SQL Server System

 From previous slide; the SQL server consist of two main components:
• Database Engine
• SQLOS (SQL server operating system)
 Database Engine
• The core component of the SQL Server is the Database Engine.
• We use SQL server Management studio to connect to database engine.

• The Database Engine consists of a relational engine that processes queries and a
storage engine that manages database files, pages, pages, index, etc. The
database objects such as stored procedures, views, and triggers are also created
and executed by the Database Engine.
 Relational Engine
 The Relational Engine contains the components that determine the best way to
execute a query. The relational engine is also known as the query processor.
 The relational engine requests data from the storage engine based on the input query
and processed the results.
 Some tasks of the relational engine include querying processing, memory
management, thread and task management, buffer management, and distributed query
processing.
 Storage Engine
 The storage engine is in charge of storage and retrieval of data from the storage
systems such as disks and SAN.
 SQLOS
 Under the relational engine and storage engine is the SQL Server Operating System or
SQLOS.SQLOS provides many operating system services such as memory and I/O
management. Other services include exception handling and synchronization services.
SQL Server Tools
 The common tools are:
 SQL server management studio (SSMS)
 It access, configure, manage, administer, and develop all components of SQL
Server.
 for GUI operation instead of command line method, hence you connecting to
server by using SSMS
 SQL server configuration manager
 Use SQL Server Configuration Manager to configure SQL Server services and
configure network connectivity. Configuration Manager runs on Windows. It
can configure:
A. For SQL Server Services- control services (start or stopping …)
- SQL Server Network Configuration
- Protocols for SQL SERVER instance; Shared Memory, Named Pipe and TCP/IP
B. For SQL Native Client Configuration
- Client Protocols; Shared Memory, Named Pipe and TCP/IP

- Aliases and check for operational status (stopped, start…)

  Other tools which increased depend on type of server are:
Qn: Differentiate each tools by using function they provide in server
 Azure Data Studio
 SQL Server Data Tools (SSDT)
 Visual Studio Code
 mssql-cli
 Sqlcmd
 mssql-scripter
SQL Server Services
 What is Services? Qn: Differentiate in this services functions they provide
 A service is a program that runs in the background that does not require any user
interaction. Some examples of services are the drivers for your keyboard and
mouse, your antivirus software, and the SQL Server Service.
 Mandatory services for database creation and maintenance are:
- SQL server database engine (or just SQL server)
- SQL server agent
 Other Services includes:
- SQL Server Browser, SQL Server Full Text Search, SQL Server Integration
Services, SQL Server Reporting Services and SQL Server Analysis Services
 Method to start services
- By services.msc; Go to Run, type services.msc and click OK
- By SQL server configuration manager
Lecture Four was project of database in VB.NET
Lecture Five Contents
Qn: Differentiate between Login account and User account
1. Logins; create and delete Logins (Demo)
Methods to create Login - by SSMS
- by script
Create login yourloginname with password='yourpassword„
Example: Create login tho with password='P@ssword„
2. Multi- users; Creating Users (Demo)
- by SSMS
- by script : Create user <username> for login <loginname>
Example: Create user john for login tho
- Assigning permission (Demo)
3. Drop (delete) database (demo)
4. Database backup (demo)
5. Restoring databases (demo)
Lecture Six for self-study was about High Availability Technologies for server
- Replication
- Mirroring
- Clustering
Qn: Differetniate the above mentioned technologies in term of
- Definition
- Advantage and dis advantages
- How they works/ How to be implemented
- Which one are supported in SQL server 2012