Transcript www.cbtnuggets.com [email protected] www.microtechpoint.com SharePoint and SQL Server integration SQL Server Configurations Demo: SQL Server Optimizing Configurations SQL Server 2014 for SharePoint 2013 Avoiding ginormous transaction logs SharePoint and SQL Server best practices.

www.cbtnuggets.com
[email protected]
www.microtechpoint.com
SharePoint and SQL
Server integration
SQL Server
Configurations
Demo: SQL Server
Optimizing
Configurations
SQL Server 2014 for
SharePoint 2013
Avoiding ginormous
transaction logs
SharePoint and SQL
Server best practices
Master
Msdb
Tempdb
Model
Configuration database of SQL Server instance
Storage of SQL Server automation configuration information
Temporary work storage area
Template used to create all new databases
Web Application
200GB
200GB
200GB
200GB
250
250
250
250
Site Collections
Site Collections
Site Collections
Project
Sites
Department
Sites
HR
Sites
Site Collections
Marketing
Sites
750mb X 250 = 187,500mb / 1024 = 183gb
Default file locations (Move off C:\ Drive)
Minimum and maximum Memory settings
Max Degree of Parallelism (MAXDOP) set to 1
Create SQL Server alias for SharePoint SQL instance
(Not used by FAST search SKU (SharePoint 2010, use DNS
alias)
SharePoint collation – Latin1_General_CI_AS_KS_WS
During installation of SQL Server hosting SharePoint content
During creation of content database in SQL Server
Model database file settings
Increase initial size of data and log files
Increase Autogrowth settings (Use MB not %)
Tempdb database file settings
Increase initial size of data and log files
Increase Autogrowth settings (Use MB not %)
Use Simple Recovery model
Place files on different drive from content databases
Steps for AlwaysOn Setup
Create
SharePoint
farm
Prepare
databases for
AlwaysOn
Prepare SQL
Server cluster
Use created
AlwaysOn
cluster with
SharePoint
Prepare
SharePoint 2013
with SP1
Be sure set to Full
Recovery model
Create new
AlwaysOn
Availability Group
Update SQL alias
on each server to
use listener
Create listener
Test SQL Server
failover with
SharePoint
Create SQL alias
and point to SQL
node
Create new farm
with DB’s & SA’s
using SQL alias
Backup all
SharePoint
databases
Performance
Standard edition supports up to 128GB of RAM opposed to
64 in SQL Server 2012
AlwaysOn Availability Groups Enhancements
Supports 8 secondary replicas as opposed to 4
Read-only secondary’s still available if lose primary or quorum
Azure support for hosting availability group data files, entire
replica, and backing up to a URL to store in Azure Blob Storage
Security
Support for native backup encryption
SQL Server 2014 with SharePoint 2013, must have May CU
Access Services 2013 not supported on SQL Server 2014 CTP
New deployments should specify Availability Group Listener
name that will host the CA and configuration DB’s
AppFabric 1.1 installation
https://www.microsoft.com/en-us/server-cloud/products/sqlserver/Comparison.aspx
SharePoint 2016 will require SQL Server 2014
SQL Server Transaction Log Process
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
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
Dedicated SQL Server 2014 instance / server
No spousal installations of SQL Server or SharePoint
Database size should not exceed 200GB
Max Degree of Parallelism (MAXDOP) set to 1
Create SQL alias for SQL Server SharePoint instance
Modify Model and Tempdb system database settings
Don’t enable Auto_Create or Auto_Update Statistics
Avoid auto-shrinking databases
Use database Autogrowth sparingly
Reduces fragmentation
Improves data entry performance
Spread data files and transaction log files across
multiple drives or place on RAID 5/10
Create multiple Tempdb files (.ndf ) on multiple
drives or RAID 10 for drives hosting Tempdb files
Generate Database Maintenance Plans
Defragment drives containing content database files
Don’t rebuild or manage indexes of SharePoint DB’s
Perform regular backups of database and t-logs
Monitor SQL Server storage and performance
Perform DBCC CHECKDB operations regularly
Say NO to Simple Recovery model
SharePoint and SQL
Server integration
SQL Server
Configurations
Demo: SQL Server
Optimizing
Configurations
SQL Server 2014 for
SharePoint 2013
Avoiding ginormous
transaction logs
SharePoint and SQL
Server best practices
Slides below this are the Microsoft template slides
SQL Server instance configurations
Database configurations
SQL Server instance configurations
Database configurations
[email protected]
Mediaacq
Microsoft Brand Tools
Presentation guidelines
Microsoft photography
Microsoft illustrations
www.superstock.com/
www.gettyimages.com/creativeimages/
royaltyfree
www.corbisimages.com/stockphoto/royalty-free
http://lcaweb/CTP/Copyrights/Third-Party-Content-Use/Pages/default.aspx




 www.microsoft.com
https://brandtools.microsoft.com/Resources/Present
ations/Pages/StoryBoard.aspx?section=Elements1