20462 Vancouver May 2016

20462 Vancouver May 2016

Welcome to our Global Knowledge presentation of

GK6553 20462D Administering Microsoft SQL Server Databases

I have run this course in the past an you can find a daily
plan along with a summary of my extra thoughts here


http://metafore2012.blogspot.ca/2015/12/20462-day-1.html
http://metafore2012.blogspot.ca/2015/12/20462-day-2.html
http://metafore2012.blogspot.ca/2015/12/20462-day-3.html
http://metafore2012.blogspot.ca/2015/12/20462-day-4.html
http://metafore2012.blogspot.ca/2015/12/20462-day-5.html

Here are some entries which expand on the concepts introduced during the course
http://metafore2012.blogspot.ca/2015/12/20462-system-account-for-sql-database.html
http://metafore2012.blogspot.ca/2015/12/20462-using-trace.html
http://metafore2012.blogspot.ca/2015/12/20462-system-stored-procedures-for.html
http://metafore2012.blogspot.ca/2015/12/20462-extended-events.html
http://metafore2012.blogspot.ca/2015/12/20462-snapshot-vs-database-backup.html
http://metafore2012.blogspot.ca/2015/07/20462-contained-databases.html


Some notes on the "D" update of this course

• geared towards "single server" admin (for multi-server see 20465)
• interactivity of courseware is NEW

"January 4, 2016: One interactive review question was added to each lesson in every module of the course. All graphics were revised to incorporate Microsoft�s new branding.

• Graphics were added to some existing slides where they were helpful to student understanding. This is great for exam review preparation
• more attention to the entry level DBA role and job progression

"Refactoring topics across the curriculum to reflect a progression of DBA role responsibilities from single database server management (this course) to full enterprise data center design and configuration (course 20465D)"

• will NOT include Azure options

"Any references to Azure have been removed."
"For this the D revision of the course we continue to use the C revision VMs as none of the hands?on material has changed."

Participant Info
------------------
Tim Simon - SQL many years
report writer
mining equipment
russia is a big customer

Howard - gaming > casinos
app support and dev specialist
backup for prod DBA

James - univara
sql - get familiar
perhaps new job role

Ed - came to class before in Calgary
senior solutions developer
20 databases
some reporting
corp
failure management 2500 pieces > monitor norms
$$$ spent on SAP
ERP
SQL v7 onwards
corp health and safety
interested in automation

James - enmax
control center > scada
has some SQL servers
network background
did 20461 - TSQL

-----------------


Here are a few resources, links and notes that we added this week.  See the above day-by-day links for more:

Day 1

It is really worth you while to learn powershell
- Automation and workflow (a layer of intelligence)
- Bulk operations
- Integration with other MS (and non MS or cloud) systems

Pls download the Course Companion
www.microsoft.com/learning/companionmoc
search for 20462D
source code for SQL and Powershell
additional courseware with answers, demos and links

//an energy related example of archive backups the electricity industry
(See Q &A)

This is a good lead-in for Day 2 (m4-m5)

//Big Data > Hadoop, Map reduce and Oozie

NOTE: ALL refs to Azure removed from this course but you may want to figure out if Azure can bring anything to your organization. Detailed coverage is expected to be in 20465 but relevant

- backup from
- backup to
- SQL as Service
- workloads
- colo
- failover
- upgrade and expansion
- redundancy options
- weary of compliance
- the down-side of the cloud


m1
Powershell 101
Get-command
Get-help
history

Powershell ISE >
Get or Set the "Execution Policy" > 4 levels
Get-alias

m2
Installation
Extras from blog

NOTE: network protocols (e.g. Named Pipes)

Day 2
Q - How does SQL 2012 compare to 2014?

Some feature improvements:
In-memory OLTP
BPE to SSD
DR to Azure
https://www.microsoft.com/en-us/server-cloud/products/sql-server/Comparison.aspx


m4
Q - what is a Media Set?
A - This can be made up of one or more files mounted on Drives or File Shares.  The backup or backup set can be stored across the media set

Q - what is a Backup Set?
A - new entries are created each time you perform a backup

e.g. it may append to a previous backup, create a backup of a log file in addition to the main backup, add a tail log file to the mix.  Even though a normal backup may consist of a single .bak file it is more accurate to use the term backup set
https://msdn.microsoft.com/en-us/library/ms186299.aspx

Discussion Question - Planning Backup Strategies

Suggested solutions are provided below.

HumanResources

Set the database to the Simple recovery model and perform a full database backup every weekday at 18:30. This ensures that the database can be recovered up to the previous day, meeting the RPO requirement. The backup should take approximately 1.4 minutes (200 MB at 150 MB/minute), and a restore can be completed in approximately two minutes (200 MB at 100 MB per minute). This more than meets the RTO requirement of an hour, even allowing for time to report the issue and locate personnel and backup media.

InternetSales

Set the database to the Full recovery model. Perform a full database backup every 12 hours (at midnight and noon), a differential backup every three hours in between full backups (at 3:00, 6:00, 9:00, 15:00, 18:00, 21:00), and a transaction log backup every 30 minutes between differential backups. The full backup will take around 1.2 hours (10 GB at 150 MB per minute), each differential backup will take around four minutes (3 x 200 MB = 600 MB at 150 MB per minute), and each log backup will take around 45 seconds (200 MB / 3 = 66.67 MB at 150 MB per minute).

In a worst case scenario, the database would fail immediately before the last transactions log backup that is scheduled before the next differential backup�for example, at 20:29. In this case, restoring the full database backup (taken at noon) will take just under 1.67 hours (10 GB / 100 MB per minute), restoring the latest available differential backup (taken at 18:00) will take around six minutes  (600 MB / 100 MB per minute), and restoring the four transaction log files taken at 18:30, 19:00, 19:30, and 20:00 will take approximately 2.7 minutes (four logs, each containing 66.67 MB = 267 MB / 100 MB per minute). The total recovery time is therefore just under 1.82 hours, with a maximum data loss of 29 minutes of transactions.

AWDataWarehouse

Set the database to the Simple recovery model. Each month, back up the read-only filegroup on the last Saturday of the month after the weekly data load and monthly archiving process has completed. This will take around 11 hours for the current volume of data (100 GB / 150 MB per minute). Then on the last Sunday of the month, perform a partial backup that includes the primary filegroup and all read/write filegroups. This should take approximately 7.8 hours (50 GB + 4 x 5 GB = 70 GB / 150 MB per minute). For every other week in the month, on the Saturday after the data load batch operation has completed, perform a differential partial backup of the primary and read/write filegroups. This should take approximately 1.67 hours on the third week (3 x 5 GB = 15 GB / 150 MB per minute).

In the event of a disaster, restore the partial database backup from the monthly backup, which should take around 11.7 hours (70 GB / 100 MB per minute), and restore the latest differential backup, which should take around 2.5 hours (15 GB / 100 MB per minute). Then restore the read-only filegroup, which should take around 16.7 hours (100 GB / 100 MB per minute). If you perform a piecemeal restore, the data in the read/write filegroup will be accessible after the differential backup has been restored (that is, after 14.2 hours). The data in the read-only filegroup will become accessible 16.7 hours later�after that filegroup has been restored. The total recovery time for the entire database is 30.9 hours.

Piecemeal restore operations are discussed in the next module.

Discussion Question - Determining Required Backups to Restore

It is important for you to  work out which backups are required for a restore.

1.      Attempt to create a tail-log backup.

2.      Restore the full database from Saturday night.

3.      Restore the differential database from Tuesday night.

4.      Restore all log backups from during the day on Wednesday (09:00, 10:00, 11:00, 12:00, 13:00, 14:00, 15:00, 16:00, 17:00, 18:00).

5.      Restore the 9:00 and 10:00 log backups from Thursday.

6.      Restore the tail-log backup (if it was successful).

Day 3 = Backup & Restore day


m4
NOTE:
LDF(m4) vs BPE(Appendix B)
Use of memory
Snapshots
Some key points
All "write" operations happen in memory and written back
to disk (See the role of Checkpoints in Appendix B)

//2014 cloud fail-over models (SQL IT camp)
//2014 Azure Storage interface (20532)
//CRA maintenance times
//Gaming, Parking
//Tim and GP > GCCoal
//ICBC
//look up health region
//more details on UK
//Airforce App
//Vanvouver -ICBC
//Surrey - Fortis BC, Natural Gas
//Burnaby - Health region IT


James (univara) Veeam > reversed incremental
https://www.veeam.com/kb1933


m5
Restore from 3 traditional strategies
Look for the advanced tools
- Piecemeal
- Page
- other tools to mount DB and repair torn pages

James(enmax) - can you restore a log file when the database is online?
A - yes
Online Restore (SQL Server)
https://msdn.microsoft.com/en-us/library/ms188671.aspx


Tim - What is the correct way to use the RECOVERY option in the answer for the Discovery Question at the end of Module 5?
A
https://msdn.microsoft.com/en-us/library/ms179451.aspx#TsqlExample

James(Enmax) - refs for Hyperconverged systems
..top 10 article
http://www.networkcomputing.com/storage/10-hyperconvergence-trendsetters-/d/d-id/1319413?image_number=3

James (univara)
On-prem backup for "SQL as a service" to on-prem
https://www.ahsay.com/jsp/en/home/index.jsp?pageContentKey=ahsay_pricing_backup-software_v7_list-price
3rd party tool

https://azure.microsoft.com/en-us/documentation/articles/sql-database-business-continuity/
better to replicate inside the cloud

https://azure.microsoft.com/en-us/blog/azure-sql-database-built-in-backups-vs-importexport-2/
using built-in backup

https://blogs.msdn.microsoft.com/mast/2013/03/03/different-ways-to-backup-your-windows-azure-sql-database/
8 different approaches to the problem

https://sqlazuremw.codeplex.com/
A great open source tool

https://sqlbackupandftp.com/Azure-SQL-Database-backup/
another tool for migration to and from Azure SQL

https://www.youtube.com/watch?v=sF10wAgrnbI
Windows Azure SQL backups

http://www.red-gate.com/products/dba/sql-azure-backup/
backup to Amazon S3

https://www.mssqltips.com/sqlservertip/3057/windows-azure-sql-database-backup-and-restore-strategy/
a graphical walkthrough

https://blogs.technet.microsoft.com/cis/2016/04/27/deploy-global-file-sharing-for-collaboration-using-microsoft-azure-storsimple-and-talon-cloudfast/
combine StorSimple with TalonFast

Tim
Q - how can I read blob data into a report?
A - IE links for sample code running in .NET
NOTE: I can help you to get up and running if you want to turn this into your own reporting
utility

https://technet.microsoft.com/en-us/library/gg316754(v=sql.105).aspx
Some developer code

https://support.microsoft.com/en-us/kb/317016#bookmark-blob2file
Using a utility called blob2file

https://msdn.microsoft.com/en-us/library/87z0hy49.aspx
retrieving binary data

https://technet.microsoft.com/en-us/library/gg316765(v=sql.105).aspx
Storing Blob data


//generally, Infrastructure folk are required to
be able to deploy databases
applications and solutions for developers

also expected to run maintenance tasks, scripts and
SSIS packages

//sharepoint and blobs

summary
m6 - Export and Import
m7 - Monitor (part 1)
m8 - Monitor (part 2)

blog articles on trends
IoT, Big Data, Machine Learning
Cloud options > AWS, vCloud, Azure, IBM, HANA
Hyperconvergence
Commercial Uses of Big Data > Google, W10
..how can you use Big Data and BI at your org?
Message analyzer > Network Monitor default (layout)

List of shared refs:
"Tractor Beam"
United States of Secrets
Alex Winter


Working with Broker Service (in SQL)
//Integration tools > Biztalk, WCF, MuleSoft
Dynamics > custom, SDK, Scribe

blog entry on System Databases

m6 - Export and Import tools
sys vs DMV > volatile vs persistent
remember the security piece with a move
- better to script it from the beginning
- use sys_sps to detect other accounts that were
created by software

//disable xxx to improve copy performance
- show constraints, indexes, triggers

SSIS and DW course
//(short) SSIS demo
// where to find demo source code for SSIS, SSAS, BI,

SSRS

.dacpac > encapsulates the app info

Lab gotchas
- typo for "candidate"
- delete rows before Ex 4

m7 - Monitor (part 1)
- must choose the location of the
data collection set

m8 - Monitor (part 2)

-------------

Ed
wants to upgrade dashboards but needs SSI,A,R
Excel POs, open work orders
EHS > built a model
.. environmental health and safety

blog > Problem Steps Recorder
blog > Expression Encoder

Tim - how do you store pictures in a
database?


blog > Developer Essentials, VSC
demo angular
Evil sound barrier > Bands, Companies
Todd McF, Nickle Back

Ed/Tim
Blob management tool
- how do you retrieve data/files from blobs
- how to repair or inspect the files
Tim .. Starting point is reporting services

Scriptomatic for powershell

Ed
Which DMVs can be useful for
which situation

Working with event logs
EventCombMT.exe
Trace32

m8
Lab gotchas
-may need to use arrow keys to find the
correct TSQL row and click filter for
Database name

my own demo:
Index, Constraint, Trigger
as they relate to Tuning advisor and bcp



Day 4 = Security day

m9 Security model
m10 Auditing, Encrypting

Illustrations for DBA concepts
----------
Q&A

James (univara)
On-prem backup for "SQL as a service"

James (enmax)
How is .mdf , .ldf and .ndf files used in the real world?
A - storage or large tables, archived data, Data Warehouse,

backup and restore
//an energy related example of archive backups the

electricity industry
e.g. IOT > BITstew
backup vs standby files (filegroup is a type of backup)
normally the backup file is a .bak
single server example..expand to multiserver (20465)
The configuration and use of files would be similar OTPP

project - diagnostic performance data, forecasting

This is a good lead-in for Day 2 (m4-m5)

//Big Data > Hadoop, Map reduce and Oozie

Ed (cf)
Blog links from Dec 2015
Extended events vs. Profiler
Service accounts


Day 5 = Maintenance day

m11 - Maintenance
m12 - Automation

Some other automation tools
https://www.paessler.com
http://www.solarwinds.com/
https://www.microsoft.com/en-us/server-cloud/products/system-center-2012-r2/Components.aspx
"Orchestrator is a workflow management solution for the data center. Orchestrator enables you automate the creation, monitoring, and deployment of resources in your environment."
http://metafore2012.blogspot.ca/search?q=automation

m13 - Alerts and Notifications