Module 12: Automating SQL Server 2014 Management
1 of 46
Module
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Management
Contents:
Lesson 1: Lesson 2: Lesson 3: Lesson 4: Lesson 5: Lab:
Module The tools provided with other database need to repeat repetitive tasks. at the required
compared common to automate execute a task tasks helps to
ensure they are This module
configure
security contexts
Objectives After completing
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
2 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
• Describe methods • Create jobs, • Manage SQL • Configure • Configure
Lesson There are many Most of the benefits SQL Server but the most working with
Management management. management tasks. management, administrators management
of SQL Server
Lesson After completing • Explain the • Describe the
the framework
that SQL • Describe SQL
Benefits
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
3 of 46
All efficient
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Some
benefits you • Reduced Server, Windows®,
with SQL stream of
repetitive
University
department
accounts. The
administrator
standard tooling.
A more efficient
execute that
instead of • The same these tasks
can perform do this. They
automate
repetitive workload
from the administrators
systems or to
perform higher-value • Reliable e
manually, there is
always a chance
administrator
could forget
to focus on
exceptions
the tasks.
• Consistent routine tasks
you perform each time.
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
4 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Imagine a
set of
production
tables need
to have the • While the
date. morning, there
is a likelihood
o
Copy
o
Copy
o
Forget
o
Format
o
Copy
Anyone who
that these
and other problems
executed by
experienced
greatly in making
sure that they Proactive Management After you automate For example,
no-one notices. noticed until
one of the backups As well as automating telling you when example, you The job may the target folder.
notifications could. For given folder. deletes or renames rectify the
situation. A proactive administrator rather than receiving might schedule
For example, administrator notification is received
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
5 of 46
when it is starting
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
performance
conditions for
The SQL
The primary when working as a Windows executing jobs, to begin automatically is for SQL Server
routine tasks Agent runs roles of configure it installation working
with it. You can configure
SQL Server
Agent service
start modes:
• Automatic • Disabled. • Manual. The
You can also unexpectedly,
stops Server
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
6 of 46
Management must be a member
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
service account Server is
installed—but
use an
external monitoring
and restart the
SQL Server Agent
SQL Server
The SQL Server
core object
types: • Jobs that you • Alerts that • Operators
Jobs You can use
scripts,
Transact-SQL
You can
also use them
the
implementation
Data Capture
(CDC), Data
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
7 of 46
Note: this course.
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
scope of
Alerts The alert system alert types, including
wide variety of counter events,
and Windows Operators You can configure
execution of a SQL
Server Agent
Agent,
administrators
notifying
operators is by
and
operators are
Note: monitoring course.
Lesson Because SQL
complex scope of this
Jobs Server,
database administrators
Server Agent
jobs. You can
categorize them
for ease of management. In this lesson,
Lesson After completing
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
8 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
• Define jobs, • Create job • Schedule jobs
Defining
Creating a job
along with
the workflow
order. In most
jobs, the steps
required. For
example, you
different step
to execute if After you determine most of your
will likely run the ability to
create a flexible It is important recreate the job you may create
quickly example, your
production environment. Job Step Types
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
9 of 46
Every job step
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
most
commonly-used • Executing • Executing • Executing • Executing
Note: compatibility, development.
and queries.
backwards
Creating Jobs You can use procedure, as After you create
stored to the job. database, alongside
all the SQL Server This example Using sp_add_job
USE msdb; GO EXEC sp_add_job @job_name @enabled @description GO
Job Categories 26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
10 of 46
You can organize
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
built-in categories,
such as Database This is useful category. For and write a PowerShell
specific Policy Check SQL Server
2005 servers.
Creating
After you create
the
sp_add_jobstep
describes
the key arguments
Argument @job_id
@job_name @step_id
or
@job_id, not both). by 1.
@step_name @subsystem
CMDEXEC
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
11 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Argument
@command @on_success_action
step).
@on_fail_action @retry_attempts @retry_interval
By default, SQL fails. However, failure flags. you can create as each job step that, even though You can specify job step if the the job step requires
when a job step success or job step, that, as well This means failure. execution of a example, if retry attempts
in case the connection This example Using sp_add_jobstep
USE msdb; GO EXEC sp_add_jobstep @job_name @step_name @subsystem @command @retry_attempts
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
12 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
@retry_interval GO
Scheduling
You can define times. In addition
requested number of special
recurrence types: • One time • Start automatically • Start whenever
When the recurrence While you can
multiple schedules. having too many
starting at the Even though concurrent execution. Server Agent
single scheduled, SQL scheduled to run
again, SQL Server
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
13 of 46
In the graphic
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
shows how to
create and attach Using sp_add_schedule
USE msdb ; GO EXEC sp_add_schedule @schedule_name @freq_type @freq_interval @freq_subday_type @freq_subday_interval @active_start_time @active_end_time GO EXEC sp_attach_schedule @job_name @schedule_name GO
Demonstration: In this demonstration, • Create a job. • Script a task • Generate
Demonstration Create a Job
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
14 of 46
1.
Ensure and log
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
are running, the
password 2.
In the D:
3.
Start SQL
database engine
instance 4.
In Object
existing jobs.
Then right-click 5.
In the New
Check
AdventureWorks 6.
In the New
7.
In the New Make Folder drop-down batch file
box, type in the Type which calls a \Demofiles
\Mod12 D:\Demofiles\Mod12\MakeDir.
8.
In the New
9.
In the New
10. In the New DB Info
box, type Get Type
drop-down EXEC
11.
In the New
box, type
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
15 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
D:\Demofiles\ 12. In the New 13. In the New Check
box, type the Type
drop-down DBCC
14. In the New
box, type
D:\Demofiles\ 15. In the New 1:Make
set to steps in the
job. 16. In the New 17. In the New Frequency ensure 18. In the New
in the frequency area, . the Jobs folder
in Object
Script a Task 1.
In Object
point to
Tasks, 2.
In the Back destination
existing backup Destination
dialog box, \AdventureWorks. 3.
In the Back
drop-down list,
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
16 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
select Script 4.
In the New
the job
(Back Up
that the job
includes 5.
In the New Schedule
Pick select the
Weekly 6.
In the New AdventureWorks
7.
Verify that
Generate Scripts 1.
In Object as, point
to Script Job generates the
Transact-SQL 2.
In Object
job, point to
Script
insertion
point at
on the Edit
menu, click 3.
Save the Using this jobs can
4.
\Demofiles\Mod12 folder. ensure that different server.
Keep SQL
Lesson When you automate help with this,
correctly. To on the
completion of
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
17 of 46
In this lesson,
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
troubleshoot
any issues that
Lesson After completing • View job history. • Query SQL • Troubleshoot
Viewing
Every job and
job or step
to find out why
tracks the
outcomes of
also choose
to write this You can view By default, the
history tables. you can
configure this
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
18 of 46
The Object Explorer view of currently
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
displays a execution, along
with the scheduled
Querying Views
and
There are many
query to
retrieve information
stored in
the msdb database dbo.sysjobhistory The following
sysjobhistory tables to
retrieve information Querying Job
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
19 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
SELECT j. FROM msdb. INNER JOIN ON jh.job_id WHERE jh. GO
Note: but an record querying
Additional Server Online.
not zero, zero to obtained by
store SQL Server Books
Troubleshooting
Jobs do not always
It is
important to
is failing.
There are four
status,
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
20 of 46
reviewing job
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
dependencies.
Checking SQL If SQL Server
to start
automatically
will still not
start, check the • That the service changed, and service will
account has not issue, the computer’s System
event log. • That the msdb
offline, SQL
Server Agent
Reviewing Review the job
because a job
step failed, which
be seen at this
level. It is necessary Checking Job If SQL Server
following items:
• That the job • That the job
next scheduled
execution • That the schedule
job will not
run on a disabled
Checking Access Verify that all available. Jobs
procedures, are them. Incorrect
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
21 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
security settings
Demonstration: In this demonstration, • Run jobs. • Troubleshoot
Demonstration Run Jobs 1.
Ensure
2.
In SQL Database
Back Up the job has
completed 3.
In Object at Step
click Start Job Close.
Troubleshoot 1.
In Object
job and click
View History 2.
In the Log
most recent
instance 3.
In Object
click View
History 4.
In the Log
most recent
instance
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
22 of 46
5.
Select the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
view the
message 6.
In Object Properties
Then in the Job select step 3
(Check 7.
In the Job
as follows
and click DBCC
8.
In the Job
9.
In Object
click Start Job
at Step 10. In Object
of the
Check 11.
Click Refresh
Run Outcome
for the
Monitor.
12. In the Start
Server
Management
and click
Close. 13. In the D: AdventureWorks was created
Check backup file AdventureWorks job.
14. Keep SQL
Lesson By default, SQL service account.
Contexts Server Agent this account
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
23 of 46
requires the appropriate privileged account. with a wide range permissions
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
in a highly account minimal categories of job
step. This lesson discusses
jobs.
Lesson After completing • Describe considerations • Create credentials. • Create proxies
Job Step
When planning
under which
the steps in those Transact-SQL
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
24 of 46
Generally, when
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
impersonates
the job owner.
fixed server
role, the job
unless the
sysadmin chooses
sysadmin fixed
server role can Other Job For job step member of the
used. When a execute using the
SQL Server Agent
variety of job
step types, granting
executed may
introduce a security
highly-privileged
account, which Proxy Accounts As an alternative associate a job create proxy
account to credential. You can Using
proxy accounts
the various
tasks required
single account to
have all the permissions
Credentials
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
25 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
A credential
required to
connect to a
user name and
password. If resources, you
same set of cannot map a SQL
Server login SQL Server endpoints. These
with specific with two hash
signs (##). Creating Credentials You create credentials
statement or
SSMS. When you create
and SQL Server
encrypts it by Creating a Credential
USE master; GO CREATE CREDENTIAL
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
26 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
WITH IDENTITY SECRET GO
After you create
LOGIN
statement. Mapping a Credential
ALTER LOGIN GO
Managing Credentials SQL Server provides
about
existing credentials. Because the
update a
credential with You need to
ALTER
CREDENTIAL Altering Credentials
ALTER CREDENTIAL WITH IDENTITY SECRET GO
You can remove
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
27 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Proxy Accounts
For a job step
credential to
a proxy account
can also
create your own.
step. SQL
Server Agent
Microsoft
Windows user.
Note: job permission
as a batch
Creating Proxy You can create
in the msdb
database, or Creating a Proxy
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
28 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
EXEC dbo. @proxy_name @enabled @description @credential_name
Creating a proxy
account that
is specified in
Windows
account that
Job steps
using that proxy A user must By default, only proxy accounts
a job step. access all principals:
• SQL Server • Server roles. • Roles within
Proxy Accounts A subsystem
in SQL
Server, for example,
proxy
accounts for
account with
one or more
subsystems.
Subsystems available to a
functions that are specified
subsystems by
Agent runs
a job step that
proxy
account and
checks
subsystem access
environment
has changed
step fails.
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
29 of 46
The following
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
SSIS
subsystem. Granting a Proxy
EXEC dbo. @proxy_name @subsystem_name
Managing SQL Server accounts, in
for proxy you can use
to query information
System View dbo.sysproxies dbo.sysproxylogin
associated with
dbo.syssubsystems dbo.sysproxysubsystem
The following
proxy account.
and their
proxy accounts. Reviewing Proxy
USE msdb; GO SELECT p. p.description FROM dbo.
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
30 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
INNER JOIN ON p.credential_id
Demonstration: In this demonstration, • Create a credential. • Create a proxy • Run a job
Demonstration Create a Credential 1.
Ensure
2.
In SQL
expand
Security 3.
In the New
o
Credential
o
Identity
o
Password
o
Confirm
MIA-SQL\FileAgent
minimal
privileges. Create a Proxy
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
31 of 46
1.
In Object
2.
Right-click
3.
In the New and in the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
FileAgentProxy, Operating
system (
Run a Job Step 1.
In Object
double-click the
Check 2.
In the Job
Steps page,
click step 3.
In the Job
drop-down list,
select FileAgentProxy 4.
In the Job
5.
Right-click the Start
6.
When the
7.
In the D:
Step. Then in Start.
that was
created 8.
In the AdventureWorks
9.
Note that used to for AdventureWorks
Advanced. account that was Security Settings AdventureWorks
Properties 10. Keep SQL
Lesson 26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
32 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
You may have
being
automated. SQL
enables you to
distribute jobs In this lesson,
administration and how to
implement jobs
Lesson After completing • Describe the • Explain the • Run jobs on • Automate
Multiserver
Multiserver administration
copy of the
jobs and distributes
also receive
events from
The target
servers are assigned
update their
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
33 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
schedule of jobs For example,
servers in your
organization,
Considerations
There are a number
setting up
multiserver administration: • Because the
events from
them, the
the master
server on • Each target server, you
requirement. your master enlist them
all to the new • The master name of the
change the rename it, and then
enlist it back • Because they
Server Agent
service and
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
34 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Configuring
You can configure
queries.
Using the Master In SSMS, you Object Explorer. server, select
node in master configure any
required logins. Using Transact-SQL You use the sp_msx_enlist server that you
The first subsequent
ones become The following
target
servers called Using sp_msx_enlist
USE msdb ; GO
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
35 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
EXEC dbo. GO; EXEC dbo. GO; EXEC dbo. GO;
Running
After you configure
from one to
the other by
Properties
dialog box for
servers,
selecting which To distribute
sp_add_jobserver
statement as Distributing
USE msdb; GO EXEC dbo.
'AWTarget1';
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
36 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
GO
If you change
servers, you
need to ensure
servers to
be updated. You can do this
shown in the
following example: Updating Distributed
USE msdb; GO sp_post_msx_operation GO
You only need
schedules;
sp_update_job
Note: dbo.sysjobs
sysjobhistory or
Demonstration: In this demonstration, • Create master • Create a job • Run a job
Demonstration 26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
37 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Create Master 1.
Ensure
2.
In SQL SQL Server
right-click Make this a
Master 3.
In the Master
Master
Server 4.
On the student@adventureworks.
5.
On the
6.
In the Connect
MIA-SQL\SQL2,
and then 7.
On the
8.
In the Checking
9.
On the
10. On the
is complete.
Click Close
Create a Job 1.
In Object
2.
Right-click
3.
In the New
4.
In the New
5.
In the Step (T-SQL)
Jobs.
Transact-SQL Transact-SQL,
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
38 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
then click BACKUP TO DISK
Because
the default
backup 6.
In the New
7.
In the New
servers. Then
select MIA-SQL\SQL2
Run a Job on 1.
In Object
Start Job at
Step. Then, 2.
In Object
Then
connect 3.
In Object
TSX:
MIA-SQL) 4.
Right-click
View
History 5.
Review
then click
Close. 6.
Close SQL
7.
In File SQL2\MSSQL\Backup
Server\MSSQL12. it contains a
master. 8.
Close File
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
39 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Lab: Automating Scenario You are a database databases on this instance
responsibility for performed on automate these
tasks using SQL
Objectives After completing • Create jobs. • Schedule jobs. • Configure
Lab Setup Estimated Time: Virtual machine: User name: ADVENTUREWORKS Password: Pa$$w0rd
Exercise
Scenario The HumanResources has been created,
the backup automatically replicated
to a cloud service
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
40 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
The main tasks 1.
Prepare
2.
Create
3.
Test the
4.
Generate
Task 1: Prepare
1.
Ensure then log
running, and password
Pa$$w0rd 2.
Run Setup.
Task 2: Create
1.
Create a
2.
Add a Transact-SQL following
Server. executes the the
D:\Labfiles\Lab12\Starter BACKUP
3.
Add an
\Backups\HumanResources.bak';
the backup file
to the D: Copy \HumanResources.
4.
Ensure
Task 3: Test
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
41 of 46
1.
Run the
2.
View the
3.
View the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
a text file
containing
Task 4: Generate
1.
Generate
and save it in
the D:\Labfiles\Lab12\Starter
Result: After HumanResources.
Exercise
Scenario You have created
must schedule
the job to run The main tasks 1.
Add a Schedule
2.
Verify
Task 1: Add
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
42 of 46
1.
Enable the
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
see the
current system 2.
Add a schedule
day one minute
from the 3.
Wait for
Task 2: Verify
1.
Use the
job.
2.
When the
and that the
Last Run
Result: After HumanResources
Exercise
Scenario You have created you want to use
However, steps.
The main tasks 1.
Create
2.
Create
3.
Configure
4.
Test the
Task 1: Create
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
43 of 46
1.
Create a
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Windows
account. 2.
The password
Task 2: Create
1.
Create a
CmdExec)
subsystem. 2.
The proxy
Task 3: Configure
1.
Configure back up
command to [Backup_User] user
in the HumanResources 2.
Configure
backup file) in
the Backup
Task 4: Test
1.
Run the
Result: After Backup HumanResources have also created FileAgent_Proxy job.
step of the should HumanResources
Review Review Question(
Module
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
44 of 46
In this module,
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
automate
database maintenance
Best Practice: practices. • Use
following best
• Create • Script
them.
• Use • Use • Apply identities
execution
Review Question(
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
45 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
26/09/2016 15:54
Module 12: Automating SQL Server 2014 Management
46 of 46
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
26/09/2016 15:54
