Microsoft has recently revealed the CTP (Community Technology Preview) version of SQL Server 2014 at TechEd North America 2013 although we know there are a lot of companies having not fully adopted SQL Server 2012. Microsoft is striving to make the next edition of its SQL Server product to be the best data platform that provides scalability to enterprise-wide business applications. In addition, AlwaysOn, as introduced, is significantly improved to meet the need of high-availability. Microsoft is putting its effort on its cloud to deliver to enterprise the powerful hybrid cloud platform that enables to build and manage database solution very quickly and easily. Go read more information about SQL Server 2014 CTP Product Guide here
- Setting up your SharePoint 2013 environment At Work – Part 1
- Setting up your SharePoint 2013 environment At Work – Part 2
- Setting up your SharePoint 2013 environment At Work – Part 3
- Setting up your SharePoint 2013 environment At Work – Part 4 (you are here)
- Setting up your SharePoint 2013 environment At Work – Part 5
- Setting up your SharePoint 2013 environment At Work – Part 6
In the previous article, I covered some benefits when deploying Active Directory in SharePoint 2013 environment and enhanced features for Active Directory virtualization that helps it to be virtualized on Hyper-V much safer. In this article, I continue setting up the environment with the steps of pragmatic SQL Server 2012 installation. You will learn some key settings during the installation to make your SharePoint databases to be effectively controlled and managed. This also helps a little bit when you accidentally have to wear a DBA hat.
SQL Server 2012 for SharePoint 2013 deployment
SQL Server 2012 provides tons of beneficial things for SharePoint 2013 and supports for various deployments with many high-level demands spreading from high performance, need of high-availability to complex compliances (e.g. PCI DSS, HIPAA). For a real example, I’ve completely done the hardening on a SharePoint environment for an IT company that is hosting SharePoint application for a big company. The company asked many questions based PCI DSS (Payment Card Industry Data Security Standards) such as database encryption rule, database audit on the SharePoint farm. Fortunately, SQL Server 2012 does support the database encryption with Transparent Database Encryption (TDE) feature so the environment was configured properly to meet that rule. A little more of the TDE feature, let’s say if your SharePoint content databases are not encrypted, someone who just needs to have Read permission can copy them and attach to another server in which he is fully controlling. All sensitive documents stored in the databases could be gained easily. Using TDE, the content databases are securely encrypted, if the attacker wants to encrypt it, he must have the decryption key that is stored in the master system database.
I remember Michael Noel – an international recognized SharePoint expert – has delivered the topic of SQL Server TDE for SharePoint 2010 to hundreds of SharePoint events and conferences over the globe. Here you go to see his slide-deck on such a topic.
When you decide to build a publishing portal or B2C e-commerce website built on top of the SharePoint 2013 platform, high-availability data management and disaster recovery of data have to be probably taken into account. Imagine if SharePoint that is functioning as critical business applications especially an interactive portal between your customers and employees in your company is unavailable for a whole working day, what will the cost of downtime be? If every hour your business applications predictably earn 1,000 $ so your company could lose 24,000$ due to the unavailability of SharePoint. In this case, you need to have high-availability solution to minimize database server failure, server crashes or something like that. But you still need disaster recovery in your plan. Note that high availability isn’t disaster recovery. Your SharePoint farm can be highly available to function, but it may have no ability to recover data from a disaster. In the edition of SQL Server 2008, fundamentally you have 3 different things for high availability: Failover Clustering, Log Shipping, and Database Mirroring. Each solution has its own pros and cons but it seems not to make system administration feel secure as they have to plan for disaster recovery. With the enhancement of high-availability feature in SQL Server 2012, companies really don’t have to be much worried about their system.
(Microsoft Press – Introducing SQL Server 2012 (figure 2-3)
The phenomenal combination of high availability and disaster recovery that is called AlwaysOn, bring the new evolution of business continuity technology to the community. Microsoft designs to provide you two options in AlwaysOn feature: AlwaysOn Failover Cluster Instance and AlwaysOn Availability Group. Going with the first option, AlwaysOn allows you to set two different SQL Server instances in a group called AlwaysOn Availability Group and these don’t need to be in the same shared storage. That said now you can deploy across different geographical location. However, one of the disadvantages is that these instances still must be in the same domain to be run under Windows Failover Clustering service. Look into the mechanism of AlwaysOn, it looks similar to Database Mirroring but much more greatly improved and removes some limitation. Database mirroring although is still available in SQL Server 2012 but Microsoft says it has been deprecated. There are many other benefits from AlwaysOn in SQL Server 2012 you need to learn in order to make your SharePoint 2013 farm highly available and plan for disaster recovery. Below are some good articles and resources to learn more:
- SQL Server 2012 AlwaysOn Availability Groups Rock
- Overview of AlwaysOn Availability Groups (SQL Server)
- AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution
The last thing I want to cover in terms of SQL Server 2012 for SharePoint is a set of new features of business intelligence solution. Previously, SharePoint can be used with SQL Server 2008 reporting service and Business Intelligence tools, such as PowerPivot, PerformancePoint Dashboard Designer. These things are still available in SQL Server 2012, but much improved to meet business needs in the “big data” era that is loudly discussed among IT forums. Reporting service now is part of SharePoint integration modes you don’t have to install it on a separate server. The capability of centralization of Reporting service helps administrator much easier in managing multiple servers.
With new enhancements in the SQL Server 2012 such as Power View, Reporting Services add-in for SharePoint, you can collect massive amounts of information and present the information in a visual dashboard. As a result, business stakeholders in your company can make effective business decisions.
The following great references about SharePoint 2013 Business intelligence you should read:
- Scenario: Business intelligence for SharePoint 2013 IT pros
- What’s new in business intelligence in SharePoint Server 2013
- Building a SharePoint 2013 BI Demo Environment (series)
- Business Intelligence in Microsoft SharePoint 2013 (book)
- Creating Business Intelligence Solutions in SharePoint 2013 (video)
Select the right SQL Server 2012 edition
Before installing SQL Server 2012 for your SharePoint 2013 deployment, you need to select the right edition of SQL Server 2012. Even you can upgrade to higher edition but this would take your time and cost more than the initial plan. There are three major editions of SQL Server 2012: Enterprise, Business Intelligence and Standard; and the features available in each edition vary.
SQL Server 2012 Enterprise is the highest edition that has full capabilities, including Business Intelligence functionality. This edition provides the best performance, scalability and availability for enterprise-wide application.
Business Intelligence edition is very new in the licensing model of SQL Server 2012 that never existed before. This edition provides a full set of Business Intelligence tools including PowerPivot and PowerView.
The typical edition named Standard offers basic data management and availability. Of course these features in this edition are not available in the Enterprise edition. Microsoft, however, has included basic Business Intelligence functionality in the Standard edition.
Through datasheet provided by Microsoft, SQL Server 2012 licensing model has changed. It is now offering two licensing options: one based on computing power and one based on users or devices.
*Requires CALs (Client Access License), which are sold separately
**Pricing is for demonstrative uses only
Microsoft provides a detailed guide of SQL Server 2012 licensing mode here.
To select the right edition at the beginning, you need to map the SharePoint requirement to the SQL Server 2012 features. For example, the Enterprise edition supports unlimited number of AlwaysOn Failover Cluster instances while the others just support up to 2 nodes. Another example is the need of security if your SharePoint 2013 needs to meet compliance like PIC DSS. That said, if you need to encrypt SharePoint databases by Transparent Database Encryption feature so you must go with the Enterprise edition because only it supports such a capability. In a nutshell, if there is something that must to be deployed using Enterprise edition, you don’t need to look at another because Enterprise contains all features.
Below is the sample table that helps you not only in selecting right SQL Server editions but also knowing which features of SQL Server 2012 support SharePoint functional and non-functional requirements relatively.
As you can see in the example, the edition you must go with is Business Intelligence.
SQL Server 2012 Installation
Before SQL Server 2012 installation, planning for service account should be taken into consideration. The number of service accounts depends on service you want it to run in the SQL Server. Initially, you need to have an account for one of the core services – Database Engine. There are other accounts you should consider to create such as Reporting Services or Analysis Service in case you need it to be run to function for your Business Intelligence, for instance.
Before you install SQL Server 2012, make sure you install all prerequisites. Refer to this article. Click Setup.exe in the installation folder. In SQL Server Installation Center windows, select Installation > New SQL Server stand-alone installation or add features to an existing installation. On the Setup Support Rules page, the system will check to identify if there is any problem occuring when you install SQL Server Setup support files.
On the Product Key page, select Enter the product key and type you license key issued by Microsoft. You can select Evaluation or Express under Specify a free edition. With the selection of Evaluation option, you can use all features in the Enterprise edition within 180-trial day.The SQL Server 2012 Express edition offers you maximum 10 GB of storage per database for testing purposes.
On the License Terms page, read Microsoft SQL Server 2012 license terms carefully and select I accept the license terms. Select Send feature usage data to Microsoft. Feature usage data includes information about your hardware configuration and how you use SQL Server and its components. This helps Microsoft evaluates your hardware as well as how you use its product.
On the Product Updates page, with the new capability, SQL Server 2012 automatically helps you check and then it installs the latest updates to enhance security and performance. Your machine must be connecting to the Internet unless you will get the error in terms of Internet or network access.
On the Install Setup Files page, wait for the system until the product updates are completely installed. SQL Server requires you to restart the installation windows. On the Setup Support Rules page, the system identifies some rules to make sure all is passed. For example, the server you use to install SQL Server 2012 should have to be joined to domain controller. You would see the Warning message of the Windows Firewall rule because your Windows Firewall is enabled so SQL Server warns you to review configuration on the firewall to make sure SQL Server ports are not blocked.
On the Setup Role page, select SQL Server Feature Installation to keep all components to be installed by default. If BI in your plan, select SQL Server PowerPivot for SharePoint. You can install it later as well.
On the Feature Selection page, select features you need to install. You don’t necessarily have to install all of the features because this will take high resources for unnecessary services. Ideally, the following are services needed for SharePoint deployment in your case but it depends on some requirements for additional features e.g. Reporting Services. You can install additional features later as well.
- SQL Server Replication
- Full-Text and Semantic Extractions for Search
- SQL Server Data Tools
- Client Tools Connectivity
- Integration Services
- Management Tools – Basic
On the Installation Rules page, you will encounter the warning message of Microsoft .NET Framework 3.5 feature if it is not installed in your machine. If you are using Windows Server 2012, you need to add .NET Framework 3.5 Features and make sure the path to store it is specified different from the source of .NET Framework 4.5. Use the following PowerShell to add:
$path = "H:sourcessxs" Add-WindowsFeature NET-Framework-Core -Source $path
Note: The DVD source of Windows Server 2012 has to be mapped to the machine. In my case, H is the name of the DVD (yours may be D by default) volume and the folder sourcessxs has many components and packages. One of them is .NET Framework 3.5
On the Instance Configuration page, we highly recommend you to use named instance because of the following reasons:
- The default instance (MSSQLSERVER) would be easily attacked through the Internet by brute-force technique because the attacker already knows such a default name. Having a named instance avoids such an attack.
- Named instanced allows you to easily create an SQL Alias to connect to SharePoint. This facilitates moving SharePoint databases across database servers.
On the Disk Space Requirements page, check drive where the directory is located to make sure you have enough space before installing SQL Server 2012.
In Server Configuration page, change account that is used to run the two services: SQL Server Agent and SQL Server Database Engine. This account must have at least Read permission on the backup folder using SharePoint backup option in Central Administration.
In the Collation tab, keep the setting by default for use of Latin characters. If you use specific language such as Danish. You need to change the collation designator to Danish_Norwegian for example, but still keep Case-sensitive (CS) and Accent-sensitive (AS). More details can be founded here and supportability.
On the Database Engine Configuration page, select Windows authentication mode. Under Specify SQL Server administrators, click Add and then type your database administrator (DBA) account you want.
In the Data Directories tab and select path used to store databases and log files. I highly recommend you to use a separate drive to store data and log files. In addition to store, Data and log file should be stored in different folder as a recommended best practice. Note that SQL Server 2012 now supports you to store tempDb to another drive.
You might need to look at the FILESTREAM tab. It was introduced at the first time in SQL Server 2008, allowing you to store binary large objects in the file system. If your plan is to store large files, such as Video, architecture document in SharePoint, you need to look into FILESTREAM with the capability of RBS (Remote Blob Storage). You can enable it later as well.
On the Error Reporting page, select Send Windows and SQL Server Error Reports to Microsoft or your corporate report server. This setting only applies to services that run without user interaction if you want to help Microsoft in SQL Server product improvement.
On the Installation Configuration Rules page, make sure all rules are passed before the system starts installing SQL Server 2012.
Finally, check all configuration again on the Ready to Install page. Now you need to create SQL Server Alias using SQL Server Client Network Utility. You may need to read this great article Plan Your SharePoint Farm Right with a SQL Server Alias
In this article, you have learned several things about the benefits of SQL Server 2012 when used in your SharePoint 2013 deployment. You are also done the SQL Server 2012 installation with some recommendations. Certainly not stopping at this point, you need to learn many further things when you are responsible for managing SharePoint databases. The free course Turning SQL Server 2012 for SharePoint conducted by Bill Baer and Brian Alderman is the great start.
The next article is SharePoint 2013 installation but it’s not the last in the series. We will cover some must-notice points during SharePoint 2013 installation. Unlike most of the SharePoint installation articles, I will not tell you the plan of ALL SharePoint service accounts required for SharePoint deployment as it makes people a bit confused. In the other hand, I’m not really a planner or at least not all things I plan for. If you are fan of ancient Chinese book on military strategy like me, you probably know one of the best famous books named Romance of The Three Kingdoms. There are many games of wits between Zhūge Liàng and Sīmǎ Yì. I’m been on the side of Sīmǎ Yì who always plays it by ear in every battle rather than Zhūge Liàng who always plan for things.