Microsoft sql server 2005 installing and configuring sql server 2005 business intelligence tools
Download
1 / 60

Chapters 1 and 2 Team1 - PowerPoint PPT Presentation


  • 183 Views
  • Updated On :

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 นายเรืองจิตร สวัสดิ์รักษ์.

Related searches for Chapters 1 and 2 Team1

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Chapters 1 and 2 Team1' - Donna


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Microsoft sql server 2005 installing and configuring sql server 2005 business intelligence tools l.jpg

Microsoft SQL Server 2005Installing and Configuring SQL Server 2005 Business Intelligence Tools

Subject: IT 700 Data Warehouse

Instructor

Assistant Professor Dr.Ohm Sornil

Member

5010421003 นายจตุพล สาระเมฆ

5010421006 นายพิชัย ศิริกิจ

5010421007 นายเรืองจิตร สวัสดิ์รักษ์


Introduction l.jpg
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


Introduction3 l.jpg
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.


Introduction4 l.jpg
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/en-us/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 l.jpg
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 l.jpg
SQL Server 2005 Core Components Tools

  • 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 l.jpg
The primary editions of SQL Server 2005 Tools

  • 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 l.jpg
Installation Files Tools

  • 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 l.jpg
SQL Server Management and Development Tools 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 l.jpg
SQL Server 2005 Management Studio (SSMS) Tools

  • 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 l.jpg
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 l.jpg
Selecting Installation Components (BIDS).

  • 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 l.jpg
Invoking the SQL Server 2005 Installation (BIDS).

  • 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 l.jpg
System Configuration Check (BIDS).

  • 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 l.jpg
Components To Install (BIDS).

  • 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 l.jpg
Features Selection (BIDS).

  • 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 l.jpg
Choosing Installation Details (BIDS).

  • 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 l.jpg
Service Account (BIDS).

  • 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 l.jpg
Account Options for Running SQL Server Services (BIDS).

  • Table 1-1 shows the options for the way the SQL Server 2005 services are run on the local machine.


Authentication mode l.jpg
Authentication Mode (BIDS).

  • 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 l.jpg
Collation Settings (BIDS).

  • 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 l.jpg
Installation Completion (BIDS).

  • 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 up-to-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 l.jpg
Keeping Current with Service Packs (BIDS).

  • 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 l.jpg
Service Pack Installation Progress (BIDS).

  • 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 l.jpg
Clustering Analysis Services and Managing Instances (BIDS).

  • 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 instances26 l.jpg
Clustering Analysis Services and Managing Instances (BIDS).

  • 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.

  • 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.

  • 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\TK70445In 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.

  • 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 instances27 l.jpg
Clustering Analysis Services and Managing Instances (BIDS).

  • 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 instances28 l.jpg
Clustering Analysis Services and Managing Instances (BIDS).

  • 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.

  • 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.

  • 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.

  • 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 l.jpg
Installing Multiple Instances of Reporting Services (BIDS).

  • 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 l.jpg
Chapter 2. Configuring SQL Server 2005 Business Intelligence Components

  • Exam objectives in this chapter:

    • Configure SSAS.

      • Configure query logging.

      • Configure error logging.

      • Configure disk allocation.

    • Configure server and database roles and permissions.

    • Configure an SSRS infrastructure.

      • 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 l.jpg
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 l.jpg
Lesson 1. Configuring the SSRS Architecture and Instances Server Setup and Management

  • 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 l.jpg
Configuration Report Server Server Setup and Management

  • 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 l.jpg
Enable an instance for development and reporting Server Setup and Management

  • 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 reporting35 l.jpg
Enable an instance for development and reporting Server Setup and Management

  • 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."

  • 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.

  • 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 reporting36 l.jpg
Enable an instance for development and reporting Server Setup and Management

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 reporting37 l.jpg
Enable an instance for development and reporting Server Setup and Management

  • 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.

  • 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 StepAlthough 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 reporting38 l.jpg
Enable an instance for development and reporting Server Setup and Management

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 l.jpg
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.


Using the reporting services command line tools to manage ssrs40 l.jpg
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



Using the reporting services command line tools to manage ssrs42 l.jpg
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 [email protected]

    • 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 [email protected]

    • 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 [email protected] /t


Using the reporting services command line tools to manage ssrs43 l.jpg
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.


Using the reporting services command line tools to manage ssrs44 l.jpg
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 [email protected]

    • 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 [email protected] /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 ssrs45 l.jpg
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.


Using the reporting services command line tools to manage ssrs46 l.jpg
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 [email protected]

    • 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-9f83-dece4dcb2775.htm.


Using the reporting services command line tools to manage ssrs47 l.jpg
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.


Using the reporting services command line tools to manage ssrs48 l.jpg
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 [email protected]


Configuring the report server for ssl communication and internet deployment l.jpg
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"/>

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 deployment50 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 properties56 l.jpg
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 l.jpg
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.

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 l.jpg
Defining Default Directories

  • The SSAS server properties also let you define the default location for data, backups, and logs, as Table 2-9 describes.


Summary l.jpg
Summary

We explored the following objectives:

  • 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

  • Configure SSAS.

    • Configure query logging.

    • Configure error logging.

    • Configure disk allocation.

  • Configure server and database roles and permissions.

  • Configure an SSRS infrastructure.

    • 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 l.jpg
The End

Thank you


ad