Installation Overview

mSQL is delivered as an executable installation file that must be run by a user with administrative privileges.

You will need to paste the activation code that was supplied by 360Science (24 alphanumeric characters plus dashes); this is done via the Product Activation page of the installer.

Choose the nationality of the data you mostly anticipate processing. This simply sets up some default settings, but you are still able to process data for other nationalities with the product.

We recommend using the default installation folder, but mSQL can be installed to any folder on any hard disk drive if necessary. (Once installed, subsequent installations of mSQL – upgrades and reinstallations – will continue to use this folder.)

Similarly, we recommend using the default data folder (for storing XML config files and T-SQL scripts), but any folder on any hard disk can be used.  Please ensure that the folder is properly secured if any files will contain confidential information (passwords in connection strings, for example).

 

Installation Options:

Web Server Selection: If you are intending to use the stored procedures, as opposed to creating SSIS packages, then a browser-based UI is available for editing configuration files. We recommend using the IIS web server on a dev machine; this will need to be installed before mSQL. (pre 2.2.0 UltiDev Cassini can be installed for development purposes). *Cassini has been removed from future versions so that we can allow for a more secure site based on feedback from clients.

Reporting: mSQL can produce summary reports when certain stored procedures and SSIS tasks have completed. Crystal Reports is used, and is automatically downloaded (35MB) and installed if you enable summary reporting.

Check for Updates: mSQL can perform a daily check to see if a new version of the product has been released.

 

Deployment Considerations (advanced):

 

If using SSIS:

mSQL must be installed on the computer that will run the SSIS Packages.  For best performance the data should be on the same computer; however, the data can also be located on any computer accessible via the network.

An SSIS package containing at least one mSQL SSIS component cannot be edited or run on a machine that doesn't currently have mSQL installed.
Additionally, SSIS packages that have previously contained any such components - but which have since been removed from the package - also cannot be edited or run on a machine that doesn't currently have mSQL installed. This is because such packages will contain hidden SSIS variables that cause a deserialization exception if mSQL is not installed, causing the package to fail to load. SSIS packages last edited with mSQL 1.4.8 or later are not affected, because the variables are no longer stored in the package.

 

Scenario 1 – Local data

The most common deployment scenario that can provide maximum performance, in which both the SSIS packages and the data are hosted within one SQL Server.

mSQL must be installed on the computer on which SQL Server Standard edition or greater is already installed.

The SSIS packages can be used by any authorized user from any networked computer.

Ensure authorized SQL Server logins have been granted data access (refer to Configuring SQL Server Security).

 

Scenario 2 – Remote data

A common deployment scenario in which mSQL cannot be installed on the computer containing the SQL Server instance hosting the data.

mSQL will be installed on the local computer.  The local computer must contain the integration services components from SQL Server Standard Edition or greater. Also SQL Server Business Intelligence Development Studio (2005 or 2008), or SQL Server Data Tools (2012 or newer) must be installed in order for you to create or edit packages containing the mSQL SSIS tasks.

The SSIS Packages can be used by any authorized user from any networked computer (refer to Configuring SQL Server Security to configure execution privileges). 

Each remote SQL Server instance must be configured to allow access by authorized SQL Server logins (refer to Configuring SQL Server for Remote Access).

Ensure authorized SQL Server logins have been granted access to the remote database(s) (refer to Configuring SQL Server Security).

 

If Using Stored Procedures:

mSQL must be installed on the computer that will run the stored procedures.  For best performance the data should be on the same computer; however, the data can also be located on any computer accessible via the network.

The stored procedures can be run using a variety of methods, including:

  • an ADO.NET application;
  • the SQL Server Management Studio;
  • the SQL Server Agent;
  • a SQL Server Integration Services (SSIS) package.

Each method uses a connection to the database containing the stored procedures; using this connection, a sequence of stored procedures (and T‑SQL statements) can be executed.  So, for example, an ADO.NET application can run from any computer on the network and connect to the SQL Server instance hosting the stored procedures; these stored procedures in turn connect to any SQL Server instance hosting the data.

 

Definitions

Local computer

This indicates the computer on which mSQL has been installed; this computer requires a SQL Server instance to host the stored procedures.

Remote computer

This indicates the computer containing the SQL Server instance that hosts the data.

 

Scenario 1 – Local data

The most common deployment scenario that can provide maximum performance, in which both the stored procedures and the data are hosted within one SQL Server instance (or even separate instances on the same computer).

mSQL must be installed on the computer on which SQL Server is already installed.

The stored procedures can be used by any authorized user from any networked computer (refer to Configuring SQL Server Security to configure execution privileges).

Ensure authorized SQL Server logins have been granted data access (refer to Configuring SQL Server Security).

 

Scenario 2 – Remote data

A common deployment scenario in which mSQL cannot be installed on the computer containing the SQL Server instance hosting the data.

mSQL will be installed on the local computer.  The local computer, and all remote computers must each contain a SQL Server instance.

The stored procedures can be used by any authorized user from any networked computer (refer to Configuring SQL Server Security to configure execution privileges).

Each remote SQL Server instance must be configured to allow access by authorized SQL Server logins (refer to Configuring SQL Server for Remote Access).

Ensure authorized SQL Server logins have been granted access to the remote database(s) (refer to Configuring SQL Server Security).

 

Scenario 3 – Local and remote data

A common deployment scenario in which mSQL accesses data hosted on both local and remote SQL Server instances.

mSQL will be installed on the local computer.  The local computer, and all remote computers, must each contain a SQL Server instance.

The stored procedures can be used by any authorized user from any networked computer (refer to Configuring SQL Server Security to configure execution privileges).

Each remote SQL Server instance must be configured to allow access by authorized SQL Server logins (refer to Configuring SQL Server for Remote Access).

Ensure authorized SQL Server logins have been granted access to the local and remote database(s) (refer to Configuring SQL Server Security).

 

Recommendations

  1. Wherever possible, for maximum performance and simpler configuration, install and use mSQL on the same computer on which the data is stored.
  2. Create a database on the local computer to be used only for hosting the stored procedures, containing no data, rather than having the stored procedures registered with multiple databases. This requires the stored procedures be registered once only, by an authorized login.  When mSQL is upgraded to a newer version, the stored procedures are simply dropped and then reregistered by ay authorized login.  (Note that this is normally done automatically by the matchIT SQL Configurator during installation.)

    Alternatives involve registering the stored procedures on each relevant database, or registering the stored procedures only when they’re needed then dropping them when they’ve completed (as per the mSQL example .sql scripts).  Both of these alternatives involve unnecessary repetition (in fact, the latter should not be done if multiple stored procedures will be run simultaneously!).