Skip to Content

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.

Alerts

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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply