Bosch Building Technologies

    cancel
    Showing results for 
    Search instead for 
    Did you mean: 

    Can I move the BVMS Logbook database to a separate Microsoft SQL Server?

    100% helpful (2/2)

    Question

     

    Can I move the BVMS Logbook database to a separate Microsoft SQL Server to ease maintenance or increase the size of the Logbook?

     

    Answer

     

    The BVMS Logbook database can be moved to another SQL Server.

    This article describes the steps that are necessary to migrate the database and describes how to confirm if the migration was successful.

     

    BVMS is using Microsoft SQL Server Express to store its logbook. Based on the information provided by Microsoft (Compare SQL Server 2017 editions), SQL Server Express is limited to a 10GB database size.

    This could be insufficient for larger installations or installations that require a very long logbook retention time. Additionally, some organizations have a dedicated SQL server environment which should be utilized by all applications.
    The BVMS Logbook database can be moved to another SQL Server. This guide describes the steps that are necessary to migrate the database and describes how to confirm if the migration was successful.

     

    Knowledge
    This guide assumes the new database server is installed and up and running. Bosch strongly recommends that an experienced database administrator executes the migration. Experience with Microsoft SQL Server Management Studio is required. This guide does not include troubleshooting of the migration process (it is expected the database administrator is able to handle this) nor does it include any performance optimizations of the SQL server. This guide assumes the SQL server "sa" (Server Administrator) account is available for administrative purposes on the used SQL server.

    Support
    The migration process itself is not supported by the Bosch technical support teams. Once the database is migrated, the technical support teams will provide support. An experienced database administrator should be able to assist the technical support team in case the system experiences issues related to the logbook.

     

    Version

    This guide is based on Microsoft Windows Server 2016 (fully updated on 2018-09-28) and Microsoft SQL Server 2017 (version 14.0.2002.14). The SQL Server should be installed in "Mixed mode" authentication (having an "sa" account) and this guide assumes an instance name "BVMS_External".

     

    1. Preparing the SQL server

    In the first step, the new SQL server is prepared for remote connections. This section assumes the SQL server has just been installed and the configuration is fresh. The instance name used in this guide is "MSSQLSERVER".

     

    2. SQL server settings

    The SQL server can be configured using the SQL Server configuration manager, which can be started using the Windows start menu.

    Central_Support_0-1629984905071.png

    In this step, the TCP/IP protocol will be enabled for the network adapter which will be used to communicate with the BVMS management server. Browse to the SQL Server Network Configuration, Protocols for MSSQLSERVER.

    Open the properties window of the TCP/IP protocol.

    Central_Support_1-1629984936515.png

    Go to the IP Addresses tab in the properties window, and find the network adapter (search for the correct IP address) which should be used for communication with the BVMS management server. Enable the SQL Service on this adapter by changing the Enabled option to Yes.

    Central_Support_2-1629984978537.png

     

    Restart the SQL Server (MSSQLSERVER) in order to apply the changes.

    Central_Support_3-1629985012434.png

     

    3. Firewall settings

    In order to allow the SQL Server to communicate with the BVMS management server, the firewall needs to be adjusted.
    Open the Windows Firewall with Advanced Security and create a new firewall rule, as shown in the image below.

    Central_Support_4-1629985045628.png

    Create a new inbound rule for ports.

    Central_Support_5-1629985121362.png

    The SQL Server communicates based on TCP port 1433. Fill in the form as shown in the image below.

    Central_Support_6-1629985155522.png

    Move through the action (grant access) step and apply the rule for all firewall profiles (private, public, domain). Give the firewall rule an appropriate name.

    Central_Support_7-1629985191618.png

    In order to increase the security level, the rule can be adjusted in order to only allow communication between the SQL Server and the BVMS Management server. Open the properties dialogue of the firewall rule, go to the Scope tab, and add the remote IP address of the BVMS Management server (the example below shows 192.168.20.151 as the BVMS Management server IP address.

    Central_Support_8-1629985229222.png

    In order to test the rule, the Microsoft SQL Server Management Studio should be able to login to the SQL server from the BVMS Management server using the "sa" account.

     

    4. Moving data

     

    Now that the SQL server has been prepared, the logbook database will be moved from the BVMS Management server to the SQL Server.

     

    4.1. Stop BVMS SQL server instance

    Open the SQL Server Configuration Manager on the BVMS Management server and Stop the SQL Server (BVMS) process.

    Central_Support_9-1629985272669.png

     

    4.2 Moving data

    The logbook database is located in C:\ProgramData\Bosch\VMS\DB on the BVMS Management server. Copy the BVMSLogbook.mdf and BVMSLogbook.ldf (using a USB stick or using the network) to the SQL server.

    Central_Support_10-1629985306596.png

    The files should be copied to the 

    • C:\Program Files\Microsoft SQL Server\MSSQL.14.BVMS_External\MSSQL\DATA folder.

    Central_Support_11-1629985366434.png

    This folder location has the right permissions for the SQL Server to adjust these files. If another location is chosen, the database administrator needs to ensure the SQL server has the correct rights

    Central_Support_12-1629985407029.png

     

    4.3 Attaching database

    After the database files have been moved, they need to be attached to the SQL Server process.

    Login to the SQL Server using Microsoft SQL Server Management Studio on the BVMS Management server. Right click the Database folder and Attach a new database to the SQL Server.

    Central_Support_13-1629985437527.png

    Browse to the C:\Program Files\Microsoft SQL Server\MSSQL.14.BVMS_External\MSSQL\DATA and select the BVMSLogbook database file.

    Central_Support_14-1629985477028.png

    The database should be attached to the SQL Server and it should appear in the tree.

     

    4.4 Set the database size

    By default, the size of the BVMS logbook has been fixed to 4GB. To allow the logbook to grow, the size of the database needs to be changed. Right click the BVMSLogbook database in the SQL Server Management Studio and click properties.

    Central_Support_15-1629985530478.png

    Go to the Files page and change the Initial Size of the data and log files as required.

    Central_Support_16-1629985597640.png

    Microsoft Support pages

    Central_Support_17-1629985630716.png

     
    4.5 Confirming data migration

    In order to confirm the data migration, the content of the database can be checked by executing a SELECT query on the data. Expand the BVMSLogbook database, go into the Tables folder, right click on the dbo.Devices and click on "Select Top 1000 Rows".

    Central_Support_18-1629985697916.png

    The result should show devices recognized from the BVMS configuration, as shown below.

    Central_Support_19-1629985740226.png

     

    5. SQL Server security

     

    It is not recommended to use the "sa" account for BVMS to login to the SQL Server. In this section a separate SQL Server user account will be created.

     

    5.1. Create user

    Browse to the Security - Login folder using the Microsoft SQL Server Management Studio. Create a new login as shown in the image below.

    Central_Support_20-1629985777371.png

    Fill the form with an appropriate user-name and password, deselect Enforce password expiration (in order to prevent the BVMS Management server to lose access once the password has expired) and select the default database (BVMSLogbook).

    Central_Support_21-1629985809503.png

    Go to the User Mapping tab, map the user to the BVMSLogbook database and enrol the user as db_datareader, db_datawriter and db_owner to the database.

    Central_Support_22-1629985839549.png

     

    Finish by clicking OK.

     

    5.2 Verify user

    Logout of the Microsoft SQL Server Management Studio. Login using the newly created user and verify if the user has access to the database.

    Central_Support_23-1629985875427.png

    The result should show devices recognized from the BVMS configuration, as shown below.

    Central_Support_24-1629985914278.png

     

    6. BVMS settings

     

    Now that the database is configured and the access is tested, BVMS needs to be re-directed to the new location of the logbook database.

     

    6.1 Change SQL connection string

    The SQL Connection string can be configured in the Options dialogue.

    Central_Support_25-1629985950329.png

    Change the SQL Server connection string based on the format and example below

    Data Source=YourDBServerAddress;Initial Catalog=DatabaseName;Persist Security Info=True;User
    ID=YourUserID;Password=YourPassword
    Data Source=192.168.20.161;Initial Catalog=BVMSLogbook;Persist Security Info=True;User
    ID=BVMS;Password=Bosch123

     

    Central_Support_26-1629985988342.png

    Restart the BVMS Management server after the configuration has been saved and activated.

     

    6.2 Verify logbook connectivity

    The last step is to verify the logbook migration in the BVMS Operator Client. Open the logbook search window, as shown in the image below.

    Central_Support_27-1629986025114.png

    When the migration has succeeded, the logbook should contain a historical overview of events, dated from before the migration of the database.

    Central_Support_28-1629986057028.png

     

    7. Upgrading BVMS

    Do not de-install SQL Server Express from the BVMS Management Server. This might cause the BVMS set-up to fail, when it used to upgrade the Management Server. The SQL Server Express services might be stopped during normal operations, but need to be started and running during the upgrade.

     

    When a new BVMS release is created changed to the database schema might have been made. Normally, when using the embedded SQL Server, the database scheme is automatically upgraded . As the database is moved to a different server, the set-up mechanism cannot realize an upgrade of the schema of the remote database.

    To allow system administrators to upgrade the database schema manually, the DbLogBookMigrator has been created. This tool can be found in the C:\Program Files\Bosch\VMS\bin directory (DBLogbookMigrator.exe). Start the tool, enter the SQL Server connection string (as entered in section 4.1) and start the migration process. The progress log will show an indication of the result.

    Central_Support_29-1629986122939.png

     

    8. Troubleshooting

     

    If the migration has failed the BVMS Operator Client will report it is not able to access the database. This could have several root-causes.

    The connectivity between the SQL Server and BVMS Management server can be tested using general network troubleshooting tools, like ping (take care of the firewall configuration!). Additionally the firewall can be temporarily disabled to verify if it has been configured correctly. The Microsoft SQL Server Management Studio should be able to login from the BVMS Management server to the SQL Server. Last, the user account which has been created should have the correct roles on the database.

    Version history
    Revision #:
    2 of 2
    Last update:
    3 weeks ago
    Updated by:
     
    Labels (6)
    Attachments
    Icon--AD-black-48x48Icon--address-consumer-data-black-48x48Icon--appointment-black-48x48Icon--back-left-black-48x48Icon--calendar-black-48x48Icon--center-alignedIcon--Checkbox-checkIcon--clock-black-48x48Icon--close-black-48x48Icon--compare-black-48x48Icon--confirmation-black-48x48Icon--dealer-details-black-48x48Icon--delete-black-48x48Icon--delivery-black-48x48Icon--down-black-48x48Icon--download-black-48x48Ic-OverlayAlertIcon--externallink-black-48x48Icon-Filledforward-right_adjustedIcon--grid-view-black-48x48IC_gd_Check-Circle170821_Icons_Community170823_Bosch_Icons170823_Bosch_Icons170821_Icons_CommunityIC-logout170821_Icons_Community170825_Bosch_Icons170821_Icons_CommunityIC-shopping-cart2170821_Icons_CommunityIC-upIC_UserIcon--imageIcon--info-i-black-48x48Icon--left-alignedIcon--Less-minimize-black-48x48Icon-FilledIcon--List-Check-grennIcon--List-Check-blackIcon--List-Cross-blackIcon--list-view-mobile-black-48x48Icon--list-view-black-48x48Icon--More-Maximize-black-48x48Icon--my-product-black-48x48Icon--newsletter-black-48x48Icon--payment-black-48x48Icon--print-black-48x48Icon--promotion-black-48x48Icon--registration-black-48x48Icon--Reset-black-48x48Icon--right-alignedshare-circle1Icon--share-black-48x48Icon--shopping-bag-black-48x48Icon-shopping-cartIcon--start-play-black-48x48Icon--store-locator-black-48x48Ic-OverlayAlertIcon--summary-black-48x48tumblrIcon-FilledvineIc-OverlayAlertwhishlist