Network SQL Express configuration

<< Click to Display Table of Contents >>

Navigation:  Network & Remote Installation >

Network SQL Express configuration

Read through all instructions first before proceeding.

 

Make sure you have the latest Windows operating system service patches installed before proceeding.

 

If you perform the installation via a remote connection and encounter any issues, first always recheck that each step was fully completed successfully before going to the next. If still an issue, perform the installation while physically at the server and redo all steps from scratch again.

 

Your SQL Express version must be 2008 R2 or higher (SQL Express 2008 R2, 2012, 2014, 2016 etc)

 

It is your responsibility to be knowledgeable about SQL databases, especially about how to backup.

 

It is HIGHLY recommended to make yourself aware of any known issues about SQL before proceeding, including those identified in our AyaNova Support Forum

 

 

Use of AyaNova with SQL Express is a 7 step process

 

1.Ensure network uses TCPIP for communication between computers and server

2.Stand-alone AyaNova database, AyaNova program and To SQL utility

3.SQL Express is installed and configured

4.Set exceptions in server's firewall for SQL

5.Creation of SQL Express AyaNova database

6.Configure and confirm access for the AyaNova program on the server to the database

7.Configure and confirm access from network computers to networked database

 

 

 

 

1. Ensure network uses TCPIP for communication between computers and server

 

The server and networked computers will communicate via TCPIP. If your network is not yet configured, do so. If you are not experienced with this, please contact your local computer center.

 

To confirm whether your network can communicate or not, determine the server’s static internal IP address and ping from a local area networked computer

 

a. Open a command prompt on the server (Start -> Run -> type in cmd and click on OK) and type ipconfig in the DOS prompt

 

Config3

 

b. Make a note of your server’s IP Address

 

c. Confirm that network computers can “talk” to the server by opening a command prompt on a local area networked networked computer and typing ping xxx.xxx.xxx.xxx and the Enter key where xxx.xxx.xxx.xxx is the IP address of your server

 

For example, if your server's IP address is 192.168.1.15, you would type ping 192.168.1.15

 

If you get a timeout, there is an issue connecting via TCPIP. Please contact your local computer center to troubleshoot.

 

 

 

2. Stand-alone AyaNova database, AyaNova program and To SQL utility is installed to server

 

a. Install the stand-alone AyaNova database and program to the server's local hard-drive while logged into that server itself. Refer to Stand-alone default AyaNova installation

 

You must also select to install the Install Firebird SQL to Microsoft SQL migration utility as this is the utility to create the AyaNova SQL database ToMSSQL.exe

FireToSQL10

 

Once you have completed all of the network steps, you can remove the AyaNova program from the server itself if you do not want the program files on it and won't be installing any optional add-on's such as Data Portal, Generator, etc. Do note though, that having AyaNova program files on the Firebird Server does not hamper it in any way, and takes up a maximum of 50MB in space; and provides you with a location to confirm your setup with AyaNova Support in the event you experience an issue.

 

b. Once installation is complete, run the AyaNova program and confirm you can log in

 

c. Exit out of the AyaNova program

 

 

 

3. SQL Express installation and configuration

 

a. AyaNova is compatible with SQL Express 2008, 2012, 2014, 2016  - any of these are acceptable. Don't forget to install the SQL Management tools too!

 

Refer to the steps SQL Express example installation which include:

oDownload the full runtime version from Microsoft that includes the Management Studio Express Tools by searching online.

oDuring the installation steps, checkmark to install Mixed mode (Windows Authentication and SQL Server Authentication), and enter a password for user sa

oDuring the installation steps, change the SQL Server Database Engine's Account Name to "NT AUTHORITY\SYSTEM" and SQL Server Browser's Startup Type to Automatic

 

It is HIGHLY recommended to make yourself aware of any known issues about your version of SQL before proceeding, including those identified in our AyaNova Support Forum

 

 

b. After installing, confirm that the TCPIP and Named Pipes protocols are enabled via SQL Server Configuration Manager

 

From your Start menu  bring up the SQL Server Configuration Manager  (for example, if using SQL Express 2008 R2 it would be Start -> All Programs -> Microsoft SQL Server 2008 R2 -> Configuration tools -> SQL Server Configuration Manager)

Expand Protocols for your SQL Express server under SQL Server Network Configuration

Make sure both Named Pipes and TCIP/IP are Enabled.

If they are not, right-click and select Properties to enable.

SQLExpress2

Also remember to enable the server's IP address too

SQLExpress7

 

c. You may also need to configure for remote access

 

Start -> Programs -> Microsoft SQL Server -> Configuration Tools -> Surface Area Configuration Utility

Click on the link for Surface Area Configuration for Services and Connections

SQLExpress3

Select Remote Connections option under the Database Engine for the SQL Express server and make sure Local and Remote Connections is checked with Using TCP/IP Only

SQLExpress4

 

 

d. confirm that your SQL Server services are set to Auto start

 

e. reboot your computer, log in as the computer administrator, and confirm your SQL services have successfully started and are running

 

 

 

4. Set exceptions in server's firewall for SQL

 

If there is a firewall enabled on the "server" computer where the SQL Express configuration is installed and setup,  set exceptions for sqlserver.exe and sqlbrowser.exe, and/or UDP port 1434 and 1433 in some cases

 

For example, if you are using Windows Firewall:

In your Control Panel, open the Windows Firewall applet.

Go to the Exceptions tab.

Click Add Program

Browse to the location of your SQL program folder and select the sqlservr.exe file - such as  "C:\Program Files\Microsoft SQL Server\MSSQL.10\MSSQL\Binn\sqlservr.exe"

Click OK.

Click Add Program

Browse to your SQL folder and select the sqlbrowser.exe file - such as "C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe"

 

If you are using a third-party software firewall, you will need to follow their instructions to add exceptions to sqlservr.exe and sqlbrowser.exe

 

 

 

5. Creation of SQL Express AyaNova database

 

a. Via the server's Start menu, select to run AyaNova -> Utilities -> Firebird to MSSQL

FireToSQL1

 

b. This will open the ToMSSQL utility

FireToSQL2

 

c. You WILL NEED to edit the two fields to what you actually have

 

 

Firebird database connection string:

 

This MUST identify the location of your actual existing AYANOVA.FDB database file.

 

If you installed for standalone use to a Windows 7 or newer computer, the default location and file is C:\ProgramData\AyaNovaData\AYANOVA.FDB

firetosql11

If you installed for standalone use to an old operating systems  such as XP or 2003, the default location and file is C:\Documents and Settings\All Users\Application Data\AyaNovaData\AYANOVA.FDB

FireToSQL3

OPEN YOUR Windows Explorer and actually confirm that YOUR AyaNova database file AYANOVA.FDB is in the path stated before proceeding.

 

 

Whereas if instead you had previously configured AyaNova for network Firebird use but are now converting to SQL, you will need to edit the entire string so that it reflects your networked AyaNova Firebird database such as the example string below:

 

ServerType=0;DataSource=SERVERIPADDRESS_OR_NAME;DataBase=AYANOVA;User=SYSDBA;Password=masterkey;Dialect=3;

 

(where SERVERIPADDRESS_OR_NAME is your network Firebird server's name or ip address, where AYANOVA is the database name you set in the aliases.conf, and where masterkey is the password you set for your Firebird Server)

 

 

 

FireToSQL4

Microsoft SQL database connection string:

 

By default the string will initially say as per below, but you NEED TO edit this string to your SQL Express server settings - with what your actual servername is and what your actual password is.

 

Server=SERVERNAME\SQLExpress;initial catalog=master;User Id=sa; Password=PASSWORD;

 

(where SERVERNAME is your server computer's name, and PASSWORD is your sa password, and note also that the initial catalog is master for this utility)

 

For example, if your SQL Express server is called AYANOVASUPPORT/SQLEXPRESS and your sa username password is letmein, then your string would look like:

 

Server=AYANOVASUPPORT/SQLEXPRESS;initial catalog=master;User Id=sa; Password=letmein;

 

Confirm what the name is of your SQL Express server by running SQL Management Studio as is identified in the Service name: field.

SQLExpressMGMTStudio1

 

 

d. Select Open Database Connections to test your connection strings

FireToSQL5

 

As the message says, it will now confirm connection to the original AyaNova database, and to the SQL server.

FireToSQL6

 

If the connection strings are not correct, a message will appear that you need to correct them - there may be an incorrect text entry in either the Firebird database connection string or the Microsoft SQL database connection string – check your punctuation, your spelling, your database location, your server name, your server password etc and retry

 

e. You will receive a message if connection is successful – select the OK button to proceed to create the SQL AyaNova database.

FireToSQL7

 

FireToSQL8

f. When finished at 100%, select the cancel button to close (the X) – wait until both lines show at 100%

FireToSQL9

 

g. The AyaNova SQL database has now been successfully created

 

 

 

6. Configure and confirm access for the AyaNova program on the server to the database

 

a. Delete the existing config.txt in the C:\Program Files (x86)\Ground Zero Tech-Works Inc\AyaNova (or C:\Program Files\... if 32 bit) folder

 

b. Move to the C:\Program Files (x86)\Ground Zero Tech-Works Inc\AyaNova/examples folder

 

c. Copy the EXAMPLE_SQLEXPRESSconfig.txt file

 

d. Move back to the C:\Program Files (x86)\Ground Zero Tech-Works Inc\AyaNova folder

 

e. Paste in the EXAMPLE_SQLEXPRESSconfig.txt file into the C:\Program Files (x86)\Ground Zero Tech-Works Inc\AyaNova folder

 

f. Rename this file to config.txt

 

g. Edit this new config.txt using NotePad

 

Do not use any other program but NotePad to edit this file

 

Edit Server= to your SQL Express server's name

 

Edit to your sa password

 

SQLExpress5

 

h. Now run the AyaNova program and confirm you can log in.

 

 

 

7. Configure and confirm access from network computers to networked database

 

a. Copy your network SQL Express configured config.txt file from the C:\Program Files (x86)\Ground Zero Tech-Works Inc\AyaNova folder to a shared location that your networked users can access.

 

b. Copy/Move to this same shared folder the AyaNova installation file AyaNovaSetup.exe that you used to install on the server.  Now that you have confirmed it works you can re-use it at all work stations.

 

c. Have each local networked computer run the AyaNovaSetup.exe file from that shared location that also has the edited config.txt in that same shared location.  When a network setup is run it will automatically use the config.txt file it finds in the same folder as the setup program.

 

Make sure the networked computer selects Network administrator setup

Network1

 

Network2

 

If the local networked computer will also run QBI, or PTI or perform an import, select when installing on that computer those optional AyaNova programs. Refer to the QBI Manual or PTI Manual available from the AyaNova download web page if using QBI or PTI. Refer to the appropriate ImportExport.csv utility section in this help file if using one of the import utilities.

 

The AyaNovaSetup.exe will "see" that config.txt file and automatically copy it to that computer's AyaNova program directory so no additional configuration is needed on that user's station.

 

The install will only install the AyaNova program files it has been told to install, will copy that config.txt file to the networked workstations AyaNova program folder and will not install a trial database.

 

The AyaNova installation will place a desktop icon for AyaNova on the desktop as well as in the start menu.

 

You can confirm that AyaNova is configured correctly on the networked computer by logging in on that networked computer, selecting Help -> About AyaNova  so that you know that you are correctly connecting to the networked SQL Express configuration

 

If you do not want your networked users to see the SQL Express sa user's password as it is in their config.txt, you might want to edit the config.txt that you copy to that shared location for networked computers to use integrated security. If so, once you have copied over the config.txt to its shared location and before networked users set up AyaNova using that config.txt, remove the User Id= sa; Password = xxxx; replacing it with integrated security=true;  Of course, do confirm this works as in the screenshot below:

SQLExpress6

 

 

Refer to Network Generator installation steps to install and configure the Event Generator in a network setup if applicable.

 

Refer to the Data Portal installation and configuration steps to install and configure the AyaNova Data Portal if applicable.