Managing SAP Business One on Microsoft SQL Server
Introduction
SAP Business One is an application that sits on a database engine. The supported databases are Microsoft SQL Server, IBM DB2 and Sybase. Clearly, SAP's relationship with Microsoft is not juvenile. It dates back to the early 90s. It is a debatable fact(if you like) that most SAP Business One system sits on Microsoft SQL Server (henceforth referred to as MSSQL Server). On a broader view, an estimated 46,000 SAP application installations run on Microsoft Windows®. This can be attributed to the "Microsoft feel" concept - simplicity of usage! With the latest offering of Microsoft's SQL Server solution: Microsoft SQL Server 2005, the system is well poised to compete with any database system in terms of functionalities.
MSSQL Server offers uparalleled benefits such as high scalabilty, high avaialability, support for large databases, ease of installation, high productivity development environment - SDK, and ultimately low total cost of ownership, especially when bundled with SAP Business One.
It is my intent to make this posting in phases. In this posting, I shall be covering three topics as it relates to managing SAP Business One on MSSQL Server. They include Installation of SAP Business One on Microsoft SQL Server , Backup and Recovery and Security. In subsequent posting, I'd be exploring other manageability features of MSSQL Server for SAP Business One.
Installation
As asserted in the opening paragraph, SAP Business One sits on a database system, hence, it can be deduced that a database system like MSSQL Server must be installed prior to installing SAP Business One. The supported platforms for SAP and MSSQL Server includes 32-bit, x64 and Itanium 64. Typically, SAP Business One supports two editions of MSSQL Server namely, enterprise edition and standard edition. The installation of SAP Business One based MSSQL Server 2005 can be done implicitly or explicitly. During implicit installation, no user input is required while for explicit installation, the user has to enter installation settings. Talking about implicit installation, a number of default settings are used. These default settings are copied from the local system or the installation CD. Worthy of note is the authentication mode selected. Windows and SQL Server authentication are used with the "B1Admin" password for the "sa" usercode.
At this junction, it is expedient to say that, these default settings can be changed using the service manager or the SQL Server management studio. Also, NTFS file system is recommended for the installation of MSSQL Server. This is because it is more stable than the FAT 32 file system. It is important to state that the set-up files created during the installation process should be securely archived because it contains information about the system configurations registered during installation.
Backup and Recovery
A laudable and tested backup and recovery strategy must be adopted to guarantee access to the copy of a company database at a point in time incase of data loss. This is so important because data loss of some sort is inevitable in a production environment. There are two ways of performing backup in an SAP Business One system. The first option is to use the backup tools available in MSSQL Server via the SQL Analyzer or the Enterprise Manager. The second option is to use SAP Business One generic backup service. The difference between the duo is that while the latter tool backups the database and other directories like Microsoft word and Bitmaps, the former only backs up the database. This represents a performance benefit over the MSSQL Server backup tool. Also worthy of note, is the fact that, the SAP Business One backup service uses a temporary folder for database backup from where the backups are transferred to a permanent directory.
Aside the backup of the normal company databases, transaction logs backup is recommended. This is because it allows you to perform point-in-time recovery. Because transaction logs grows fast and may affect system performance, it is good idea to backup transaction logs regularly or shrink them. Also, the SBO Common database should be backed up before and after maintenance activities such as installation and upgrades.
The restore process involves copying a database that was backed up on another database or same database. Typically, the essence of backup is to be able to restore in case of system crash or breakdown. This task at the moment is only possible within the MSSQL Server. The enterprise manager offers good flexibility for achieving this task with ease. Additionally flexibility is offered because the system does not distinguish between a backup that was made in SAP Business One and that made in MSSQL Server as it were for the restoration process.
Security
Security is one concept that can't be compromised no matter what. Securing fat client - two tier architecture based system can be a daunting task. This is because the client performs data manipulation and onward transfer to the database server, thus, a single point of attack. The database server is where all company data reside, hence it is important to guide it as a jewel (of which it is).
Two database types resides on the SAP Business One server. They are the company database and the common database. The common database contains objects that are needed for the system initialization of company databases. Aside the functionalities of SAP Business One, MSSQL Server provides additional security offerings. A complex password for the "sa" user account offers unparalleled authentication and authorization at the database level. Leveraging the trusted connection functionality is laudable especially as it guarantees security at the user level. In like manner, Microsoft SSL encryption offers security for TCP/IP connection, hence securing the networks.
Depending on the job functions of users, it is important to create user accounts with database creators role and db_owner access privilege for the SBO_Common database and the company database. Because MSSQL Server supports many multiple company databases and application, it is important to adhere to the following recommendations.
1. Create and clearly distinguish between regular user account and super user account. While the normal user account allows maintenance activities to be carried on a single company, the super user account allows cross company maintenance and management activities
2. Deactivate data access rights for guest on the MSDB database
3. Ensure that you create SBO_SP_Role in every database. This role permits execution of SBO stored procedures in a particular company.
4. Audit user access to the MSSQL server at regular intervals.
5. Leverage the Microsoft Baseline Security Analyzer to detect weak configuration on your MSSQL Server.
SAP Business One is an application that sits on a database engine. The supported databases are Microsoft SQL Server, IBM DB2 and Sybase. Clearly, SAP's relationship with Microsoft is not juvenile. It dates back to the early 90s. It is a debatable fact(if you like) that most SAP Business One system sits on Microsoft SQL Server (henceforth referred to as MSSQL Server). On a broader view, an estimated 46,000 SAP application installations run on Microsoft Windows®. This can be attributed to the "Microsoft feel" concept - simplicity of usage! With the latest offering of Microsoft's SQL Server solution: Microsoft SQL Server 2005, the system is well poised to compete with any database system in terms of functionalities.
MSSQL Server offers uparalleled benefits such as high scalabilty, high avaialability, support for large databases, ease of installation, high productivity development environment - SDK, and ultimately low total cost of ownership, especially when bundled with SAP Business One.
It is my intent to make this posting in phases. In this posting, I shall be covering three topics as it relates to managing SAP Business One on MSSQL Server. They include Installation of SAP Business One on Microsoft SQL Server , Backup and Recovery and Security. In subsequent posting, I'd be exploring other manageability features of MSSQL Server for SAP Business One.
Installation
As asserted in the opening paragraph, SAP Business One sits on a database system, hence, it can be deduced that a database system like MSSQL Server must be installed prior to installing SAP Business One. The supported platforms for SAP and MSSQL Server includes 32-bit, x64 and Itanium 64. Typically, SAP Business One supports two editions of MSSQL Server namely, enterprise edition and standard edition. The installation of SAP Business One based MSSQL Server 2005 can be done implicitly or explicitly. During implicit installation, no user input is required while for explicit installation, the user has to enter installation settings. Talking about implicit installation, a number of default settings are used. These default settings are copied from the local system or the installation CD. Worthy of note is the authentication mode selected. Windows and SQL Server authentication are used with the "B1Admin" password for the "sa" usercode.
At this junction, it is expedient to say that, these default settings can be changed using the service manager or the SQL Server management studio. Also, NTFS file system is recommended for the installation of MSSQL Server. This is because it is more stable than the FAT 32 file system. It is important to state that the set-up files created during the installation process should be securely archived because it contains information about the system configurations registered during installation.
Backup and Recovery
A laudable and tested backup and recovery strategy must be adopted to guarantee access to the copy of a company database at a point in time incase of data loss. This is so important because data loss of some sort is inevitable in a production environment. There are two ways of performing backup in an SAP Business One system. The first option is to use the backup tools available in MSSQL Server via the SQL Analyzer or the Enterprise Manager. The second option is to use SAP Business One generic backup service. The difference between the duo is that while the latter tool backups the database and other directories like Microsoft word and Bitmaps, the former only backs up the database. This represents a performance benefit over the MSSQL Server backup tool. Also worthy of note, is the fact that, the SAP Business One backup service uses a temporary folder for database backup from where the backups are transferred to a permanent directory.
Aside the backup of the normal company databases, transaction logs backup is recommended. This is because it allows you to perform point-in-time recovery. Because transaction logs grows fast and may affect system performance, it is good idea to backup transaction logs regularly or shrink them. Also, the SBO Common database should be backed up before and after maintenance activities such as installation and upgrades.
The restore process involves copying a database that was backed up on another database or same database. Typically, the essence of backup is to be able to restore in case of system crash or breakdown. This task at the moment is only possible within the MSSQL Server. The enterprise manager offers good flexibility for achieving this task with ease. Additionally flexibility is offered because the system does not distinguish between a backup that was made in SAP Business One and that made in MSSQL Server as it were for the restoration process.
Security
Security is one concept that can't be compromised no matter what. Securing fat client - two tier architecture based system can be a daunting task. This is because the client performs data manipulation and onward transfer to the database server, thus, a single point of attack. The database server is where all company data reside, hence it is important to guide it as a jewel (of which it is).
Two database types resides on the SAP Business One server. They are the company database and the common database. The common database contains objects that are needed for the system initialization of company databases. Aside the functionalities of SAP Business One, MSSQL Server provides additional security offerings. A complex password for the "sa" user account offers unparalleled authentication and authorization at the database level. Leveraging the trusted connection functionality is laudable especially as it guarantees security at the user level. In like manner, Microsoft SSL encryption offers security for TCP/IP connection, hence securing the networks.
Depending on the job functions of users, it is important to create user accounts with database creators role and db_owner access privilege for the SBO_Common database and the company database. Because MSSQL Server supports many multiple company databases and application, it is important to adhere to the following recommendations.
1. Create and clearly distinguish between regular user account and super user account. While the normal user account allows maintenance activities to be carried on a single company, the super user account allows cross company maintenance and management activities
2. Deactivate data access rights for guest on the MSDB database
3. Ensure that you create SBO_SP_Role in every database. This role permits execution of SBO stored procedures in a particular company.
4. Audit user access to the MSSQL server at regular intervals.
5. Leverage the Microsoft Baseline Security Analyzer to detect weak configuration on your MSSQL Server.
posted by Kehinde Disu @ 4:53 PM
0 Comments:
Post a Comment
<< Home