Page 1
SQL Server Setup Guide for Business Objects
Planning
Business Objects Planning 10.8
Page 2
Patents
Business Objects owns the following U.S. patents, which may cover products that are offered and sold by Business Objects:
5,555,403, 6,247,008 B1, 6,578,027 B2, 6,490,593 and 6,289,352.
Trademarks
Copyright
Third-party
contributors
Business Objects, the Business Objects logo, Crystal Reports, and Crystal Enterprise are trademarks or registered
trademarks of Business Objects SA or its affiliated companies in the United States and other countries. All other names
mentioned herein may be trademarks of their respective owners.
Copyright © 1999-2006 Business Objects. All rights reserved.
Business Objects products in this release may contain redistributions of software licensed from third-party contributors.
Some of these individual components may also be available under alternative licenses. A partial listing of third-party
contributors that have requested or permitted acknowledgments, as well as required notices, can be found at:
http://www.businessobjects.com/thirdparty
Page 3
Contents
Introduction
SQL Server Versions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Intended Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Technical Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
SQL Server User Interface Differences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Support Contact Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Chapter 1: SQL Server Installation Requirements
Using Existing SQL Server Installations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
Installing SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
SQL Server 2000 or 2005. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
SQL Server 7.0. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
SQL Server 2000 or 2005. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
SQL Server 7.0. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
Chapter 2: Configuring the Planning Database in SQL Server
Server Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
Creating the Login User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
Creating the Database Transfer and Backup Devices . . . . . . . . . . . . . . . . . . . . . . . . . . . .10
Restoring the Original Business Objects Planning Database . . . . . . . . . . . . . . . . . . . . . .10
Restoring a Database in SQL Server 2005. . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Restoring a Database in SQL Server 7.0 or 2000. . . . . . . . . . . . . . . . . . . . . . .11
Configuring User and Database Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12
User Rights and Ownership . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12
Database Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13
Chapter 3: Additional Configuration and Administration Issues
Backing Up the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15
Recommended Operating System Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15
Performance and Virtual Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15
Server Memory Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16
Recommended SQL Server Configuration Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . .17
Microsoft SQL Server Setup Guide for Business Objects Planning 3
Page 4
Contents
4 Microsoft SQL Server Setup Guide for Business Objects Planning
Page 5
Introduction
This manual provides information necessary for the setup and administration of
Microsoft® SQL Server for use with Business Objects Planning.
SQL Server Versions
Business Objects Planning is supported for use with the following SQL Server versions.
z SQL Server 2005
z SQL Server 2000 (Service Pack 3 or latest, version 8.00.194 or higher)
z SQL Server 7.0 (version 7.00.842 or higher)
Both Standard and Enterprise Editions of the above versions are supported.
This document applies to all supported versions of SQL Server. Differences in setup
requirements and recommendations are noted in the text.
Intended Audience
Installation, configuration and administration of SQL Server is typically handled by the
organization’s Information Technology department. Business Objects Planning Support is
also available to assist you with issues relating to use of SQL Server with Business
Objects Planning.
Technical Requirements
The recommended client and server hardware and software requirements are outlined in
the separately produced document entitled Business Objects Planning Technical
Requirements. Please contact the Business Objects Planning Support department or visit
www.mysrc.com
for a copy of the most recent version.
SQL Server User Interface Differences
SQL Server 2005 presents a different user interface than 7.0 or 2000. In 7.0 and 2000,
dialog boxes are organized by tabs along the top of the dialog. In 2005, dialog boxes are
organized by “pages” along the left side of the dialog.
In many cases, the names of the tabs and the pages are the same and present similar
options. This document uses the convention of “tab” to refer to either the tab or the page,
except where 2005 is specifically discussed.
Support Contact Information
If you have any questions after consulting this document, please contact Business Objects
Planning Support. The contact information is listed in the following table by region.
Microsoft SQL Server Setup Guide for Business Objects Planning 5
Page 6
Introduction
Region Phone Email
Americas 1 800 544 3477 PBUSupportNA@businessobjects.com
Asia Pacific 65 64166567 PBUSupportAsia@businessobjects.com
Europe, Middle East and Africa 0800 389 4130 PBUSupportEurope@businessobjects.com
6 Microsoft SQL Server Setup Guide for Business Objects Planning
Page 7
SQL Server Installation Requirements
This chapter details the required SQL Server installation parameters for Business Objects
Planning. Business Objects Planning can also be used with an existing installation of SQL
Server, if the existing installation meets certain requirements.
Using Existing SQL Server Installations
SQL Server 2000 or 2005
Any existing SQL Server 2000 or 2005 installation that meets the supported version
requirements can be used.
All Business Objects Planning databases default to the following data settings. If you
need a data set with different settings, please contact your Business Objects Planning
consultant as soon as possible.
.
Data Type Settings
Unicode Data z In 2000: Latin1-General, case-insensitive, accent-sensi-
tive, kanatype-insensitive, width-insensitive
z In 2005: Latin1_General_CI_AS
Non-Unicode Data SQL Server Sort Order 52 on Code Page 1252
SQL Server 7.0
Existing installations of SQL Server 7.0 can be used if the installation meets the following
conditions:
1. The Sort Order must be set to Dictionary order, case insensitive.
2. The Character Set must be set to ISO.
3. Unicode Collation must be set to General Unicode, with the following settings:
z Case-Insensitive
z Accent-Sensitive
z Width-Insensitive
z Kana-Insensitive
Microsoft SQL Server Setup Guide for Business Objects Planning 7
Page 8
SQL Server Installation Requirements
1
Installing SQL Server
Installing SQL Server
SQL Server 2000 or 2005
Please follow the instructions in the SQL Server documentation to install SQL Server
2000 or 2005. During the install, there are certain choices you should make to ensure that
the installation will be compatible with Business Objects Planning. These settings are
detailed in the following table.
Installation Choices Required Setting
Services Accounts / Service
Settings
Authentication Mode Choose Mixed Mode (Windows Authentica-
Collation Settings Choose SQL Collations with Dictionary
Choose Use a Domain User account (this setting is preferred, but not required).
tion and SQL Server Authentication).
order, case-insensitive for use with the 1252
character set.
SQL Server 7.0
While SQL Server 7.0 is supported, it is an older version of the product. If you are using
SQL Server 7.0, it is most likely an existing installation (see “Using Existing SQL Server
Installations” on page 7). Contact Business Objects Planning Support if you need
assistance determining appropriate installation choices for SQL Server 7.0.
8 Microsoft SQL Server Setup Guide for Business Objects Planning
Page 9
Configuring the Planning Database in SQL Server
This chapter explains the process of configuring the Business Objects Planning database
in SQL Server. This chapter applies to all supported versions of SQL Server except where
differences are noted in the text.
To configure the Business Objects Planning database, you will need to perform the
following activities:
z Create the srcadmin login user.
z Create the database transfer and backup devices.
z Restore the Business Objects Planning database.
z Configure the user and database settings.
Please consult the SQL Server documentation for full instructions on these activities. The
following sections provide an overview and detail the key configuration parameters.
consultant or Support to obtain it.
Server Configuration
There are two crucial components to any SQL Server database: the log file and the data
file. If possible, these components should reside on two physically independent hard
drives or on two fast RAID-5 /10 arrays. The log file is a sequentially written file,
whereas the data file access is characterized by random reads and writes. Separating these
components on different physical devices allows the disk controller to write data faster.
(According to Microsoft technical support, your system will perform even faster – up to
30% – with two separate SCSI controllers controlling the two physical devices.)
contains the master database and the Business Objects Planning data file.
Note: During this process, you will need to input a password for the Business
Objects Planning Admin account. If the password has not already been
communicated to you, contact your Business Objects Planning implementation
Note: The examples in this chapter assume that the SQL Server database has
two physical hard drives, and that the operating system is on drive C. The
Business Objects Planning log file is assumed to be located on C. Drive D
Creating the Login User
To create the srcadmin login user:
1. Open the Security folder, right-click on Logins and then select New Login.
Microsoft SQL Server Setup Guide for Business Objects Planning 9
Page 10
Configuring the Planning Database in SQL Server
2
Creating the Database Transfer and Backup Devices
2. Use the following settings on the General tab. Remember that dialogs in SQL Server
2005 are presented using pages instead of tabs, but the page names and settings are
otherwise essentially the same.
Option Setting
Name srcadmin
Authentication SQL Server authentication
Password Contact your Business Objects Planning consultant or Sup-
Database master
Language <Default>
3. On the Server Roles tab, make sure that no permissions are selected.
port for the password.
NOTE: In SQL Server 2005, do not select Enforce Password
Policy.
Creating the Database Transfer and Backup Devices
To prepare to restore the Business Objects Planning database created by your
implementation consultant into your SQL Server environment, create two backup devices
– one to restore the database, and one to back up the database.
1. Using Windows Explorer, create a new directory on the SQL Server. The examples in
this chapter use a directory on the D drive named D:\SQLDATA.
2. Create two database backup devices: one named SQLBackup, and one named
SQLTransfer. These devices should be saved to the backup directory created in Step
1 (for example: D:\SQLDATA\Backup\SQLTransfer.bak).
The method of creating a backup device differs slightly depending on whether you are
using SQL Server 7.0 / 2000 or SQL Server 2005:
z In SQL Server 2005, open the Server Objects folder, right-click Backup
Devices and select New Backup Device.
z In SQL Server 7.0 / 2000, open the Management folder, right-click Backup and
select New Backup Device.
Restoring the Original Business Objects Planning Database
After creating the backup devices, you must restore the Business Objects Planning
database provided by your implementation consultant.The Business Objects Planning
database is named SQLTRANSFER.BAK.
First, save the SQLTRANSFER.BAK file into the directory where you created the backup
devices (for example: D:\SQLDATA\BACKUP). Then follow the restoration steps as
10 Microsoft SQL Server Setup Guide for Business Objects Planning
Page 11
Configuring the Planning Database in SQL Server
Restoring the Original Business Objects Planning Database
appropriate for your SQL Server version. Although the steps are similar, the option names
are different enough to warrant separate instructions for clarity.
Restoring a Database in SQL Server 2005
To restore SQLTRANSFER.BAK in SQL Server 2005:
1. Right-click on the Databases folder and select Restore Database.
2. On the General page, use the following settings:
Option Setting
To database SRCIA
Source for restore From device
Device Select the SQLTransfer.bak device
Restore checked
2
3. On the Options page, use the following settings:
Option Setting
Restore option Overwrite the existing database
Restore as:
Recovery State Leave the database ready to use by rolling back
* Although the logical file names cannot be changed, you can edit the
*
SRCIA D:\SQLData\SRCIA.mdf
SRCIA_Log C:\SQLLog\SRCIA.ldf
uncommitted transactions....(RESTORE WITH
RECOVERY)
physical path to place the files in a different location. For example, your
C drive might not have enough room for the log file, in which case you
might place the file on the E drive.
Restoring a Database in SQL Server 7.0 or 2000
To restore SQLTRANSFER.BAK in SQL Server 7.0 or 2000:
1. Right-click on the Databases folder, select All Tasks and then select Restore
Database.
2. On the General tab, use the following settings:
Microsoft SQL Server Setup Guide for Business Objects Planning 11
Page 12
Configuring the Planning Database in SQL Server
2
Configuring User and Database Settings
Option Setting
Restore as database SRCIA
Restore From device
Devices Select the SQLTransfer.bak device
Backup number 1
Restore backup set selected
Database - complete selected
3. On the Options tab, use the following settings:
Option Setting
Force restore over existing database Checked
Restore database file as:
*
SRCIA_Data D:\SQLData\SRCIA.mdf
SRCIA_Log C:\SQLLog\SRCIA_Log.ldf
Recovery completion state Leave database operational. No addi-
* Although the logical file names cannot be changed, you can edit the
physical path to place the files in different locations. For example, your C
drive might not have enough room for the log file, in which case you
might place the file on the E drive.
Configuring User and Database Settings
After the restore is complete, all settings should be verified for accuracy and some
settings must be changed.
User Rights and Ownership
To verify srcadmin’s user rights and change ownership of the database:
1. Make sure that no account permissions have been assigned to the SRCIA database for
the srcadmin account. To do so, open the Security folder, click Logins, and select the
srcadmin account. Verify the following settings:
z On the Server Roles tab, verify that no server roles are checked for database
SRCIA.
tional transaction logs can be restored.
z On the User Mapping page (SQL Server 2005 only), verify that only the public
role is checked for the master database and the SRCIA database.
12 Microsoft SQL Server Setup Guide for Business Objects Planning
Page 13
Configuring the Planning Database in SQL Server
Configuring User and Database Settings
z On the Database Access tab (SQL Server 7.0 / 2000 only), verify that database
SRCIA is not checked for user srcadmin.
2. Open the SQL Server Query Analyzer and run the following query with the SRCIA
database selected:
sp_changedbowner ‘srcadmin’
The database is now owned by srcadmin, and the account does not have any defined
server roles. To verify, go back to the srcadmin login and see which permissions are
checked. The only permissions that should be checked are the following:
z In SQL Server 2005: map for the SRCIA database and with dbo as the
assigned user and default schema.
z In SQL Server 7.0 /2000: permit for the SRCIA database and with dbo as the
assigned user.
Database Options
The following database options should be verified:
2
1. Open the Databases folder, right-click on the SRCIA database and select
Properties.
2. Verify the following settings on the Options tab. The settings vary by SQL Server
version, as detailed below.
SQL Server 2005
Option Setting
Recovery Model Simple
Auto Create Statistics True
Auto Update Statistics True
All other options should be left at the default setting.
SQL Server 2000
Option Setting
Recovery Model Simple
Auto Create Statistics checked
Auto Update Statistics checked
No other options should be checked on this dialog.
Microsoft SQL Server Setup Guide for Business Objects Planning 13
Page 14
Configuring the Planning Database in SQL Server
2
Configuring User and Database Settings
SQL Server 7.0
Option Setting
Select into / bulk copy checked
Truncate Log on Checkpoint checked
Auto Update Statistics checked
No other options should be checked on this dialog.
The Recovery Model: Simple option and the Truncate Log on Checkpoint option are
essentially the same. If you choose not to enable this option, the log size should be set
much larger. You should carefully monitor the available log space, as it can fill up quite
rapidly with certain log transactions. You should also automatically schedule the backup
and truncation of the log file. Please refer to the SQL Server documentation for
information related to automated scheduling and transaction log truncation.
14 Microsoft SQL Server Setup Guide for Business Objects Planning
Page 15
Additional Configuration and Administration Issues
Backing Up the Database
Business Objects recommends backing up the SRCIA database in coordination with your
normal backup activities. Please consult your SQL Server documentation for information
on backup procedures.
Important: Most standard file system backups do not back up in-use files, such
as those associated with a SQL Server database. You should configure a
process where the SQL Server database is copied to a separate backup file,
which can then be backed up as normal (since it is not in use).
Recommended Operating System Settings
The following operating system settings are recommended for optimal performance with
SQL Server and Business Objects Planning. These settings apply to Windows NT Server,
Windows 2000 Server and Windows 2003 Server.
Performance and Virtual Memory
Performance should be set so that all applications are running on the same priority,
regardless of whether they are foreground or background applications. Additionally, no
virtual memory should be set up on the drive that contains the SQL Server data. The
following sections explain where to configure these options within each operating system.
Windows 2003 Server
To configure the performance and virtual memory settings in Windows 2003 Server:
1. Go to Control Panel > System and select the Advanced tab.
2. In the Performance section, click Settings to open the Performance Options dialog.
3. On the Advanced tab, verify or change the following settings, then click Apply:
Option Setting
Processor Scheduling Background Services
Memory Usage System Cache
Virtual Memory Click Change to view the virtual memory settings.
Verify that no virtual memory is set up on the data
drive (drive D in the examples in this document).
Microsoft SQL Server Setup Guide for Business Objects Planning 15
Page 16
Additional Configuration and Administration Issues
3
Recommended Operating System Settings
Windows 2000 Server
To configure the performance and virtual memory settings in Windows 2000 Server:
1. Go to Control Panel > System and select the Advanced tab.
2. In the Performance section, click Performance Settings to open the Performance
Options dialog. Verify or change the following settings on this dialog:
Option Setting
Optimize performance for Background Services
Virtual Memory Click Change to view the virtual memory settings.
Windows NT Server
To configure the performance and virtual memory settings in Windows NT Server, go to
Control Panel > System and select the Performance tab. Verify or change the following
settings on this dialog:
Verify that no virtual memory is set up on the data
drive (drive D in the examples in this document).
Option Setting
Application Performance Set the Boost slider to None.
Virtual Memory Click Change to view the virtual memory settings.
Verify that no virtual memory is set up on the data
drive (drive D in the examples in this document).
Server Memory Access
The server should be configured so that SQL Server has maximum access to the server’s
memory.
Windows 2003 Server and Windows 2000 Server
To configure the server memory access settings in Windows 2003 Server and Windows
2000 Server:
1. Go to Control Panel > Network Connections > Local Area Connection, then click
Properties. (Note: in Windows 2000 Server, the menu item is named Network and
Dial-Up Connections.)
2. On the General tab, click File and Printer Sharing for Microsoft Networks and
then click Properties.
3. Select Maximize Throughput for Network Applications. Also, verify that Make
Browser Broadcasts to LAN Manager 2.x Clients is not selected (unless the server
does have LAN Manager 2.x clients connecting to it).
16 Microsoft SQL Server Setup Guide for Business Objects Planning
Page 17
Additional Configuration and Administration Issues
Recommended SQL Server Configuration Settings
Windows NT Server
To configure the server memory access settings in Windows 2003 Server:
1. Go to Control Panel > Network and select the Services tab.
2. Click Network Services.
3. Click the name of the server and then click Properties.
4. Select Maximize Throughput for Network Applications. Also, verify that Make
Browser Broadcasts to LAN Manager 2.x Clients is not selected (unless the server
does have LAN Manager 2.x clients connecting to it).
Recommended SQL Server Configuration Settings
SQL Server has robust default configuration settings. A few minor changes to these
settings are recommended for optimal use with Business Objects Planning. After
changing the settings, restart the SQL Server.
1. To edit the configuration settings, right-click on the SQL Server name in
Management Studio (2005) or Enterprise Manager (7.0 / 2000) and select Properties.
3
2. On the Processor tab, verify or change the following settings:
SQL Server 7.0 and 2000
Options Settings
Processor Make sure that all processors listed
are checked for use.
Boost SQL Server priority on Windows checked
Use all available processors checked
SQL Server 2005
Options Settings
Automatically set processor affinity mask for all
processors
Automatically set I/O affinity mask for all processors
Maximum worker threads 0
Boost SQL Server Priority checked
checked
checked
3. For SQL 7.0 / 2000, click on the Memory tab, and verify that Dynamically
configure SQL Server is selected.
Microsoft SQL Server Setup Guide for Business Objects Planning 17
Page 18
Additional Configuration and Administration Issues
3
Recommended SQL Server Configuration Settings
18 Microsoft SQL Server Setup Guide for Business Objects Planning
Loading...