Installing MeasureColor to an existing SQL stack

MeasureColor uses Microsoft SQL for all database tasks. An express version of SQL with a data limit of 10 Gb is included with the software.

In some cases you might want to add the MeasureColor data to an existing SQL stack. Perhaps because you need over 10 Gb data, or simply want to have all your data in one place. This tutorial describes how to add MeasureColor to an existing SQL stack during installation.

Warning: Colorware holds no responsibility for database integrity, security, maintenance, backup and restore procedures. This QuickStart is provided as additional help for experts. It is advised that a trained system engineer monitors and manages your system at all times.

install procedure

  • First make sure that an existing SQL Server instance has been installed and has been setup. This includes creating a database and a user which has access to the database. Make sure that the connection parameters are known for a later step.
  • At the end of the installation process, when asked to download the SQL database select advanced.
  • The migration window should now popup and can probably not find an existing connection to the database. If the database and/or user was not created beforehand, please make sure to do that now.
  • Enter the connection string to SQL Server in the “Connection String” input box. If all is well, the system should show a green check-mark to indicate that a connection could be made.
  • Click “Start…” to start the migration process. The database should now be ready for use, the only step left is to make sure that the server itself is connecting to the proper database.
  • Open IIS manager console and select the ColorwareServer site.
  • Open the “Connection Strings” item and make sure the connection string for “ColorwareDatabase” is setup correctly for the database used (this is something that you should only have to do on the first install).

Everything should now be ready to go and the regular setup process can now be finished as usual.

Database creation examples

Instances for MeasureColor should be created with ‘SQL Server and Windows Authentication mode’ server authentication.

MeasureColor database example

Let’s say you want to prepare to install MeasureColor on a server called MyServer, in an instance ‘MeasureColor‘ you created. You can run this query (after changing the example parameters shown in Bold):

USE [master]

GO

CREATE LOGIN [MyLogin] WITH PASSWORD='MyPassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

CREATE DATABASE [Colorware]

COLLATE SQL_Latin1_General_CP1_CI_AS

GO

USE [Colorware]

GO

CREATE USER [MyLogin] FOR LOGIN [MyLogin] WITH DEFAULT_SCHEMA=[dbo]

GO

EXEC sp_addrolemember 'db_owner', 'MyLogin'

This would result in a connection string ‘Data Source=MyServerMeasureColor;database=Colorware;uid=MyLogin;pwd=MyPassword

MeasureColor Reports example

Let’s say you want to prepare to install MeasureColor Reports on a server called MyServer, in an instance ‘MeasureColor‘ you created. You can run this query (after changing the example parameters shown in Bold):

USE [master]
GO

CREATE LOGIN [MyLogin] WITH PASSWORD='MyPassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE LOGIN [colorwareQueryExecutor] WITH PASSWORD='MyPassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE DATABASE [Colorware.Reports]
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE [Colorware.Reports]
GO

CREATE USER [MyLogin] FOR LOGIN [MyLogin] WITH DEFAULT_SCHEMA=[dbo]
GO

CREATE USER [colorwareQueryExecutor] FOR LOGIN [colorwareQueryExecutor] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember 'db_owner', 'MyLogin'

EXEC sp_addrolemember 'db_datareader', 'colorwareQueryExecutor'

This would result in two connection strings:

  • ‘Data Source=MyServerMeasureColor;database=Colorware;uid=MyLogin;pwd=MyPassword
  • ‘Data Source=MyServerMeasureColor;database=Colorware;uid=colorwareQueryExecutor;pwd=MyPassword