Step 1: Install SQL Server
AgWare MGR requires the installation of SQL server, and both SQL Server 2022 and 2025 are supported. If you need to run an older version of SQL, please contact AgWare so that we can discuss it. We do not have any specific server requirements outside of the standard Microsoft minimum server requirements which you can find here. The expected size of the database is less than 2 GB, so the server should have at least that much space plus room for your backup plan.
There are three options for installing SQL server:
- Use an existing or new full version of SQL Server. MGR does not require its own server. If you have an existing SQL Server, you can add a new database to that server. The cost of the MGR software does not cover any license fees for Microsoft SQL Server. Those will need to be purchased directly from Microsoft, or one of the free options needs to be used.
- SQL Server Express – Microsoft provides a free version of SQL Server called SQL Server Express. You can download SQL Server Express here. It is free for databases up to 10GB in size, which is much larger than MGR users should need.
- SQL Server LocalDB. LocalDB is a free, non-server, single user version of SQL Server. It can be used when a single user is running MGR on a single computer. This will not work on network drives and cannot be shared with other users. The main advantage is that no security setup is required. To install LocalDB use the same link for SQL Server Express.
When installing SQL Server, we also recommend installing SQL Server Management Studio. This is a free tool from Microsoft that can be downloaded here. This will be needed to create a new database and configure the security for the database. Other third-party tools are also available for these tasks. This is only needed on the server and any IT admin computers.
Client computers will also need the SQL Server ODBC Driver version 18 installed. You can download that from Microsoft here. In almost all cases, you will install the (x64) version of the driver. This is needed on every computer that has the MGR client software installed.
Step 2: Create a Blank SQL Database
Once you have SQL server and SQL Server Management Studio (SSMS) installed, you will create a new blank database. The SQL Server requirements help article outlines the steps for installing SQL server and SSMS.
Using SSMS, create a new blank database. This can be on an existing server or a new one. The name of the database doesn’t really matter as long as you can distinguish it from other databases that may be on the server. We see ‘Farm’, ‘MGR’, ‘MGRFarm’, and ‘<CompanyName>Farm’ used most often. The default database settings will all work with MGR. The only items you may want to change is what folder you want the data to be in and where backups should be placed.
The user that creates the database will be the database owner. We need at least one database owner to create the database as well as converting the Access data and creating the mgr-sql link file. This can be done by any user’s accounts. Be aware that the AgWare tools will need to be run as this user. You can either:
- install MGR on the server and run as the same user OR
- install MGR on IT admin computers and ensure that the admin’s desktop user is a db_owner of the new database.
Here is a detailed article from MS SQL Tips on how to create a database.
Step 3: Add and Fill Tables
After your blank SQL database has been created, you will need to add and fill the database tables.
You will need to make sure the latest client is downloaded and installed from our website. It needs to be on a computer that has access to both the new SQL Server database and the existing Access database.
First, you will launch the MGR program. Once the login screen is visible, you will hit the ‘F8’ key on your keyboard to launch the database maintenance tool. The new version will have a number of database updates that have not been applied yet. You’ll start by ensuring that the backup database option is checked and click the ‘Run Database Updates’ button.
Next, you’ll go to the database tools tab on the left and click the ‘compact and repair database’ button. If the delete MSysCompactError Table becomes enabled, please call AgWare at 605-791-4872 for assistance and do not continue with the conversion.
Next, you’ll click on the last tab in the list ‘Convert to SQL’. Make sure that you are on the ‘connect’ tab and enter the server name and database name. Then, click connect. If you are successfully connected, you will automatically be moved to the next tab ‘initialize database’ and you should see a message similar to the one shown in the screenshot below. If it is unsuccessful please double check the server name and database name and try again.
Now you will need to initialize the database. First, make sure that the current table count shows zero. Then, click the initialize database. This will create the needed tables. Once it is successful, you will see a message similar to the screen below and be moved to the third tab – convert database.
Lastly, you’ll click the start conversion button on the convert database tab. This will load the data in the currently open Access database into the SQL Server database that you entered on the connect tab. This process may take several hours depending on the size of your existing database and the network speed between MGR and the SQL Server. You will see several progress bars and information messages as the conversion is being run. This is copying the data from the Access database into the SQL database. No changes are being made to your Access database. Once the conversion is complete, you will see a message that says ‘Database Transfer Complete Finished Access –> SQL’ similar to the image below.
Next, you’ll move on to create the mgr-sql link file.
Step 4: Setup mgr-sql link file
MGR requires an mgr-sql configuration file that contains the server and database information in order to connect to the server. After the database has been set up, you will need to create your mgr-sql file.
The main login screen contains a hidden area below the the database that houses configuration options. To access this, you will hold the ctrl key on your keyboard and click the AgWare logo on the login screen. You’ll see two tabs – Access Database and SQL Database. On the SQL Database tab, click on the Create SQL Server Link button.
The create SQL server link button will pop up a new window where you will enter the server and database name and choose your type of authentication. If you cannot use “Trusted Connection”, please contact AgWare. You will most likely need to also check the “Trust Server Certificate” option. Please see Microsoft’s documentation for more information on that. Once the details are entered, you will click the “Test Database” button to test the connection to the database. You will hopefully see a message that the connection was successfully established. If you don’t, double check your server name and database name.
Then, use the “Save As…” button to save your mgr-sql file to the same folder as your current Access Database (if you are a previous Access user, otherwise save this on a file share that each user will have access to). Do not put this in a different location or you will lose your custom reports, transaction attachments, and logo. The network share where these items are stored will continue to be used, and the location of the share will be determined by the physical location of the mgr-sql file.
After the mgr-sql file is created and saved, make sure the login page is pointed to your new mgr-sql file before continuing. You can use the change button if it is not pointed to the correct location.
Then, click login. If the connection is not correctly configured, you will see an error message in a tooltip. This will be a message directly from the SQL Server driver similar to the message shown below:
Reasons the login can fail include:
- the server name or database name is wrong, or the network is incorrectly configured to allow access to the server
- the user is not mapped to a SQL server login
- the user has a login but is not mapped to a database user with db_owner permissions
Next, you’ll map the other user accounts.
Step 5: Map other user accounts
First, you will setup the user in SQL Server Management Studio (or another third-party tool you are using to manage the database).
Add the login mapping the user to your active directory domain. SQL Server accounts are also supported. Here is some additional information about logins versus SQL server accounts. Next, you’ll need to map the login to the new MGR database and give the user three permissions: 1) db_datareader 2) db_datawriter and 3) public.
Now, you’ll go back into the MGR program to the Setup, Maintain User Accounts section. For each user in the drop down at the top of the screen, you’ll map them to their SQL login. Start by selecting the first user, and click on the ‘Map User to SQL Login’ button on the toolbar. This will pop up a window with a list of SQL users – choose the one that matches the current MGR user and click OK. You’ll repeat this for each user in the program.
If you are setting up a brand new user that does not exist in MGR first, you will still add them through SQL first and then when you click the Add New User button in MGR, the select database user pop up will appear for you. Once you click OK, the username and first name fields will be defaulted to the SQL user and you can continue with the user setup.
