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
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:
- 1702408: Configuration Parameters for SQL Server 2012
- 1134345: Using locked pages for SQL Server
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.
Software
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.
Preliminary Steps
Service Account
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.
Windows Updates
Apply the latest after enabling .NET Framework 3.5 but before proceeding further. A reboot may be required.
Pagefile
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:
- Backup
- Operating System / Pagefile
- SQL Server
- SAP (/usr/sap)
- TempDB
- 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.
Installation
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.
Setup
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.
Product Updates
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.
Feature Selection
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.
Instance Configuration
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.
Server Configuration
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).
Additional Resources
SAP Notes
- 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 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, Maximu... (yes, that's a doozy of a title, and I have yet to see Part II, unfortunately).
External Resources
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.
UPDATE:
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.