1 2 Previous Next

SAP on SQL Server

27 Posts

Performance issue becomes a real problem for some SAP systems. Especially if your DB size is big, there are many customer codes.


In one of our customer's system we had a performance issue. We had a meeting with all parts involved in SAP Systems. HW vendor, Network group, Business, System Admins and BASIS. The result of the meeting was an action list.

Beside all architectural actions, one of the action was finding fragmanted indexes and applying defragmantation and it was my duty to do the needful.

This blog is output of this action and about index defragmantation on SQL Server.


I will try to explain the topic in the following order.

  1. List the indexes that will be possibly defragmanted and effecting the system performance
  2. Analyze the indexes
  3. Defragmant the indexes
  4. Run new Statistics for the indexes
  5. Results and Chart
  6. Pros and Cons of defragmanting


And these are the details of the steps:


1. List the Indexes

When we were listing the fragmanted indexes our scope was on the indexes that are used in expensive SQL Statements. We also checked the indexes of the tables that are accessed sequentially on process overview.


Go to DBACOCKPIT --> Performance --> SQL Statements


Double Click on one of the SQL Statements.

Go to Explain Tree Tab to see the index names used in the SQL Statements.


For other indexes we use SM66 Global Work Process Overview.

Check the sequential reads and related tables.



After taking the list of related tables you go to SE11 and take the index names.



2. Analyze the Index

Go to DBACOCKPIT --> Space --> Single Table Analysis

Enter the related table name and select the index name.

Select DBCC showcontig  button.



     Run one of the options Fast, All Levels and Normal.


Yo can also use SQL SQL Server Management Studio to find DBCC Showcontig results using similar Queries like below.


DBCC SHOWCONTIG allows you to measure fragmentation and page density levels on indexes.


The result is analyzed according to the document "Microsoft SQL Server 2000 Index Defragmentation Best Practices" mentioned in SAP Note 159316 - Reorganizing tables on SQL Server. You can check the "Analyzing Output from DBCC SHOWCONTIG" section in the document for details.

The document says that  "High values for logical scan fragmentation can lead to degraded performance of index scans. Consider defragmenting indexes with 20 percent or more logical fragmentation."

It states also that "indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."


So we selected all indexes that has logical scan fragmantation more than 20% and more pages.


3. Defragmant the indexes


We used SQL Server Management Studio Query editor to run scripts for defragmanting.

Examples:   DBCC INDEXDEFRAG (<SID>, "<sid>.EKPO", [EKPO~0])

                    DBCC INDEXDEFRAG (<SID>, "<sid>.RFBLG", [RFBLG~0])


Duration of defragmantation changes according to the index sizes and Logical Scan Fragmentation percentage. As you see below screen it took 3 hours. Some lasts more than a day.


This process also generates logs. So you must monitor your log space during the process. Otherwise your system can stop due to lack of log space.


DBCC INDEXDEFRAG reports the estimated percentage completed every five minutes.


DBCC INDEXDEFRAG can be stopped at any point in the process, and any completed work is retained.



4- Update Statistics

     We updated statistics of the related tables after defragmantation to obtain the changed statistics.


5- Results and Graph

    When we rerun DBCC Showcontig command after defragmantation we can easily see the decrease in Logical Scan Fragmentation.

     The value became an acceptable value as it is seen in below picture.


     As a result, we took the I/O Stall read and write times from DBACOCKPIT --> Performace --> History --> Database Collector Data Time Series table.


     We exported data to an excel sheet and took the averages of all read and write times for the whole period. (Red and orange lines in below chart).

     From the chart below it can easily be seen that I/O values decreased significantly. So it worth defragmenting.


6- Pros and Cons of defragmanting

  • First of all, SAP explicitly recommends not to reorganize or rebuild any database objects on a regular basis. You should not even reorganize or rebuild objects as an attempt to solve a performance problem as long as it is not evident that fragmentation is the root cause of the problem (which it hardly ever is).


  • You can do this process for particular indexes that are fragmented with high percentages, has alot off pages and used in expensive sql statements.


  • It is time consuming job


  • Generates lots of logs, monitoring is must


  • Defragmentation can be done online. Instead DBREINDEX can be used for offline solution.



159316 - Reorganizing tables on SQL Server

1660220 - Microsoft SQL Server: Common misconceptions

Microsoft SQL Server 2000 Index Defragmentation Best Practices

You're back from your coffee break, and your SQL Server 2012 installation has completed successfully. What next? You're ready to install your SAP application now, right?


Not quite yet. There are a few configuration and housekeeping steps you should take before going further. Note: many of these steps can also be handled within an ABAP system via DBACOCKPIT, or from Solution Manager, but I'm an old hand and still find some of this considerably easier using the SQL Server native tools.


SQL Server Post-Install Configuration (Pre-SAP)



You should still have your Upgrade to and Installation of SQL Server 2012 in an SAP Environment guide open to chapter 4.2 (Installing the SQL Server 2012 Database Server Software Manually). You are now at Step 6 in the guide. In most circumstances the TCP/IP protocol will be enabled by default, so you should not have to change anything. However, it is worthwhile to follow this step to ensure this is the case, and this is a good time to pin the SQL Server Configuration Manager to your taskbar and/or start menu.


Database Mail

First up, let's give SQL Server the ability to email you when it's in trouble.


  • Open the SQL Server Management Studio tool from your Start menu (and pin it to your taskbar and start menu as well), and logon using Windows authentication.
  • Expand your server, then expand the Management folder.
  • Right-click on Database Mail, then select Configure Database Mail.
    • Click Next, then on the Select Configuration Task screen, choose the first (default) option for Set up Database mail... Click Next.
    • A popup warns that the Database Mail feature is not available and asks if you'd like to enable it. Click Yes.
    • On the New Profile screen, give your mail profile a name. I usually choose the SID of the SAP system I'm about to install.
    • Under SMTP accounts click Add.
    • The New Database Mail Account window pops up. Fill in the following fields (those not specified are optional and can be left blank or at their default):
      • Account name: You can again use the SID just as you did for the profile name.
      • Outgoing Mail Server (SMTP)
        • E-mail address: Here I usually enter something like "SID@server.domain.com", using your SAP system's SID, your SQL Server host name as the server, and your organization's domain (or that of your mail server) for "domain.com".
        • Server name: This is the host name of your email server, not your SQL Server. Enter it fully-qualified, i.e. "mail.domain.com".
      • The other fields on this screen should only be changed if your mail server requires it, or to fit your organization's email connection standards. Most often the defaults work fine.
    • Click OK.
    • You are now back on the New Profile screen, and your SMTP account shows up in the list. Click Next.
    • On the Manage Profile Security screen, on the Public Profiles tab, your SID profile shows up in the list. Select the checkbox next to it under Public, and switch the property under Default Profile to Yes. Click Next.
    • On the Configure System Parameters screen, review the defaults and click Next.
    • On the Complete the Wizard screen, review the actions and click Finish.
    • If all goes well, you should see a "Success" message. Click Close.
  • Back in the Management Studio's Object Explorer, right-click Database Mail again, and choose Send Test E-Mail.
    • Your newly-created Database Mail Profile should be shown. In the To: field enter your own email address, then click Send Test E-Mail.
    • In a moment you should receive an email in your inbox. If it worked, click OK on the test popup window.


SQL Agent

While humming the tune SQL Agent Man to yourself, refer back to your Upgrade to and Installation of SQL Server 2012 in an SAP Environment guide and open it to chapter 5, Setting the SQL Server Agent Configuration. Keep that handy.


  • In SQL Server Management Studio, expand SQL Server Agent.
    • Right-click Operators and select New Operator.
      • On the General page, enter the name of a system administrator or group to receive alerts from this SQL Server. If it's not a production system, this might be just yourself. Make sure the Enabled checkbox is checked.
      • Under Notification options, in the E-mail name field, enter the email address for the system administrator or group distribution list (or yourself) as appropriate. Click OK.
    • Right-click SQL Server Agent and select Properties.
      • Switch to the Alert System page.
        • Under Mail session select the checkbox for Enable mail profile.
          • In the Mail profile field, select the SID mail profile you configured earlier. Don't worry if the Test button is greyed out.
        • Under Fail-safe operator select the checkbox for Enable fail-safe operator.
          • The operator you created earlier should already be defaulted; if not, select it.
          • Next to Notify using: select the checkbox for E-mail.
      • Switch to the History page.
        • Per the instructions from the installation guide, set the following parameters:
          • Maximum job history log size (in rows): 6000
          • Maximum job history rows per job: 500
      • Click OK.



Now it's time to configure some alert notifications for general error conditions that hopefully will not occur.


  • In SQL Server Management Studio, expand SQL Server Agent (if not still expanded from the previous section).
  • Right-click Alerts and select New Alert.
    • On the General page, give the alert a name of 11 - Specified Database Object Not Found.
      • Under Event alert definition, in the Severity field, select severity 011.
    • Switch to the Response page.
      • Select Notify operators and the checkbox under E-mail next to your operator's name.
    • Switch to the Options page.
      • Under Include alert error text in: select the checkbox for E-mail.
      • Set Delay between responses to 30 minutes.
    • Click OK.
  • Repeat this process, creating new alerts for severity conditions 013 through 025. I like to name the alert using the severity number and name so it is logically ordered and descriptive when looking at them in the Management Studio.


When you've finished, your list of alerts should look like this:


sql alerts.png


Later, after you've installed your SAP system, you may want to add additional database-specific alerts (for instance, Transaction Log Full) which can, in addition to notifying you by email, kick off a process, such as a log backup.


Configuration Parameters

Now it's time to open Note 1702408: Configuration Parameters for SQL Server 2012.


It is not necessary at this time to set any trace flags, as SWPM will set the appropriate ones automatically during the installation of your SAP system (though you will want to review them afterwards to ensure this happened correctly).


The Note walks you through setting parameters via SQL Query commands. However, many of them can also be set using the Management Studio's GUI options, and it's not a bad idea to familiarize yourself with the tool this way.


  • In SQL Server Management Studio, right-click on your server name (top of the list in Object Explorer) and select Properties.
  • On the General page, make a note of the number (in MB) shown for Memory. This should match the total physical memory installed in your server.
  • Switch to the Memory page.
    • Here the fields Minimum server memory (in MB) and Maximum server memory (in MB) equate to the configure options mentioned in the Note min server memory (MB) and max server memory (MB). How much you set these values to depends on your particular landscape and how much else is also on this server, and the Note gives some general guidelines for making a decision. No matter what you choose, you are going to set both parameters to the same value, so SQL Server will use a fixed amount of memory.
    • If this is a dedicated database server, subtract between 1.5 GB and 6 GB from your total physical memory (as described), convert the remainder into MB, and enter this value in the fields.
    • If this is a central instance, i.e. you will be running both SQL Server and an SAP application instance on this box, then divide your total physical memory by three and enter that number into the fields (i.e., give SQL Server 1/3 of the physical memory). Later, after installing and running your SAP application for a while, you may find you need to adjust this number, but this is a good starting point.
    • If you have a previously installed system with the same amount of physical memory and the same release of NetWeaver application installed, you may find that the installer set a different default for the SQL Server memory. If so, and that system is running well, you may want to use that number again here. For instance, on a machine with 48 GB of physical memory and one NetWeaver AS Java 7.4 instance co-hosted with SQL Server 2012, the number might be 19656.
  • Switch to the Database Settings page.
    • Under Backup and restore, select the checkbox for Compress backup.
  • Switch to the Advanced page.
    • Under Parallelism, set Max Degree of Parallelism to 1.
      • There are exceptions to this rule, generally for BW systems, but in almost all other cases this will give better performance.
  • Click OK.


Now again right-click on your server name and this time select New Query.


  • In the query window, type:
    • sp_configure 'show advanced options', 1
    • reconfigure with override
  • Click on Execute (or hit F5 on your keyboard).
  • Edit the query command so that 'show advanced options' becomes 'xp_cmdshell'. Now the query should look like:
    • sp_configure 'xp_cmdshell', 1
    • reconfigure with override
  • Execute (or hit F5).
  • If you want to confirm all the settings you have made, delete all but the command sp_configure (or highlight just that part) and execute. You will get a list of all the parameters, their minimum and maximum values, and their configured and runtime settings.
  • Close the query window. Do not save changes.


The rest of the parameters described in the Note only apply after installing an ABAP system, so you are done with it for now.


Lock Pages in Memory

There is one additional, important setting that is not described in Note 1702408. It is, however, described in Note 1134345: Using locked pages for SQL Server. This setting cannot be made using the Management Studio, and it is the first reason why I recommended during installation to use an actual service account and not a builtin account (such as Local Service).


  • In Windows, click on Start and select Run.
  • In the Run dialog box, type gpedit.msc in the Open field, and click OK.
  • This will open the Local Group Policy Editor. On the left, expand Local Computer Policy -> Computer Configuration -> Windows Settings -> Security Settings -> Local Policies.
  • Select User Rights Assignment. A list of related policies appears on the right.
  • Find and double-click Lock pages in memory.
  • Click Add User or Group.
  • Type in the name of the domain user account used for your SQL Server service, then click Check Names.
  • Confirm that the correct account is returned, then click OK.
  • Confirm the account appears in the list, and click OK again.
  • The account should now show up under Security Setting next to Lock pages in memory.
  • Close the Local Group Policy Editor.


The setting requires a restart of SQL Server to take effect. Close the SQL Server Management Studio (if it's still open) and start the SQL Server Configuration Manager. On the left, select SQL Server Services, then on the right right-click SQL Server (MSSQLSERVER) and select Restart.


This setting will prevent SQL Server's buffer memory from being paged to disk, which can be a performance-reducing event. The Note gives more detail about this setting, and also some considerations around its use. However, in general, for the majority of installations, it will be appropriate to set this.


You can confirm the setting is operational after the restart by examining the most recent SQL Server Log in the Management Studio. Expand Management -> SQL Server Logs and double-click the one marked Current. In the log, near the beginning, look for the line Using locked pages in the memory manager.


System Maintenance Plans

It's time to get a backup of the system databases and set up some housekeeping jobs.


System DB Backup

When installing SQL Server, you set aside a dedicated drive for database backups, and created a folder there called Backup. If you didn't, now is a good time to do so. Under the Backup folder, create another folder called System. Eventually you will have others here as well, but this will do for now.


Back in the SQL Server Management Studio, expand the Management folder under your server. Right-click Maintenance Plans and select Maintenance Plan Wizard.


  • On the starting page, click Next.
  • On the Select Plan Properties page, give the plan a name: System DB Backup.
    • Under Schedule, click Change.
      • On the New Job Schedule page, set the frequency and time of the backup job as appropriate. For the system databases (Master, Model, and MSDB), I recommend daily backups.
      • Under Frequency, for Occurs: select Daily.
      • Under Daily frequency, for Occurs once at: set an appropriate time (e.g. 12:30:00 AM).
      • Click OK.
    • Back on the Select Plan Properties page, click Next.
  • On the Select Maintenance Tasks page, select the checkboxes for Back Up Database (Full) and Maintenance Cleanup Task. Click Next.
  • On the Select Maintenance Task Order page, use the Move Down or Move Up buttons so that Maintenance Cleanup Task is at the top of the list. Click Next.
  • On the Define Maintenance Cleanup Task page:
    • Under Search folder and delete files based on an extension, search for and select your \Backup\System folder.
    • In the File extension: field type bak (no periods).
    • Select the checkbox for Include first-level subfolders.
    • Under File age: set an appropriate amount of time you wish these backups to remain on disk (at least long enough for network filesystem backups to pick them up, and also long enough for any potential need for them to be covered). Two or three weeks is probably sufficient in most cases.
    • Click Next.
  • On the Define Back Up Database (Full) Task page:
    • For Database(s): select System databases.
    • Under Create a backup file for every database, select the checkbox for Create a Sub-directory for each database.
    • Set the Folder: to your \Backup\System folder.
    • Select the checkbox for Verify backup integrity.
    • Click Next.
  • On the Select Report Options page, click Next.
  • On the Complete the Wizard page, click Finish, then after the success notification, click Close.


Clean Up History

Now use the wizard to create a second maintenance plan.


  • Name the plan Clean Up History.
  • Give this plan a schedule frequency of monthly. A possible option is to occur on the first Saturday of each month at 2:00am.
  • Select the tasks Check Database Integrity, Update Statistics, Clean Up History, and Maintenance Cleanup Task.
  • There is no need to change the order of operation.
  • For Define Database Check Integrity Task, choose All databases.
    • Note, for ABAP systems it is often recommended to schedule this check via DBACOCKPIT, and it may be scheduled automatically on installation. In this circumstance, you may choose to restrict this check to your system databases.
  • For Define Update Statistics Task, again choose All databases and leave the other options at default.
    • Again, note, at least for ABAP systems it is generally recommended to not schedule this job, in part due to the large database sizes involved, and in part because it may not be necessary (autostats should handle this). So, you may choose in this circumstance to restrict this to system databases.
  • For Define History Cleanup Task, set an appropriate amount of time to retain the job history data. The default is 4 weeks, but I feel this is too short. I recommend 2-6 months.
  • For Define Maintenance Cleanup Task:
    • Set Delete files of the following type: to Maintenance Plan text reports.
    • Set Search folder and delete files based on an extension to the folder in your SQL Server installation where job log reports are kept. By default this is \Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log.
    • Set an appropriate file age. I suggest 2 months.


Maintenance Plan Notifications

The wizard does not include an option for setting email alerts in the event of job failure. However, you can set this on the jobs themselves.


  • Expand SQL Server Agent and double-click Job Activity Monitor.
  • On each of your two new jobs (Clean Up History.Subplan_1 and System DB Backup.Subplan_1), double-click to open the job properties.
  • Select the Notifications page.
  • Select the checkbox for E-mail and select your previously defined operator.
  • Click OK.


Now you will receive an email in the event one of these jobs fails for any reason.


Get a Backup

You're done with initial configuration of SQL Server and ready to proceed with installing SAP. Before you do so, I recommend that you get a backup of your configuration. In the Management Studio, expand Management -> Maintenance Plans and right-click System DB Backup. Select Execute.


Let me know if you find any errors in this guide, or if you have alternate suggestions.

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. It can be downloaded from the Service Marketplace (logon required) at http://service.sap.com/instguides -> Database Upgrades -> MS SQL Server.


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.



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.


server manager add roles features.png


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


server manager select features.png

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.



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.




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.


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.


setup product updates.png


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.


setup feature selection directories.png


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.


setup instance configuration directory.png


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.


setup server configuration service accounts.png


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


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.



Please see SQL Server Configuration: Pre-SAP Installation for my personal recommendations on what to do next after initial installation.

As I’ve been quite busy within the last year I didn’t find much time to contribute or share any breaking new for a while – even though the lack of spare time didn’t improve much recently it’s finally time to talk about a ‘new’ topic which at least everyone working with BW has definitely come across lately – Columnstore.


Within the last year I spent a lot of time migrating BW systems to SQL Server, implementing SQL Server Columnstore Indexes and trying to make the queries and loads as fast as I can or if possible faster than they are on Hana. Today I want to share the results with you.


What is a SQL Server Columnstore Index, how does it work and how to implement it in an SAP BW system?


Many documents answer these questions a lot better than I ever could so I will not repeat the explanations for you but rather point you to the documentation that I found most helpful:


Column Store Indexes Described

Brief explanation on
- what Columnstore is
- how it works technically and
- what the main advantages are

Using SQL Server 2012 Column-Store with SAP BW

My personal favorite as the author managed to compress all the information you need to understand:
- which requirements need to be fulfilled
- how to implement column store indexes
- what to expect

within 19 pages


How difficult is it to implement SQL Server Columnstore Indexes and how long does it take?


In my opinion, SAP does a very good job with providing easy-to-use tools on SAP software level to implement quite powerful features on SQL Server level.
I saw this with row/page compression where a single report (MSSCOMPRESS) was all one needed to row/page compress database objects online/offline. With implementing SQL Server Columnstore Indexes it’s as simple again. SAP provides a report called MSSCSTORE which allows you to create Columnstore Indexes for a single infocube or all infocubes in the system and you can also use this report to switch back to rowstore for a single or all Infocubes (while I can’t think of a reason to switch back) with just a few clicks. The interface of the report is quite self-explanatory – if some questions still remain open it makes sense to look into SAP Note 1771177 and  Using SQL Server 2012 Column-Store with SAP BW.


I didn’t do any detailed performance analysis on the runtimes of implementing Columnstore Indexes and runtimes - as always – are influenced by many factors (hardware, system load, database size, and many more). However, the database sizes where I implemented column store indexes varied from 3 to 12 TB, and the runtime for all Infocubes in total varied from 3 to 12 hours.


Real-World Results with SQL Server Columnstore Indexes

The experiences I gained with SQL Server Columnstore Indexes are based on several proof-of-concept projects where the aim was to find out how fast BW queries and data load processes could get after switching from the conventional table structures of Infocubes to the usage of SQL Server Columnstore Indexes. Even though reducing the database size was not an important goal in any of the POCs I could observe a mentionable and sometimes even a massive reduction of the database size in every single case.

Case Study 1 - Reduction of space consumption

These numbers show how the space consumption of the database changed after different actions.




Original Size of the database

Allocated MB in transaction DB02 so no freespace within the DB Files contained in that number. All objects in the DB are page compressed.

9,5 TB

DB Size after compressing requests of Infocubes

All requests except the ones from the current month were compressed in transaction RSA1.
This has been done for all Infocubes. The aggregates still existed.

9,5 TB

DB Size after implementing Columnstore Indexes for the infocubes and deactivating aggregates

All requests except the ones from the current month were still compressed from the last action.
Columnstore Indexes were created with the option to deactivate all aggregates during the run.

6,2 TB

DB Size with Columnstore Indexes for Infocubes and aggregates using Columnstore Indexes as well

Aggregates were configured to use Columnstore Indexes as well and have been activated again

7 TB

Looking at the tables and indexes of an Infocubes in more detail before and after implementing Columnstore Indexes I gained an idea on how this amount of space is saved without doing anything but creating Columnstore.

Space Consumption of E + F fact table of an Infocube after different actions


Size of F-fact table [GB]

Size of E-fact table [GB]

Size of E + F fact [GB]

Delta to last step [GB]

Original state – no BW requests compressed, no Columnstore indexes, page compressed objects





After compressing all requests of the Infocube except the ones from the last and current year




+ 4,8

After compressing all requests of the Infocube except the ones from the last and current month




+ 0,6

After creating Columnstore indexes for the Infocube




- 13,3

In this case I’ve been using SQL Server 2012 for my tests. As Columnstore indexes are not updatable with this SQL Server release they are only used on the E-fact table of an Infocube. After creating a Columnstore Index including all columns of the E-fact table all other secondary indexes on the table become superfluous – for this reason MSSCSTORE drops them when it creates the Columnstore Index. The space previously occupied by the secondary indexes of E-fact tables becomes freespace now. The Columnstore Indexes of course consume space as well but they are stored column-wise and not row-wise and as columns often have similar data, high compression rates can be achieved (also pointed out here Column Store Indexes Described). In all my tests the Columnstore Indexes were quite small in comparison to the table sizes.

In another project I didn’t record the sizes in this level of detail but it might still be interesting to know that the original database size (Oracle with no database compression feature in use) was around 3 TB and after migrating to SQL Server and creating columnstore indexes (page compression in use as well) it went down to 800 GB.

Case Study 2 – Comparison of Query Runtimes

Looking at the runtimes of a defined set of BW queries on the source system (Oracle) and a SQL Server system with Columnstore Indexes I could observe the following runtimes:


Source System [sec]

SQL Server 2012 with columstore on E-fact tables but without any aggregates [sec]

SQL Server 2012 with columstore on E-fact tables and selected aggregates without Columnstore [sec]

Query 1




Query 2




Query 3




Query 4




Query 5




When I carried out this test SAP’s recommendation was to let report MSSCSTORE deactivate all aggregates while creating the Columnstore Indexes. As the above runtimes show I was able to further speed up 2 queries after re-activating aggregates for 2 queries – back then these were still conventional aggregates without a Columnstore. In the SAP released column-store also for BW-aggregates (SAP Note 1951490) which presumably would have speeded up all tested queries again.


The total runtime of all tested queries looked like this:


Total runtime for all tested queries [sec]

Source System (Oracle, no database compression)


SAP Hana


SQL Server 2012 with page compression and Columnstore Indexes on the E-fact tables but without any aggregates


SQL Server 2012 with page compression and Columnstore Indexes on the E-fact tables and with selected conventional aggregates without Columnstore Indexes


SQL Server 2012 with page compression and Columnstore Indexes on E-fact tables and on aggregates

Unfortunately didn’t have the chance to test this but presumably < 308

Lessons Learned

Keeping it brief without going too much into detail I learned the following lessons:


  • In any case mentionable, mostly even massive reduction of the database size
  • Speedup by factor 0-50 per query depending on the query and the cube design
  • The bigger the cube, the more speedup
  • If bottleneck is not the database, the speedup with implementing tuning measures on DB level like Columnstore Indexes understandably turns out to be a modest affair
  • Load runtimes improve as less index maintenance is required
  • As soon as the requirements are fulfilled the implementation of Columnstore Indexes is very simple
  • The integration of Columnstore Indexes in SAP BW is constantly improved so it makes sense to look for new features on a regular basis and adopt them
  • Considering the achievable speedup it makes sense to benchmark if a BWA still makes sense or if the required runtimes can already be achieved using SQL Server Columnstore Index


Continuous Improvements

Within the last year Iots of improvements were introduced regarding the integration of SQL Server Columnstore Indexes into SAP BW. To make sure I do not miss any of them I keep looking for SAP documentation, SAP notes, blogs on SCN and the SAP on SQL Server section of MSDN before I start with a new migration. It seems that some improvements are shipped within SPs without being announced explicitly for this reason I also try to always use a recent SAP BW and SAP Basis support package.

Ok, first a few caveats:

  • Windows Server 2012 R2 isn't quite yet officially supported by SAP at the time of this writing, but it's coming very soon.  It is currently supported in the First Customer Shipment program, details of which can be found in Note 1955608.
  • In-place upgrades of Windows servers with SAP software installed are supported, but not in all situations, and generally a system copy or migration to new hardware with a fresh install of Windows is recommended instead.  Details about when in-place upgrades are supported can be found in Note 1494740, which is applicable for Windows Server 2012 and higher as well as the versions explicitly mentioned in the Note's title.


So, with that out of the way, here's an interesting 'gotcha' which you may encounter if you do elect to perform an in-place upgrade with SQL Server installed.


The scenario is upgrading a Windows Server 2008 R2 sp1 system with SQL Server 2012 sp1 to Windows Server 2012 R2.  SQL Server is previously upgraded from 2005, which may or may not be a factor.


After the Windows upgrade is complete, everything works fine, except the SQL Server Configuration Manager tool is missing from the Start menu.  This is definitely a minor annoyance, and it appears to not be an isolated case, as I found external blogs mentioning this issue.  I want to give credit to Marnix Wolf for figuring out what is happening and how to fix it (Thoughts on OpsMgr and System Center 2012: Quick Trick: Where Is SQL Server Configuration Manager After Updating To WS20…).  In a nutshell, the upgrade is unregistering the Configuration Manager MMC snap-in, but the files required to operate the Configuration Manager are still present.

So, how to fix it?

The easy way is to find another SQL Server 2012 system and copy a shortcut from it.  If you install SQL Server 2012 fresh on Windows 2012, you won't have this problem, so with a little luck you have such a system sitting around.  Open the folder C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2012\Configuration Tools, and copy the shortcut SQL Server Configuration Manager from that folder to the same folder on your upgraded system, where you'll notice that it is missing.

Alternatively, you may be able to copy the shortcut from a location on the same server you just upgraded.  By default, the 2012 R2 upgrade creates a copy of the critical folders on your C: drive before starting the upgrade and places them in C:\Windows.old.  So, if you drill into C:\Windows.old\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 2012\Configuration Tools, you should find that same shortcut for SQL Server Configuration Manager there.  Copy it to the same location outside of \Windows.old (i.e., just remove \Windows.old from the file path), and you will be good to go.

That's it.  Look in your Start menu, and now the Configuration Manager shows up as it should, with the correct icon.  Click it, and the tool starts and operates correctly.

If you don't have another functioning SQL 2012 system from which to copy the shortcut, then you should be able to make your own shortcut, in this same folder, with the target pointing to C:\Windows\SysWOW64\mmc.exe /32 C:\Windows\SysWOW64\SQLServerManager11.msc.

If you're doing a system copy of a SAP System on top of Microsoft SQL Server 2008 R2 and you're using a local admin other than Administrator, then you need to continue to read. Otherwise you can skip this blog and read other valuable blogs.



When you try to logon to the target system to restore a SQL Database Backup to restore, and you're not .\Administrator, probably you'll get the following error:


Login failed for user 'Domain\User'. (Microsoft SQL Server, Error: 18456)



1. Open an elevated Command Prompt.

2. Stop the SQL service with command: net stop mssqlserver

3. Start SQL Server in Single User Mode with command: "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr" -m"SQLCMD"

4. Now open another command prompt window and write command SQLCMD. In case of connecting to named instance we need to supply server and instance name as with -S switch:


5. Grand your admin user to SQL role sysadmin with following commands:

> EXEC master..sp_addsrvrolemember @loginame = N'STFANET\c2eadm', @rolename = N'sysadmin'

> go


6. Go back to first command window and press Ctrl+C to stop the SQL server and then type 'Y' for confirmation. This will stop the sql server.

Start SQL server again and no startup parameters need to specified this time.


Source: http://beyondrelational.com/modules/2/blogs/115/posts/11143/how-to-access-a-newly-installed-sql-server-2008-r2-instance-…

While one can search a lot of documents in SAP on SQL. Running SAP on SQL server and notes in BC-DB-MSS, what I want to do is integrate all materials togather and provide a single place to check your SAP on SQL system. Okay, let's build a checklist and if you have any comments, please give me a feedback below.


1. First things first, the most important factor to affect the system performace is memory so please make sure you install and allocate sufficient memory for SQL server. SAP KBA 1612283 - Hardware Configuration Standards and Guidance mentions this in great detail. I recommend the size of data cache should be more than 20% of the database size after compressed.


2. Check if you use the correct core-based licening E.E. if more than 40 logical processors are used.


3. Check if all configurations for SAP on SQL are met. SAP KBA 1744217 - MSSQL: Improving the database performance.


4. Leverage AlwaysOn or Database mirroring to prevent from Corruption Handling in SAP Databases.


5. Also check juergen's white paper for the sake of completeness. 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.


6. If thousands of IOPS is still required, consider to use Fusion IO to maximize IO performance. Compared to traditional SAN storages, MLC NAND flash is much cost effective.


7. Leverage RZ20 auto reactions to perform daily monitor like oldest open transactions, transaction log utilization, SAP DB backup status...etc. and read EWA to check additional configurations and system performance.


8. Leverage DBACockpit to run DBCC CHECKDB and monitor expensive SQL statements(SQL Statement History is supported after SAP Netweaver 7.0 EHP2).

If your backup (or restore) have been started by another adminitrator or by a job, you cannot use the GUI (or the Messages tab) to follow the progression.


In this case some dynamic management views can be used to track the backup/restore progress:


SELECT command,
estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated completion time"
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s




If you are not interested in the command used, the s.text and view dm_exec_sql_text can be removed:


SELECT command,
estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated completion time"
FROM sys.dm_exec_requests r



Of course, you can adapt this statement to select more (or less) information.


Hope you like this blog and any comments/suggestions are welcome!



Eduardo Rezende

IDES on SAP ERP6 EHP6 on Windows 2008R2/MS SQL Server 2008R2
  • Common preparations;
  • Performance tuning;
  • Applying patches;
I. IDES common preparations to update SP (support package).
1. SAP Kernel update
Stop your SAP system, including any SAP services that were installed. Backup your kernel. Download the most recent kernel and apply. We need to download a kerned for this particular release, taking OS version and DB server into consideration. Assuming Kernel Release is 720_REL 64B UNICODE for IDES ERP6 EHP6 (as a matter a fact it is!), latest patch of Kernel on SMP is Patch 402 under path: 

SAP MarketPlace => Software Downloads => Support Packages & Patches => A-Z Index => K => SAP Kernel 64 Bit Unicode => SAP KERNEL 7.20 64-BIT
UNICODE => <Your OS> => #Database independent

SAP MarketPlace => Software Downloads => Support Packages & Patches => A-Z Index => K => SAP Kernel 64 Bit Unicode => SAP KERNEL 7.20 64-BIT
UNICODE => Windows on IA64 64bit -> MS SQL Server
  • Backup files in directories:
  • Unpack the kernel:
  • Create a directory (‘c:\update’ for instance) and copy ‘sapcar.exe’ there (from one of the directory specified above). Copy kernel update files to just         created dir (SAPEXE_100-10011367.SAR and SAPEXEDB_100-10011365.SAR for instance… the name of the file can vary, depending on kernel release). Unpack the *.SAR archives using the command in command line:
sapcar -xvf SAPEXE_100-10011367.SAR
  • Stop all the SAP* services in windows ‘control panel’ and sapmmc.
  • Copy all the extracted files from both archives to the folders specified on the step B with replace.
  • Now run the services again.
  • It is highly recommended to read SAP Note 19466 before update and detailed user manual on this subject located here.
2. Make changes to the instance profile (RZ10)
First of all, import all active profiles (in RZ10 go to ‘Utilities’ menu -> Import profiles -> Of all active servers).
  1. Second of all, please read carefully this article about optimal values for ‘rdisp’
    processes and calculate them in accordance to your system configuration.
  2. Here is the typical values (and
    again, they could be not suitable for you hardware and may affect badly the
    overall performance):
rdisp/wp_no_dia = 10
rdisp/wp_no_btc = 3
rdisp/wp_no_enq = 1
rdisp/wp_no_vb = 3
rdisp/wp_no_vb2 = 3
rdisp/wp_no_spo = 1
login/system_client = 800 (for IDES system)
rdisp/keepalive_timeout = 600
rdisp/keepalive = 6H
ms/keepalive = 9999999
ms/conn_timeout = 100000
ms/http_timeout = 600
em/initial_size_MB = 1024
icm/keep_alive_timeout = 6000
icm/conn_timeout = 9999999
rdisp/max_wprun_time = 14400
rdisp/gui_auto_logout = 0
c.  Restart sapmmc to apply the changes.
3. Other post-install activities

     a. Change passwords for SAP* (default values are: 06071992 or PASS on clients 000, 001) and DDIC users (19920706 for the same clients).

     b. Connect SAP Online Help using SR13.

     c. Use SE06 to initialize the TMS (t-code STMS).

     d. Import RFC's

     e. Check TMS configuration via ABAP report, use SE38, report RSTPTEST.

     f. Add an initial screen system message called ZLOGIN_SCREEN_INFO using SE61.

     g. Recompile all your ABAP loads by scheduling a job using SGEN.

     h. Apply to SAP for your permanent license key.

     j. Back up your system.

4. Update SAP components
Apart from kernel update it is required to patch other system components such as: DBSL (database package), TP, DISP+WORK, R3TRANS, R3TA, R3SZCHK, R3LOAD, R3LDCTL. On the screenshot below DBSL is already patched to the latest release:
Download, unpack and replace outdated system files with the new ones.

II. IDES common preparations to update SP (support package).

1. MS SQL Server tweaks & tricks


     a. Apply latest SQL Server patches (there is SP2 available for MS SQL Server 2008 R2)

     b. Update SQL Server statistics (sp_updatestats). There is no need to schedule a job because SAP has already done that itself.

     c. Check out DBACOCKPIT to make sure there are no warnings on ‘Alert’ tab (it this tab is missing it means that current state of MS SQL Server is fine). Correct existing alerts according to the suggestions given:


     d. Check DB02 t-code to make sure there are no missing objects in the database (Diagnostics -> Missing tables and indexes -> Objects missing in the database):

If there are indexes missing in the DB, please create them by scheduling a job, it will improve overall performance dramatically.
     e. Check this article written by  Beate Grötschnig toimprove SQL Server performance and align it to optimal parameters.

     f. It is highly recommended to read SAP note 1237682 (SQL Server 2008 parameters).

     g. Short instruction on how to apply a trace flag to SQL Server configuration:

    • On the server where SQL Server 2008 has been installed.
    • Start SQL Server Configuration Manager
    • Choose the "SQL Server Services" in the left pane and then
    • Right click on the SQL Server instance of interest
    • Choose Properties
    • In the Properties dialog box select the "Advanced" tab
    • Drop down the list box to the right of "Startup Parameters"
    • Add a semicolon at the end of all those parameters and then the trace flag:  ;-T1117

     h. It also required to move TEMPDB apart from SAP databases to improve the performance (SAP DB log should be moved to another disk as well). To do that please use the next t-sql statement:

    • The first step is to determine the logical file name of the data files for tempdb:
tempdb GO EXEC sp_helpfile GO
    • Knowing this we can run the following ALTER DATABASE statement in SQL Server 2005 or SQL Server 2008 to move tempdb.  Set the FILENAME parameter to the location where you'd like each file:
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\tempdb2005.mdf')
MODIFY FILE (NAME = templog, FILENAME = 'D:\tempdb2005.ldf')
Restart SQLserver services and check whether configuration is correct.

2. Other preparation activities


     a. Make sure after the Kernel update all of the below locations are of same Patch Level.

LOCATION 1: /sapmnt/<SID>/exe
LOCATION 2: /usr/sap/<SID>/<Instance>/exe

     b. From the productive client check the TRBAT entries. 

  • You can clear two entries there (one is header information and other is ‘Tr’ number) from SM30 and then save.
  • Then login to 000 client. Run RDDNEWPP in dialog mode and give it a Priority A.
  • Then come back to the productive client and reimport the request.
  • When reimporting, please check TRBAT table again (it should contain the current request only)
Theoretical help:
  • Executing the report RDDNEWPP schedules the transport dispatcher RDDIMPDP as an event-controlled background job in the current client (use SE38, run (F8) and choose "High priority"). The transport dispatcher RDDIMPDP must be scheduled as a background job in client 000 and in all the clients from which data is exported or into which data is to be imported.
  • When you execute the report RDDNEWPP, you are asked whether you want the job to be scheduled as normal (job class C) or with high priority (job class A). If you choose high priority, the transports will have priority over other background jobs.
  • RDDIMPDP can be scheduled or rescheduled at any time by executing RDDNEWPP. Any earlier scheduling in the same client is deleted.


III. Applying service patches to IDES.


1. Update support package manager (SPAM)


     a. As a first step before any serious update – you have to update SPAM to the latest release available, you can check current release version by entering to this transaction:


     b. In the console (cmd) go to “/usr/sap/trans” and unpack SPAM update package (KD73149.SAR for instance) using SAPCAR as it was shown in the first topic:

sapcar -xvf KD73149.SAR
     In the “/usr/sap/trans/EPS/in” you will see *.PAT file.

     c. Now go to ‘Support package -> Load package -> From application server’ menu:

     Extracted files will be loaded to SAP.

     d. Go back to the same menu and choose ‘Import SPAM/SAINT update’. Wait until SAP finished update processing.

2. Update SAP BASIS and ABAP components


     a. First thing you need to know is that all the SAP SP updates are strongly sequential, ie we cannot have component of level 0002 and jump over the next one right to 0004 for instance, because 0003 should be applied first (in another words they are not cumulative!)

     b. To check components patch level go to ‘System -> Status’ menu, and press ‘Component information button’:


     c.  Now you have to see all the components level:


     d. Assuming you have already downloaded all the necessary patches (taking in account SAP release number, which is 731 in our case) you can go ahead to unpack them using SAPCAR.  Go to “/usr/sap/trans” directory as usual and copy all the patches there:

     After unpack they will be available in the “/usr/sap/trans/EPS/in”.

     e. Go to client 000 and login with user DDIC.

     f. Proceed to ‘Support package -> Load package -> From application server’.

     g. Define the queue:


     h. Remember that you have to update BASIS subsystem first, then ABAP:


     j. You can add all the patches for BASIS in one queue or apply them one by one, but read the SAP note  1597765 first (about common errors of patches update). Be careful, as this note explains some common mistakes of update and also (which is more important) the right approach for BASIS update (the combination of SAP BASIS patches).  I recommend to use small queues for better control on SAP update.



     k. Remember! If you apply one single patch in the queue it shouldn’t take more than 2 hours! Thus it means that you didn’t follow the instructions preceding patching procedures.

     l. Use ‘Component information’ window from step ‘c’ to make sure the update was successful.

3. Common errors during update and their solution.


     1. =====================================================================================

Problem: Phase CHECK_REQUIREMENTS. The system has found a number of open data
extraction requests. These should be processed before starting the object import process.
- use se38 (report RMCEXCHK) to identify application which cause an error (for instance, let it be 02 (logistics));
- if there are awaiting V3 in SM13, process them all (as a selection criteria, begin from 2000 year);
- clear open extractions in the system (t-code LBWG);
- clean up SMQ1 from the BW system (use t-code SMQ1 to select and delete existing requests for all the clients (*));
- after that se38 shouldn’t show any errors;

     2. =====================================================================================

Problem: "R3TRCLASCL_XXXX was repaired in this system" error message.
It's shown because the object mentioned in the message is locked in a transport request that has not been released yet.
In order to solve the incidence you have to do the following:
SE03 -> Objects  -> Display Repaired Objects   -> click the 'Execute' icon (F8)  -> select the R3TR CLAS CL_XXXX  object by single click on it ->
click the button 'Repair flag' (F5) to reset the repair of this object.

     3. ======================================================================================

Problem: SPAM import finished with the following error.
! There are inactive objects in the system
! Abort the import due to an error situation
This issue arise because of some objects stuck in transport.
a.Enter transaction code: SE10
b.Next, click on Display
c.Locate your request under "Modifiable"
d.Click on the plus + next to the request to expand the node
e.Select the customizing task and then click on transport "Release Directly"
             f. Wait till the customizing task is fully released.
             g. Thereafter, select the main request and click again on the transport "Released Directly".
h.Once the request is fully released, give that number to your basis guys to transport to test system for you.
The object could be released right from the log (Go to -> Status -> Queue). Click on the object name and in the next window click
'activate'. Do not forget to release transport after that as well (in SE10).

     4. =====================================================================================

Problem: SPAM import ended with return code ==>8<== (phase TEST_IMPORT)
SE03 -> Objects -> Display Repaired Objects If R3TRFUGRSZA1 is listed and you are sure it has been released (check with developers if unsure)  then select it and choose the "Repair Flag" button which will reset the repair flag.

     5. =====================================================================================

Problem: SPAM import warning 'PERFORM ADJUSTMENT' SPDD
Right from SPAM go to menu: GoTo -> Statu -> Queue (Click 'Approve adjustment').

     6. =====================================================================================

           Problem: During SPAM package update GUI interface became unavailable
           If you're unable to control package update progress in SPAM or SM37 you can use windows-level tools such as 'dpmon'.
           It can be found in the 'run' directory, i.e. C:\usr\sap\<SID>\SYS\exe\uc\NTAMD64
           Run 'dpmon' in console (cmd), in work processes you can see SAPLSTPA process is still running.
           Wait until it finish background job and SAP GUI will be available again.

In this blog, I am trying to accumulate all the relevant information one should consider in the planning phase for upgrading MSSQL database to 2012.


Supported Environments for Upgrade

Upgrades to products based on SAP NetWeaver 7.1 (including EhP1) and SAP NetWeaver 7.2 are not supported on SQL Server 2012.

Hence, if you plan to upgrade your SAP product based on SAP Netweaver 7.0 (including all EhPs ) on SQL Server 2012, you have to upgrade to a SAP product based on SAP NetWeaver 7.3 and higher.

SAP products prior to SAP NetWeaver 7.0 are not supported at all on SQL Server 2012.


SAP Application pre-requisites for upgrading to MSSQL 2012

Before upgrading any SAP system running on MSSQL (lower than SQL server 2012) to MSSQL 2012, SAP application needs to fulfill certain patch level pre-requisites.

Required minimum SAP Netweaver Support Package Stacks (SPSs) for SQL Server 2012







SAP NetWeaver 7.2 - no restriction




For BI Systems the SAP application pre-requisites are as follows

The minimum required SPSs are the same for SAP BW as for all other SAP NetWeaver products. However, SAP recommends the following newer SPSs when running SAP BW on SQL Server 2012:






SAP NetWeaver 7.2 - no restriction



Note: If your system is running on a SPS lower than the one required above, you have to apply the minimum required SPS before upgrading/migrating to SQL Server 2012.


Platform support

The minimum OS release supported for running on SQL Server 2012 is Windows Server 2008. In particular, SP2 is required for Windows Server 2008, while SP1 is required for Windows Server 2008 R2.

In addition, SAP will only support SQL Server 2012 on x64 (also known as X86_64, AMD64, EMT64).Restrictions:  SAP will not support SQL Server 2012 on Windows IA64 or 32 bit.


Tool used to perform MSSQL 2012 upgrade

To complete the database upgrade, use the latest STM tool to finalize the configuration of your database (see SAP note 683447).
Run the SAP Tools for MS SQL Server on the central instance host. You have to use the SWPM for 7.3+ (SWPM.SAR, not 70SWPM.SAR) in all cases - even when you are performing the task for a SAP NetWeaver 7.0x system (e.g. SAP EHP4 for SAP ERP 6)


Reference Docs/ SAP notes while upgrading database to MSSQL 2012

Note 683447 - SAP Tools for MS SQL Server

Note 1651862 - Release planning for Microsoft SQL Server 2012

Note 1676665 - Setting up Microsoft SQL Server 2012


After performing a system copy, you must perform the post steps but could find the "Generic Installation Options" in SWPM?




Check note 1890950!


Checking SAP Note 1680045, you notice there are two different versions of the SWPM:

  • 70SWPM*.SAR


Checking SAP Note 683447, you can see the tools are only available in the SWPM*.SAR (not 70SWPM*.SAR):

o Run the SAP Tools for MS SQL Server on the central instance host.
You have to use the SWPM for 7.3+ (SWPM.SAR, not 70SWPM.SAR) in
all cases - even when you are performing the task for a SAP
NetWeaver 7.0x system (e.g. SAP EHP4 for SAP ERP 6)


...does not work after a system copy?


Check note 1886616!


SAP Netweaver based systems uses two different variables sets, environment and profile variables.


During a system copy those variables should be updated.


If only the profile variables (<Unit>:\usr\sap\<SID>\SYS\profile\DEFAULT.PFL) are updated, you will be able to start your SAP system but some tools (e.g. tp) which uses the environment variables of your <sid>adm user will not work.


To exemplify:


You have a SAP system ERM located on host VM-MSS-OLD with default database instance, the database variables should be:


  • C:\usr\sap\ERM\SYS\profile\DEFAULT.PFL
    • dbms/type = mss
    • dbs/mss/dbname = ERM
    • dbs/mss/schema = erm
    • dbs/mss/server = VM-MSS-OLD
  • Environment variables of user ermadm:
    • DBMS_TYPE=mss
    • MSSQL_SCHEMA=erm


After a system copy of system ERM to host VM-MSS with default database instance, the database variables should be:


  • C:\usr\sap\ERM\SYS\profile\DEFAULT.PFL
    • dbms/type = mss
    • dbs/mss/dbname = ERM
    • dbs/mss/schema = erm
    • dbs/mss/server = VM-MSS
  • Environment variables of user ermadm:
    • DBMS_TYPE=mss
    • MSSQL_SCHEMA=erm


If a mismatch in the server variable is observed, this could be the cause of the issue:


  • C:\usr\sap\ERM\SYS\profile\DEFAULT.PFL
    • dbms/type = mss
    • dbs/mss/dbname = ERM
    • dbs/mss/schema = erm
    • dbs/mss/server = VM-MSS
  • Environment variables of user ermadm:
    • DBMS_TYPE=mss
    • MSSQL_SCHEMA=erm


If the system <SID> is changed during the system copy, all database variables must be updated.

This variables mismatch might affect other SAP procedures, as other tools (e.g. R3trans) also uses the <sid>adm user variables.


Hope you like this blog and any comments/suggestions are welcome!



Eduardo Rezende



Is this possible database refresh in sand box server from production server backup and different SID if yes please send me link step by step procedure.


Environment detail is below:


SAND Box server    : windows server 2008 R2 , SQL Server 2008 R2 and SAP ECC 6.0 ,  support pkg level 157, kernel release 701


Production Server : windows server 2008 R2 , SQL Server 2008 R2 and SAP ECC 6.0  ,  support pkg level 157, kernel release 701  



your responce is highly appreciation


Thanks in Advance



Recently my client asked me to run a promotion. They were moving code from development to QA and that too in a newly configured server.

The promotion contained certain SQL scripts which queries the Active directory. But since there were no ADSI linked server created and configured, I had to create it.


Sometimes we would need to query the Active directory using SQL query.

The script below will create a linked server and and use OPENQUERY to retrieve data.


1. Create the linked Server -  to create a linked server

          EXEC sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'

2. Add a security Context - create a security context

          EXEC sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'False', @locallogin = 'sa', @rmtuser = '<DOMAIN>\<username>', @rmtpassword = '<password>'

3. Configure the server to allow OPENQUERY functions

--Configure the server to allow OPENQUERY functions

sp_configure 'show advanced options', 1
reconfigure with override

sp_configure 'Ad Hoc Distributed Queries', 1


We can now access Active Directory using a query such as this:

SELECT * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC=<DOMAIN>,DC=com'' WHERE objectCategory=''User'' ')


Hello ,


In layman language, I found MS SQL very user friendly in SAP environment which tackles most of all the database administration things automatically. By bench-marking with SAP with oracle, I got following major positive points, which are being handled automatically without any manual intervention :-


1. We don't need to check the table space .

2. No need to maintain Archive log mode --- ON /OFF  which generally leads to full the archive directory .

3. No need to do statistics update manually .

4. Upgrade is also comparatively easier than oracle.


I have raised all above points based on my perception , so there may be some ambiguities which I would like you to suggest for correction, so that we can get to a bottom line of the topic being discussed over here.


And of course,  there are many advantages are also there for oracle and other databases, but all mentioned points only subjects to past experience. Request you all to update on this to make a valuable blog.


With Thanks,



Filter Blog

By author: By date:
By tag: