SQL Server is normally very reliable. However setting up SQL Server initially can be problematical because of the large amount of configuration options, security issues, different SQL versions, and different OS versions.
•If you have problems connecting to the database please also see: Problems Connecting to the database
•If you have problems restoring a backup please see: Problems Restoring Data
•It is often useful to connect to the database using Microsoft MS SQL Management Studio (MSSMS). This is a free utility to view and maintain SQL databases. You can also backup, restore and create new databases and users . This literally takes Evopos out of the equation. Note you also have the ability to connect using Windows Authentication in case you are unsure of the connection details
•Make sure your drive has not been compressed – MS SQL Server will not work on a compressed drive.
•On some Windows configurations you may have to install Windows Power Shell before Management Studio will install (especially XP). You can download from Microsoft or our Portal
•Check that the SQL Server services are running - Sometimes the SQL Services do not start automatically after a restart (such as a Windows Update). Check by selecting Control Panel / Administration / Services and make sure the SQL Server <InstanceName> and SQL Browser services are running. Sometimes you need to stop and restart the SQL Services. Sometimes you need to shut down the computer completely, re-start the computer and then re-start the services.
•If SQL Server was installed directly (not from within Evopos) then you will normally need to manually configure the Instance, Users and other settings such as remote connections and Complex Password Policy.
•Make sure you are running Microsoft SQL Server 2005 SP3 or later. By default Evopos will attempt to install Microsoft SQL Server 2008 on an initial installation.
•If you are setting up on a Microsoft Small Business Server then a version of SQL server may be already installed. Later versions of Microsoft SQL Server (SQL 2012, SQL 2014) often have settings such as 'Complex Password Policy' set as default.
•Password Complexity - Later Servers and OS may be configured to only accept secure passwords In these cases you may either need to disable security complexity rules or change the configuration temporally to enable you to install and then change the password using Group Policy Editor. You can run Microoft SQL Management Studio and login using Windows authentication to change passwords etc.
•Restricted Access - Make sure the current Windows User is set as an Administrator. Also you may have to set Evopos to be run in Administrator mode by right clicking the Desktop shortcut and select Run as Administrator. You may also need to turn User Account Control off by selecting Control Panel, then Users
•Avoid the use of Defrag - We have had problems after running defrag on a drive running SQL Server. Sometime it is ok after shutting down and re-starting
•If running on Small Business Server (SBS2003) make sure it has SP1 - SBS2003 requires SP1 to run SQL2005 or SQL2005Express. This is a large update that requires 5 different service pack components to be installed in the correct order (1.Server2003, 2.SharepointServices, 3.Exchange, 4.XPSp2Client, 5.SBS2003).
•If running Windows 8 or 10, it may say the version of SQL is incompatible, if so just select to run it anyway.
•You will normally need about 5gb of free memory on the C: drive of the server which can be a problem on older Servers (especially older Dells) which often only had a 20gb partition allocated for the operating system files.
•If you get a message similar to 'Database not found' - This can happen if the database files are deleted or corrupted (especially after removing and re-installing SQL Server). To correct remove and recreate the BBS instance as detailed in the next section
•On some computer configurations (especially Windows Vista), Evopos will only connect to the database if the database in in the 'Not named' or default "SQLExpress" instance. To set this up run \SQLExpress\setup.exe and create a default Not Named instance (or make sure one already exists), then connect leaving the instance field blank, then create (Restore) the data from a template / backup
•Remove and Re-create the BBS instance of SQL Server - Run the SQL setup program either from the Control panel / Add or remove programs or from the c:\SQL2005Express folder. First remove the BBS instance. Then delete the previous BBS data files called BBSMain.mdf and BBSMain_Log.ldf. The location can vary but it is normally "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data". Then run the setup again and create the BBS instance. Create a different Instance of SQL Server -
•You can also use Microsoft SQL Server Management Studio to manually create databases, backup, restore, change passwords and perform maintenance tasks. Running SQL Server Setup Manually - If you have a problem setting up the SQL Server from within Data Admin, or you wish to use a different version of SQL Server, or if you need to specify a different instance or password, then you may need to run the SQL Server setup manually. Setting up SQL Server manually normally requires several settings to be set manually (See Connecting Evopos to a database sections above).
•Final resort - We have had rare situations where the last resort to get SQL Server working was to completely re-install Windows. Sometimes you can remove all SQL instances and Databases, remove all folders (eg: C:\Program files\Microsoft SQL Server) and even delete SQL Database entries in the registry and then re-install MS SQL. WARNING These last steps should only be performed by experienced users. Please ensure you have copies of all programs and data if you may still need a complete re-install of Windows.
•An alternative way to get Evopos to prompt for the connection details when starting is to delete the registry entry manually (Please take care and backup registry first). The registry key to delete is: HKEY_Local_Machine\Software\Evopos\Main. On a 64 bit version of Windows the resistry key is: HKEY_Local_Machine\Software\WOW6432Node\Evopos\Main
Fixing problems and performance issues with the Database
There are SQL Commands that can fix up various issues with the database
•DBCC CHECKDB - Checks the database and displays any problems
•DBCC DBREINDEX (<Table>, '', 70) - Re-indexes specified <Table> in the current database
•DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS - Must open the database in single user mode for this (see below)
To put a database in Single user Mode:
ALTER DATABASE EvoposMain SET SINGLE_USER WITH ROLLBACK IMMEDIATE
To put a database back into Multi-user Mode:
ALTER DATABASE EvoposMain SET MULTI_USER
Issues with 64Bit Versions
New computers frequently come with 64 bit version of Windows. This is ok, and later versions of Evopos come with SQL 2008 which is better for 64 bit versions and is required for Windows 8 or later.
However, if using SQL2005 Server we need the 64 bit option of XMO for SQL 2005 Server before we create and restore the database. This is normally included in the older \BBSCD\Evopos folder. You can also download from:
Once MS SQL2005 Setup has run successfully and you have run this XMO update you can re-start Evopos and create the database. Note: Sometimes the MS SQL 2005 Server install can fail the first time you run, but will normally work if you run it again. You may need to reboot the computer and re-try.
Note: You cannot convert data from Business Manager data using Evopos Data Admin on a 64 bit computer as the dBase drivers are not supported. Please use a computer with a 32 bit operating system. Or Customer Services can convert for you.
Setting up SQL Server manually
To create or modify an Instance we need to run the SQL Server Installation.
This will require the SQLServer setup file (you may need to download the appropriate version of Sql Server Express from Microsoft (or our Portal). It is normally best to get the one with Tools as this contains SQL Management Studio
When you run SQL Server setup it will first check for any pre-requisites it needs
You can then Create a new Instance or make changes to an existing Instance. will be prompted to enter the new Instance name and a passcode (will normally enter it under the user name of: sa).
After you have created a new instance you will normally need to configure SQL Server using SQL Configuration manager to use IP Ports dynamically
•Select Start / Programs / Microsoft SQL Server / Configuration tools / SQL Configuration manager.
•On the left hand window expand the SQL Server Network configuration option, select Protocols for the Instance, right click TCP/IP and select Properties. From the Properties window select the IP Addresses tab and for IPALL, make the TCP Dynamic Ports blank (instead of 0), and set the TCP Port to 1433.
•You will then have to stop and restart the SQL Server by selecting SQL Server Services from the left hand window, right click SQL Server (BBS), select Restart.
•On SQL 2005 you may also need to Configuring SQL Server for remote access from the Surface Area Configuration Utility
Using Microsoft SQL Management Studio to create Databases / Users
•Open SSMS on the server. Select Start / Programs / Microsoft SQL Server / SQL Management Studio
•Login using SQL Authentication, or use Windows Authentication if you are not sure of the connection details
•Right Click "Databases" from the object explorer on the left and click "New Database" Enter database name (Normally EvoposMain) and select Ok
•Right Click "Secuirty" then select "New Login". Confirm Login Name, select "Sql Server Authentication", confirm password, and ensure "Enforce Password Policy" is unchecked. Select "OK" when completed
•Navigate back to "Databases >> <YourNewDB> >>Security". Right click the "Users" node and select "New User", Confirm the User Name / Login Name . Then in the "Database Role Membership" table - tick the db_Owner option. Click OK!
•Populate the database by restoring a backup of the database structure (and / or data). See Problems Restoring Data
Note: It may be advantageous to disable the default 'sa' user name as Login attacks are becoming more frequent and they normally use this user name.
Fixing a database marked as 'Suspect'
This is not a common issue. To fix start Management Studio. Expand the Management node and view the latest SQL Server Error Log and check for errors logged for the database which is marked as suspect. Then run the following commands under New Query:
ALTER DATABASE EvoposMain SET EMERGENCY
DBCC CHECKDB EvoposMain
ALTER DATABASE EvoposMain SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB EvoposMain REPAIR_ALLOW_DATA_LOSS)
(may have to run the above a couple of times)
ALTER DATABASE EvoposMain SET MULTI_USER
Disabling password complexity requirements on Windows Server
•If there is a domain controller.
oGo to Administrative Tools / Domain Security Policy :: Security Settings | Account Policies | Password Policy
oEnsure "Minimum password length" and "Password must meet complexity requirements" set to "Not Defined" state.
•Go to Administrative Tools / Local Security Policy :: Security Settings | Account Policies | Password Policy
•Turn "Minimum password length" to zero and "Password must meet complexity requirements" to "Disable"
•Go to SQL Installation window and press "Retry".
•After installation successfully done, turn changed security settings from steps 1-4 back.
Please also see: ‘General - Installing Evopos - Connecting to the database’ and ‘General - Troubleshooting - Problems connecting to the database‘