1. No category

DOC-025 How to use InfoSys+ SQL Server
Kantar Media Spain
23/10/2012
INDEX
1
ABSTRACT ............................................................................................................................................. 3
2
TECHNICAL REQUIREMENTS ................................................................................................................. 4
3
2.1
REQUIREMENTS TO RUN INFOSYS+ SQL ..................................................................................... 4
2.2
SERVER REQUIREMENTS: SQL SERVER 2008 R2 .......................................................................... 4
2.3
SERVER REQUIREMENTS: SQL SERVER EXPRESS ......................................................................... 6
HOW TO SET UP InFOSYS+ TO CONNECT TO SQL SERVER .................................................................... 7
3.1
HOW TO IMPORT THE ACCESS DATA BASES TO SQL SERVER ..................................................... 7
3.2
HOW TO SET-UP INFOSYS+ TO WORK WITH SQL SERVER DBS ................................................... 9
4
SQL SERVER IN REMOTE SERVERS: POSSIBLE PROBLEMS. ................................................................. 11
5
HOW TO SET UP A SQL SERVER INSTANCE ......................................................................................... 15
6
HOW TO SETUP the SQL SERVER Client in a client PC ........................................................................ 17
7
ADVICES FOR BETTER PERFORMANCE................................................................................................ 18
8
7.1
DEDICATED INSTANCE ............................................................................................................... 18
7.2
AUTO-CLOSE PROPERTY ............................................................................................................ 18
HISTORY .............................................................................................................................................. 19
1
ABSTRACT
This document explains a general overview of how to set up InfoSys+ config in order to use databases
stored in Microsoft’s SQL Server.
From InfoSys+ 2.1 it’s possible to use SQL Server as DB engine; it’s not strictly necessary to have all DBs
in SQL Server, it’s allowed to have some of them in Access and some of them in SQL Server. However,
InfoSys+ 2.1 does not allow attaching SQL Server databases inside the audience packages, which makes
it impossible to update remote SysIngredients. Apart from that, the SQL Server databases are not
password-protected, so the contents could be seen by final clients, which could be risky
Therefore, for the moment being InfoSys+ SQL Server is only recommended for centralized installations
(Citrix, Terminal Services, etc..)
2
2.1
TECHNICAL REQUIREMENTS
REQUIREMENTS TO RUN INFOSYS+ SQL
InfoSys SQL has the same requirements as MS Access counterpart; the only additional requirement is to
have SQL Server Client installed in the machines which will run InfoSys+
2.2
SERVER REQUIREMENTS: SQL SERVER 2008 R2
The following sections will list the minimum hardware and software requirements to install and run SQL
Server 2008 R2.
Microsoft recommends that you run SQL Server 2008 R2 on computers with the NTFS file format. For
upgrades to SQL Server 2008 R2, FAT32 file systems will not be blocked.
((http://msdn.microsoft.com/en-us/library/ms143506(v=sql.105).aspx)
Hardware and Software Requirements 32 and 64-bits
The following requirements apply to all SQL Server 2008 R2 installations:
Component
Framework2
Software2
Network
Software
Hard Disk
Requirement
SQL Server Setup installs the following software components required by the
product:
NET Framework 3.5 SP11
SQL Server Native Client
SQL Server Setup support files
SQL Server Setup requires Microsoft Windows Installer 4.5 or a later version.
After installing required components, SQL Server Setup will verify that the computer
where SQL Server 2008 R2 will be installed also meets all the other requirements for
a successful installation
Network software requirements for the 64-bit versions of SQL Server 2008 R2 are
the same as the requirements for the 32-bit versions.
Supported operating systems have built-in network software. Stand-alone named
and default instances support the following network protocols:
Shared memory
Named Pipes
TCP/IP
VIA
Disk space requirements will vary with the SQL Server 2008 R2 components you
install. For more information, see Hard disk space requirements later in this topic.
For further information about the installation the SQL Server in virtual machines environment please
check the Microsoft website.
(http://msdn.microsoft.com/en-us/library/ms143506(v=sql.105).aspx)
http://support.microsoft.com/?id=956893
1. The following .NET Framework versions are required:
SQL Server 2008 R2 on Windows Server 2003 (64-bit) IA64 — .NET Framework 2.0 SP2
SQL Server Express — .NET Framework 2.0 SP2
All other editions of SQL Server 2008 R2 — .NET Framework 3.5 SP1
2. SQL Server Setup will not install the following required components for SQL Server Express, SQL
Server Express with Tools, and SQL Server Express with Advanced Services. You must install these
components manually before you run SQL Server Setup:
SQL Server Express — .NET Framework 2.0 SP2 and Windows installer 4.5. On Windows Vista SP2, use
.NET Framework 3.5 SP1.
3. As with all virtualization technologies, SQL Server 2008 R2 running in a Windows Server 2008 SP2
Hyper-V virtual computer will be slower than on a physical computer with the same physical resources.
Memory and Operating System Requirements
WOW64
WOW64, a feature of 64-bit editions of Windows that enables 32-bit applications to run natively in 32bit mode. Applications function in 32-bit mode, even though the underlying operating system is running
on the 64-bit operating system. WOW64 is supported only for stand-alone instances of SQL Server.
WOW64 is not supported for SQL Server failover cluster installations
For SQL Server 64-bit Edition installations on 64-bit supported operating systems, Management Tools
are supported in WOW64. On a supported 64-bit operating system, SQL Server 32-bit Edition
installations can be installed to the Windows on Windows (WOW64) 32-bit subsystem of a 64-bit server.
Other operating system notes
SQL Server 2008 R2 is not supported on Windows Server 2008 SP2 Server Core or Windows Server 2008
R2 Server Core installations.
Operating systems listed in the SQL Server Standard sections are also supported for SQL Server Standard
for Small Business
SQL Server editions that are supported on Windows Server 2008 R2 64-bit x64 Standard are also
supported on Windows Small Business Server 64-bit x64.
Core Server Editions (64-bit and 32-bit)
SQL Server 2008 R2 Datacenter (64-bit) x64: The following table shows system requirements for SQL
Server 2008 R2 Datacenter (64-bit) x64.
Component
Processor
Memory
Requirement
Processor type:
Minimum: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support,
Intel Pentium IV with EM64T support
Processor speed:
Minimum: 1.4 GHz
Recommended: 2.0 GHz or faster
RAM:
Minimum: 1 GB
Recommended: 4 GB or more
Maximum: Operating system maximum
2.3
SERVER REQUIREMENTS: SQL SERVER EXPRESS
This is a freeware version of SQL Server; it has the following limitations:



The SQLServer.exe process can’t use more than 1 GB of memory
The SQLServer.exe is not multi-process (it can use only one core)
Number of connected users.
For InfoSys+ uses we estimate that these limitations should not be a real drawback.
The following tables list the prerequisite software and minimum hardware and software requirements
for running Microsoft SQL Server 2005 Express Edition (SQL Server Express), Microsoft SQL Server 2005
Express Edition with Advanced Services (SQL Server Express), and Microsoft SQL Server 2005 Express
Edition Toolkit (SQL Server Express Toolkit).
http://technet.microsoft.com/en-us/library/ms143680(v=SQL.90).aspx
System requirements (32-bit) for SQL Server Express
Prerequisite
software
Microsoft .NET Framework 2.0
Internet
Requirements
Microsoft Internet Explorer 6.0 SP1 or later (prerequisite for .NET Framework)
SQL Server Setup requires Microsoft Data Access Components (MDAC) 2.8 SP1 or
later. You can download MDAC 2.8 SP1 from this Microsoft Web site.
RAM
Minimum: 512 MB
Recommended: 1GB or higher
Hard Disk space
600 MB free space
Processor
Pentium III Compatible or higher
Minimum: 600 MHz
Recommended: 1 GHz or higher
3
HOW TO SET UP INFOSYS+ TO CONNECT TO SQL SERVER
During the installation of SQL Server has been created a SQL instance, if have decided to run InfosysPlus
64-bits then is mandatory that all data base Access must be migrated to SQL server. If have decided to
use the InfosysPlus 32 bits version you can mix both types of Data bases with the only limitation that
both Ingredients and SysIngredients DBs must use the same DB engine.
3.1
HOW TO IMPORT THE ACCESS DATA BASES TO SQL SERVER
To import the data bases from Access to SQL Server you should use the tool DBImport (please make sure
to use always the latest version), it will create and insert all tables keeping the same content and
structure of an Access data base. Apart from importing the DB contents, this tool also creates some
users, stored procedures, etc.. in the SQL Server instances which are mandatory for correct InfoSys+
behaviour; therefore, do not use any other methods (DTS or whatever) to import from MS Access to
SQL Server
For this run the DBImport and complete the necessary fields to import.
Where the most important fields are:
The Source data part corresponds to the Access data base
The Target data part corresponds to the SQL Server information.
Server : the server where has created the Data Base
Database : write here the database name, you can use a prefix as in the example it is useful when have
more than one InfosysPlus configuration hosted in a same SQL Server database.
Exchange folder: it’s an auxiliary folder uses by DBImport to process temporary bulk insert files. It must
be accessible by both the SQL Server computer and the computer where DBImport is run.
UNICODE (BulkInsert file) : mark this option if the information of tables contains symbols of languages
as Chinese, Korean, Russian etc instead of latin characters.
[ Go ] : starts the import process.
Once the import process has finished correctly the tool will show the message “Import process
successfully completed …” (see image below).
Then in SQL server you’ll be able to see the data base with all their respective tables:
3.2
HOW TO SET-UP INFOSYS+ TO WORK WITH SQL SERVER DBS
Once all tables were imported, the second step will be update the DBCfg.XML so InfoSysPlus can read
the data bases from the SQL Server, see the following example and pay special attention to the
parameters in red color because they are different when I+ work with Access data bases:
DBCfg.XML:
<?xml version="1.0" encoding="UTF-8"?>
<DB admin_type = "2">
<Ingredients type = "1" server = "scg-mmaidana-2\mmzsql" catalogue = "Beeline_Ingredients"
pathServer = "" secure = "0" user = "TnsInfosys" password = "ENCDOPHOKOKMIDDF"/>
<SysIngredients
type
=
"1"
server
=
"scg-mmaidana-2\mmzsql"
catalogue
=
"Beeline_SysIngredients" pathServer = "" secure = "0" user = "TnsInfosys" password =
"ENCDOPHOKOKMIDDF"/>
<SysAttribs type = "1" server = "scg-mmaidana-2\mmzsql" catalogue = "Beeline21_SysAttribs"
pathServer = "" secure = "0" user = "TnsInfosys" password = "ENCDOPHOKOKMIDDF"/>
<Programs type = "1" server = "scg-mmaidana-2\mmzsql" catalogue = "Beeline_ProgramsPlus"
pathServer = "" secure = "0" user = "TnsInfosys" password = "ENCDOPHOKOKMIDDF"/>
<Spots type = "1" server = "scg-mmaidana-2\mmzsql" catalogue = "Beeline_SpotsPlus" pathServer =
"" secure = "0" user = "TnsInfosys" password = "ENCDOPHOKOKMIDDF"/>
</DB>
HINT: If a port different from standard one (1433) is used in the SQL Server instance, the DBCfg.XML
must include the port number separated from the server name by comma. Example, if port 1500 is used:
<Ingredients type = "1" server = "scg-mmaidana-2\mmzsql, 1500" catalogue = "Beeline_Ingredients"
pathServer = "" secure = "0" user = "TnsInfosys" password = "ENCDOPHOKOKMIDDF"/>
Of course this must be changed for all the databases included in the DBCfg.XML.
From Interface and from Control panel / Data Configuration / Database option is also possible to set the
databases parameters for SQL Server, see the example below (in blue frame the most important fields):
4
SQL SERVER IN REMOTE SERVERS: POSSIBLE PROBLEMS.
· One of the common problem is the ports of remote machine are closed, then to open the ports run the
following script:
(Source: http://support.microsoft.com/kb/968872/en-us)
If the script haven’t solved the problem, deactivate manually the Windows Firewall of the Domain
networks:
· When the loading of programs or spot data imply the updating of databases ProgramsPlus or SpotsPlus
can appear a problem because InfosysPlus and SQL don’t find the folder where, these two applications,
drop and get respectively, the information to update the tables, it can happen because in InfosysPlus
hasn’t defined the exchange folder.
To check this issue go to Control panel / Data configuration / Directories / Exchange location path with
SQLServer.
Note that the folder where is pointing this parameter must be visible from SQL Server and from
InfosysPlus and the most important must have the full rights reading-writing. The folder can be located
in the Server or in a Local machine.
· Besides in server manager window verify that all SQL services are running.
5
HOW TO SET UP A SQL SERVER INSTANCE
http://technet.microsoft.com/en-us/library/ms143547(v=sql.105).aspx
An installation of SQL Server consists of one or more separate instances. An instance, whether default or
named, has its own set of program and data files, as well as a set of common files shared between all
instances of SQL Server on the computer.
For an instance of SQL Server that includes the Database Engine, Analysis Services, and Reporting
Services, each component has a full set of data and executable files, and common files shared by all
components.
To isolate install locations for each component, unique instance IDs are generated for each component
within a given instance of SQL Server.
Important: Program files and data files cannot be installed on a removable disk drive, cannot be
installed on a file system that uses compression, cannot be installed to a directory where system files
are located, and cannot be installed on shared drives on a failover cluster instance.
Do not delete any of the following directories or their contents: Binn, Data, Ftdata, HTML, or 1033. You
can delete other directories, if necessary; however, you might not be able to retrieve any lost
functionality or data without uninstalling and then reinstalling SQL Server. Do not delete or modify any
of the .htm files in the HTML directory. They are required for SQL Server tools to function properly.
Shared Files for All Instances of SQL Server
Common files used by all instances on a single computer are installed in the folder <drive>:\Program
Files\Microsoft SQL Server\100\, where <drive> is the drive letter where components are installed. The
default is usually drive C.
File Locations and Registry Mapping
During SQL Server Setup, an instance ID is generated for each server component. The server
components in this SQL Server release are the Database Engine, Analysis Services, and Reporting
Services.
The default instance ID is constructed by using the following format:
· MSSQL for the Database Engine, followed by the major version number, followed by an underscore and
the minor version when applicable, and a period, followed by the instance name.
· MSAS for Analysis Services, followed by the major version number, followed by an underscore and the
minor version when applicable, and a period, followed by the instance name.
· MSRS for Reporting Services, followed by the major version number, followed by an underscore and
the minor version when applicable, and a period, followed by the instance name.
Examples of default instance IDs in this release of SQL Server 2008 R2 are as follows:
· MSSQL10_50.MSSQLSERVER for a default instance of SQL Server 2008 R2.
· MSAS10_50.MSSQLSERVER for a default instance of Analysis Services.
· MSSQL10_50.MyInstance for a named instance of SQL Server 2008 R2 named "MyInstance."
The directory structure for a SQL Server 2008 R2 named instance that includes the Database Engine and
Analysis Services, named "MyInstance", and installed to the default directories would be as follows:
· C:\Program Files\Microsoft SQL Server\MSSQL10_50.MyInstance\
· C:\Program Files\Microsoft SQL Server\MSASSQL10_50.MyInstance\
You can specify any value for the instance ID, but avoid special characters and reserved keywords.
You can specify a non-default instance ID during SQL Server Setup. Instead of <Program Files>\Microsoft
SQL Server, a <custom path>\Microsoft SQL Server is used if the user chooses to change the default
installation directory. Note that instance IDs that begin with an underscore (_) or that contain the
number sign (#) or the dollar sign ($) are not supported.
Settings after SQL Server setup
During the SQL Server setup has created an instance, now login into SQL server management using the
Widows authentication, right-click on your SQL instance to display its properties. In the security tab,
please make sure that SQL server authentication is enabled alongside with Window’s:
6
HOW TO SETUP THE SQL SERVER CLIENT IN A CLIENT PC
For the client machines is necessary to install only the SQL Server Client, not full package; this way the
client computer will be able to connect to SQL instance and data base.
7
ADVICES FOR BETTER PERFORMANCE
In order to get the best performance of InfoSys+ when working in SQL Server versions, we recommend
doing two things.
7.1
DEDICATED INSTANCE
We recommend not sharing the SQL Server instance with databases from other applications, since that
would burden the SQL Server instance service with more workload and this could impact negatively on
InfoSys+ performance.
Therefore, please create a dedicated instance for your particular InfoSys+ version.
7.2
AUTO-CLOSE PROPERTY
Since SQL Server 2000 onwards, SQL Server DBs have a property called “Auto-Close” which, if enabled,
makes the SQL server close the connections to the DB when the last user disconnects. This is an
interesting feature, because it releases unused RAM when not used, but in applications which have
constant accesses to the databases (as InfoSys+ is) this auto-close feature can impact negatively on
performance.
Therefore, our recommendation is to set the “Auto-Close” property to “False” in InfoSys+ databases
If you used older versions of DBImport with SQL Server Express, or if you imported DBs from SQL Server
2000, you could have this property to “True”; in that case, please disable them in each of the DBs.
Newer versions of DBImport (April’14) always set this property to “False”.
ALTER DATABASE <DB_Name> SET AUTO_CLOSE OFF;
More details in http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases
8
HISTORY
Version
1.0
1.1
Author
KMA.InfoSys
KMA.InfoSys
Date
05/12/2013
15/04/2014
1.2
KMA.InfoSys
24/04/2014
Comments
Creation
Additition of section #7 Advices for better
performance.
Additition of comments about how to set up
InfoSys+ when the SQL Server instance uses
a port number different from standard
1433.