Single Server Deployment

Setup the SQL Database

To be able to work with SQL Database Engine, your login should be a member of the sysadmin fixed server role. Members of this role can perform any activity in the Database Engine. By default, all members of the Windows BUILTIN\Administrators group (the local administrators group) are members of the sysadmin fixed server role.

 

• Open SQL Management Studio and connect to the SQL server instance where you want to install the database;
• Expand the SQL server instance in the object explorer, click right on the 'Databases' node and select 'Restore database';
  
   
  Beginning the Restoration of the Database
   
  
• Enter a database name in the 'Restore Database' window (we recommend customername_projectname_dev) and select the Sana Commerce SQL backup file in the 'Source for restore > From device' section from the temporary folder where the release of the Sana Commerce solution is stored. Go to the 'Database\Full database' directory to locate the SQL backup file: 'SanaCommerce_Sql_Provider.bak' in case you install Sana Commerce - SQL Provider and 'SanaCommerce_Live_Nav_Connector.bak' - if you install Sana Commerce - NAV Connector;
• Check the 'Restore' check box;
  
   
  Selecting the Sana Commerce Database for Restore Operation 
  
  
• Go to the 'Options' page and set the destination of the new database and the .log file by clicking the button next to the 'Restore As' column (we recommend to choose the SQL Server 'database' directory: 'Microsoft SQL Server\MSSQL.1\MSSQL\Data');
  
   
  Specifying Files for the Restored Database 
  
  
• Give the appropriate names to the database and .log files (their extensions should be '.mdf' and '.ldf' respectively);
  
   
  Saving the Database and .log Files  
• 
  
• Check the 'Overwrite the existing database' check box in the 'Restore options' section;
• Click 'OK' to create the database.
  
   
  Successful Restoration of the Database 

  

Since the Sana Commerce database has a limited size (5MB out of the box), we advise to set the Recovery model to 'Full' ('Database Properties > 'Options' page > Recovery model' dropdown) to guarantee the integrity of your data.

There are two ways to make sure the database is accessible from the Sana Commerce webshop and backoffice:

Use SQL Authentication
If you are going to use this type of authentication please check if it is enabled in SQL Server. Click right on the SQL server instance and select 'Properties > Security'. Make sure that the 'SQL Server and Windows Authentication mode' is selected in the 'Server authentication' section: 

  
Enabling the SQL Server and Windows Authentication Mode 

SQL Server password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When the check box 'Enforce password policy' is checked new passwords must meet the following requirements: The password does not contain all or a 'part' of the user's account name. Part of an account name is defined as three or more consecutive alpha-numeric characters delimited on both ends by 'white space' (space, tab, return) or any of the following characters: , . - _ # The password is at least six characters long The password contains characters from three of the following four categories: English uppercase letters (A - Z) English lowercase letters (a - z) Base 10 digits (0 - 9) Non-alphanumeric symbols (For example: !, $, #, or %) Password expiration policies are used to manage the lifespan of a password. When the check box 'Enforce password policy' is checked, users are reminded to change old passwords and accounts with expired passwords are disabled. When the checkbox 'User must change password at next login' is checked, the SQL Server will prompt the user for a new password the first time the new login is used. Please note that password policies for SQL logins require Windows Server 2003 or later versions. If Sana Commerce project is installed on a workstation with Windows XP, you need to uncheck the 'Enforce password policy' check box.

              

• Go to the 'User Mapping' page, check the 'Map' check box in the Sana Commerce database and check the 'db_owner' role in the membership role list;
  
   
  Specifying the Database and Roles for New Login 
  
  
• Click the 'OK' button to finish the setup.

Use Windows authentication

• Open the SQL server instance > Security, click right on the 'Logins' folder and select 'New login' (if the windows user already has rights to the SQL server instance, please select the properties of this user instead of creating a new one);
• Enter the windows username in the 'Login name' textbox. You can  use the search option to lookup users (e.g. in Active Directory);
• Select the newly created Sana Commerce database as the Default database;
  
   
  Selecting the 'Windows Authentication' Option 
  
  
• Go to the user mappings option, check the Map check box on the Sana Commerce database and check the db_owner role in the membership role list;
  
   
  Specifying the Database and Roles for New Login 
  
  
• Click the 'OK' button to finish the setup.

You also need to add the SC_Nasuser to the SQL database and grant him the 'db_owner' role to your NAV SQL database (use the steps described earlier). This is done in order to give the SC_Nasuser the ability to access the NAV database when NAS service will be started (described later).