Transcript Optimizing SQL Server 2012 for SharePoint 2013

www.microtechpoint.com
Brian’s Blog
[email protected]
1
Modification is sent by
application to SQL Server
Modification is recorded
in transaction log on disk
3
Buffer
Cache
2
Data pages are located in,
or read into the buffer cache
and then modified
4
Later, checkpoint writes
dirty pages to database
Recovery Model
Description
Simple
Does NOT permit transaction log (t-log)
backups. Automatically truncates log to
reduce space requirements
Full
Requires LOG BACKUPS to manage t-log
space requirements. Avoids data loss if
damaged or missing database file occurs.
Permits point-in-time recovery.
Bulk Logged
Requires log backups to manage t-log
space requirements. Improves
performance during bulk copy operations.
Reduces t-log space usage by using
minimal logging of operations.
Tempdb
Model
Web Application
200GB
200GB
200GB
200GB
250
250
250
250
Site Collections
Site Collections
Site Collections
Site Collections
Project
Sites
Department
Sites
HR
Sites
Marketing
Sites
750mb X 250 = 187,500mb / 1024 = 183gb
1
Modification is sent by
application to SQL Server
Modification is recorded
3 in transaction log on disk
Buffer
Cache
2
Data pages are located in,
or read into, the buffer cache
and then modified
4
Later, checkpoint writes
dirty pages to database
and FLUSHES transactions
from T-log.
1
Modification is sent by
application to SQL Server
Modification is recorded
3 in transaction log on disk
Buffer
Cache
2
Data pages are located in,
or read into, the buffer cache
and then modified
4
Later, checkpoint writes
dirty pages to database
but RETAINS transactions
in T-log.
Monday
Sunday
mdf
ldf
Full Backup
(2)
mdf
Wednesday
Tuesday
ldf
mdf
Differential
ldf
Differential
mdf
ldf
Differential
(3)
BACKUP LOG DB_Name TO D:\SQLBackups\Weekly_T_Log.Bak WITH INIT
You Lose mdf file of database on Thursday at 4:00pm
(1) BACKUP LOG DB_Name TO D:\SQLBackups\TempBackup.Bak WITH NORECOVERY
(4) RESTORE LOG FROM D:\SQLBackups\TempBackup.Bak
www.microtechpoint.com
Brian’s Blog
Speaking Events
[email protected]
Sponsored by