Module 1: Introduction to SQL Server 2014 Database Administration
1 of 32
Module Administration
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Database
Contents:
Lesson 1: Lesson 2: Lesson 3: Lab:
Module This module
the
components,
database
administrator
Objectives After completing • Describe the • Describe common • Use SQL
Lesson 26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
2 of 32
Most organizations these applications applications
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
activities, and increasingly reliant on component of
a business’s The role of a that ensure that efficiency. This
responsibilities and tasks optimum will be
explored in greater
Lesson After completing • Describe common • Describe common • Describe the
What Makes
While this course 2014 database,
Server successful DBA.
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
3 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
While there are
world, each
with their own
to success
include: • Technological of the database
knowledge familiar with host
operating • Business-aware the business
understands supporting the
business. • Organizational subsystems must keep • Ability to business stakeholders on their individual based on factors services, the
components and a good DBA they arise. application users and situation based of issues database the problems
are affecting
Common
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
4 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Depending on
multiple
database servers
for a specific
application, database Regardless of
tasks that a
DBA must perform • Provisioning instances based on template
and configuring machines their data
and log files • Maintaining
and populated
with data
continues to
perform optimally.
data files as
records are
appropriate size, and
ensuring that • Managing business operations, and recovery
critical to appropriate backup restore the
database in • Importing
DBAs often
need to extract • Applying valuable asset costly, expensive and confidence. data they need,
its most can be customer trust access the obligations,
protects its • Monitoring operations
administration troubleshoot and
remediate
a proactive
approach,
to detect
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
5 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
potential problems
Documenting
One of the key
DBAs are
familiar with
day-to-day
basis. However, compile and
commonly includes
information • Configuration • Personnel • Standard maintenance • Disaster recovery
While it may
is an
important part
when a new
DBA must take emergency occurs to respond to steps to recover
unexpected DBA is available documented a faster
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
6 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
resolution of
Lesson As a DBA, it your data. SQL focused. Rather
Platform used to store data structured as a
series of components. You can install
called an
instance and SQL Server scenarios. It editions and
for different SQL Server years results
in today’s stable
Lesson After completing • Explain the • Describe the • Explain the • Explain how
SQL Server
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
7 of 32
SQL Server
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
that. It is a
complete data
Component Database Engine
platform. It provides a language that business includes a memoryimprove performance
Analysis Services
processing (OLAP) enterprise BI
Integration Services
load (ETL) between SQL
Reporting Services
web services, in native mode,
Master Data Services
managing
Data Quality Services
quality tool for
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
8 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Component StreamInsight
Full-Text Search
Replication
PowerPivot for Server
Power View for Server
that perform
sophisticated
technologies for
Server Analysis enable tabular
Services when exploration, impromptu
SQL Server
It is sometimes server. Many
on a single instances.
SQL Server
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
9 of 32
The ability to
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
server is useful
in a number • You may want
sets of
databases. • Some of your incompatible
inconsistent or configure each
instance of • Your application availability.
in relation to differing service
level agreements ( • You might • Your applications can have different
each database collation of the
tempdb database
Different versions
instances.
This can assist Default and Prior to SQL system. SQL
a server it is hosted.
To maintain
and is known
as the default
database engine
is named MSSQLSERVER. Additional instances with the server named instances,
conjunction instances to be install all
components
instance, client
applications
instance
called Test on
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
10 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
APPSERVER1\Test There is no need
server. You can
use a single installation
SQL Server
SQL Server
and levels of
capability. SQL Server Each SQL Server
the table
below:
Edition Parallel Data Warehouse
to execute quickly. Parallel complete software
Enterprise
for demanding
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
11 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Edition Business Intelligence
Standard
offerings from
management platform.
Express
and small
Compact
occasionally for a very
Developer
Web
Microsoft Azure
demonstrating all SQL
scalable platform
applications on a
SQL Server
SQL Server has and features.
capabilities maintaining
strong levels
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
12 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Early Versions The earliest versions ( and later moved
Server 4.2 Windows NT.
Subsequent SQL Server reducing the
were made in Analysis
Services) was SQL Server support for data was introduced
introduced Services (SSRS) 64-bit
processors. SQL Server
product. It
introduced support • Non-relational • SQL Server
previous
administrative • SSIS replaced • Another key
the Common
Language • The T-SQL • Dynamic introduced • Substantial
exception handling. DMFs) were troubleshooting. particular,
database mirroring • Support for
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
13 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
SQL Server • Filestream
data.
• Spatial data • Database • Specialized
support for time
zones within • Full-text indexing
Previously full-text
indexing was • A policy-based
to more
declarative-based • A Windows
The enhancements • Substantial • The introduction • Improved • Support for
Master Data
Services. • StreamInsight
before storing
it in a database. • Data-tier applications
application
development
The enhancements • Further substantial
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
14 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
• Substantial • The introduction
SSAS).
• The migration • The introduction
Availability.
• The introduction • Strong enhancements
sequences, new error-
handling capabilities, • The introduction • The introduction • Many general
SQL Server SQL Server
and provides
even better performance,
new
in-memory capabilities
recovery
functionality
Lesson Techniques A DBA for a database solution
aspects of the tools, and
the techniques
Lesson After completing • Describe common
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
15 of 32
• Use SQL
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
databases.
• Run Transact-SQL • Use the SQLCMD • Use Windows
SQL Server
SQL Server provides
of the
database system. • SQL Server for SQL Server Transact-SQL databases. • SQL Server control SQL
management tool and a and SSIS, and SSRS. configure and protocols and
aliases. • SQL Profiler model, you enables you
or SSAS data replayed. This on actual
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
16 of 32
usage patterns.
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
and is
replaced by • SQL Server uses indexes schema recommendations
database provides can provide a
useful starting • SQL Server
simplifies the
process of • The sqlcmd can use to • The bcp utility
tool that you or scripts. command line
tool for importing
Additionally, components Data Services. Tools for Business
specific Services, and Master Server Data and use them
to develop database components.
SQL Server
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
17 of 32
SSMS is the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Visual Studio
shell used for • Object Explorer manage the connect to
instances and prompted to Explorer. You
can then connect • Code Editor (typically
interfaces Transact-SQL
statements
management tasks
enables you
later time or
scheduled provides auto-completion readability.
which improve script used
statements.
most tasks
that can be
scripts for
administrative • Solutions connections,
scripts, track of all the
script files • Reports. configuration
view detailed and other
objects.
The sqlcmd
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
18 of 32
The sqlcmd scripts in a SQL
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
statements or from the
command line,
SSMS is not
available. In
connection
(DAC) to a server Parameters The sqlcmd
and perform
tasks. These
-S server_name ( -d database_name ( -U login ( -P password ( password) -E (use -A (open -i input_file (
specified
input file) -o output_file (
file)
-q "Transact-SQL -Q "
exit)
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
19 of 32
-v var
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
input
script)
The sqlcmd utility
-? At the
command line.
Using Transact-SQL
You can perform
interface.
However, some
and even if a
task can be performed that can be saved
Note: the equivalent
Transact-SQL code scheduled job.
that generates the UI.
Transact-SQL • Explicit data Transact-SQL
use the corresponding
DROP DATABASE
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
20 of 32
• System stored
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
procedures
and functions
tasks. For
example, you
Server instance
configuration • DBCC (Database configuration database.
perform specific SQL Server the logical
and physical
Using Windows
Windows PowerShell
for system
administrators. It can be used and non-Microsoft
Azure services, scripting
language across Windows PowerShell In traditional simple (such PowerShell cmdlets
from the very Windows tools built
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
21 of 32
into the shell.
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
perform complex
tasks. Cmdlets have Get-Help, Get-Process performs; for "format" cmdlets The noun specifies
dash (-), such as cmdlet change data, destination. retrieves
information Cmdlets are PowerShell environment
loaded into the available
modules on a
you need.
When you install
SQLPS
module, which
instances and
objects. Using SQLPS The SQLPS • A SQL Server
to file
system paths.
where the drive
is associated
based on the
object model retrieve objects and dir, to
Get-ChildItem to such as cd command
prompt window. • A set of SQL
as running
Transact-SQL
You can use SQL Server Instances of
instances of later. Server 2014
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
22 of 32
Windows PowerShell
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
they are
limited to the PowerShell You can use • The Windows
user interfaces: window in which
you can run • The Windows PowerShell
provides a other features to
simplify script • SQL Server which the
SQLPS.exe in within SQL
Server Management
Lab: Using Scenario As a new database
familiarize yourself
with the SQL
Objectives After completing • Use SQL • Use the sqlcmd • Use Windows
Lab Setup Estimated Time:
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
23 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Virtual machine: User name: ADVENTUREWORKS\Student Password: Pa$$w0rd
Exercise
Scenario The DBAs at
the primary
administrative
yourself with
it. The main tasks 1.
Prepare
2.
Use Object
3.
Create
4.
Run a Transact-SQL
5.
Create
Task 1: Prepare
1.
Ensure then log
running, and password
Pa$$w0rd 2.
Run Setup.
Task 2: Use
1.
Start SQL
engine using
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
24 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Windows 2.
Ensure
the databases
that are 3.
View the
Task 3: Create
1.
Under MIA-SQL
create a new
database 2.
View the
database has been
created.
Task 4: Run
1.
In SQL Server Transact-SQL EXEC
2.
View the
you created
in the previous 3.
Save the
Task 5: Create
1.
In SQL Server
D:\Labfiles
\Lab01\Starter 2.
Ensure connection
The new Windows
authentication. 3.
Add a new
4.
In Object
point to Tasks,
and click
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
25 of 32
5.
In the Back
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
select Script
Action to 6.
Paste the
7.
Edit the
\Labfiles\Lab01\Starter
\AWDatabase. 8.
Save all
Server
Management
Result: At Studio project
Management
Exercise
Scenario DBAs at Adventure perform maintenance
Server and sqlcmd.
The main tasks 1.
Use sqlcmd
2.
Use sqlcmd
Task 1: Use
1.
Open a
sqlcmd
parameters: sqlcmd
2.
Enter the
Windows
authentication:
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
26 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
sqlcmd
3.
In the sqlcmd Verify that
on MIA-SQL. exercise:
SELECT GO
4.
Enter the
Exit
Task 2: Use
1.
In the command
the
GetDBInfo sqlcmd
Note that
command prompt
screen. 2.
Enter the sqlcmd D:\Labfiles\Lab01\Starter\DBinfo.
3.
Use Notepad
Result: At
database.
Exercise
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
27 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Scenario IT administrators across a range
configuration tasks PowerShell with SQL
Server. The main tasks 1.
Use Windows
2.
Using PowerShell
3.
Create
Task 1: Use
1.
On the taskbar,
2.
At the Windows Get-Process
3.
Review
Then enter the
following Get-Process
4.
To find a Get-Help
5.
Review Get-Process
6.
Verify that
PowerShell.
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
28 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Task 2: Using
1.
In SQL Server
then click Start
PowerShell 2.
Enter the
they include
SQLPS Get-Module
3.
Enter the Set-location
4.
Use the
on the server:
Get-ChildItem
5.
Use the
\SQL\MIA-SQL
\DEFAULT\Databases 6.
Use the
7.
Use the
the server
version: Invoke-Sqlcmd
8.
Close the
Studio without
saving any
Task 3: Create
1.
On the task
2.
In the PowerShell
PowerShell ISE. SQLPS
module
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
29 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Get-Module
3.
Use the
Get-Module cmdlet to
verify that Import-Module
4.
If the Commands the Commands noting that
Add-on. Then in the module, and starting
SQL Server 5.
If the Script
Script pane, type
the following Import-Module Set-location
Get-Childitem
|
Out-GridView
6.
Click Run
may take a few
minutes 7.
Close the Import-Module Set-location
Get-Childitem
|
Out-File
8.
Save the
close the
PowerShell 9.
In the D:
with
PowerShell 10. When the
results. Then close
Notepad.
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
30 of 32
Result: At about databases
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
information
Review
Module This module
to manage it.
The rest of this
operations in
a SQL Server
Review Question(
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
31 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
26/09/2016 15:42
Module 1: Introduction to SQL Server 2014 Database Administration
32 of 32
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
26/09/2016 15:42
