| Previous Article | matchIT SQL Index | Next Article |
Below article assumes you're using stored procedures, which do require a local SQL instance, if you're only using SSIS you don't necessarily need a local instance, but would need components from SQL installed - particularly Integration Services, and SQL Server Data Tools, and Management studio while not necessary, makes it easier to view results and run the job in debug mode on the same machine.
Deployment for Stored Procedures
matchIT SQL 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 matchIT SQL 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).
matchIT SQL must be installed on the computer on which SQL Server is already installed.
The stored procedures can be used by any authorised user from any networked computer (refer to Configuring SQL Server Security to configure execution privileges).
Ensure authorised SQL Server logins have been granted data access (refer to Configuring SQL Server Security).
Scenario 2 – Remote data
A common deployment scenario in which matchIT SQL cannot be installed on the computer containing the SQL Server instance hosting the data.
matchIT SQL 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 authorised 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 authorised SQL Server logins (refer to Configuring SQL Server for Remote Access).
Ensure authorised 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 matchIT SQL accesses data hosted on both local and remote SQL Server instances.
matchIT SQL 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 authorised 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 authorised SQL Server logins (refer to Configuring SQL Server for Remote Access).
Ensure authorised SQL Server logins have been granted access to the local and remote database(s) (refer to Configuring SQL Server Security).
Recommendations
- Wherever possible, for maximum performance and simpler configuration, install and use matchIT SQL on the same computer on which the data is stored.
- 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 authorised login. When matchIT SQL is upgraded to a newer version, the stored procedures are simply dropped and then reregistered by ay authorised 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 matchIT SQL 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!).
Configuring SQL Server for Remote Access
Since the 2005 version, SQL Server implements the Secure by Default principle, which means that upon installation SQL Server disables a number of features that are considered potentially insecure; this includes support for remote connections.
The following actions must be performed on the computer containing the SQL Server instance that will be remotely accessed (either by the stored procedures themselves, or by any application that will run the stored procedures):
SQL Server Configuration Manager
This can be found from the Start Menu:
Microsoft SQL Server 2005* -> Configuration Tools -> SQL Server Configuration Manager.
Select “SQL Server 2005* Network Configuration”. (If the operating system is 64-bit and a 32-bit SQL Server instance is being configured, select “SQL Server 2005* Network Configuration (32-bit)” instead.)
Select “Protocols for instance”. Note that instance refers to the name of the SQL Server instance; there will be at least one instance listed (the default instance is named MSSQLSERVER). Select the correct instance.
The right-hand pane lists the available remote connection protocols. By default, only the Shared Memory protocol is enabled. To enable remote connections, right-click the TCP/IP protocol and select Enable from the menu.
Acknowledge the confirmation box, then close the Configuration Manager.
SQL Server Browser
The SQL Server Browser service must be running on the remote computer.
Open up the Services console by using Run from the Start Menu, then typing:
services.msc
and clicking OK.
Within the console, locate the SQL Server Browser and open its properties dialog. If the service is stopped, click Start.
Additionally, the service’s startup type can be set to Automatic so that the service is always available, even after the operating system is restarted.
Click OK to close the dialog, then close the Services window.
* or 2008 or 2008 R2 if either of these versions is installed
| Previous Article | matchIT SQL Index | Next Article |