If an Online Workspace is not suitable and you require multiple users operating in the same Calxa workspace, then you should consider an SQL Server database.
For more details on the different workspace types and what will best suit your requirements please refer to the Help Article Which Workspace Type Should I Use?
Calxa can take advantage of Microsoft’s SQL Server database engine to create a robust multi-user workspace. By installing the free SQL Server Express (or the full version) you will get full concurrent user access in your local network environment. SQL Server needs to be installed separately and then Calxa can store its data in a SQL Server database.
Using SQL Server as your Calxa data source requires planning and some technical expertise so you may want to involve your IT department or support person. See also the detailed training video above.
As outlined below, there are 4 general steps to creating and configuring a network database. Follow the links for more detail on each step.
- Install SQL Server
- Configure Installed Server for Remote Access
- Create Calxa Database - Note: the database must be created in Calxa
- Create and Configure SQL Logins
Install SQL Server
In this help note we will be installing SQL Server 2014 Express, which you can download for free from https://www.microsoft.com/en-au/download/details.aspx?id=42299, however all SQL Server versions from 2005 through 2016 are supported. Calxa does its own testing using SQL Server 2012 and 2014.
Microsoft’s SQL Server 2014 Express installation instructions can be found at: http://social.technet.microsoft.com/wiki/contents/articles/23878.installing-sql-server-2014-step-by-step-tutorial.aspx and https://msdn.microsoft.com/en-us/library/ms143219(v=sql.120).aspx
Steps to Install
- Download the SQL Server installation media from the official Microsoft download page:
We recommend you choose to install SQLEXPRWT to install as this version includes SQL Server Management Studio to assist with configuration. Use x64 for 64-bit operating systems, x86 for 32-bit operating systems.
- Run the downloaded SQLEXPRWT install file.
Choose Directory for Extracted Files will pop up. You can accept the default path as this is a temporary location only. Click Ok.
- After the files are extracted the SQL Server Installation Center will open. Click on New SQL Server stand-alone installation or add features to an existing installation.
- Installation Type: choose Perform a new installation of SQL Server 2014 and click Next.
- Licence Terms: you have to accept the license terms by checking the box shown below to continue. The option to ‘Turn on Customer Experience Improvement Program and Error Reporting’ to Microsoft is optional. Click Next.
- Feature Selection: generally the default settings on this screen are fine. Ensure that the Management Tools are being installed, as these will be used to configure users later on in the installation. Click Next.
- Instance Configuration: select Named instance. Type Calxa in the box next to it to name the SQL server Calxa (you can use another name if preferred). Click Next.
Remember the name of the instance! You will need this name every time you want to connect to the server. Note: server instance names cannot begin with a number.
- Server Configuration: it is fine to use the default settings, however we will change the Startup Type to Disabled for the SQL Server Browser here in order to illustrate how to turn it on using the SQL Configuration Manager. This way you’ll know where the settings are should you ever need to troubleshoot or reconfigure. The browser service helps find SQL Server instances while browsing your network. Click Next.
Note: You can also use specific user accounts to run the SQL Server services, but in this example we will use the default setting.
- Database Engine Configuration: choose Mixed Mode for authentication.
Windows authentication mode is also fine and is what we recommend in terms of authenticating Calxa users in a domain environment, however by choosing Mixed Mode you can specify a sa, or SQL Server administrator account and password. This setup is advisable in case the windows user who installed the server leaves your organisation or for whatever reason you lose admin access to the server. If you are working in a non-domain environment it will be easier to work with SQL users.
With Mixed Mode selected you’ll just need to set and confirm a sa (server administrator) password. You can also specify additional Windows users to be SQL Server administrators, but the user installing the server will be added by default.
Use default settings on the other tabs (Data Directories, User Instances, FILESTREAM). Click Next.
- The installation may take a few minutes to complete, when finished the Complete box will come up. If all has gone well you will see green check marks next to the features. Click Close.
Configure Installed Server for Remote Access
If you are using Calxa on a different machine and will be connecting to the SQL Server across your network, you will need to enable networking protocols using the SQL Server Configuration Manager.
Note: in all examples shown we will use the default server instance name of CALXA.
- Launch the SQL Server Configuration Manager from installed programs (installed in steps above).
- Click on the SQL Server Network Configuration. Here you’ll see protocols for all the installed instances.
- Select the appropriate instance, in this example it is CALXA.
- Double-click on TCP/IP and change 'Disabled' to ‘Enabled’. You could also connect using the Named Pipes protocol, but in this help note we will describe connection using TCP/IP.
- Click on SQL Server Services.
- Click on SQL Server Browser. Right-click your mouse and select Properties.
- Now go to the Service tab. Set the Start Mode to Automatic and click OK.
- Now you’ll need to restart the SQL Server service to ensure all the changes have been applied. Click on SQL Server (CALXA), right-click your mouse and select Restart from the menu.
Note: If you call your server instance something other than CALXA, you would select the instance called SQL Server (your instance name).
SQL Server Configuration is now complete. The next step is to create your Calxa database.
Create Calxa Database
Note: Do Not create the database in SQL Server Management Studio, it must be created in Calxa.
- Log in to Windows with the same user id that configured the SQL Server. This is important because we haven’t configured any other users yet, so if you try to run Calxa using some other log in you may get connection problems to the SQL Server.
- Run Calxa by clicking the Calxa icon on your desktop. You need to be at the Open or create Calxa Workspace screen for the next step. If this screen is not what you see because you’ve used Calxa before, click on the Calxa menu in the upper left corner of your screen and then select Authentication.
- On the Open or Create Calxa Workspace screen, select Network database from the list of Workspaces (left side of screen).
- Enter your Server Name. If you know your server name you can enter it directly. Enter your computer machine name followed by a back slash (\) and your SQL Server instance name. In this example, the computer name is PC05-CALXA, and the SQL Server instance name is CALXA. You can also click on the drop-down menu to browse all available SQL servers on your network and select your server from the list.
Enter a Database name by typing it directly in the field (do not use the drop-down menu).
- You will get a message asking if you want to create a new database with this name. Click Yes.
Calxa will create the database as long as the Windows user has the appropriate SQL Server permissions.
- Click Sign In.
If you have connection errors at this point you should revisit the previous section on configuring for network access and also ensure there is no firewall blocking the communication. See our help note Troubleshooting Remote Connection to SQL Server Database for more information.
- Select Create organisation to create or copy your organisation.
- Use the following help notes as guides for creating or copying your organisation data.
Create and Configure SQL Logins
Once you have created a Calxa database in your SQL Server instance you can create SQL logins for your users and set their permissions for the Calxa database. We recommend using Windows Authentication in a domain environment and these instructions will illustrate Windows Authentication. If required, you can set up logins with SQL Authentication. For details on using SQL Authentication please refer to the help note Connect to a Network Database Using SQL Authentication.
- Open SQL Server Management Studio from installed programs (installed in first section).
Log in to the appropriate server using either Windows Authentication or SQL Server Authentication (for SQL Server Authentication use sa user name and password).
- Expand the Security folder and select Logins. Right-click mouse and select New Login.
- Next to Login name field click Search. Leave authentication set to Windows.
- Ensure the Object Types and Location fields are set to the appropriate type and location for your search.
Note: You may use individual Windows users or Domain Security Groups to create your SQL Logins. It is preferable to use Domain Security Groups because it enables you to manage permissions for all users in one place, and new users can just be added to the Security Group rather than needing to manage them in SQL Management Studio. Choosing to set up by specific domain user means that each individual user’s login will have to be configured separately.
If you are going to use Domain Security Groups, click on Object Types and check off Groups.
Then click on Locations and change the search scope to Entire Directory. This will allow you to search for your Domain group.
Type the domain group name in the object name field (in this case Calxa), then click Check Names to search.
Select the group you want to use and click OK. Then click OK again.
- Now we will set the permissions for this user (or domain group in this case). Select Server Roles.
As a minimum all users will need to have public access. If you want users to be able to restore Calxa backups they will also need dbcreator access. Sysadmin would give them full control of the SQL server including more control when restoring backups. Most users will need only public and dbcreator access. Once the server roles are selected click OK.
- Now switch to User Mapping, which is where you’ll grant specific permissions to the Calxa database.
Tick the check box next to the Calxa database to map this user to this database (it’s called Test Calxa database in this example).
In the Database role membership section you should select the db_owner role. This will provide full control of the Calxa database, which is required so users can perform all database operations including database upgrades if a Calxa software update requires it.
Note: the public role will be selected as well by default.
Note: If you have given a user Sysadmin access via Server Roles you won’t need to set any permissions on the User Mapping screen.
- The setup is now complete. You have installed an SQL server, created a Calxa database and configured the users to connect to it.
Each user should now be able to run Calxa. Via the Open or create Calxa Workspace screen browse and select the appropriate Server name by clicking the drop-down arrow. Next browse for the Calxa Database and click Sign In.
If you have any problems with the steps throughout, please feel free to contact Calxa Customer Support on 1800 733 249 AU or 0800 086 614 NZ, alternatively you can email us at firstname.lastname@example.org. We will be happy to assist with your configuration or troubleshoot any problems you encounter.