i4connected Knowledgebase

Configuring the SQL Database

Abstract

The following article follows the steps required to properly set up the SQL database for using it with the i4connected portal or server.

The following article follows the steps required to properly set up the SQL database for using it with the i4connected portal and/or server.

The configuration of the database consists in restoring the SQL database and Analysis Services database backups; once the databases are restored, the connection must be restored between them. Other access and security settings will be made in order to ensure a proper functionality.

The SQL database backup (.bak) and the Analysis Services database backup (.abf) files are required in order to proceed with this guide. Because there is no default place where the database backups are located, both database and Analysis Services backup files should be requested from the development team.

Restoring the SQL database:

  1. Copy the SQL database backup file (.bak) to Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup (where MSSQLSERVER - marked with italic fonts - is the SQL instance name).

  2. Open the SQL Server Management Studio and connect to the Database Engine server.

    Clipboard01.jpg
  3. Right-click on the Databases to open the contextual menu. Select Restore Database... .

    Clipboard01.jpg
  4. In the Restore Database dialog, select Device and browse for the backup (.bak) file.

    Clipboard01.jpg
  5. In the Select backup devices dialog, make sure that the Backup media type is set to File and click Add to browse for the backup file.

    Clipboard01.jpg
  6. The Locate Backup File dialog will point you to the exact location of the backup file (copied earlier to the backup folder of the SQL Server). Select the backup file and press OK. Confirm the Select backup devices dialog also. If the dialog doesn't point directly to the backup file location, use the left-side tree structure to locate the backup file on your hard drive.

    Capture976.jpg
  7. Back in the Restore Database window, another option is needed if the backup to be restored will overwrite an existing database:

    Select the Options page from the left-side menu and enable the Overwrite the existing database (WITH REPLACE) option.

    Clipboard01.jpg
  8. Click OK to start the restore process. A dialog will confirm the success of the restore operation.

    Restoring the Analysis Services database:

  9. Copy the Analysis Database backup file (.abf) to Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Backup (where MSSQLSERVER - marked with italic fonts - is the SQL instance name).

  10. In Microsoft SQL Server Management Studio, connect to the Analysis Services server by clicking Connect > Analysis Services... in the Object Explorer.

    Clipboard01.jpg
  11. Select the Databases from the Microsoft Analysis Server and right-click on it to open the contextual menu. Select Restore... .

    Capture979.jpg
  12. In the Restore Database dialog, go to Restore Source Backup file and click the Browse button.

    Capture980.jpg
  13. In the Locate Database Files dialog, the Analysis Services backup file should be located under the first displayed option (note that the path displayed is exactly the location where we have previously copied the .abf backup file). Select the backup file and press OK.

    Capture981.jpg
  14. Back in the Restore Database dialog, enable the Allow database overwrite option and make sure to uncheck the Overwrite security information option.

    The Overwrite security information option becomes available only after activating the Allow database overwrite option.

    Capture982.jpg
  15. Select OK to start the restore process.

    Connecting the Analysis Service database to the SQL database:

  16. Go to the Analysis Services database > Data Sources > connection string (named WEBfactory.DWH.Data.Database in our example).

    Connecting the Analysis Service database to the SQL database:

    Capture983.jpg
  17. In the Data Source Properties, Select the Connection String and press the browse (...) button to open the Connection Manager.

    Capture984.jpg
  18. In the Connection Manager window, test the connection by pressing the Test Connection button from the bottom-left corner. The Server name should be correct by default. Otherwise, type the correct server name.

    Best practice: use SQL Server Authentication in order to avoid security issues. This is not required but recommended.

    Capture985.jpg
  19. Confirm the dialog after the connection test succeeds.

Setting up security

  1. In the Object Explorer, go to SQL Server > Security > Logins. Right-click on Logins and select New Login.

    Capture986.jpg
  2. In the Login - New dialog, press the Search... button next to the Login name field to search for a specific login name.

    Capture987.jpg
  3. In the Select User of Group window, go to Enter the object name to select (examples): field and write NT Service\MSSQLServerOLAPService name and press Check Names.

    Capture988.jpg
  4. After pressing the Check Names button, the name will be resolved. Confirm the action by pressing OK.

  5. Next, in the Login - New window, go to Server Roles and mark the sysadmin role from the Server roles list.

    Capture990.jpg
  6. Next, go to User Mappings and map the user to the database by checking the box corresponding to the SQL database name. Click OK to confirm and close the window.

    Capture991.jpg
  7. In the Object Explorer, right-click on the Microsoft Analysis Server and select Properties.

  8. In the Analysis Server Properties window, go to Security and add a new Server administrator.

    Capture992.jpg
  9. In the Select User of Group window, go to Enter the object name to select (examples): and enter Everyone. Press the Check Names button. Press OK to confirm.

    Capture993.jpg
  10. The final step is the testing. In the Object Explorer, go to Microsoft Analysis Server > Databases > databaseName (where databaseName is the name of your database) and open the contextual menu. Select Process.

    Capture994.jpg
  11. In the Process Database window, press OK to start processing the selected analysis database.

    Capture995.jpg