SharePoint 2013 & SQL Server 2012

1. Alexander Meijers SharePoint 2013 & SQL Server 2012 A talk about what SQL Server version you want to use from a SharePoint 2013 perspective

3. Introduction • Name: Alexander Meijers • Workplace: Portiva • Role: SharePoint Lead Architect / Project Lead • Experience: 10+ Years SharePoint (2003/2007/2010/2013) • SharePoint Enthusiast • Projects: Internet facing websites, Intranet design, Architecture, Migration, Advisory on infrastructure and development.

4. Our talk of today… A talk about what SQL Server version you want to use from a SharePoint 2013 perspective

5. What version of SQL Server? Your customer wants to have SharePoint 2013. So what version of SQL Server you are gonna suggest?

6. What version of SQL Server? • Obviously you want to go for SQL Server 2012. • Latest released technology • In your mind the best there is

7. What version of SQL Server? • But is it? • Your customer wants to know why • Why not just use SQL Server 2008 R2?

8. What version of SQL Server? • You need to convince your customer. • Explain the benefits • Look at the future • Take into account the customer needs

9. Agenda The agenda of the presentation

10. Agenda • The choices you have • Dependencies to make the right choice Advantages using SQL Server 2012: • Software • Infrastructure • Functional

11. The choices you have What are the requirements for SharePoint 2013?

12. The choices you have Minimum database requirements for SharePoint 2013 So what do you choose?

13. Dependencies What has influence on the choice between the two SQL Server versions?

14. Influencers • Roles which could influence the choice between the SQL Server versions • Decisions are based on requirements, infrastructure and used solutions

15. Levels of dependencies • Infrastructure • Functional Requirements • Knowledge level • Costs

16. Infrastructure • Are there other solutions inside the organization depending on SQL Server • Shared SQL Server platform • There could be a shared platform based on SQL Server 2008 • Do we comply to the governance rules set by the IT department? • Backup & Recovery • Updates & Service Packs • Multiple versions of SQL Server • Is it possible to grow at our current infrastructure location? • On premise / Virtual • Cloud • Hosting Service • Monitoring tools (SCOM, etc.) should support SQL Server 2012

17. Functional requirements • Do some requirements need SharePoint 2013 Enterprise functionality? • Excel Services • Performance Point Services • Reporting Server • Access Services • Performance requirements • Blob storage • Scalability • International organization with multiple farms at different geographic locations • Future • To prevent SQL Server version upgrades from 2008 to 2012 • Depending on functionality it is possible that developers use technology depending on the SQL Server version

18. Knowledge level • What is the current level of knowledge at the IT Department? • Is additional training required and possible? • Does the hosting service support the new technology? • Time • Are employees available for training?

19. Costs • Licenses • Differences in CALs depending on SQL Server version • Additional hardware due to higher specifications • Training • Change in Service Level Agreement and contracts • Possible when infrastructure is hosted at a hosting service • More FTE needed at IT Department • Temporarily two versions of SQL Server when doing a migration to SharePoint 2013 • More servers due to an extra farm because other solutions use other SQL Server versions

20. And then? • All the things we just discussed have to be considered when making a decision • But to convince the business and the IT Department and to get your story right, you will need to know what the advantages are of using SQL Server 2012 at different levels • Software • Infrastructure • Functional

21. Software level The advantages of SQL Server 2012 at software level

22. Advantages - Software • SQL Versions • SQL Server 2008 R2 SP1 will only have standard support (at October 2013) • SQL Server 2012 is released for over a year now and is seen as “proven” technology • Licensing • SQL Server 2008 R2 is Server+CAL or Processor based • SQL Server 2012 is Server+CAL or Core-Based • Much faster due to xVelocity • Primarly in-memory processing • Enabled when you choose “Tabular mode”, a new type of analysis services database structure • Replacement of “VertiPaq” in SQL Server 2008 called “xVelocity in-memory analytics engine”

23. Advantages – Mixed Licenses • SharePoint 2013 allows you to use partly enterprise license functionality. • For example; one division of 20 people inside an organization of 1000 people uses the BI functionality. • Only 20 Enterprise CAL’s are needed, the other 990 CAL’s are standard • This increases the chance that enterprise functionality is going to be used which needs SQL Server 2012

24. Infrastructure level The advantages of SQL Server 2012 at infrastructure level

25. Advantages – (High) Availability

26. AlwaysOn AlwaysOn provides a high-availability and disaster-recovery solution for SQL Server 2012. • Designed to support “High Availability” • Uses WSFC to perform failovers • Failover takes place automatic, manual or forced • Creates up to 4 readable secondary replicas • Built-in compression & encryption • Introduction of Availability Groups • Part of SQL Server 2012 Enterprise Edition

27. AlwaysOn Availability Groups AlwaysOn Availability Groups allow you to configure groups of databases you would like to failover all together when there is a problem with the host server • It replaces database mirroring • Has more features due to AlwaysOn • Allows failover of multiple databases which are related to each other

28. AlwaysOn Availability Groups Flexible Integrated Efficient • Active Secondary • Readable Secondary • Backup from Secondary • Improves primary server performance by offloading work to secondary • Monitoring and Troubleshooting enhanced • Automation using PowerShell • Application failover using virtual name for faster failover • Configuration Wizard • Dashboard • System Center Integration • Rich diagnostic infrastructure • File-stream replication • Replication publisher failover • Multi-database failover • Multiple secondary's • Total of 4 secondary's • 2 synchronous secondary's • Synchronous and asynchronous data movement when high latency is required • Built in compression and encryption • Automatic page repair for corrupt pages • Automatic and manual failover (new design) • Flexible failover policy

29. AlwaysOn SQL Failover Cluster Instances Is intended to replace the SQL Server clustering technology • It uses WSFC to provide high availability • Automatic failover when application, operating system or hardware fails • Minimizes downtime during a failure • Allows across multiple subnets

30. “High Availability” in comparison (1) An AlwaysOn Availability Group can have no more than a total of four secondary replicas, regardless of type. (2) This feature will be removed in a future version of Microsoft SQL Server. Use AlwaysOn Availability Groups instead. (3) Backup, Copy, Restore is appropriate for disaster recovery, but not for high availability. (4) Automatic failover of an availability group is not supported to or from a failover cluster instance. (5) The FCI itself doesn’t provide data protection; data loss is dependent upon the storage system implementation. (6) Highly dependent upon the workload, data volume, and failover procedures. Source: Whitepaper “Microsoft SQL Server AlwaysOn Solutions Guidefor High Availability and Disaster Recovery”

31. Advantages – Cross-Cluster Migration • Moves one availability group or a batch of availability groups to a new, destination Windows Server Failover Clustering (WSFC) cluster with minimal downtime • Only available with SQL Server 2012 SP 1 and AlwaysOn enabled

32. Advantages – Windows Azure Blob Storage Windows Azure Blob storage is a service for storing large amounts of unstructured data that can be accessed from anywhere in the world via HTTP or HTTPS. Use for example as a backup strategy for SharePoint databases. • SQL Server Backup / Restore with WABS service • Better alternative for tapes • backup file which is stored through WABS service is directly available to either an on-premises SQL Server or another SQL Server running in a Windows Azure Virtual Machine • Without the need for database attach/detach • Without downloading and attaching the VHD

33. Advantages – Windows Azure Blob Storage When backup file is stored through WABS service • Cost Benefits because you use an off-site backup archive option. • You only pay for the service that is used • When off-site there is no overhead of hardware management

34. Advantages – BLOB storage Filestream data type allows you to store unstructured data such as documents and images on a file system. • Storage of BLOBS • Copying files to disk • SQL Server 2008 was insecure due to storage in files which allows people with the right permissions to open them • With SQL Server 2012 BLOBs are stored with the SQL Server shell and are only available through SQL Security • Dramatic increase of speed • Document insertion • Document retrieval

35. Functional level The advantages of SQL Server 2012 at functional level

36. Advantages – Report Server • Use of Report Server and Reporting Services Add-in with SharePoint 2013 • Direct Access from within SharePoint 2013 • Running 30-60% faster • Alert users when data in a report has changed • Single dashboard for data in SharePoint • SQL, Excel, PerformancePoint, Report Services all together • Only available in combination with SQL Server 2012 SP 1

37. Advantages – Power Pivot Allows users to manipulate large data sets, integration of data and possible analysis through SharePoint 2013 • New Excel 2013 features like • Filter data when importing or just import a subset • Advanced data models which allows you to enhance the data model, create relationships and use drag and drop • Define you own calculate fields (workbook) • Define KPI’s • Only available in combination with SQL Server 2012 SP 1 CPT3 Analysis Services

38. Advantages – Power Pivot Demo

39. Advantages – Power View Power View enables quick and easy visualization of your data while allowing sharing and collaboration of business insights in a familiar SharePoint Server environment • Based on Silverlight to enhance report appearances • Cross-reference related data • Only available in combination with SQL Server 2012

40. Advantages – Power View Demo

41. Wrap up

42. Wrap up • Take into account the dependencies like governance, current infrastructure, knowledge and costs • Faster, more functionality and “proven” technology • High Availability due to AlwaysOn technology • Tighter integration with SharePoint 2013 with Power Pivot and Power View • At a functional level for future purposes it is better to have SQL Server 2012. (use mixed licenses)