SQLUG-träff med Tobiasz Janusz Koprowski i Malmö 13/2...SQLUG-träff med Tobiasz Janusz Koprowski...
Transcript of SQLUG-träff med Tobiasz Janusz Koprowski i Malmö 13/2...SQLUG-träff med Tobiasz Janusz Koprowski...
-
SQLUG-träff med Tobiasz Janusz Koprowski i Malmö 13/2
Friday, January 23, 2015SQLUG inbjuder till träff med Tobiasz
Janusz Koprowski i Malmö vilket håller sin föreläsning: 2 AM. A
Disaster just began...
Plats: Radisson Blu Malmö
Tid: Fredagen den 13:e februari kl 10.00 - 12.00Läs mer och anmäl
er: https://eventbrite.com/event/15426789944
1
https://eventbrite.com/event/15426789944
-
2AM
A Disaster just Began...TOBIASZ JANUSZ KOPROWSKI
SQL SERVER MVP, MCT
-
about me... Former Leader of Polish SQL Server User Group
Microsoft Certified Trainer (SharePoint, SQL Server, Security, Azure)
SQL Server MVP (five years)
MVP Mentor Program Member
consultant, security & licensing auditor
active blogger, influencer, technical writer
last 9 years living in Data Processing Center Jungle
last 15 years working arroung banking and ICT
frequently speaker at several conferences: SQL Server Community Launch, Time for
SharePoint, CodeCamps, InfoMEET, SharePoint Community Launch, CISSP Day, SQL in
the City, InfoTRAMS, SQL Bits, IT Camp, SQL Saturday, CareerCon, Sharepoint & SQL
Connection, Kariera IT, SQL Rally, SQL Relay, InfoTech…
Microsoft Terminology Community Member / translations for Wndows 7/8 and Visual
Studio 2010/2012
SharePoint Subject Matter Expert at CQURE
5
-
2:00 AM … In a dreams…
Your best time for dreaming … is the best time for Disaster
Your mobile phone ringing and ringing…
And Your husband / wife say…….
6
-
2:15 AM … in a car
What’s happen with my server?
When I made last backup?
Where is my backup?
Have I ever tried to restore?
If yes – I hope that all in a team (about team soon) remember
about (me?)
If not – who can help me NOW?
7
-
2:40 AM … in a SERVER ROOM
$#$$@$^^#^&^@!#
Is Windows Server alive?
YES (thanks all saints)
or NOT (damn)
who is responsible for it?
Is my SQL Server alive?
YES (why phones ringing)
or NOT (…)
Why I’m responsible for it?
8
-
A DISASTER – WHAT IS IT 9
CAUSES OF
DISASTER?
NATURAL
CAUSES
HUMAN ERROR
INTENTIONAL
CAUSE
fire
flood
lightning
earthqua
ke
volcano
hurricane
tornado
temperat
ure
sabotage
terrorism
vandalism
viruses
theft
union activities
disgruntled
employees
programming
errors
unauthorized
personel
improper
maintenance
lack of training
carelessness
cable cuts
-
Causes of The Disaster? 10
http://shoutitforlife.com/wp-content/uploads/2012/09/The-Names-of-
God.jpg
http://shoutitforlife.com/wp-content/uploads/2012/09/The-Names-of-God.jpg
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE:
BE PREPARED
11
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
Backups
about type of backup ( simple rm / full rm)
about place for stored backup data
about backup window
about procedure used for backup
about backup tools
about backup of „backup logs”
about estimated time for executing backup
about REAL TIME of executing backup
12
-
BACKUP > extract from SOP*
In the request, backup, should include the following information:
Information about the operating system and application version for online backup and installed updates for these components a file backup policy, in particular:
a number of versions of a file stored
the storage time of the next version of the file
the frequency of execution of such incremental backups with the proposal of their implementation
Online Backup Policy
the storage time of a full backup with storage time such an incremental backup
the time of transaction log files
the frequency of execution of a full backup with the proposal deadline for its implementation
the frequency of execution of the transaction log backup
Information about trees directories / files that should be omittedor included during backup tasks (include / exclude list)
Number and type / model of physical processors,
Does the node will use the connection to the SAN to implement backup
13
-
BACKUP (registry) > extract from SOP*
This register contains information about a backup plan implemented.
Backup file space:
number of versions of a file stored in a backup
number of days that are kept more versions of a file
number of versions of a file stored in the backup system after its removal from
client device
number of days that will store the latest version of the deleted file from the
client device
number of days that will be stored in the archive
The list of nodes defined in the system backup:
Domain | Node name | IP address of the node
The list of defined backup tasks (called schedule)
name of the task (schedule) | execution time
a period of at which the task is repeated
14
-
SQL Server Backup Best Practices | Written on October 17, 2007 by Brent Ozar in SQLServer
> http://bit.ly/12oXm4h
I’ve been backing up SQL Servers for almost a decade now, and it’s time to sharethe lessons I’ve learned. All of this is my own opinion – your mileage may vary – butI’ll try to explain the reasoning behind the choices I make. I won’t address logshipping or snapshots this time around.
• Never back up databases to local disk.
• Back up databases to a fileshare, then back the share up to tape.
• Cost justify the network share with lower licensing costs & simpler backups.
• Back up to a different SAN if possible.
• My sweet spot for the backup array is raid 10 SATA.
• Backup agents like NetBackup and Backup Exec mean giving up schedulingcontrol.
• Do regular fire drill rebuilds and restores.
• Build a standalone restore testbed.
• Keep management informed on restore time estimates.
• Trust no one.
Best Practices by Brent Ozar 15
http://bit.ly/12oXm4h
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
Restore
about type of backup ( simple rm / full rm)
about place for stored backup data
about the procedures of recovery
about estimated time for recovery
about REAL TIME for recovery
about tools for recovery
about Corporate Backup Manager
about password for access to library
16
-
RESTORE > extract from SOP*
Registery for Recovery/Restore/Replacement Tests
This register contains information about the tests and replacement of
part or all of the environment. It consists of the following fields:
the date of commencement and completion of the recovery test
client for which the test was performed recovery test
servers involved in testing and replacement
extent of testing and replacement
person / persons performing the recovery test
person on the client side accepts the correctness of the recovery
test
subsequent to the recovery test
17
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
Procedures
It is not about stored procedures!!!
It’s about storing procedures with answers for the following:
One piece of paper
How to start restore
Who can help
How to processing a restore
When we can finish
It MUST be simple
18
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
Roles
Database Administrator
Windows Administrator
Backup Administrator
Network Administrator
Customer Key Account
Manager of division
Data Center Manager
Nightshift Operator - BOFH
Customer Administrator!!
19
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
PSO > USO > SLA
PSO Planned System Outages – Planned System Unavailability
Minimum planned unavailability, due to the need to carry out modernizationwork, installing patches, replacement / extension of hardware,
Agreed/accepted by/with the client and not affecting the provisions of theHA, and SLA, until
...USO Unplaned System Outages – Unplanned System Unavailability
an error that prevents a partial or total work environment in a tangible,measurable customer
resulting in high costs if you need repairs, as well as penalty payments fornon-SLA
20
-
The Magic nines… 21
Availability % Downtime per year
Downtime per
month*
Downtime per
week
90% 36.5 days 72 hours 16.8 hours
95% 18.25 days 36 hours 8.4 hours
98% 7.30 days 14.4 hours 3.36 hours
99% 3.65 days 7.20 hours 1.68 hours
99.5% 1.83 days 3.60 hours 50.4 min
99.8% 17.52 hours 86.23 min 20.16 min
99.9% ("three nines") 8.76 hours 43.2 min 10.1 min
99.95% 4.38 hours 21.56 min 5.04 min
99.99% ("four nines") 52.6 min 4.32 min 1.01 min
99.999% ("five nines") 5.26 min 25.9 s 6.05 s
99.9999% ("six nines") 31.5 s 2.59 s 0.605 s
-
DISASTER EXAMPLE 22
-
DISASTER EXAMPLE 23
#SQLSatKrasnodar
-
DISASTER EXAMPLE 24
-
DEMO 1 with SQL
What are You looking for…
25
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
Envelope
With ACTUAL!!! User names and passwords for:
Windows Server Administrator
SQL Server Administrator
SQL Server Agent
SQL Server Services (if You didn’t use default)
SQL Server Applications Services
Backup accounts
26
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
Hardware
Some of the hard stuff for replacement:
Server
Motherboard
Memory (RAM)
Processor (CPU)
Network Adapter (LAN/NIC)
Fibre Channel Adapter
Hard Disk (IDE/SATA/SAS/SSD…)
RAID Controller
27
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
Software
Windows
2000/2003/2003R2/2008/2008R2/2012/2012R2
SP 1,2,3,4 +CU 1, 2,3, …
Standard, Enterprise, Data Center
x32, x64, ia64
SQL Server
6.5, 7.0, 2000, 2005, 2008, 2008R2, 2012, 2012R2, 2014
SP 1,2,3,4 +CU, 1,2,3,4,5,6,7,8,9,10,11,12,13, 14, 15, 16….
Drivers (servers, lan card, video card)
AGENT ORANGE
28
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
Keys
Some keys which You need…
Serial keys
Rack keys
Server keys
Storage keys
Knife
Torchlight; torch; flashlight (lighter)
Phone
29
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
ENCRYPTION
If You use encryption (such a TDE)
TDE
Create encryption key
Export encryption key
Backup encryption key
CA
Remember about expiration date
BitLocker
30
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
TEAM
You can work with disaster as:
Team Member
Team Leader
Last Samurai
31
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
MANAGERS
hmm
32
-
DEMO 2 no SQL
What I AM looking for…
33
-
MY BASIC DISASTER KIT 34
-
MY BASIC DISASTER KIT 35
-
MY BASIC DISASTER KIT 36
-
MY BASIC DISASTER KIT 37
-
MY BASIC DISASTER KIT 38
-
MY BASIC DISASTER KIT 39
-
MY BASIC DISASTER KIT 40
-
MY BASIC DISASTER KIT 41
-
MY BASIC DISASTER KIT 42
-
MY BASIC DISASTER KIT 43
-
MY BASIC DISASTER KIT 44
-
MY BASIC DISASTER KIT 45
-
MY BASIC DISASTER KIT 46
-
MY ADVANCED DISASTER KIT 47
-
MY ADVANCED DISASTER KIT 48
-
LAST STEP OF DISASTER 49
-
BEST PRACTICE FOR SURVIVING DISASTER
ITS ONLY ONE: BE PREPARED
• Backups (and know-how about stored place, and restoring way)
• Procedures (the short is better | one page is the best)
• Roles (who can help, who is necessary for access)
• SLA (90? 95? 99,99? in minutes, hours or days you have to recover)
• Envelope (with user names and passwords for all important accounts)
• Hardware (server, motherboard, CPU, RAM, LAN, HDD, SDD, USB)
• Software (Windows+SP+CU, SQL+SP+CU, drivers, AGENT ORANGE)
• Keys (serial numbers, physical keys, knife)
• ENCRYPTION (arrghhhhh!!! Certificates, keys, internal/external)
• TEAM (Team, leader, separate…)
• MANAGERS (hmmm)
50
-
AND one more very important:
DON’T PANIC !!!
51
-
AFTER SESSION
CONTACT:
MAIL: [email protected]
TWITTER/FACEBOOK/LINKEDIN: KoprowskiT
SLIDES FROM SESSION:
SQL User Group Sweden: http://SQLug.se/
SlideShare Profile: http://www.slideshare.net/Anorak
BLOGS:
ITPRO Anorak’s Vision: http://itblogs.pl/notbeautifulanymore/ [PL/EN]
Volume Licensing Specialites: http://koprowskit.eu/licensing/ [PL]
My MVP Blog: http://koprowskit.eu/geek/ [EN/ES/PL]
52
https://www.sqlsaturday.com/311/schedule.aspxhttp://www.slideshare.net/Anorakhttp://itblogs.pl/notbeautifulanymore/http://koprowskit.eu/licensing/http://koprowskit.eu/geek/
-
TACK
AND PLEASE RATE MY SESSION:
http://speakerscore.com/8VWT
53