Transcript Powerpoint

High Availability
Partial Database Availability
28.11.2015
28.11.2015
We would like to thank our Sponsors!
28.11.2015
About me
 10+ years experience with SQL Server
 Consultant
 telecommunication
 banking
 transportation
 Trainer
 Contact
 email: [email protected]
 Twitter: @JanosBerke
28.11.2015
Agenda











What does RPO/RTO mean?
What HA/BC options are there?
What is a database?
What is a database file?
What is a filegroup?
Bringing them together – Database, Filegroup, File
Demo: creating a database with multiple files and filegroups
Restore scenarios
Disaster happens 
Demo: Online piecemeal restore
Additional materials/readings
28.11.2015
What does RPO/RTO mean?
 RPO: Recovery Point Objective
 What a customer wants is 0
 What a DBA wants is >0 
 Plan backup strategy
 RTO: Recovery Time Objective
 Define/document restore steps
 Define/document case scenarios + script them!
 Practice, practice, practice!
28.11.2015
What HA/BC options are there?
There are many different options in SQL Server like:
 Log Shipping
 Database Mirroring (Deprecated )
 Failover Cluster Instance (FCI)
 Availability Groups (2012 or later version)
 Partial Database Availability
28.11.2015
What is a database?
•
•
•
•
•
•
•
Containers
Boundaries
Tables
Views
Procedures
Functions
Users
Roles
Schemas
• Security
Accounts
• Permissions
• Default
collation
28.11.2015
Backed by Files
• Data files
• Log files
• Organized in
filegroups
What is a database file?
 Every database has:
 a primary data file: it contains the startup information
for the database and points to the other files in the
database. Recommended extension is mdf.
 optionally one or more secondary data file: it contains
user-defind data. Recommended extension is ndf.
 a transaction log file: it holds the log information that
is used to recover the database. There must be at least
one log file for each database. Recommended
extension is ldf.
28.11.2015
What is a filegroup?
 Filegroup is a logical container for data files:
group data files together for administrative, data
allocation, and placement purposes.
 Every database has:
 a PRIMARY filegroup: contains the primary file. All
system tables are allocated to the primary filegroup.
 optionally additional user-defined filegroup(s)
28.11.2015
Bringing them together – Database, Filegroup,
File
File1.mdf
File3.ndf
FileN.ndf
File2.ndf
File4.ndf
FileM.ndf
FileX.ndf
FileY.ndf
FileZ.ndf
PRIMARY
FG1
FGn
Log1.ldf
logN.ldf
28.11.2015
Transaction log
Demo device
 Original idea from Kimberly L.Tripp’s SQL MCM
Readiness videos
 It is *NOT* production ready!!! 
28.11.2015
Demo
 Creating a database with multiple files and
filegroups
28.11.2015
MultiFG Database
MultiFG_Data – C:\
PRIMARY
FG1_Data1 – E:\
FG2_Data1 – G:\
FG1_Data2 – F:\
FG2_Data2 – H:\
FG1
FG2
MultiFG_Log – C:\
Transaction log
28.11.2015
Restore scenarios
 Piecemeal restore can be:
 offline
 online
 Depends on :
 Recover models:
 full recovery model
 simple recovery model
 SQL Server edtion:
 Enterprise
 not Enterprise (Standard, Web, Express)
28.11.2015
Disaster happens 
MultiFG_Data – C:\
PRIMARY
FG1_Data1 – E:\
FG2_Data1 – G:\
FG1_Data2 – F:\
FG2_Data2 – H:\
FG1
FG2
MultiFG_Log – C:\
Transaction log
28.11.2015
Demo
 online partial restore with Enterprise Editon + full
recovery model
28.11.2015
Additional materials/readings
 SQL Server 2008 Microsoft Certified Master
(MCM) Readiness Videos – Partial Database
Availability & Online Piecemeal Restore:
http://technet.microsoft.com/enUS/sqlserver/gg545009.aspx
 Piecemeal Restores:
http://msdn.microsoft.com/enus/library/ms177425.aspx
 Piecemeal Restore of Databases With MemoryOptimized Tables: http://msdn.microsoft.com/enus/library/dn387567.aspx
28.11.2015
We would like to thank our Sponsors!
28.11.2015