BioStar 2 MS SQL version is available starting BioStar 2.4.1. 

You cannot upgrade from an older Maria DB version BioStar 2 to MS SQL database version BioStar 2.
You can only make a new BioStar 2 installation with MS SQL.


Basic installation instructions guide is provided in the following link : Installation Guide

If you run into issues with the installation even after following the installation guide, follow the instructions below.


Caution: 

- MS SQL installation is only recommended to small to medium size businesses (1~100 devices, 1~5000 users). [BioStar 2] System Requirements / Supported Operating System 


- MS SQL has not been optimized for performance. Maria DB BioStar 2 version will perform better especially with larger sites. 

* MSSQL performance has been improved in BioStar 2.6 but Maria DB still performs better. 

* If you use MSSQL Express Database only 1 CPU can be used because its a free version. 


- You cannot restore a backup of a different version of BioStar because the database structure is different for every version. 

If you do proceed to make such restoration, it will cause multiple issues with the server, including large amount of system logs, server crashes, device disconnections. 

i.e: backup of BioStar 2.4 restored in BioStar 2.5, backup of BioStar 2.5 in BioStar 2.4 > all not supported


- When restoring the T&A database, you have to restore the T&A database that was backed up in the same time as the AC database. If the AC and TA databases are backed up and restored in different time periods, it will cause issues with the server. 




Note the additional items that needs installation as shown in the manual. The web client will not operate properly without having this installed: 

If you are using MS SQL Server 2014 Express, install the Service Pack 2 by referring to the following web page.
https://www.microsoft.com/en-us/download/details.aspx?id=53168
If MS SQL Server and BioStar 2 are installed on different PCs, you should install the Native Client on a PC with BioStar 2 installed.
https://www.microsoft.com/en-us/download/details.aspx?id=50402



Note: Do not rely on the database connection feature in the installation phase and the test connection button in BioStar Setting. Both of those features currently do not work in BioStar 2.4.1 because of a bug.

** BioStar setting is fixed in BioStar 2.5


[Highlighted features are not working in Biostar 2.4.1]


Confirm that Two Databases were Created

BioStar AC and BioStar TA databases are two different databases.

Confirm that you created two different databases in MSSQL and gave ownership of that database to your admin user.


Troubleshooting if Only T&A isn’t Working 

If you can log in to Biostar it means your AC connection is working. However if you see an error as below, it means your database server configuration is not allowing you to connect to the T&A module.

[error: localhost denied the connection]

In such cases check that you have configured as below:


1. Set your Host name with only IP.
* you cannot enter the SQL instance name or PC name


2. Click Save.

3. Start the BioStar 2 services again.



Setting the MSSQL Server to allow IP Connections

Refer to the link below to configure your MS SQL Database to allow connection via IP.

If you do not configure as below, IP connection is disabled by default in MS SQL Server.

How to create a SQL server authentication login ID


 

Checking if MSSQL Port is Correct

You can check if you can connect to your database with your MSSQL port and IP by entering the following information on server name:

[server ip]\[server instance name], [port number]


If you can connect with this setting in MS SQL Management Studio, it means that your connection information is correct and working.


Else if it is not working, check your MS SQL port as shown below after you have configured MS SQL to allow IP connection as shown in the previous section.


1. Find SQL Server Configuration Manager in the start menu.

 

2. Expand the SQL Server Network Configuration tab by clicking on the arrow.


3. Click Protocols for SQLSERVER 2012.


4. Double click TCP/IP.


5. Click on the IP Addresses tab.


6. Drag to the bottom to the IPAll tab.


7. Your port is your TCP Dynamic Port or TCP Port if you configured that manually.

 

 

 

 

Checking the Database Instance

 If you have multiple MS SQL versions installed, you might be attempting in to the wrong SQL instance in your SQL Management Studio. You can check what instances you have by clicking on the down arrow on Server name and clicking Browse for more.


 

 

Setting SQL Server Authentication On


If your MSSQL Server is set to only use Windows authentication, it will fail. Check that both your Database and user allows SQL Server authentication mode.


1. Log in to MS SQL Server Management Studio.


2. Right click on your database.


3. Click Properties.


4. Select the Security page.


5. Select SQL Server and Windows Authentication Mode.


6. Click OK.


7. Open SQL Server Configuration Manager.



8. Click On SQL Server Services.

9. Double click your SQL Server instance.

10. On the Log On tab click Restart.

 


11. Log in to MS SQL Management Studio with your SQL Server Authentication.

 

Setting Database Privilege

 

Your account will need DB ownership to access the database.

 

Your account will need DB ownership to access the database.

Right click on your user.


1. Click properties.


2. Select the User Mapping page.


3. Select db owner for both your BioStar 2 databases.


4. Click OK.



If installation still fails with your account after setting this correctly, try using the sa (system admin) account for your credentials.



Resolving account is disabled error 

If you cannot login with your account in MSSQL Management Studio check that your account login is enabled. If it is disabled it will have an arrow pointing down on the user account.



1. Right click on your user.


2. Click properties.


3. Select the Status page.


4. Select Login Enabled.


5. Click OK.


   

 

 

Manually Initializing the Database

 

If your connection information was not correct and the database was not built through the installation process, one way of building the database is installing BioStar 2 again.

However if you have confirmed that your connection information is correct by taking the steps above, you can just run a script without installing again to build the database.


1. Open the folder in the following path:
C:\Program Files\BioStar 2(x64)\dbscript\mssql2.

2. Right click on ac_mssql_init.sql and Open with a text editing tool like notepad.

3. Copy all of the content.


4. Log in to MS SQL Management Studio.


5. Click New Query.


6. On the dropdown box below New Query, select your AC database.



7. Paste the content of the script into the query page.


8. Click Execute.


If you want to build the T&A database as well, follow the same process with ta_mssql_init.sql.


** Another way to import the script is to drag and drop the script file to the query page on MS SQL Management Studio.