Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

SQL Server 2012 Installation Recommendations

Matt_Fraser
Active Contributor
‎01-23-2015 2:20 AM


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 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, 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.

9 Comments
Derek_Fox
Contributor
‎01-24-2015 9:00 PM
0 Kudos

Hi Matt

Excellent post.

With regards to the section 'Drive and Folder Structure', what would an appropriate size be for the disks, an example below:

  • Operating System: 100 Gb or 200 Gb
  • Swap/Pagefile: RAM x 4
  • SQL Server: 100 Gb
  • SAP: 80 Gb or 100 Gb
  • TempDB: RAM x 1 or size of the largest table in the database
  • Transaction Log: RAM x 1
  • Database: Variable - depends on the amount of data to be stored

Regards

Derek

Matt_Fraser
Active Contributor
‎01-25-2015 5:43 PM

Hi Derek,

Thanks for the compliment!

Disk space allocations can be highly dependent upon the application being installed and how busy it will be. However, I can give some observations on your example, and also an example of my own most recently installed system.

Operating System (C:): I have been using 136 GB local drives (mirrored) for this, which gives plenty of space for the operating system and a pagefile, in most circumstances.

Swap/Pagefile: I used to separate this from the OS, but in this day and age of SAN storage, my data center folks have been advising that they prefer for pagefiles to be on local disks and not SAN disks. Generally this means putting it on the C: drive.

A note about swap/pagefile sizing: The old rule-of-thumb about making the swap file 3 or 4 times the size of physical RAM is pretty much ancient history today, unless your server still has very small RAM. These days, with Windows servers having 48 GB or 64 GB of RAM (or much more) as standard from the factory, making a swap file three times as large is just not reasonable, nor is it necessary. In fact, since the goal is to minimize paging of memory as much as possible, if you actually find a lot of this activity on your server, then you might want to consider adding more physical RAM. juergen.thomas in his excellent document SAP with Microsoft SQL Server 2008 and SQL Server 2005: Best Practices for High Availability, Maximu... states that a pagefile equal to physical RAM is sufficient (p.11), and karl-heinz.hochmuth has a very good document (Virtual Memory Required by SAP System Components) in which he gives formulas by which you can calculate your needed pagefile size (in a nutshell, he says after you figure out your total memory requirements, subtract how much physical RAM you have, and what's left is how much pagefile you need; if you have enough RAM, you may not need any pagefile at all, though for Windows systems it's mandatory to have at least some amount). When 64-bit Windows systems first started becoming popular, the recommendation was to make the pagefile a minimum of 20 GB for any 64-bit system, so in general I've stuck with that unless I calculate or observe that I actually need more.

SQL Server (D:): 100 GB is far too much, assuming you store your backups elsewhere. The actual installation will require only a few GB, but of course it's always good to leave plenty of headroom for traces and such. As a rule of thumb I make this drive 20 GB, but even so it ends up being mostly empty space. I do not store database backups here, though. I'll talk about those separately. This drive contains only the SQL Server executables, traces (logfiles, not the transaction log), and system databases other than TempDB (Master, Model, and MSDB).

SAP (E:): I generally make this drive 50 GB. Again, this usually ends up as empty space, but you do need room for traces, work files, and such. Also, when you do upgrades and updates with SUM, you will probably store your SUM files here, although that's optional. Probably the biggest consumer here is for the transport host, as your transport files will be stored here, but that is not on every system. For a transport host, you may find that 100 GB is more likely.

TempDB (F:): How large to make your TempDB is highly dependent upon the type of SAP system you are installing. Your example would probably be appropriate for a BW system, which uses TempDB heavily, but is probably too large for a standard ABAP system, and is definitely too large for a standard Java system. Note 1174635 goes into details about sizing TempDB for BW systems, and Juergen Thomas (in the best practices document I linked earlier) goes into even more detail about the differences in usage between OLTP and OLAP systems (pages 39-40). For an OLTP system (e.g., R/3) he suggests only 2-4 GB, which I admit seems too small to me. My practice to date has been to size it at 10 GB initially on a 20 GB SAN disk (meaning it can be expanded online if need be), but I have come across a few instances (mainly during basis maintenance operations) where that was not enough, so I may increase that to a 15 or 20 GB initial size. For Java systems, though, I find it is used much, much less, so a 1 GB TempDB seems more than sufficient. I still put it on a disk with plenty of autogrowth headroom, though, just in case.

Transaction Log (G:): Again, this depends on usage, and also on your backup strategy. As a starting point I like to make it 10%-15% of the database size. For production systems, I back it up hourly (some shops do it far more frequently, and I won't say they're wrong to do so, but we aren't the largest or busiest) (for DEV/QAS systems, daily backups seems sufficient). I also set an alert in SQL Server Agent to monitor for Percent Log Used, and if it exceeds 80% I have that fire an email to the operator group and kick off the log backup (I put a delay between alerts, so that it doesn't repeatedly try to kick off that backup every minute while the previous backup is still running). And, of course, autogrowth is enabled and there is headroom on the disk. See my blog on SQL Server Configuration which I'll be publishing tomorrow morning for more information about configuring alerts. Juergen Thomas goes into transaction log sizing on pages 38-39 of his document.

Database (H: - ?): As you say, this is highly variable. For an ERP system, it's not easy to predict how much space you will need when first installing, though there are various formulas for doing so. The only thing that's certain is that it will grow, and keep growing. Of more consideration initially, probably, is how many drives to spread it across. The installation tool (SWPM) these days recommends a number of files based upon the number of logical processors, for purposes of splitting up the I/O load efficiently. That results in a recommendation of 4, 8, or 16 files. I'm generally going with 8 for most of my systems these days, so that means I'm using drives H: - O: for the database (one file per drive). This is on servers with sixteen logical processors.

Backups (B:): You didn't mention this, but I thought I would go ahead and address it. I backup my databases to disk initially, keeping the disk backups available for a couple weeks before removing them, and then the data center folks run filesystem backups that capture those images. As mentioned in my blog, I don't keep these DB backups on the SQL Server drive, but rather specify a dedicated drive during the installation. Since modern servers generally lack a floppy drive, that means that technically A: and B: are available, so I use the B: drive. This works well, with one caveat. A big, empty backup drive seems like a good place to temporarily store source files during installation, and for installing SQL Server that actually works fine. However, SWPM won't read from A: or B: during installation, so you have to store your SAP sources elsewhere, which is unfortunate, but not a big deal. Post-installation, there doesn't seem to be any problem with using this drive letter. As for how big to make it, that's again highly dependent upon your policies and, of course, the size of your database. How often do you backup the transaction log? Do you make differential backups? How long between full backups? How many weeks worth of backups do you keep online? All those are factors. Obviously, this drive can quickly become the largest of all (for my production ERP system, it runs at 1.5 TB), so you might consider using less-expensive disks in the array to make this one up, though of course that could impact the speed of your backups.

Finally, I'll point to a discussion thread I participated in a few months ago on this question of disk space requirements for ERP on SQL: SQL Server 2012 disk requirements for ERP 6.07?.

I hope you've found this helpful!

Regards,

Matt

Derek_Fox
Contributor
‎01-26-2015 3:48 AM
0 Kudos

HI Matt

Thank you for the insight - this definitely helps.

Regards

Derek

former_member212248
Explorer
‎04-10-2015 4:56 PM
0 Kudos

Matt,

It is being recommended by a security team to revoke permissions to xp_regread, and xp_dirtree, and to disable xp_cmdshell ... I have not found much discussion on these and wanted to know if you have any experience with doing such for SAP installations on SQL Server.  Thank you in advance!

Melanie

Matt_Fraser
Active Contributor
‎04-10-2015 6:13 PM
0 Kudos

Hi Melanie,

If you disable xp_cmdshell then you will not be able to monitor your SQL Server instance (performance, etc) from DBACOCKPIT, Solution Manager, etc. The SAP installation instructions specifically call for enabling this procedure, although it is true that a default (non-SAP) SQL Server installation does not have it enabled.

I am not sure what the effects would be of disabling xp_dirtree or xp_regread, but I can imagine some things not working anymore.

In a default SQL installation for SAP usage, xp_cmdshell should only be executable by the SAP system, i.e. by the 'sid' schema user that the SAP application connects as. As long as you are not granting anyone or anything else membership in the sysadmin server role, I don't think you'll find this easily abusable.

xp_dirtree and xp_regread, out of the box, are granted to the 'public' database role in the master database (xp_cmdshell is not). However, that doesn't mean that anyone can just connect to the DBMS and start executing these. A user still has to be granted permission to connect. In general, you shouldn't be granting to permission to anyone other than the SAP service accounts.

Now, it's true that the SAP installation guide for SQL Server recommends adding the local Administrators group to the sysadmin server role, which means that all your Domain Admins, by default, will have full access to the database, DBMS, and anything else in it, including these extended stored procedures. So, that's a point you may want to change. In general, the thinking is that if they're Domain Admins, they're highly trusted individuals, but certainly you could choose to segment out this access at a more granular level. You just need to ensure that someone can logon to your box and have sysadmin access in order to manage it. So, instead of disabling these stored procedures, which could potentially cause SAP and SQL functions to fail, I would focus on narrowing the scope of who is in the sysadmin role, and ensure your SQL Server logins are tightly controlled.

Regards,

Matt

srinivaas
Participant
‎06-26-2015 10:28 AM
0 Kudos

Hi Matt

its really useful information and Excellent post.

Good overview. Thanks for your time and efforts.

Can I install the SQL 2012 in Windows server 2008 r2

Regards

Derek

Matt_Fraser
Active Contributor
‎06-26-2015 3:45 PM
0 Kudos

Hi Derek,

Thank you. Yes, SQL Server 2012 is supported from Windows 2008 onwards. For Windows 2008 R2 you need at least Service Pack 1 for Windows. Just keep in mind supported kernel releases and, for ABAP systems, minimum Basis Support Pack levels. Check the Product Availability Matrix and Note 1651862 (Release planning for Microsoft SQL Server 2012) for the latest information.

Cheers,

Matt

former_member349777
Explorer
‎11-29-2017 1:12 PM
Hi Matt,

You are linking the the SQL Server Upgrade to and Installation Guides in this amazing blog. I just wanted to let you know that all these guides, including “Upgrade to and Installation of SQL Server 2012”,have been migrated to the new SAP Help Portal. And no logon is required anymore.

You can find detailed information on the new location and formats here: https://blogs.sap.com/2017/11/28/upgrade-to-and-installation-of-sql-server-guides-moved-to-sap-help-... and on this updated wiki page: https://wiki.scn.sap.com/wiki/x/3hO7Gg

Best,
Jessica

 
Matt_Fraser
Active Contributor
‎11-29-2017 11:21 PM
0 Kudos
Thanks Jessica! I've updated the blog to reflect this new location.

I admit when I first saw a notification for your comment, I thought you were saying that you had moved my blog to a new location, and I got a little worried. 😉

Cheers,
Matt
Labels in this area
Popular Blog Posts