16 March

SQL Server

  • zeembe
  • Kommentare deaktiviert für SQL Server

Of the SQL Server from Microsoft is a relational database platform that is used by SAP Business One to store, manage and retrieve operational data. It forms the technical basis for many of the system's central functions.

Use in SAP Business One

  • Storage of all master data, receipts and postings in Company databases
  • Use for and Service Layer interfaces
  • Support up to and including Microsoft SQL Server 2019 (from B1 version 10.0 PL02)

Supported SQL Server versions

  • SQL Server 2008 / 2008 R2 (up to SAP B1 9.3, not recommended due to end of support)
  • SQL Server 2012 / 2014 (compatible with B1 9.x and 10.0)
  • SQL Server 2016 / 2017 / 2019 (from SAP B1 9.3 PL05 and higher)
  • 64-bit versions only Supported as of SQL Server 2016
  • Express Edition Only recommended for the Starter Package (limited scalability)

Database access and query tools

  • Query Generator: Create simple SQL queries at the click of a mouse
  • Query Wizard: Guided creation of queries for end users
  • query manager: Management and execution of stored SQL queries
  • SQL Server Management Studio (SSMS): Direct access to all database objects

Service Layer and SQL

  • As of SAP B1 10.0 PL02, the Service Layer SQL views automatically and makes them available via OData V3/V4
  • From FP 2011 SQL-based database views can also be used without explicit API exposure

Configuration and security

  • Server name must not contain special characters such as &, <, " or _ contain
  • When using non-standardised ports (≠ 1433), this must be specified explicitly, e.g. SERVERNAME,9033
  • SAP user authorisations:
    • db_owner on company database
    • db_datareader and db_datawriter on Common database
  • Recommendation: Deactivation of guest access to msdb
  • sysadmin role required for administrative tasks in SLD/landscape management

Further information

  • Reports can be generated on SQL or Crystal Reports based
  • The setting DbServerType at client start depends on the SQL version (e.g. "11" for SQL Server 2017)
  • For analysis functions, a Correct SQL configuration essential

Note

Choosing the right SQL Server version and configuring it correctly are crucial for the stability, security and performance of SAP Business One. It is recommended that installation and maintenance be carried out by qualified specialists.