How Do I Install SQL Server 2014 for DiscoveryQuant™ 3.0

Date:	12/22/2015
Categories:	Pharma CRO , DiscoveryQuant Software

1 Votes

Print Rate Article:

For research use only. Not for use in diagnostic procedures.
Answer

Download the Express Version of SQL Server 2014 With Tools from here …. Pick the one appropriate to your server architecture
Run the software by double click. Choose a directory for the software to extract to. The default directory is good for this.

Wait while the extractor extracts the contents

Once complete it will open the SQL Server Installation Center. Click New SQL Server ……

Tick the I accept the license terms checkbox and click ‘Next >’

In the Feature Selection Window leave the standard settings and click ‘Next >’

In the Instance Configuration window you should give your instance a Name (in this case I have used DISCOVERYQUANT30 but the name is not important (as long as you know what it is) You can see on this machine I already have a different version of SQLServer installed (in the table at the bottom) this is not required for this install.

Click Next and then Next again on the server Configuration Page.
The Database Engine Configuration page has some options which are specific to your organisation.

Section A relates to the Authentication or how access is granted to the server. What you choose here very much depends on your type of network. If you have an Active Directory network with your instruments attached and you want to manage the permissions to upload and read data using your domain active directory then Windows authentication mode may be quick and easy for you to setup. However this setting will rely on help from your active directory administrators in order to maintain. If you do not meet all of the criteria above or you do not know we would suggest choosing Mixed Mode in Section A. Once you choose Mixed mode you will be asked to enter a password for the (sa) user. This is like an Administrator Account for the database. In this instance I have entered “!DQO30”.

Section B Defines the additional administrator users for the system. Unless your IT dept has a specific requirement for this leave it as default (the installing user account). Once Complete Click ‘Next >’

The system will now install your SQL Server.

Once your install has completed you can Click ‘Close’.

And then close SQL Server Installation Center. Your SQL Server is now installed.
Go to Start > All Programs > Microsoft SQL Server 2014 > Configuration Tools > SQL Server 2014 Configuration Manager

Go to SQL Server Network Configuration > Protocols for <SQL INSTANCE NAME> (I Used DISCOVERYQUANT30 in step 7) and then choose TCP/IP from the center area. Double click on TCP/IP to open up the properties

Set Enabled to Yes and click ‘Apply’.

Click OK to Close the Warning and OK to close the properties window.

Back in the Configuration Manager Select ‘SQK Server Services in the left Panel and then select SQL Server (<Instance Name>) with a right click and select restart.

The service will restart.

Once completed you can close the Sql Server Configuration Manager.

Go to Start > All Programs > Microsoft SQL Server 2014 > SQL Server 2014 Management Studio.

Your Server name: should be <SERVERNAME>\<SQL INSTANCE NAME> (I Used DISCOVERYQUANT30 in step 7). In this case Authentication can be done by Windows Authentication as we added the user as an Admin in step 9 Section B.

Download the Script to Create the Database from Here. And then open it using SQL Server Management Studio using File > Open > File …

This will open what looks like a code window. This is the SQL code used to make the Database for DiscoveryQuant.

There are 2 Files that are created for Any SQL Database. The locations of these are hardcoded at the top of the script. If you are installing this Database on a dedicated server i.e IT DOES NOT HAVE DISCOVERYQUANT 3.0 INSTALLED you will need to create a directory of C:\DiscoveryQuant30 on the computer as this is where the files are stored. THE SCRIPT WILL FAIL IF THIS DIRECTORY IS NOT CREATED

Back in SQL Management Studio, to build the Database simply click Execute.

In the Messages Section you should see the Message ‘Command(s) completed successfully’

If you are able to use Active Directory authentication (Step 9) you do not need to do this. For all other users we need to add a SQL Server user for the clients to use to attach to the database. To do this from the Object Explorer on the left of the SQL Management Studio Expand Security then Right Click on Login and choose New Login ….

Enter a login name (A) (can be anything you want but following the guide will help if anything goes wrong) I have used ‘DiscoveryQuant’, select SQL Server authentication and add a password (B)(I have used !DQO30). Uncheck Enforce password policy (C) and change Default database to ‘DiscoveryQuantv30’(D).

Swich to User Mapping in the Select a Page Section and check Map checkbox next to DiscoveryQuantv30. Check db_owner in the roles at the bottom of the pane and click OK.

The Database setup is now complete so we need to test the access of the user to ensure that the clients will be able to connect. Close down the SQL Server Management Studio window.

The first test is to check that a user on the local server can connect to the database. To do this right click on your desktop and select New > Text Document.

Once you get a ‘New Text Document.txt’ (note very important you see the .txt – if you don’t you need to change folder options to show known extensions) right click and select Rename. Change the name to ‘LocalConnection.udl’ Windows will give a warning about changing file extension – Click Yes. Your icon should change to this.

Right click on the udl file and Select Properties

On the Provider Tab Select ‘Microsoft OLE DB Provider for SQL Server’

The Connection Tab - Section 1. – Pressing Refresh should find all available instances of SQL server. However I have not found this completely reliable in all environments. The format should be <LOCAL COMPUTER NAME>\<SQL INSTANCE NAME> so if you have followed this guide completely your ComputerName\DISCOVERYQUANT30 should work (and you can just type it in the box).

In Section 2. If you have used Active Directory Security you can Select ‘Use NT Integrated Security’ or if you have not Select Use a specific user name and password and Enter the details you provided in step 19. Check Allow saving password. For Section 3. Click the dropdown for Select the database on the server. At this point it will give an error if anything is wrong – if not it will list the databases, assuming it does select DiscoveryQuantv30 and press ‘Test Connection’. Hopefully you will see ……

This confirms that the Login in working and we can connect to the database from the local machine. We now need to configure and test a network connection. Click Apply and OK to close down the properties dialog.

Move to one of your instrument Client Computers and confirm that you can ‘see’ your server computer. Once you have confirmed this repeat all of step 20 with the exception that the ServerName is the remote computer name (In this case the UDL pictured above would be OK as it uses the full name – but if your DNS does not resolve the computername you may need to use IP Address. Firewalls can also be particularly tricky at this stage – reach out to your local IT if this is causing problems as they will be able to assist in getting the firewalls sorted so that 2 way communication can be established between server and client. I would like to add more here but no two situations are the same, generally speaking you should look at DNS resolution of the server, Firewalls (fix to port 1433 and a single adapter by setting this in the SQL instance TCP/IP Properties