Transcript Slide 1
Microsoft SQL Server 2005
Installing and Configuring SQL Server 2005
Business Intelligence Tools
Subject: IT 700 Data Warehouse
Instructor
Assistant Professor Dr.Ohm Sornil
Member
5010421003 นายจตุพล
สาระเมฆ
5010421006 นายพิชย
ั
ศิ รก
ิ จ
ิ
5010421007 นายเรืองจิตร
สวัสดิรั์ กษ์
Introduction
Reference Text Book:
MCTS Self-Paced Training Kit (Exam 70-445): Microsoft® SQL Server™ 2005
Business Intelligence—Implementation and Maintenanceby Erik Veerman; Teo
Lachev; Dejan Sarka; Javier Loria of Solid Quality Mentors
Introduction
Hardware Requirements
A computer with a 600 MHz Pentium III compatible or faster processor.
512 MB of RAM or more (1 GB or higher recommended).
2.1 GB free hard disk space for SQL Server Installation files and samples
(which includes all the business intelligence services, client components,
developer and management tools, sample databases and projects, and online
help files).
A CD-ROM drive or DVD-ROM drive.
A Super VGA (1024 x 768) or higher resolution video adapter and monitor.
A keyboard and Microsoft mouse, or compatible pointing device.
Introduction
Software Requirements
A compatible operating system:
SQL Server 2005 can be installed on many versions of Windows server
and desktop operating systems (OS), from Windows 2000 to Windows
Vista.
SQL Server 2005 Enterprise Edition can be installed on many of the Server
operating system products (such as Windows 2000 SP4 or Windows Server
2003), but it cannot be installed on the desktop operating systems.
SQL Server 2005 Developer Edition can be installed on the same Windows
server editions that the Enterprise Edition can be installed on, and it can also
be installed on the desktop operating systems, such as Windows XP and
Windows Vista.
Note: For Windows Vista, you also need SQL Server 2005 Service Pack 2. You can
download SQL Server 2005 SP2 from http://technet.microsoft.com/enus/sqlserver/bb426877.aspx.
Microsoft Internet Explorer 6.0 SP1 or later.
Internet Information Services (IIS) 5.0 or later.
Chapter 1. Installing SQL Server 2005
Business Intelligence Tools
Exam objectives in this chapter:
Install SQL Server Analysis Services (SSAS).
Install multiple instances of SSAS.
Install a clustered instance of SSAS.
Install a SQL Server Reporting Services (SSRS)
infrastructure.
Install multiple instances of SSRS
SQL Server 2005 Core Components
SQL Server 2005 is made up of four core
components:
Relational Database Engine
Analysis Services
Reporting Services
Integration Services
The primary editions of SQL Server 2005
The SQL Server 2005 platform includes the core database engine, BI components,
and tools to support development and administration of relational database and BI
applications. The primary editions of SQL Server 2005 are:
SQL Server 2005 Enterprise Edition Includes the full features of SQL Server 2005
and provides enterprise performance and capabilities
SQL Server 2005 Developer Edition Includes the full features of SQL Server 2005
and can be used for development
SQL Server Standard Edition Includes the core functionality of SQL Server 2005 but
does not contain the scalability options and advanced capabilities that SQL Server
2005 Enterprise Edition and SQL Server 2005 Developer Edition contain
SQL Server 2005 Workgroup Edition Contains the core database components, but is
limited in functionality, with only a small subset of BI features
SQL Server 2005 Express Edition Freely distributable lightweight edition of SQL
Server 2005 has limitations, but can be used for development and to embed in
applications
SQL Server 2005 Compact Edition The lightweight mobile version of SQL Server
2005 database engine
Installation Files
All the features of SQL Server 2005 are available on a single
DVD and can be installed on the same server. You can also
install the features on separate servers if that works best
within your BI architecture.
The components of SQL Server 2005 are split into two
primary areas:
Server components, which include the core services of SQL Server,
such as the Database Engine, Integration Services, Analysis Services,
and Reporting Services; the last three in the list are the primary focus
of this training kit.
Client components, which include the management and design tools
and other supporting tools and features to assist in the development
and management of SQL Server 2005.
SQL Server Management and Development
Tools
SQL Server 2005 includes several client tools
for security administration, configuration
management, performance tuning, and
support. The main tools are:
SQL Server 2005 Management Studio (SSMS)
SQL Server 2005 Business Intelligence
Development Studio (BIDS).
SQL Server 2005 Management Studio
(SSMS)
SSMS targets the management and support of applications for the Database
Engine, Analysis Services, Reporting Services, and Integration Services (as well
as for the Compact Edition). Figure 1-1 shows SSMS connected to SQL Server
2005.
Figure 1-1. SSMS provides management capabilities for the SQL Server core components.
SQL Server 2005 Business Intelligence
Development Studio (BIDS).
BIDS targets the design and development of the SQL Server 2005 BI components,
including Integration Services, Analysis Services, and Reporting Services. Figure
1-2 shows a screen shot of the BIDS main start screen with a project titled "ETL
Solution" open.
Figure 1-2. BIDS provides the development interface for designing core SQL Server BI applications.
Selecting Installation Components
The prerequisite software: (included on the
installation CD)
Microsoft .NET Framework 2.0
SQL Server Native Client
Internet Information Server (IIS), which is
needed for Reporting Services.
Invoking the SQL Server 2005 Installation
To install only the SQL Server 2005 tools or samples, execute setup.exe from the Tools
folder of the installation DVD or the installation setup files folder.
To install all the components of SQL Server 2005 (including the client tools) or just the
server components, execute setup.exe from the Servers folder of the installation DVD or
the installation setup files folder.
Figure 1-3. Before running the SQL Server 2005 setup, the setup files will install any missing prerequisites.
System Configuration Check
Then the SQL Server installation process will scan your machine for the required
configuration. The System Configuration Check results will indicate whether configuration
changes need to be made before the installation proceeds. If any configurations are not
correct, setup will block the installation of SQL Server 2005. Figure 1-4 shows a completed
and successful configuration check.
Figure 1-4. A successful System Configuration Check
Components To Install
You will be prompted to enter the product key. Once it is entered successfully, you will be
able to choose the components that you want to have installed. For a complete installation,
select all the components, as shown in Figure 1-5.
Figure 1-5. On the Components To Install screen, select all the components for a complete SQL Server installation.
Features Selection
To include the sample databases and applications with the install, you need to select them by
clicking the Advanced button to open the detailed Feature Selection screen, and then under
Documentation, Samples, And Sample Databases, clicking both the Sample Databases and
Sample Code And Applications options and choosing Entire Feature Will Be Installed On
Local Hard Drive, as shown in Figure 1-6.
Figure 1-6. To install the samples or customize elements of the installation, such as the drive location, use the
Advanced Feature Selection screen.
Choosing Installation Details
Instance Name
The first selection you will be prompted to make will determine the instance name.
Several components of SQL Server 2005 can be installed on the same machine
multiple times. Each time the same component is installed, it needs a new instance
name for that installation. Instances apply to the Database Engine, Analysis Services,
and Reporting Services.
Choosing the Default Instance means that the installation components that you selected will
be installed with no name.
Alternatively, you can name the new installation instance with the Named Instance option.
When you choose the default instance, the connection strings to access the servers need
to contain only the server name and not the named instance extension. Having multiple
named instances also allows you to install different versions and editions of SQL
Server on the same physical machine. For example, you can have multiple installations
of SSAS on the same physical machine, each at different service pack levels.
Best Practices: Renaming an Analysis Services instance
Analysis Services comes with a tool that allows you to rename an installed instance. Just run
the asinstancerename.exe executable found in C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\VSShell\Common7\IDE.
Service Account
On the Service Account screen, you can choose the system security account under which to
run the Analysis service. Figure 1-7 shows the selection screen on which you can indicate
the service account to be used.
Figure 1-7. In the Service Account installation window, you can define the security accounts that are used
when the service starts.
Account Options for Running SQL Server
Services
Table 1-1 shows the options for the way the SQL Server 2005 services are run on the local
machine.
Table 1-1. Account Options for Running S QL S erver S ervices
S election
Des cription
Customize for each service account
Allows you to customize the service account settings for
each component service.
Use the built-in system account
The Local system account has full access to local
machine resources. The Network service account has
more limited access but is more secure.
Use a domain user account (Username,
Password, Domain)
Allows you to use a domain account that has selective
access to the local machine and domain resources.
Start services at the end of setup
The services you selected to install will be started when
the installation is complete, and they will start
automatically whenever the server is started.
Authentication Mode
The Authentication Mode configuration setting is
specific to the SQL Server database engine and
defines the way in which users are able to connect.
Windows Authentication Mode specifies that a user can
connect only with the local machine account or domain
account.
Mixed Mode authentication allows users to connect with
Windows Authentication or with authentication defined in
SQL Server.
Note that you can change the Authentication Mode
after installation in the Server Properties window.
Collation Settings
Figure 1-8 shows the Collation Settings screen, which defines the way in which the database
engine handles data sorting based on locale settings, case sensitivity, and binary order.
The Database Engine collation settings can be defined independently from Analysis Services
collation settings. To define separate collations, select the Customize For Each Service
Account check box and change the value of the Service drop-down list for each service.
Figure 1-8. The Collation Settings screen lets you define the way data will be sorted.
Installation Completion
The final two setup screens allow you to choose whether to send errors to Microsoft and to
confirm the installation detail summary. Clicking Finish will begin the file copy and
installation process, and the Setup Progress screen, shown in Figure 1-9, will keep you upto-date on the installation progress.
Figure 1-9. The Setup Progress screen indicates status of the installation process for each component.
Keeping Current with Service Packs
Microsoft occasionally releases service packs for SQL Server that contain rollups of patches,
security updates, and at times, even additional product features. To find the most recent
service pack for SQL Server, go to http://www.microsoft.com/sql,
Figure 1-10. The Service Pack installation process allows you to select components to upgrade.
Service Pack Installation Progress
As the installation progress page for the initial component installation does, an Installation
Progress window will provide the current status of the service pack installation, as shown in
Figure 1-11.
Figure 1-11. The Service Pack Installation Progress window highlights the installation status.
Clustering Analysis Services and Managing
Instances
SQL Server 2005 leverages the clustering support provided by Microsoft
Clustering Services (MSCS). Within the SQL Server 2005 BI components,
Analysis Services is cluster-aware and can be installed in an MSCS installation.
For SQL Server clustering, MSCS uses a shared-nothing model, which means that
the drive volumes (that hold the database and cube data) can only be controlled by
one machine at a time in the cluster. This provides automatic failover of the drives
if a server has a hardware or software problem that causes the SQL Server 2005
service to stop. The servers in a cluster are called nodes, and the Database Engine
instances or Analysis Service instances are called failover instances when installed
in an MSCS installation.
To install Analysis Services in a cluster, begin the setup process as described in
Lesson 1 on a cluster node in the MSCS cluster. Follow the same procedures that
are described in Lesson 1 until you get to the Components To Install page.
Proceed with the following steps:
Clustering Analysis Services and Managing
Instances
1.
When you install Analysis Services on a cluster node, SQL Server 2005 will recognize its environment
and enable the option to install a failover instance of Analysis Services. Select the Create An Analysis
Services Failover Cluster check box. This option is grayed out when installing SSAS on a non-cluster
machine, but it is available when installed on a cluster node.
2.
For the Instance Name, you can choose the Default Instance (if no other instances have been installed), or
you can choose Named Instance and provide a name.
3.
Because you indicated that you wanted to install on a failover cluster instance, you will now need to
provide a Virtual SQL Server name. This name will be used along with the instance name to identify the
Analysis Services connection for access. For example, if you named the virtual server VSQLSSASCL1
and the instance name was TK70445, then your server reference in any connection string would be:
VSQLSSASCL1\TK70445
In other words, the server name you enter here will fail over with the service, and all the connections will
be able to access Analysis Services no matter which node the Virtual SQL Server is currently running on.
4.
The Virtual Server Configuration page requires you to assign an IP address for each subnet. These IP
addresses will also be tied to the Server name, and therefore, if there is an application or hardware failure
that causes the Analysis Services service to fail over, the IP address (or addresses) will follow.
Best Practices: Virtual name and IP address
Both the Virtual SQL Server name and the IP address(es) must be unique on the network. The
Virtual SQL Server name cannot be the same as the physical server name or the cluster name.
Clustering Analysis Services and Managing
Instances
5.
The Available Cluster Group screen shows all the available cluster groups into which you
can install Analysis Services. MSCS groups resources that have dependencies and that need
to fail over together, such as drive volumes, server names, IP addresses, and application
services that need these resources. Only cluster groups with shared drives are available for
use, because Analysis Services requires its data to be stored on a shared drive for clustering.
After choosing the cluster group that contains the appropriate drive volumes, you can
customize where the data files are stored on one of the shared drives.
Best Practices: Analysis Services program files
The path to where the program files are stored cannot be modified. This is because the
Analysis Services program files will reside on the local system drive of the server. In fact,
when the file installation process runs, the Analysis Services program files are installed on
all the nodes of the cluster in the same location.
6.
On the Cluster Node Configuration page, you can choose the nodes on the cluster on which
the Analysis Services failover instance can run or to which it can fail over. Highlight the
nodes, and then use the arrows to move the nodes to the Required Node list.
Clustering Analysis Services and Managing
Instances
7.
Choose an account on the Remote Account Information screen that has administrator rights
on all the nodes selected in Step 6. This account will be used to install Analysis Services, but
it is not used after the installation.
8.
The Service Accounts need to have the same access rights on all nodes of the cluster.
Therefore, you need to choose a domain account and cannot use the local system account or
network service account.
9.
The final cluster-specific screen is the Domain Groups for Clustered Services screen. Enter
the Domain and Group that Analysis Services will use. The Analysis Services service
account needs the ability to add users to the group, and the best practice is to make the group
exclusive to this Analysis Services instance installation.
10.
The final screens define the authentication, collation, and error reporting for the installation
and are identical to the standard installation screens reviewed in Lesson 1.
Installing Multiple Instances of Reporting Services
The creation of two databases in SQL Server called ReportServer and ReportServerTempDB
that are used for catalog storage and data caching.
The creation of two virtual servers in IIS called Reports and ReportServer that are used for
the Report Manager Web interface and the SSRS Web Service, respectively.
Figure 1-12. For the second and subsequent installations of Reporting Services on a server, choose
the Named instance option and type in a new instance name.
If you need to install a second instance of Reporting
Services, you can name the instance (or use the Default
Instance if a named instance was used for the first
installation). The Reporting Services installation is
similar to the steps outlined in Lesson 1, except that you
will use a different named instance and you will not be
able to have the installation use the default
configuration. Figure 1-12 shows the Report Server
Installation Options screen, which shows that the option
to install the default configuration is grayed out.
Chapter 2. Configuring SQL Server 2005
Business Intelligence Components
Exam objectives in this chapter:
Configure SSAS.
Configure server and database roles and permissions.
Configure an SSRS infrastructure.
Configure query logging.
Configure error logging.
Configure disk allocation.
Configure SSRS for Internet deployment.
Create and configure SSRS instances by using the Reporting
Services Configuration tool (Rsconfig.exe).
Install an SSRS infrastructure.
Manage private keys for encryption
Using the Report Server Configuration Manager
Tool for Server Setup and Management
The installation of the SSRS server component includes
the Reporting Services Configuration Manager tool,
a user interface (UI) administration tool for configuring SSRS server settings.
It can be found on the Start menu in All Programs\SQL Server 2005\Configuration Tool.
This tool performs the common setup and configuration tasks required to
implement an SSRS instance, including:
Creating the Virtual Directories in IIS.
Configuring the Service Startup account.
Defining the ASP.NET account.
Setting up the database connection to the SSRS Repository.
Managing the symmetric encryption keys.
Performing initialization steps to enable new instances for a scale-out deployment,
which is commonly called a Web farm.
Defining operational accounts for email and other administration tasks.
Lesson 1. Configuring the SSRS
Architecture and Instances
Using the Report Server Configuration Manager Tool for
Server Setup and Management. This tool performs the
common setup and configuration tasks required to implement
an SSRS instance, including:
Creating the Virtual Directories in IIS.
Configuring the Service Startup account.
Defining the ASP.NET account.
Setting up the database connection to the SSRS Repository.
Managing the symmetric encryption keys.
Performing initialization steps to enable new instances for a scale-out
deployment, which is commonly called a Web farm.
Defining operational accounts for email and other administration
tasks
Configuration Report Server
When you install SSRS as described in Chapter 1, and you choose not to apply the default
configuration to SSRS during the installation, then you will need to use the Reporting
Services Configuration Manager to set up the components and settings that are required for
the SSRS instance to be enabled. Figure 2-1 shows the Reporting Services Configuration
Manager tool as it will appear for an instance that was installed without the default
configuration options.
Figure 2-1. The RSCM consolidates all configuration tasks needed to set up SSRS after installation.
Enable an instance for development and
reporting
1.
Create the Virtual Directories in IIS. In the Report Server Configuration Manager,
navigate to the Report Server Virtual Directory property page on the left navigation pane.
You then have the option to create a new Virtual Directory for the Report Server. Figure 2-2
shows the dialog box that appears when you select New in the Report Server Virtual
Directory settings page.
Figure 2-2. You can define a new Virtual Directory in the Report Server Virtual Directory dialog box.
The Web service that SSRS uses to perform report management, publishing, and rendering operations is
located in the Report Server Virtual Directory. By default, the Virtual Directory is named ReportServer,
but this can be changed if an instance that uses the Virtual Directory already exists or if your application
requires a name that is directly related to the application.
Enable an instance for development and
reporting
2.
Create the Virtual Directory for the Report Manager, which is the Web-based
management tool for Reporting Services to set up directories and manage security on the
Web site. Configure this in the same way you configured ReportServer. The default name for
the Virtual Directory for Report Manager is Reports. Using Report Manager is reviewed in
Chapter 18, "Managing and Securing SSRS Reports."
3.
The Windows Service Identity property page allows you to select a local or domain
account/group to be used to run the Windows service. Likewise, the Web Service Identity
property page allows you to select a local or domain account/group that is used to run the
ASP.NET service account.
4.
Set up the SSRS repository databases, which are used by SSRS to store the report
definitions, data sources, virtual folders for Report Manager, and security, and for temporary
operations such as report caching. The databases can be created on the same local machine
or on a remote machine. To create the databases, navigate to the Database Setup page,
connect to the database instance (local) or a remote database instance, and then select New
next to the Database name drop-down list. Figure 2-3 shows the new database entry screen,
where you can set the Reporting Services database name and the credentials needed for
database creation rights.
Enable an instance for development and
reporting
Figure 2-3. SSRS uses a SQL Server database to store report definitions, data sources, and
security settings.
By default, the repository database is named ReportServer. A temporary database is also created that uses the base
name of the repository database appended with TempDB. So the default name of the temporary database is
ReportServerTempDB.
Enable an instance for development and
reporting
5.
Initialize the instance. If the Initialization settings page is still marked as unconfigured (a
red X appears next to Initialization in the navigation list), then the final step is to go to the
Initialization Settings page, and click the Initialize button. Note that the Initialization page
can also be used for scale-out deployment to add additional SSRS instances to a Web farm.
See the section titled "Using the Reporting Services Command-Line Tools to Manage
SSRS" later in this chapter to find out how the rskeymgmt.exe command-line tool is used to
handle scale-out server management.
6.
Start the SSRS instance. To do this, navigate back to the Server Status settings page, and
click the Start button to start the service. To verify that the instance is running correctly,
open a Web browser and connect to the Report Manager at http://localhost/reports, or
connect to the virtual directory you created for the Report Manager in Step
Although your SSRS instance is now installed and started, it is a good idea to go one step
further and back up your encryption key, which you will need if you have to recover the
content of an SSRS installation. To back up the encryption key, navigate to the Encryption
Key settings page in the Reporting Services Configuration Manager. Figure 2-4 shows the
options within the tool.
Enable an instance for development and
reporting
Figure 2-4. The RSCM lets you back up and restore your encryption keys.
Your choices are to:
•Back up the key to store it in a password protected file.
•Restore a key in the case of rebuilding a standalone instance.
•Change the key, which will re-encrypt the content with a newly generated encryption key.
•Delete the encrypted content, which will remove the deployed content items from the Report Server
database
Using the Reporting Services Command-Line
Tools to Manage SSRS
Using rsconfig.exe
The rsconfig.exe tool manages the connection and settings for the SSRS instance, mainly to
manage the repository database connection, but also to set up the default credentials for
report execution against databases. The rsconfig.exe command parameters define the
connection to the SSRS instance and then define the database connection to the Report
Server database.
Use the parameters in Table 2-1 for the connection to the SSRS server and instance.
Table 2-1. rs config.exe S S RS Connection Parameters
Command Parameter
Des cription
/m "remote machine name"
The name of the server installed with SSRS. This is an
optional parameter, with localhost used when it is not
explicitly defined.
If a named instance has been installed, you use this to
define the instance.
/i "instance name"
Using the Reporting Services Command-Line
Tools to Manage SSRS
If you are configuring the Report Server database connection, you will need to specify the
connection details. Use the command-line parameters in Table 2-2 to set these
Table 2-2. rs config.exe Databas e Connection Parameters
Command Parameter
Des cription
/c
Indicates that you are defining the connection information with other
parameters. This parameter does not use a value and is required if
you are defining the connection.
/s "database server name"
Used to identify the SQL Server name and instance of the host Report
Server database.
Defines the database name of the Report Server database on the
specified SQL Server instance.
Indicates whether the database connection will use "Windows" or
"SQL" authentication to connect to the Report Server database.
Defines the username that will be used if SQL Server authentication is
specified; if a Windows domain account other than the SSRS service
account is used, this represents the Domain/ Account. This parameter
is also used for the default source database connection, mentioned in
Table 2-3.
Defines the password for the account specified in the /u parameter.
This parameter is also used for the default source database
connection, mentioned in Table 2-3.
/d "Database name"
/a "Authentication Method"
/u "username"
/p "password"
/t
Optionally writes out error messages to the SSRS trace log.
Using the Reporting Services Command-Line
Tools to Manage SSRS
Table 2-3. rs config.exe Unattended Account Parameter
Command Parameter
Des cription
/e
Indicates that you are setting the data source account to be used for
an unattended report execution. This parameter requires that you also
use the /u and /p username and password parameters. Although it is
not required, the /t parameter can also be used.
Using the Reporting Services Command-Line
Tools to Manage SSRS
Examples Using rsconfig.exe
The first example below uses the rsconfig.exe command statement to connect a locally installed
SSRS instance to a local database called ReportServer using Windows Authentication:
rsconfig.exe /c /s (local) /d ReportServer /a Windows
In the next example, a locally installed SSRS instance is connected to a remote database server
called ProdSQLSvr using the specified domain account Corporate\SSRSSvc:
Code View:
rsconfig.exe /c /s ProdSQLSvr /d ReportServer /a Windows /u Corporate\SSRSSvc /p
pass@word1
Note the difference between the preceding command-line example and the one that follows. In the
next one, the /m switch is added, which specifies a remote SSRS instance. Also, the /a SQL
parameter is added to specify that SQL authentication is used for the connection to the ReportServer
database on ProdSQLServer:
rsconfig.exe /c /m ProdSSRSSvr /s ProdSQLSvr /d ReportServer /a SQL /u SSRS_Login /p
pass@word1
Finally, this last example sets the account to be used for unattended report execution to a domain
account called Guest and logs any errors to the SSRS trace:
rsconfig.exe /e /u Corporate/Guest /p pass@word1 /t
Using the Reporting Services Command-Line
Tools to Manage SSRS
Using rskeymgmt.exe
SSRS includes a second command-line utility to assist in the management of the symmetric
encryption keys that SSRS uses to secure and encrypt content in the Report Server database.
This utility can perform common operations such as backup and restore, but it is also used to
help in the management of SSRS instances that are part of scale-out deployments.
Table 2-4. rs keymgmt.exe Key Management Tas ks
Command Parameter
Des cription
/e
Specifies that the SSRS encryption key should be extracted to a file for
backup.
Specifies that the SSRS encryption key should be restored from a file
and overwritten.
Deletes the encryption key on the SSRS instance or instances and
deletes all the encrypted data.
Replaces the existing encryption key with a newly generated one, and
re-encrypts all the existing content with the new key.
Specifies the location of the file for the encryption key if the /e or /a
parameter is used.
The password used to secure the encryption key file so that if the file is
found, the encryption key is still secure.
Optional argument used to specify a named instance of SSRS if the
SSRS instance is local to the command-line execution. This is not
required if SSRS has been installed with the default (no name)
configuration.
Captures errors to the SSRS trace log.
/a
/d
/s
/f "file path"
/p "file password"
/i "local SSRS instance name"
/t
Using the Reporting Services Command-Line
Tools to Manage SSRS
Examples Using rskeymgmt.exe for Standard Management Tasks
The following statement backs up the encryption key to a file named SSRS_Keys (no extension)
with a password set:
rskeymgmt.exe /e /f c:\SSRS_Keys -p pass@word1
In the next example, the backed-up keys are restored to the local server with the named instance
SSRSAdmin:
rskeymgmt.exe /a /f c:\SSRS_Keys /p pass@word1 /i SSRSAdmin
The final example deletes all the keys and encrypted content on the Report Server database the local
instance is connected to:
rskeymgmt.exe /d
It is important to note that when you are performing any of the operations described above, you
cannot run them against a remote server. They must be executed locally on the server. If, rather
than the default, a named instance of SSRS exists, use the /i command-line parameter to specify
the instance name.
Using the Reporting Services Command-Line
Tools to Manage SSRS
You use the set of parameters in Table 2-5 to add and remove SSRS instances to help
manage a scale-out SSRS deployment. These share the /i and /t parameters described in
Table 2-4. The difference is that you can reference a remote SSRS instance that you want to
add or remove from a scale-out deployment.
Table 2-5. rs keymgmt.exe S cale-Out Ins tance Management
Command Parameter
Des cription
/j
Adds a remote instance of SSRS to the Report Server database of a
local instance. The remote server and instance are specified with the
/m and /n parameters, and if a named instance is used locally, it is
specified using the /i parameter.
Removes an instance of SSRS from a scale-out deployment
implementation. The instance to be removed is identified by the
Installation ID, a unique identifier mapped to the instance and specified
in the reportserver.config file.
Specifies the account of a local administrator on the server where the
remote SSRS instance (the instance that will be joining a scale-out
deployment) runs. This parameter is optional if the user who is
executing rskeymgmt has local administrator rights on the remote
server.
This defines the password of the local administrator account specified
by the /v parameter.
In connecting to an SSRS instance on a remote machine, this
parameter is used to specify the server.
This is used in conjunction with the /m parameter to specify the SSRS
instance name on a remote machine. If the default instance is used,
this is not required.
/r "GUID Installation ID"
/u "account name"
/v "password"
/m "remote SSRS Server Name"
/n "remote SSRS instance name"
Using the Reporting Services Command-Line
Tools to Manage SSRS
Examples Using rskeymgmt.exe to Manage Scale-Out SSRS Installations
In the following example, the remote SSRS instance ProdSSRSSvr1 is joined to the scale-out
implementation of SSRS shared by the local SSRS instance. The remote local administrator account
is Corporate\SSRSSvc with the associated password:
rskeymgmt.exe \j \m ProdSSRSSvr1 \u Corporate\SSRSSvc \v pass@word1
The next example removes an instance of SSRS that is part of a scale-out deployment. The UID
was acquired from the rsreportserver.config file:
rskeymgmt.exe -r {632e859c-5352-4712-a9e5-f28a0206a68f}
Best Practices: Using Reporting Services Configuration Manager for scale-out deployment
Many of the functions that the command-line tools perform can be accomplished through the Reporting
Services Configuration Manager. One of these functions is to manage a scale-out SSRS implementation and
add or remove SSRS instances from the implementation. For more information, see the SQL Server 2005
Books Online (BOL) topic "How to: Configure a Report Server Scale-Out Deployment (Reporting Services
Configuration)," ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en /rptsrvr9/html/b30d0308-4d9b-4f85-9f83dece4dcb2775.htm.
Using the Reporting Services Command-Line
Tools to Manage SSRS
Using rs.exe
The final command-line utility, rs.exe, lets you script SSRS report operations such as deployment and
management and run scripts through the command line. The rs.exe tool references a Report Server Script
(.rss) file, which contains Visual Basic .NET code based on the Web Service Description Language
(WSDL) API. To see how to build an .rss file, read the SQL Server 2005 BOL topic "Scripting with the rs
Utility and the Web Service." The rs.exe tool works similarly to the way other SSRS command-line tools
work, using the command-line parameters in Table 2-6.
Table 2-6. rs .exe Report Deployment and Management
Command Parameter
Des cription
/i "input .rss file"
/s "SSRS Server URL"
/u "username"
Specifies the Report Server Script file to execute.
Defines the URL path to the SSRS Report Server virtual directory.
To override the user running the command, this parameter can define
a different domain\user account to be used to connect to the Report
Server instance.
Used to specify the password for the associated username of the
account defined with the /u parameter.
Used to override the default timeout of 60 seconds; the l (lowercase L)
parameter is measured in seconds, with 0 representing an unlimited
execution time.
/p "password"
/l "timeout seconds"
/b
/e "SOAP endpoint"
/v "Global Variable mapping"
/t
Runs the commands in a batch so that if a failure occurs anywhere in
the script, the entire operation will be rolled back.
Defines the Simple Object Access Protocol (SOAP) SSRS Web
service endpoint to use. By default, the SSRS 2005 management
endpoint mgmt2005 is used, but the SSRS 2005 execution endpoint or
the SSRS 2000 management endpoint can also be used.
If the script contains embedded variables, the /v parameter can pass
values into the variables.
Captures errors to the SSRS trace log.
Using the Reporting Services Command-Line
Tools to Manage SSRS
Examples Using rs.exe
In the example below, an RSscript.rss script is executed against a local instance of SSRS that was
installed with the default ReportServer virtual directory for Report Server:
rs.exe /i RSscript.rss /s http://localhost/ReportServer
In the more complicated example below, a script is executed under the corporate\SSRSSvc account
while passing the value "ProdSQLSvr" into the script for the variable named vDataSource:
rs.exe /i RSscript.rss /s http://localhost/ReportServer /v vDataSource="ProdSQLSvr" /u
Corporate\SSRSSvc /p pass@word1
Configuring the Report Server for SSL
Communication and Internet Deployment
Secure Certificate-Based Communication
Report management can be deployed and rendered to leverage port 80 HTTP connections, but it can also
be set up to require HTTPS Secure Sockets Layer (SSL) connections or a combination of connection
types. SSRS allows four security levels for communication to the Report Server. To set SSRS to require
SSL connections, you need to change the RSReportServer.config file, located in the Program
Files\Microsoft SQL Server\MSSQL.#\Reporting Services\ReportServer folder. The # represents the SQL
Server instance for the Reporting Services instance.
Open the RSReportServer.config file and locate the SecureConnectionLevel property, which will look like
this:
<Add Key="SecureConnectionLevel" Value="0"/>
Table 2-7. S ecureConnectionL evel Values
Property Value
0
1
2
3
Des cription
Secure connections are not required but can be used.
Secure connections are required for calls that specify user credentials
or that deal with data sources.
Secure connections are required for report rendering and for direct
Web service calls and for any connection or data source operations.
All communication must use secure connections.
In addition to setting this property, you can define the IIS authentication method, such as Basic Authentication, Windows
Authentication, or Anonymous access, for the Virtual Directory. Windows Authentication is recommended for security, but Basic
Authentication with SSL will provide secure connections from machines that are not logged on to your corporate domain.
Configuring the Report Server for SSL
Communication and Internet Deployment
Configuring SSRS for Internet Deployment
When you need to make the SSRS instance accessible on the Web for Internet deployment
and rendering, certain settings will prevent unnecessary security risks. Take these
considerations into account when setting up an SSRS instance that will be accessible from
the Internet:
Your Report Server database should always remain behind the firewall.
You can install Report Manager on a remote Internet-facing server separate from Report Server if
you need access to the Report Manager only. This will require a separate license for SQL Server
2005, and you will not have Report Builder capabilities or report drill-through capabilities (for
example, to Excel, Web archive, and HTML3.2 formats).
Report Server and Report Manager can be installed together on an Internet-facing machine in order
to connect to the Report Server virtual directory for deployment. When deploying Report Server
and Report Manager over the Web to an SSRS instance, be sure to put the fully qualified domain
name in the address.
For more about deploying SSRS on the Internet, see the SQL Server 2005 BOL topic "Internet
Deployment Considerations," ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html
/b7feb272-ffcb-4e14-ab58-3212f3b0ac74.htm.
Lesson 2. Setting SSAS Server Security and
Server Properties
Setting SSAS Administrative Roles and Permissions
When it comes to setting up SSAS security for management activities, there are two primary
security roles:
The server role, which provides access to complete SSAS server functions
Database roles, which define database-level administration tasks and end-user data access
SSAS security is based on Windows authentication only. Unlike the SQL Server database
engine, which contains a second security option called SQL authentication, SSAS uses only
Windows authentication.
Exam Tip
SSAS uses only Windows authentication. This means that SSAS security can only be assigned to
local users and groups and domain users and groups.
SSAS Server Role
A single server-level role, which provides complete SSAS access to assigned users. The
server role assignment is available through the server properties window.
To manage the users and groups assigned to the role, connect to SSAS through the SQL
Server Management Studio (SSMS), right-click the SSAS server, and then select Properties.
Figure 2-5 shows the Analysis Services Properties window with the Security settings page
selected on the left.
Figure 2-5. The Analysis Services Properties window includes a Security tab to assign the server role to users and groups.
SSAS Database Roles
The database role. Database roles are used for two primary purposes:
For administrative task assignments
For user access to data and data-related functionality
Figure 2-6. The Create Role window lets you assign a role administrator and assign read and/or process functions.
Three database-level permissions
Full control (Administrator) Assignment to the Full control
role gives complete access to the database, including data,
schema, processing, and operations. Administrators can also
manage security roles.
Process database The Process database role allows SSAS
processing. This means a user can be limited to processing
the database in which the role is created. However, this option
does not give a user read access to the data or definition; read
access must be assigned separately.
Read definition The Read definition role lets a role member
see the full definition of a database; it does not allow a user to
have data-access rights or processing rights.
Editing SSAS Server Properties
Server-level properties that apply to the entire instance. The settings include service-level
properties to manage and tune the ways in which some functionality is applied, and they
include properties that allow various levels of logging.
Figure 2-7. To manage SSAS server properties, connect to SSAS through SSMS, right-click the Server,
and select Properties; properties are managed on the General page.
Editing SSAS Server Properties
Modifying Query and Error Logging
The logging properties in the SSAS server let you define what information is captured and
how it is captured. The logging options are listed under the Log category in the server
properties list.
Flight Recorder Activity and Error Log
The first log properties to note are the ones listed under the Log\FlightRecorder property. To
see all the flight recorder properties, you must select the Show Advance (All) Properties
check box. The flight recorder is an error and activity log for SSAS. To use the flight
recorder:
1. Set the Log\FlightRecorder\Enabled property to True.
2. Optionally, set the Log\File property; the default msmdsrv.log is set for the flight recorder.
3. Restart the SSAS service for the flight recorder to take effect.
By default, the flight recorder captures default activity such as processing errors and server-level errors,
but it does not capture queries. However, the flight recorder leverages SQL Server Profiler tracing
definitions, which means that you can define your own SSAS trace and capture the trace definition. With
the Log\FlightRecorder\TraceDefinitionFile property, you can override the default capture. For more
information about the flight recorder properties, see the SQL Server 2005 BOL topic "Log Properties,"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en /uas9/html/33fd90ee-cead-48f0-8ff9-9b458994c766.htm.
Query Log
The Log\QueryLog properties. These enable you to capture queries that are run against
any SSAS database in the instance. You can use the queries that are captured to optimize
aggregation.
Table 2-8. S S AS S erver L ogging Properties
Property Name
Des cription
Log\QueryLog\ CreateQueryLogTable
Set this property to true if you need the table to be created by the
QueryLog operation.
Log\QueryLog\ QueryLogConnectionString This property uses the general Connection Manager to define a
connection string to the server and database to which the log table will
be captured.
The QueryLogSampling property defines how often queries are
Log\QueryLog\ QueryLogSampling
captured. The default value of 10 means that only 1 of every 10
queries is captured. This reduces the overhead.
Log\QueryLog\ QueryLogTableName
You can define the name of the table to which the queries will be
captured.
The query log does not capture the full MDX query that is sent to the server. Instead, it captures a numeric list of the
hierarchies and attributes used in each dimension, such as 01,00000010200000,100,00,100,12000. Each comma separates the
level numbers between dimensions. See Chapter 6, "Developing SSAS Cubes," for information about dimensions and
attributes. The server can use this list to know which hierarchies were accessed and at what level, so it can optimize its
aggregates without having the details of the query.
Defining Default Directories
The SSAS server properties also let you define the default location for data, backups, and
logs, as Table 2-9 describes.
Table 2-9. S S AS S erver File L ocation Properties
Property Name
Des cription
BackupDir
Set this to the name of the folder in which SSAS backups should be
stored by default. The backup location can be overridden, but this
assigns the default backup directory.
Set this to the name of the folder in which SSAS data should be stored
by default. Note that all dimension data will be stored in this location,
but partitions and aggregates can be customized within the cube and
partition properties.
Set this to the name of the folder in which logs should be written by
default.
DataDir
LogDir
Summary
We explored the following objectives:
Install SQL Server Analysis Services (SSAS).
Install a SQL Server Reporting Services (SSRS) infrastructure.
Configure query logging.
Configure error logging.
Configure disk allocation.
Configure server and database roles and permissions.
Configure an SSRS infrastructure.
Install multiple instances of SSRS
Configure SSAS.
Install multiple instances of SSAS.
Install a clustered instance of SSAS.
Configure SSRS for Internet deployment.
Create and configure SSRS instances by using the Reporting Services Configuration tool
(Rsconfig.exe).
Install an SSRS infrastructure.
Manage private keys for encryption
The End
Thank you