SQL Server 2012 Installation Recommendations
SQL Server 2012 is relatively easy to install, and the installation guide from SAP is very clear. However, there are a few guidelines from my own experience that may be of benefit to others performing this installation for the first time. The following example assumes an installation on Windows Server 2012 R2.
SQL Server 2012
- SQL Server 2012
- Obtaining Installation Guides, Notes, and Software
- Preliminary Steps
- Additional Resources
Obtaining Installation Guides, Notes, and Software
Critical Guides and Notes
The main installation guide is Upgrade to and Installation of SQL Server 2012 in an SAP Environment. You can find this guide online at https://help.sap.com/viewer/upgrade_sql2012.
The two most important SAP Notes (logon required) are:
A list of other useful Notes, documents, and websites for reference appears at the end of this blog. Many of the performance and configuration Notes, such as for setting traceflags, are covered in Note 1702408. Others refer to settings specific to tables or ABAP databases and thus are not required for the initial installation.
If you licensed SQL Server through SAP, then you can download the software from the Service Marketplace (login required) at http://support.sap.com/software/databases.html -> MS SQL Server -> Database -> MS SQL SERVER X86_64 -> MS SQL SERVER 2012/X86_64 -> Installation -> Microsoft SQL Server. Download all 8 files that collectively make up DVD material number 51047515. Run the self-extracting executable that makes up the first file, and it will automatically combine with the other 7 files to create a folder structure mirroring an installation DVD.
Service Pack and Cumulative Update
SAP’s distribution comes with Service Pack 1 Cumulative Update 7. However, Microsoft has released both a new Service Pack and new Cumulative Updates for both Service Packs 1 and 2. Unfortunately, at this time Microsoft’s support website for downloads does not make it easy to find the latest SP and CU, nor even to know which are the latest. Therefore I recommend checking the Microsoft SQL Server Version List blog found at http://sqlserverbuilds.blogspot.com. Scroll down to the SQL Server 2012 section and identify the most recent Service Pack and the most recent Cumulative Update for that Service Pack. At this time, that is SP2 and CU4. The link to the Service Pack will take you directly to a Microsoft download page. The link to the Cumulative Update will take you to a Microsoft page describing the CU. From there, you must supply your email address, and a few minutes later you will receive an automated email with a link to a temporary download location. Unzip both archives.
I recommend using a domain account as the service account for SQL Server (the installation guide recommends using Local System). This will be of use when setting configuration parameters such as using locked pages in memory. Create (or request) your service account now so that it is ready during the setup. The account should be configured according your organization’s policies for service (non-interactive) accounts, i.e. the password should never expire and the account should not have a logon script associated with it. It does not require any special domain privileges beyond this.
.NET Framework 3.5
The SQL Server Setup program requires .NET Framework 3.5 SP1. This feature is not typically installed by default with Windows 2012 R2, and it is not included with SQL Server. It is available, however, on the Windows 2012 installation media. Assuming the media is available to you, you can install or enable it by launching Server Manager (by default, this tool launches upon logon to the server console, which I personally find annoying and usually turn off), then selecting Manage -> Add Roles and Features.
On the page Before you begin click Next.
On the page Select installation type choose Role-based or feature-based installation and click Next.
On the page Select destination server select your server and click Next.
On the page Server Roles make no changes and click Next.
On the page Features select .NET Framework 3.5 Features and its subcomponent .NET Framework 3.5 (includes .NET 2.0 and 3.0).
If the feature is already enabled (the checkboxes are already selected), then click Cancel. Otherwise, click Next and follow the prompts to complete the installation.
Apply the latest after enabling .NET Framework 3.5 but before proceeding further. A reboot may be required.
This is a good time to set your desired pagefile size for your SAP installation. The size chosen will depend on many factors and is beyond the scope of this blog, but as a rule of thumb it should be a minimum of 20 GB. If your server has a mix of local and SAN storage, the general recommendation is to locate the pagefile on a local disk (which will likely be your C: drive).
Open Control Panel and select System and Security -> System -> Advanced system settings -> Performance: Settings -> Advanced -> Virtual memory: Change. Deselect Automatically manage paging file size for all drives. Choose the appropriate drive and set Custom size with Initial size and Maximum size at the same value. Click Set then OK. (Reboot usually required).
Windows Explorer Preferences
This is optional, but to avoid annoyance I prefer to change a couple of these settings. In Windows Explorer select View -> Options -> Change folder and search options. On the General tab select Automatically expand to current folder. On the View tab deselect Hide extensions for known file types. This will make your life easier.
Drive and Folder Structure
My recommendation is to have a separate drive or volume for:
- Operating System / Pagefile
- SQL Server
- SAP (/usr/sap)
- Transaction Log
- Database (spread across 4, 8, or 16 drives, depending on number of logical processors)
On the Backup drive, create a Backup folder, and on the TempDB drive, create a TempDB folder.
Set Up Source Files
Copy the SQL Server installation media you downloaded from SAP to a temporary location on your server with at least 7 or 8 GB of free space (e.g. C:\source\SQLServer). Expand \Source\SQLServer\x86-x64\ServicePacks and create a subfolder called SPxCUy (where x and y represent the Service Pack and Cumulative Update numbers you downloaded, e.g. SP2CU4). Copy the SP and CU executables you downloaded from Microsoft into this subfolder. If there are older SP and CU folders present, you may optionally delete them.
Start Setup With Integrated SP and CU
Click the Windows Start menu icon, then the ‘down-arrow’ to get the Apps menu. From the apps, choose Run. In the Run dialog box select Browse and navigate to \source\SQLServer\x86-x64\EnterpriseEdition\setup.exe and open it. Back in the Run dialog, edit the ‘Open’ field to add the following command-line switches so that your command looks like this:
C:\source\SQLServer\x86-x64\EnterpriseEdition\setup.exe /Action=Install /UpdateSource=”C:\source\SQLServer\x86-x64\ServicePacks\SP2CU4″
This will eliminate the need to separately apply patches after the installation, as they will be included.
In general, from this point the setup will follow the installation guide as described in chapter 4.2 “Installing the SQL Server 2012 Database Server Software Manually.” I will only highlight where I deviate from the instructions given in the guide.
After the License Terms page, you will see a Product Updates page where you can confirm that your Service Pack and Cumulative Update were correctly selected.
In addition to the features listed in the installation guide, I recommend selecting Integration Services (under Shared Features). This is not required to run SAP, but if you choose to setup backups and other housekeeping jobs via Maintenance Plans in the SQL Server Management Studio, then you will need this component. If you choose to setup your backups and housekeeping via DBACOCKPIT, then this is not required.
I strongly recommend changing the default drives suggested by the setup program. By default, setup will recommend installing on your C: drive. I recommend having a dedicated drive for SQL Server, e.g. D:, and thus changing the Shared feature directory and Shared feature directory (x86) as shown below.
Clicking the ellipsis after each entry box will give you an opportunity to create these folders directly.
Again, I strongly recommend changing the Instance root directory to be the same as the Shared feature directory you just set. Otherwise, leave the selection at Default instance and do not change the Instance ID.
I do not recommend using Local System accounts as suggested in the guide. I recommend using a domain account for the SQL Server Agent and SQL Server Database Engine.
After entering the service account ID and password, select the Collation tab to customize the server collation to SQL_Latin1_General_CP850_BIN2 as described in the guide.
Database Engine Configuration
After setting the Server Configuration with Authentication Mode and specified SQL Server administrators (i.e., set BUILTIN\Administrators for this), switch to the Data Directories tab. Change the Temp DB directory to the TempDB folder you created earlier, and the Backup directory to the Backup folder you created. There is no need to change the User database or log directories, as you will be creating multiple directories across multiple drives later when you install your SAP system.
After a few more screens, the installation will begin. It will take several minutes or so, so this is a good time for coffee. Later, when you come back, we’ll talk about initial (pre-SAP installation) configuration of SQL Server (in another blog).
- 1238993: Proportional File Auto-Growth with SQL Server 2008
- 1459005: Enabling index compression for SQL Server
- 1482275: Setting Traceflags for SQL Server
- 1488135: Database compression for SQL Server
- 1558087: SQL Server Statistics Maintenance
- 1612283: Hardware Configuration Standards and Guidance
- 1648817: Disallow Page Level Locks for Microsoft SQL Server
- 1649078: Disabling autostats for certain tables with Microsoft SQL Server
- 1651862: Release planning for Microsoft SQL Server 2012
- 1676665: Setting up Microsoft SQL Server 2012
- 1744217: MSSQL: Improving the database performance
- 1725220: New Trace Flags set and recommended with SQL Server 2012
SCN Space and Documents
There are many good blogs and documents in the SAP on SQL Server space on SCN. One in particular to highlight for planning purposes is SAP with Microsoft SQL Server 2008 and SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability – Part I: SAP Architecture and SQL Server Basic Configurations, Features Used, and Windows Configurations (yes, that’s a doozy of a title, and I have yet to see Part II, unfortunately).
- Running SAP Applications on the Microsoft Platform (blog by Microsoft engineers on MSDN)
- Microsoft SQL Server Version List (independent, previously mentioned)
Please comment if you have any thoughts on any of my recommendations, as I am always open to learn new ways of doing things and to benefit from the experience of others.
Please see SQL Server Configuration: Pre-SAP Installation for my personal recommendations on what to do next after initial installation.
UPDATE 2017-11-29: Updated instructions and link to find the latest official installation and upgrade guide from SAP.