Evopos Help - 2.09.078

Problems with SQL Server

Problems with SQL Server

Previous topic Next topic  

Problems with SQL Server

Previous topic Next topic JavaScript is required for the print function  

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.

 

General issues

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:

http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/SQLServer2005_XMO_x64.msi

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:

USE master
GO
ALTER DATABASE EvoposMain SET EMERGENCY
GO
DBCC CHECKDB EvoposMain
GO
ALTER DATABASE EvoposMain SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB EvoposMain REPAIR_ALLOW_DATA_LOSS)
GO
(may have to run the above a couple of times)
ALTER DATABASE EvoposMain SET MULTI_USER
GO

 

 

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‘