How to change the SQL Server connection

Now Assistant provides an easy way to simultaneously operate across multiple servers. In this guide we will show how to connect two separate instances of Now Assistant running on virtual machines to use a single SQL Server database. To simulate a server farm we're going to use VirtualBox to run two Windows Server 2016 virtual machines both running individual Now Assistant instances. For simplicity we'll be referring to the server storing the data as 'parent' and the server forwarding the data as 'child'. Let's begin!

You can find more information about how to set up your server, including guides for installing Now Assistant on SQL Server in our knowledge base.

Connecting the servers

To begin with, we need to ensure that our servers can communicate with each other. We will do that by sending a 'ping' between parent and child servers. In order for this to work our servers need to have separate IP addresses, therefore make sure that if you are on a shared network your servers are allocated individual addresses.

On VirtualBox we recommend to either use a Bridged Network adapter or a NAT Network adapter. More information can be found here.

If we open the Command Prompt and type in ipconfig it will display the network information of our server. We're specifically interested in the IPv4 addresses of both the parent and the child servers. The IP address of the parent server:

The ipconfig of the first server

We'll enter the same command in our child server to get the second address:

The ipconfig of the second server

If we tried to send a ping between the servers it would most likely fail because by default Windows Server Firewall settings have strict rules that deny any incoming or outgoing pings unless explicitly specified otherwise.

Firewall settings

That means that for our 'ping' to go through, we need to temporarily change inbound and outbound connection Firewall rules. Let's open the 'Windows Firewall with Advanced Security' window and enable the rule for 'File and Printer Sharing (Echo Request) in inbound and outbound rules for both servers:

Setting the ping rule for both servers

Now if we ping the servers again we should see that they're successfully coming through:

Pings are successfully received

Establishing the database connection

The next step is to forward our Now Assistant data from child to parent server. The parent server needs to have a Firewall rule allowing incoming database traffic to pass through, thus let's go back to the Firewall settings and add a rule to allow inbound connections on port 1433 (our SQL Server database port):

First we'll click on 'New Rule...':

Add new rule in the Firewall settings

Then, in the opened window we'll select 'Port' rule type:

Select port rule type

And will specify the port 1433 to be allowed on TCP connection:

Set the port settings

Then, we'll allow the connection:

Allow database connection

Finally, you can set the scope of the rule to apply to 'Domain', 'Private' or 'Public'. For this guide we'll allow it apply to all 3 and we'll name our rule 'Microsoft SQL Connection' to uniquely identify it in the rule list.

Testing the database connection

We need to test the database connection to ensure our child server can connect to the database on the parent. The easiest way to do that is to create a Universal Data Link file on the child server. All we need to do is to open the Notepad and save an empty file with the '.udl' extension e.g. 'test.udl'.

Make sure you set save type to 'All files' if you're using Notepad

Now if we open the saved file it should show a dialog box for the Data Link properties. Here we'll enter the access credentials for the parent's database, such as its IP address, username, password and the name of the database:

Use the same credentials you used when setting up the SQL Server.

Data Link properties credentials

Click 'Test Connection' to see if the connection succeeds.

Forwarding the Database data

Now we're ready to forward any changes made in Now Assistant running on the child server to be stored in the parent server's database. Luckily, with Now Assistant it only takes a minute to accomplish. Let's go to the Now Assistant installation directory on our child server and navigate to Web → App_Data → Sites → Default

Here we'll edit the 'DataConnectionString' in the 'Settings.txt' file to have the same access credentials that we tested earlier:

Database access credentials in the settings file

We'll save the settings file, which should make the child Now Assistant instance store data on our parent database. Our task is complete!

If you wish to test that it works correctly try editing the title of a component in Now Assistant on the child. The same changes should be visible on the parent.