MS SQL Installation Guide

Microsoft SQL Database For Debtors Manager

Table of contents

1. Overview 2. Minimum server installation requirements for MS SQL 3. Installing MS SQL on your server 4. Installing Debtors Manager SQL version on your workstation 5. Connecting your workstation to the SQL server over the network

1. Overview

The MS SQL database version of Debtors Manager is identical in terms of functionality to the Access Version.

NOTE: MS (Microsoft) SQL database is NOT the same as Pervasive SQL database!

MS SQL is provided for users that have a large number of debtors / high volume of invoice transactions. If you are experiencing slow program refresh speed, or database corruption in Access, the solution is to install the MS SQL database.

MS SQL is a proven solution that is both fast and stable when dealing with large databases.

If you are currently on the Access version and wish to upgrade, the upgrade is free. Insert your Sage Pastel CD, and re-install Debtors Manager, go through the installation Wizard, and select MS SQL database.

2. Minimum server installation requirements for MS SQL • Server Hardware:

Minimum: Pentium III processor or faster, 2.0Ghz RAM: 4 GB

• Server Software: Windows Server 2003 SP2 Standard (or higher) Windows Server 2003 R2 SP2 Standard

Windows Vista SP2 Business Windows Server 2008 SP2 Standard Server

NB: If the PC used as a server does not run the above software, but uses regular desktop software instead (such as XP Pro or Windows 7 Pro) the security protocols for seeing the data across the network are more difficult. You must not use the Windows Security across the network – ensure that you select the option to use MS SQL security with the SQL username sa and password Debtorsmanager@123.

3. Installing MS SQL onto your server It is highly recommended that your MS SQL installation is performed by IT personnel with MS SQL installation experience. This will save you time and unnecessary frustration, and get you up and running smoothly.

TIP: What if MS SQL is already installed for a different application? – make sure that you know the MS SQL username and password that was used when SQL was installed. If you do not know the password, then you will end up having a successful installation on the server, but you will not be able to access the data over the network from a desktop PC! If the password is lost, you can install a new instance of SQL that runs alongside the existing SQL version. SQL is specifically designed for this and there is no conflict in this situation. Alternatively, if the existing version of SQL is inactive, you can uninstall and reinstall SQL, and the default user name and password will be applied (user name sa and Password Debtorsmanager@123).

Notes on the installation process:

• The MS SQL database is installed ONLY on the server. • The MS SQL database is not installed on the workstation.

IMPORTANT: install Debtors Manager SQL version in this sequence: MS SQL must be installed on the server before you install the Debtors Manager SQL version on your workstations.

1.

2.

3.

4.

5.

• When you click through to the install wizard, the screen will display for Mixed mode authentication.

Mixed mode authentication allows the user to select data security options for the flow of data over your company network. The install wizard is preloaded with a default user name and password.

• If you change the default SQL password, be very sure to store it in your password vault, so that it

can be retrieved as required. If you forget the password, any changes required at a later date will not be possible, and you will need to create a new SQL instance.

1. Click on Browse for SQL Server – 2. Default SQL server name: SERVERNAME\DEBTORSMANAGER 3. Default SQL Login ID: sa 4. Default SQL Password: Debtorsmanager@123

NOTE: IF SQL HAS BEEN INSTALLED ON YOUR SERVER PREVIOUSLY: In certain environments it may be necessary to re-start your SQL Server Browser settings. Use Microsoft SQL Server Configuration Manager to do this. Instructions below:

1. Click on Start | All Programs

2. Once the Debtors Manager SQL installation wizard has completed, you will see the Debtors Manager database login screen:

Step One: Browse for the SQL Server Click Browse – “Processing data now” will display. The system is now searching for your SQL Server. Step Two: When the “Processing data now” message disappears, click on the drop down for “Server” A list of severs will display. Select “YOUR SERVERNAME\DEBTORSMANAGER”

On the server, you can use either Windows Security, or SQL security. If you tick the box that says “Use Windows Security” the SQL user name and Password field will be greyed out. Depending on the settings in your server environment, either Windows Security or SQL security will be accepted on the server in order to create a new database. If one security option does not work, try the other option.

NOTE: when you install Debtors Manager on the workstation, do not re-install MS SQL – simply browse for the SQL server (or type in the SQL server name), and use SQL server authentication. Please see Section 4 below for detailed instructions).

Step Three:

Create a New Database The first time you install MS SQL it will be necessary to create a new MS SQL database to store your Debtors Manager information.

NB: Do not click on “New Database” until you have entered your SQL User name and Password (or you have ticked the box “Use Windows Security”.

Naming Protocol for your New Database Use CAPITAL letters, and do not leave a space between letters, and do not use a fullstop “.” between the letters. Example - correct: ABC2012 Example - incorrect: ABC.2012 or ABC 2012

Enter the name of your database, click OK, and you return to this screen:

Once you have created a new Database, Click on the drop down.

NB: Click on the dropdown once only, and then … wait! Your system will take a few moments to populate the database name in the drop down field, and your database name will then display. Click OK and the next screen will pop up asking you to Browse for your Pastel Company.

1.

2.

Username: sa Password: Debtorsmanager@123 Step 4: Link the SQL database to the data-source – i.e. the Pastel Company.

Once selected, the company name will populate as shown:

Click OK to pull data through from Pastel into the SQL database:

Data is being pulled through now. Depending on the number of debtors and the volume of transactions in the Pastel transactions file, and the processing speed of your computer environment, you may need to wait a couple of minutes.

Once data processing is complete, the Age Analysis will display. You have successfully installed Debtors Manager SQL version on the Server.

The next step – the SQL version of Debtors Manager must be installed on all workstations that need to run Debtors Manager. 4. Installing Debtors Manager SQL version on the Workstation Step One: Run your Pastel CD at the workstation and select Debtors Manager.

• If you previously ran the Access database version of Debtors Manager (or Cash Manager) on your workstation, you must uninstall Debtors Manager prior to installing the Debtors Manager SQL version. (Go to Control Panel - Add/Remove Programs)

• Your Access data will remain accessible once you reinstall SQL – you will be able to run a data

conversion wizard. See the relevant section below:

Run through the Debtors Manager install wizard:

NB: On the Workstation, do NOT re-install MS SQL.

NB: Select Browse to search for the MS SQL Server that you have already installed on the Server.

Step Two:

On the Workstation, you must use SQL Authentication to connect the workstation to the Server to access the SQL database.

Enter the User name and Password

a) Click on Browse for SQL Server – b) Default SQL server name SERVERNAME\DEBTORSMANAGER c) Default SQL Login ID: sa d) Default SQL Password: Debtorsmanager@123

4. Connecting the Workstation to SQL Server over the network

Step One: Enter Username and Password which MUST match the Username and Password that was used to setup SQL on the Server. Step Two: Click on database drop down to display your database (that you created on the Server). Select it.

NB: Click the database dropdown once, and then wait. Your system will take a few moments to populate the drop down. Click - wait - be patient, and your database name will display.

If MS SQL displays a “Connection closed” error message:

1. Your user name / password may be incorrect, or

2. Your connection to the server may be blocked by Firewall or Anti-Virus.

Disable the firewall and try again. It may help to restart the server to re-establish the connection which may happen if your SQL engine is not set to run.

NOTE: If you are running Windows XP as your server software you may encounter connection difficulties across the network. Microsoft did not design Windows XP to be used with SQL Server. SQL does work correctly with Windows XP Server – however it will take someone with experience to open the connection between Workstation and Server). If you run Windows XP on your desktop, there is no difficulty connecting to a Server that runs Windows Business 2003 (SP2) or Windows 7 Server software.

Once the Workstation is connected with the SQL Server, the Age Analysis will display. Congratulations, you have successfully installed the SQL database version of Pastel Debtors Manager.

Mapped Network Drives: You may need to re-set the SQL Server path if your network runs on a Mapped Drive. Debtors Manager will “remember” the path that is entered. If different users are on different mapped drives, Debtors Manager will keep updating according to the last mapped drive that has been entered. It can cause confusion – therefore ensure all users use the same mapped drive path.

In certain environments it may be necessary to re-start your SQL Server Browser settings. Use Microsoft SQL Server Configuration Manager.

Click on Start |All Programs: