MCT USE ONLY. STUDENT USE PROHIBITED

MCT USE ONLY. STUDENT USE PROHIBITED

Module 2: Managing SQL Server Supporting

Services

Time estimated: 165 minutes

Table of contents

Module 2: Managing SQL Server Supporting Services... 1

Lesson 1: Managing SSIS Packages... 4

Manage SSIS Packages. ... 4

What Is an SSIS Package? ... 6

Concept: Explain the concept of SSIS packages. ... 6

Demonstration: Guidelines for Deploying SSIS Packages ... 9

Principle: Apply guidelines for deploying SSIS packages. ... 9

Guidelines for Monitoring SSIS Packages... 13

Principle: Apply guidelines for monitoring SSIS packages. ... 13

Lesson 2: Managing Replication ... 15

Manage Replication... 15

Discussion: Guidelines for Administering Replication... 17

Principle: Apply guidelines for administering replication... 17

Considerations for Choosing Replication Monitoring Tools ... 19

Principle: Evaluate scenarios for monitoring and verifying replication, and choosing monitoring tools. ... 19

Multimedia: Replication Monitor in SQL Server 2005 ... 21

Concept: Explain the usage of Replication Monitor in SQL Server 2005... 21

Lesson 3: Managing Reporting Services ... 22

Considerations for Managing the Report Server Configuration ... 23

Principle: Evaluate considerations for managing the report server configuration... 23

Guidelines for Managing Reporting Services Databases... 25

Principle: Apply guidelines for managing Reporting Services databases. ... 25

Guidelines for Managing Report Servers... 27

Principle: Apply guidelines for managing reporting services at Web server. ... 27

Lab: Managing SQL Server Supporting Services ... 29

Exercise 2: Initializing a Transactional Subscription from a Backup ... 35

MCT USE ONLY. STUDENT USE PROHIBITED

Module objective

In this module you will learn how to:

MCT USE ONLY. STUDENT USE PROHIBITED

Introduction

Microsoft® SQL Server™ 2005 has many components that serve a wide range of purposes, each of

which has its place in the provision of database services and related supporting services. In this module you will focus on several of the key supporting services, namely Integration Services, Replication, and Reporting Services.

Microsoft SQL Server Integration Services (SSIS) is the key component of SQL Server 2005 that supports Extract, Transfer, and Load (ETL) operations and related work flows through FTP,

messaging, transformation services, script execution, and other processes, usually through the use of SSIS packages. Integration services has its own management and administration requirements that database administrators (DBAs) must be aware of separate from the rest of the product.

Replication is not new to SQL Server 2005 but the replication features include a number of new capabilities and tools. To manage replication well, the DBA must be familiar with these product features. In this module, you will learn about these features and how to use them to run and automate replication.

Reporting Services provides SQL Server 2005 with a flexible, Web-enabled reporting capability. Reports can be based on various data sources and produced in a range of formats. Like all core services, Reporting Services requires particular administration and can be usefully automated.

For more information

MCT USE ONLY. STUDENT USE PROHIBITED

Lesson 1: Managing SSIS Packages

Lesson objective

After completing this lesson, you will be able to:

MCT USE ONLY. STUDENT USE PROHIBITED

Introduction

SSIS is new to SQL Server 2005. Although related conceptually to the older Data Transformation Services (DTS) tools in SQL Server 2000, the new tool set has more capabilities along with new graphical tools. DBAs who have created DTS packages must be aware of specific migration issues from old packages to SSIS packages.

MCT USE ONLY. STUDENT USE PROHIBITED

What Is an SSIS Package?

MCT USE ONLY. STUDENT USE PROHIBITED

What is an SSIS package?

SSIS packages are an organized collection of tasks and workflow elements that you assemble using either the graphical design tools that SSIS provides or build programmatically. You save a package to SQL Server 2005, the SSIS Package Store, or the file system.

The emphasis in this definition is on organized. Packages are structured to execute package tasks in a particular manner by using a specified workflow. Dependencies between the package steps ensure that later steps do or do not execute depending on the outcome of earlier steps. Steps can be scheduled, managed with flow control operations, and reused.

Packages can also be secured, stored, configured, deployed, and executed in a variety of ways through SSIS functions and through more general SQL Server automation options.

There are many uses for SSIS packages in daily database work. Most typically, they are used for data import and export, population of data warehouses and data marts with new data, dissemination of data to other systems, integration of data from other systems into SQL Server, and manipulation of data moving between systems. In SQL Server 2005, there is also extended support through SSIS packages for the handling of XML data.

SSIS package contents

Major components of packages include one or more of the following: • Control flow elements

• Data flow elements • Data tasks • Connection managers • Event handlers • Configurations • SSIS variables • Logging

• Checkpoints and restarts • Security settings

• Transaction attributes

For more information

For details about the objects and components see “Integration Services Objects and Concepts.” In SQL Server Books Online.

Securing SSIS packages

SSIS packages might include important business logic, business rules, or other sensitive material that represent corporate intellectual property (IP). Examples can include data transformations or other data handling that involve patents, finances, business strategies, scientific data, or national security–related processes. Precisely because of these requirements, SQL Server 2005 has a special provision for “sensitive data” in addition to its normal security measures.

MCT USE ONLY. STUDENT USE PROHIBITED

• Controlling access and execution through SSIS database roles • SQL Server security through package storage selection • Restricted Integration Services folder access

• File system security for files referenced by SSIS packages • Digital signatures on packages

• Limiting access to packages in SQL Server Management Studio

For more information

See the topic “Security Considerations for Integration Services” in Books Online.

Differences between DTS and SSIS packages

DTS and a number of the features associated with it have been deprecated in SQL Server 2005. For purposes of version transition, this SQL Server 2005 provides for backward compatibility with DTS packages.

When Integration Services is installed, DTS features (DTS runtime and package enumeration) are also installed. This provides support for side-by-side deployment and execution. The Package Migration Wizard is provided to facilitate the migration of DTS packages into an SSIS package format.

However, there are significant differences between the DTS and SSIS technologies and tools. There is no DTS package editor, although it might be provided in an updated edition after the release of SQL Server 2005 by means of a Web download add-on.

SSIS packages are Microsoft .NET Framework compliant and support .NET compliant languages, whereas DTS are COM based and not .NET Framework compliant.

For more information

See the topic “Integration Services programming” in SQL Server Books Online.

Furthermore, there is no Meta Data Services repository or support for Repository packages because this feature has been deprecated.

MCT USE ONLY. STUDENT USE PROHIBITED

Demonstration: Guidelines for Deploying SSIS Packages

Principle: Apply guidelines for deploying SSIS packages.

Introduction

MCT USE ONLY. STUDENT USE PROHIBITED

Guidelines

To use packages properly, the DBA must do the following: • Identify deployable packages.

• Import and export packages as required. • Configure packages for deployment.

• Do package deployment using either a deployment utility or through automation using dtutil.exe.

Preparation steps before the demonstration

Create the tmpSalesByCreditCard table in AdventureWorksDW. 1. Run the PrepareDB.cmd command file in D:\Democode.

Verify that the MIAMI SQL Server instance is the database repository for the SSIS service. 2. Click Start, click Run, type C:\Program Files\Microsoft SQL Server\90\DTS\Binn, and

then click OK.

2. Open the MsDtsSrvr.ini.xml file by using Notepad. 3. Verify that file contains the following code: <?xml version="1.0" encoding="utf-8"?> <DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShu tdown> <TopLevelFolders> <Folder xsi:type="SqlServerFolder"> <Name>MSDB</Name> <ServerName>.</ServerName> </Folder> <Folder xsi:type="FileSystemFolder"> <Name>File System</Name> <StorePath>..\Packages</StorePath> </Folder> </TopLevelFolders> </DtsServiceConfiguration>

Pay attention to the first occurrence of the <ServerName> tag. Its value must be “.”.

4. Save the file and exit Notepad.

5. In SQL Server Configuration Manager, right-click the SQL Server Integration Services service and then click Restart.

6. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click

SQL Server Management Studio.

7. In the Connect to Server dialog box, click Integration Services in the Server type list and

MIAMI in the Server Name list, and then click Connect.

8. In the Object Explorer window, expand Stored Packages and then expand the MSDB folder. The Maintenance Plans folder is listed. This demonstrates that SSIS can successfully connect to the MSDB database in the specified server in Step 3.

MCT USE ONLY. STUDENT USE PROHIBITED

To save an SSIS package to SQL Server

1. In the D:\Democode folder, right-click the SalesByCreditCard.dtsx file, and then click Edit. The package will open in SQL Server Business Intelligence Development Studio.

10. On the File menu, click Save Copy of SalesByCreditCard.dtsx As.

2. In the Save Copy of Package dialog box, in the Package location list, click SQL Server. In the Server list, click MIAMI. In the Package path box, type /Demo.

3. Click the button next to the Protection level box.

4. In the Package Protection level dialog box, in the Package Protection level list, click Rely

on server storage and roles for access control, and then click OK.

5. In the Save Copy of Package dialog box, click OK.

6. Close SQL Server Business Intelligence Development Studio.

7. In SQL Server Management Studio, refresh the MSDB folder in Object Explorer and verify that the Demo package has been copied to this server.

8. Keep SQL Server Management Studio open. You will use it again later in this demonstration.

To modify packages properties and use configurations

1. In the D:\Democode, folder, double-click Demo 2789 M2L1.sln to open it in SQL Server Business Intelligence Development Studio.

2. In Solution Explorer, double-click SalesByCreditCard.dtsx. 3. On the SSIS menu, click Package Configurations.

4. In the Package Configurations Organizer dialog box, select the Enable package

configuration check box.

5. Click the Add button.

6. In the Package Configuration Wizard, click Next.

7. In the Configuration type list, click XML configuration file.

8. Select Specify configuration settings directly and then type D:\Democode\Demo 2789

M2L1\config.dtsconfig.

9. Click Next.

10. From the objects tree in the connection managers folder, first expand Adventure works

DW, then expand the properties folder, and finally select the ServerName check box. This

process allows administrators to modify the server that the data source connects to when deploying the package.

11. Click Next.

12. In the Configuration name text box, accept the default value, and then click Finish. 13. In Package Configurations Organizer, click Close.

14. Press CTRL+SHIFT+S to save all the projects.

To create a deployment utility

1. In the SQL Server Business Intelligence Development Studio, on the Project menu, click

Demo 2789 M2L1 Properties.

MCT USE ONLY. STUDENT USE PROHIBITED

the DeploymentOutputPath property. Click OK. 4. On the Build menu, click Build Demo 2789 M2L1. 5. Close SQL Server Business Intelligence Developer Studio.

6. Using Microsoft Windows® Explorer, view the D:\Democode\Demo 2789 M2L1\bin folder,

and then open the Deployment folder. 7. Notice the following three files:

• SalesByCreditCard.dtsx—This is the package.

• config.dtsconfig —This is the configuration file specified for changing the value of

ServerName of one of the connection managers.

• Demo 2789 M2L1.SSISDeploymentManifest—This is the installation descriptor.

To deploy a package

8. Double-click the Demo 2789 M2L1.SSISDeploymentManifest file to start the Package Installation Wizard and click Next.

9. On the Deploy SSIS packages screen, click SQL Server deployment and then click Next. 10. On the Specify Target SQL Server screen, in the Server Name box, type MIAMI and then

click Next.

11. On the Select Installation Folder screen, click Next. 12. On the Confirm Installation Screen, click Next.

13. On the Configure Packages screen, notice the name of the configuration file. Expand the

Property node, and note the current value of the \Package.Connections[Adventure Works],Properties[ServerName] property (which should be MIAMI.) Then click Next.

14. On the Finish Package Installation Wizard screen, click Finish.

To verify the export of the package to the server

1. In SQL Server Management Studio, refresh the MSDB folder in Object Explorer and verify that the SalesByCreditCard package has been deployed to this server.

11. Right-click SalesByCreditCard and then click Run Package.

12. In the Execute Package Utility window, select Connection Managers. The Adventure

Works and Adventure Works DW connection managers appear. Notice that the Connection

String for the Adventure Works DW configuration manager has the value of the configuration specified at deployment time.

13. Click Execute to run the package. After it completes, click Close in the Package Execution Progress dialog box and click Close in the Execute Package Utility window.

MCT USE ONLY. STUDENT USE PROHIBITED

Guidelines for Monitoring SSIS Packages

Principle: Apply guidelines for monitoring SSIS packages.

Introduction

SSIS packages can do a range of tasks from the very simple to the very complex. The more complex the package, the greater the likelihood that there will be performance effects to consider. It is also important to routinely monitor packages to ensure that they are executing properly, that they are returning expected results, and that they have not failed for any reason.

Packages can be monitored through a number of tools. The following guidelines provide a starting point for deciding which tools to use for which purposes.

You can run these tools manually or automate them to monitor SSIS packages.

Guidelines for Monitoring SSIS Packages and Automating Monitoring

• Use the SSIS service

Monitoring packages, which use the tools available in SSIS and SQL Server Management Studio, provide quick and accessible information related to the activities of a server at any time. SSIS provides centralized storage information, regardless of the particular type of storage that is implemented.

MCT USE ONLY. STUDENT USE PROHIBITED

SSIS package, you can configure your choice of location to store the logs. For instance, you can store logs in a SQL Server table, an XML file, or the Windows Event log. You can define logging at a package level or a task level. You can also choose the events and columns that you want to log for a specified level.

• Use Application Event Viewer and alerts

When logging events to Windows Event log, you can generate alerts related to specific SSIS events. For instance, you can create an alert to indicate that a particular task inside a package has failed. This alert can be based on the OnError event, either at the package level or at the task level.

• Create alerts for performance monitor counters

An alert generated as a result of a performance counter not meeting a specified threshold helps you realize the important execution conditions in packages. An example of an execution condition can be the successful completion of a package.

• Implement notifications

SSIS has a control task that permits sending e-mail messages. You can include this kind of notification in a package to ensure that you are updated when a specific sequence of tasks has been executed.

• Use database activity monitoring tools

MCT USE ONLY. STUDENT USE PROHIBITED

Lesson 2: Managing Replication

Lesson objective

After completing this lesson, you will be able to: Manage Replication.

Introduction

SQL Server 2005 has a range of replication technologies that provide a powerful and flexible mechanism to distribute data to multiple servers and keep everything synchronized. The

synchronization process has the effect of binding the two servers together so that issues on one server can affect the other server. In all forms, replication can be used to copy and distribute data between database objects, between databases, and between servers and mobile devices. To ensure that replication happens properly; is done in a timely manner; and is managed with regard to data

processes, backup, restore, and performance, there are a number of things that DBAs must understand and be familiar with.

In addition to the three types of replication (transactional, merge, snapshot) there are important differences between server-to-server replication and server-to-client replication.

For more information

See the topics “Replicating Data in a Server to Server Environment” and “Replicating Data Between a Server and Clients” in Books Online.

MCT USE ONLY. STUDENT USE PROHIBITED

includes extensive material on SQL Server Mobile replication.

Note

Replication is covered in detail in Course 2788, Designing High Availability Database Solutions

MCT USE ONLY. STUDENT USE PROHIBITED

Discussion: Guidelines for Administering Replication

Principle: Apply guidelines for administering replication.

Discussion questions

Q. What considerations do you need to make when backing up a publisher participating in

transactional replication?

A. A replicated environment contains many dependencies to ensure that all changes are sent from the

publisher to each subscriber. The distribution database contains a replication watermark that is used as the starting point for the Log Reader Agent. If the replication watermark is out of synch with the transaction log, transactional replication will shut down with an error. Transactions are held in the distribution database until written to all subscribers. If the subscription database were to be restored to a previous point in time, it would be out of synch with the rest of the environment and unable to be reconciled without being reinitialized. Because of the very strict state relationship between the publisher, distributor, and subscriber, you must ensure that each database is backed up in a coherent state with respect to the replication environment. You can accomplish this task by using the synch

with backup option.

Q. How do backup considerations change with merge replication?

A. The tracking tables that determine changes to be sent between publisher and subscriber are

MCT USE ONLY. STUDENT USE PROHIBITED

to be exchanged between sites, there are no special considerations for backups and restores with merge replication.

Q. In what order should service packs be applied in a replicated environment?

A. Service packs and hot fixes should always be applied to the distributor first. After the distributor is

updated, changes should be applied to the publisher. The last step in the upgrade process is to apply the service packs and/or hotfixes to all subscribers.

Q. How do you manage schema changes in a replicated environment?

A. Previous versions of Microsoft® SQL Server™ imposed very strict limitations on changes to the

schema. SQL Server 2005 does not impose any restrictions and it does not require special procedures. All schema changes are sent from the publisher to all subscribers during the first synchronization cycle following the change.

Q. How do you determine how long it will take to catch up, once replication has fallen behind in distributing transactions to the subscriber?

A. In previous versions of SQL Server, this question could not be answered. The infrastructure did not

exist to determine the two components required to answer this question: number of changes pending distribution and how long it takes a transaction to move from publisher to subscriber. SQL Server 2005 introduces addition instrumentation into the engine, which provides the ability to calculate end-to-end transit time for a transaction. With this additional piece of information provided by tracer tokens, an administrator can now determine how much time it will take to finish synchronizing the environment.

These suggested topics are not exhaustive but provide a starting point for the class.

ƒ After which replication related changes should you capture a new backup of each replication database? Your answer should include the changes and the related replication database to backup.

ƒ How do you ensure that all subscribers are synchronized after a restore of the publication database in a merge replication? How is this synchronization process different if the replication is using the HTTPS protocol?

MCT USE ONLY. STUDENT USE PROHIBITED

Considerations for Choosing Replication Monitoring Tools

Principle: Evaluate scenarios for monitoring and verifying replication, and choosing monitoring tools.

Considerations for choosing monitoring tools

In selecting monitoring tools, the DBA should consider the following:

• Monitoring health of environment. A monitoring tool should be able to display the status of any replication component and the task it is performing. Any monitoring solution, especially for replication, should display aggregated information across an entire environment.

Note

It is a common mistake to classify a tool that can connect to each server and display information for each server on an individual basis as providing an aggregated view. Replication has components running on multiple servers simultaneously, which requires the use of tools that can establish multiple connections to multiple servers and aggregate all the monitoring data into a single console without requiring a DBA to connect manually to multiple servers.

• Troubleshooting errors. In the event of an error, a monitoring tool should display any error messages in a single consolidated view and eliminate the need for a DBA to access tables, event logs, or error logs separately to obtain the error information.

• Determining latency. A critical element in any replication environment is latency. DBAs are always working to minimize latency. Any monitoring tool that does not meet the needs to measure latency is only marginally effective in any production environment.

MCT USE ONLY. STUDENT USE PROHIBITED

DBA which rows are not synchronized and generate compensating transactions so that the DBA can repair the environment.

MCT USE ONLY. STUDENT USE PROHIBITED

Multimedia: Replication Monitor in SQL Server 2005

Concept: Explain the usage of Replication Monitor in SQL Server 2005.

Usage of Replication Monitor

This multimedia covers the basic features of the new Replication monitor, including the following: • Layout of the tool

MCT USE ONLY. STUDENT USE PROHIBITED

Lesson 3: Managing Reporting Services

Lesson objective

After completing this lesson, you will be able to:

Introduction

Reporting Services is a powerful but complex product. This complexity comes from the number of elements involved in his architecture: databases, Web services, windows services, Web sites, and, occasionally, custom assemblies.

MCT USE ONLY. STUDENT USE PROHIBITED

Considerations for Managing the Report Server Configuration

Principle: Evaluate considerations for managing the report server configuration.

Assess ways configuration can be managed and determine the most appropriate tool to use

DBAs should determine the specific configuration requirements for their particular installations and deployments. No two sites are the same in their requirements. SQL Server 2005 documentation provides extensive guidelines and discussion of these issues in addition to detailed checklists that should be used to comprehensively assess requirements.

After the requirements are determined, the DBA must assess the relative strengths and weaknesses of each configuration management tool or approach:

• Reporting Services Configuration Tool • Reporting Services command-line utilities • Manual configuration

Graphical tools provide easy-to-use interfaces for configuration management and include Reporting Services Configuration Manager, SQL Server Management Studio, and SQL Server Configuration Management.

MCT USE ONLY. STUDENT USE PROHIBITED

purposes; in other cases, some settings can be changed only manually.

Through the use of these tools, the DBA can manage virtual directories, Reporting Services databases, encryption, service accounts, and many other settings.

Caution

In the case of manual editing of configuration files, be aware that incorrect configuration can cause Reporting Services to use a default value, fail to start at all, or log an error to the Windows application log. In most cases it is advisable to work through the graphical user interface (GUI) or command-line tools.

For more information

See the topics “Report Server Administration Tools” and “Reporting Services Configuration Files” in Books Online for detailed discussions of tool features and the configuration files that they affect. Follow the specific configuration file links to see the items managed by each file and the types of values that must be supplied.

Plan Reporting Services Configurations

Reporting Services configurations can cover a range of requirements with different types of

deployment. Consider the differences between default configurations of stand-alone installations and scaled-out solutions. There are potentially differences in granting users permission to access Reporting Services, backing up encryption keys, or enabling functionalities such as My Reports or downloading the ActiveX® client print control. There are also differences to consider in handling server load, load

balancing in the case of scaled-out deployments, and monitoring requirements in each case. As with all administration functions, deployment and configuration management should be properly

documented in policies, procedures, and in the run book.

DBAs in charge of configuration settings might also need to set up Secure Sockets Layer (SSL) security, manage certificates, move databases from one server to another, or update accounts used by services. In addition, password maintenance can also be required for Reporting Services service accounts.

Choose an automation method

Reporting Services operations can be automated in two major areas: report processing and delivery, and other administration operations:

• Automating report processing and delivery is managed through the Report Manager Web application.

• For automating other administration operations you can use the rs.exe utility. This utility executes scripts written in Microsoft Visual Basic®. These scripts use the Reporting Services

application programming interface to perform selected operations

MCT USE ONLY. STUDENT USE PROHIBITED

Guidelines for Managing Reporting Services Databases

Principle: Apply guidelines for managing Reporting Services databases.

Security

Reporting Services databases are accessed by internally configured users. Reporting Services users do not access these databases directly, so you do not need to grant specific access to them. Following the usual security recommendations, the use of specifically created internal-service users is recommended for all Reporting Services–related operations.

Reporting Services stores sensitive data such as user names and passwords in its databases. It encrypts sensitive data using a symmetric key. Key-related information must be backed up and secured using the provided configuration tools or the command-line utilities.

Backup

To back up and restore Reporting Services databases, you would use your accustomed SQL Server backup tool because these databases are just regular databases. You can discard backing up the

ReportServerTempDB database because it contains information that has a short expiration time.

However, in case of disaster, you must create at least a database with identical name for Reporting Services to run normally.

MCT USE ONLY. STUDENT USE PROHIBITED

IIS

Reporting Services depends on Internet Information Services (IIS) virtual directories. These directories must be properly configured and secured. DBAs might need to work with other IT professionals to deal with IIS issues.

Logging

Reporting Services provides for extensive logging of its operations. Logs must be reviewed on a regular basis to determine whether there are any problems with the service.

Performance

Use of Reporting Services is usually a core business activity and is usually heavily used. To determine patterns of use and any related performance issues, DBAs need to do regular performance monitoring of all aspects of the service.

Temporary data

You can configure Reporting Services to store temporary snapshots in the file system instead of using

ReportServerTempDB. These snapshots can be compressed to take up less storage space. You can

enable this functionality through configuration files. You should change the default storage path because it defaults to a subfolder of the Reporting Services installation path.

Moving Reporting Services Databases

If you decide to move Reporting Services databases to a different database server, you can move the current database using regular tools. However, after moving the database you must update Reporting Services configuration files accordingly. To do so, use Reporting Services Configuration Manager or the rsconfig command-line utility

Storage Space

Storage space required for the Report Server database varies depending on how your solution is configured. The Report Server database can grow to a significant size.

MCT USE ONLY. STUDENT USE PROHIBITED

Guidelines for Managing Report Servers

Principle: Apply guidelines for managing reporting services at Web server.

Analyze reports workload

Users demand reports that have a very different report processing workload, refreshing frequency, and targeted audience size. Understanding how reports will be processed and consumed is the first step to plan a good reporting strategy. Pay attention to reports with bigger workloads, are frequently

requested, or are targeted to big audiences. You should establish a life cycle strategy for those reports found to be more critical.

Establish a caching and report history strategy

After report processing and consumption is analyzed, use Reporting Services features to adjust the processing and timeliness of reports. To adjust it, you might use simple features such as caching to more sophisticated features such as combining snapshots with data-driven subscriptions. Gaining a good understanding of these features is critical.

Evaluate data source connections

Many Reporting Services features benefit from unattended execution capability. Such features must use specific credentials to access data sources and perform report processing and delivery. Options for credentials vary when accessing reports interactively on demand. You should analyze which users will use preprocessed reports or on-demand reports and establish a credential strategy that does not

MCT USE ONLY. STUDENT USE PROHIBITED

Manage security

Reporting Services has its own set of roles, permissions, and authorized users. You should revise those roles to decide whether adding new roles is necessary.

When there are many users, and volatility or turnover ratio is significant, you can benefit from using data-driven subscriptions instead of grating permissions to user and groups specifically. Alternatively, you should create some scripts to automate user management.

Manage subscriptions

Users are granted to subscribe to updated reports and to receive results at selected destinations. This kind of subscription is managed by the subscription owner. Obsolete subscriptions, no longer required but still active, might cause unnecessary processing. Therefore, you should establish a strategy for keeping track of subscriptions and checking whether they stay current.

Review execution logs

Reporting Services maintains logs, which can be adjusted in logging detail level or completely deactivated through configuration files. The Report Execution Log maintains information about report execution history on a server. This log can be deactivated through Report Manager, although it is enabled by default. To exploit log information, you must use the provided Integration Services package to put log data into a database table

Determine a scheduling strategy

Scheduling automatic processing of reports is a very useful feature. Scheduling is available from shared schedules or from report-specific schedules. Only users and roles with the appropriate credentials are allowed to create shared schedules or to schedule individual reports. Therefore, you should plan which users will be granted permission to create shared schedules or to schedule

individual reports. In case you decide to create shared schedules, you might consider offering different schedules for hourly, weekly, and monthly reports, for example.

User-created subscriptions are managed by users themselves, and there is no interface available to list all existing user-managed subscriptions. Therefore, in case many users require scheduling

MCT USE ONLY. STUDENT USE PROHIBITED

Lab: Managing SQL Server Supporting Services

Estimated time: 70 minutes

Introduction

In this lab you will configure and deploy an SSIS package, initialize transactional replication from a backup, and move the Reporting Services database from one server to another.

Scenario

You are a newly hired DBA at Adventure Works. The company makes extensive use of SQL Server Integration Services, replication, and Reporting Services.

Preparation

Ensure that the virtual machine 2789A-MIA-SQL-01 is running and that you are logged on as

Student.

If a virtual machine has not been started, perform the following steps: 1. Close any other running virtual machines.

15. Start the virtual machine.

MCT USE ONLY. STUDENT USE PROHIBITED

Exercise 1: Deploying an SSIS Package

Introduction

In this exercise you will configure and deploy an SSIS package.

Configuring and deploying an SSIS package

Task Supporting Information

Prepare the

AdventureWorksDW database.

1. Run the PrepareDB.cmd command file in D:\Labcode\Starter.

Verify that the MIAMI SQL Server instance is the database repository for the SSIS service.

1. View the MsDtsSrvr.ini.xml configuration file in C:\Program Files\Microsoft SQL Server\90\DTS\Binn. 2. Verify that the database repository for the SSIS service is

the MSDB database on the local default server (.). Save the SalesByCreditCard

SSIS package to SQL Server.

1. Open the SalesByCreditCard.dtsx package, which is provided in the D:\Labfiles\Starter folder in SQL Server Business Intelligence Developer Studio.

2. Save a copy if the package in the MIAMI SQL Server. The copy should be named CCSales and should rely on server storage and roles for access control.

Create a package configuration for the SalesByCreditCard package in the AWPackages solution.

1. Use SQL Server Business Intelligence Developer Studio to open the AWPackages.sln solution in

D:\Labfiles\Starter.

2. Add an XML configuration file package configuration to the SalesByCrediCard package. The package

configuration should enable administrators to change the server that the Adventure Works connection manager uses.

Create a deployment utility for the AWPackages solution.

1. Configure the AWPackages project properties to create a deployment utility.

2. Build the AWPackages project.

3. Verify that the deployment utility has been generated. Deploy the SalesByCreditCard

package.

1. Use the deployment utility you generated in the previous task to deploy the SalesByCreditCard package to the

MIAMI SQL Server instance.

2. Use the default value for the server name property in the configuration file.

MCT USE ONLY. STUDENT USE PROHIBITED

Procedure answer key

Preparing the AdventureWorksDW database

You must perform the following steps to prepare the AdventureWorksDW database. 1. Run the PrepareDB.cmd command file in D:\Labcode\Starter.

Verifying that the MIAMI SQL Server instance is the database repository for the SSIS service

You must perform the following steps to verify that the MIAMI SQL Server instance is the database repository for the SSIS service.

1. Click Start, click Run, type C:\Program Files\Microsoft SQL Server\90\DTS\Binn, and then click OK.

2. Open the MsDtsSrvr.ini.xml file by using Notepad. 3. Verify that the file contains the following code: <?xml version="1.0" encoding="utf-8"?> <DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShu tdown> <TopLevelFolders> <Folder xsi:type="SqlServerFolder"> <Name>MSDB</Name> <ServerName>.</ServerName> </Folder> <Folder xsi:type="FileSystemFolder"> <Name>File System</Name> <StorePath>..\Packages</StorePath> </Folder> </TopLevelFolders> </DtsServiceConfiguration>

Pay attention to the first occurrence of the <ServerName> tag. Its value must be “.”.

4. Save the file and exit Notepad.

5. In SQL Server Configuration Manager, right-click the SQL Server Integration Services service and then click Restart.

6. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click

SQL Server Management Studio.

7. In the Connect to Server dialog box, click Integration Services in the Server type list and

MIAMI in the Server Name list, and then click Connect.

8. In the Object Explorer, expand Stored Packages and then expand the MSDB folder. The Maintenance Plans folder is listed. This procedure demonstrates that SSIS can successfully connect to the MSDB database in the specified server in Step 3.

9. Keep SQL Server Management Studio open. You will use it in the next procedure.

Saving the SalesByCreditCard SSIS package to SQL Server

You must perform the following steps to save the SalesByCreditCard SSIS package to Microsoft®

MCT USE ONLY. STUDENT USE PROHIBITED

2. Right-click the SalesByCreditCard.dtsx file, and then click Edit. The package opens in SQL Server Business Intelligence Development Studio.

3. On the File menu, click Save Copy of SalesByCreditCard.dtsx As.

4. In the Save Copy of Package dialog box, in the Package location list, click SQL Server. In the Server list, click MIAMI. In the Package path box, type /CCSales.

5. Click the button next to the Protection level box.

6. In the Package Protection level dialog box, in the Package Protection level list, click Rely

on server storage and roles for access control and then click OK.

7. In the Save Copy of Package dialog box, click OK.

8. Close SQL Server Business Intelligence Development Studio.

9. In SQL Server Management Studio, refresh the MSDB folder in Object Explorer and verify that the Demo package has been copied to this server.

10. Keep SQL Server Management Studio open. You will use it again later in this exercise.

Creating a package configuration for the SalesByCreditCard package in the AWPackages solution

You must perform the following steps to create a package configuration for the SalesByCreditCard package in the AWPackages solution.

1. In the D:\Labfiles\Starter, folder, double-click AWPackages.sln to open it in SQL Server Business Intelligence Development Studio.

2. In Solution Explorer, double-click SalesByCreditCard.dtsx. 3. On the SSIS menu, click Package Configurations.

4. In the Package Configurations Organizer dialog box, select the Enable package

configuration check box.

5. Click the Add button.

6. In the Package Configuration Wizard, click Next.

7. In the Configuration type list, click XML configuration file. 8. Select Specify configuration settings directly and then type

D:\Labfiles\Starter\AWPackage\config.dtsconfig.

9. Click Next.

10. From the objects tree in the connection managers folder, expand Adventure works DW, expand the properties folder, and finally then the ServerName check box. This will allow administrators to modify the server that the data source connects to when deploying the package.

11. Click Next.

12. In the Configuration name box, accept the default value and then click Finish. 13. In Package Configurations Organizer, click Close.

14. Press CTRL+SHIFT+S to save all the projects.

Creating a deployment utility for the AWPackages solution

MCT USE ONLY. STUDENT USE PROHIBITED

AWPackages.

2. Click Deployment Utility.

3. In the right pane, select True for the CreateDeploymentUtility option. Notice the value of the DeploymentOutputPath property. Click OK.

4. On the Build menu, click Build AWPackages.

5. Close SQL Server Business Intelligence Developer Studio.

6. Using Windows Explorer, view the D:\Labfiles\AWPackages\bin folder, and then open the

Deployment folder.

7. Notice the following three files:

• SalesByCreditCard.dtsx—This is the package.

• config.dtsconfig —This is the configuration file specified for changing the value of

ServerName of one of the connection managers.

• AWPackages.SSISDeploymentManifest—This is the installation descriptor.

Deploying the SalesByCreditCard package

You must perform the following steps to deploy the SalesByCreditCard package.

1. Double-click the AWPAckages.SSISDeploymentManifest file to start the Package Installation Wizard and then click Next.

2. On the Deploy SSIS packages screen, select the SQL Server deployment option and then click Next.

2. On the Specify Target SQL Server screen, in the Server Name box, type MIAMI and then click Next.

3. On the Select Installation Folder screen, click Next. 4. On the Confirm Installation Screen, click Next.

5. On the Configure Packages screen, notice the name of the configuration file. Expand the

Property node, and note the current value of the \Package.Connections[Adventure Works],Properties[ServerName] property (which should be MIAMI). Click Next.

6. On the Finish Package Installation Wizard screen, click Finish.

3. In SQL Server Management Studio, refresh the MSDB folder in Object Explorer and verify that the SalesByCreditCard package has been deployed to this server.

4. Right-click SalesByCreditCard and then click Run Package.

5. In the Execute Package Utility window, select Connection Managers. The Adventure

Works and Adventure Works DW connection managers appear. Notice that the Connection

String for the Adventure Works DW configuration manager has the value of the configuration specified at deployment time.

6. Click Execute to run the package; After it completes, click Close in the Package Execution

Progress dialog box and click Close in the Execute Package Utility window.

MCT USE ONLY. STUDENT USE PROHIBITED

Questions

Q When do you think it is necessary to deploy packages to a server?

MCT USE ONLY. STUDENT USE PROHIBITED

Exercise 2: Initializing a Transactional Subscription from a Backup

Introduction

Adventure Works Cycles must develop a reporting solution to manage various aspects of company operations. To minimize the impact of reporting operations in the environment, a dedicated reporting database is being created, which will be synchronized with production using transactional replication.

Initializing a Subscription

Task Supporting Information

Create a SQL Server Scripts project named

AWReplication.

1. Start SQL Server Management Studio. 2. Create a new SQL Server Scripts project

named AWReplication in the D:\Labfiles\Starter folder. Create a publication on MIAMI named

AWTables that contains all tables in AdventureWorks.

1. Use the New Publication Wizard to create a new transactional publication named

AWTables. Use the following information to

help you:

• MIAMI should be configured as both

the publisher and the distributor.

• The publication should contain all tables in the AdventureWorks database.

• Do not create an initial snapshot.

• The snapshot agent should use the

MIAMI\Student account with the password Pa$$w0rd.

• Generate a script for the publication and add it to the project.

Configure the AWTables publication for initialization from backup.

1. View the properties of the AWTables publication.

2. In the Subscription Options category, set the Allow initialization from backup files property to True.

Back up the AdventureWorks database on

MIAMI.

1. Use Object Explorer to back up the

AdventureWorks database on MIAMI,

generating a script file for the action. 2. Add the script file you generated to the project.

Restore the AdventureWorks database on

MIAMI\SQLINSTANCE2.

1. Use Object Explorer to restore the

AdventureWorks database to

MIAMI\SQLINSTANCE2. You must move

the database files as part of the restore process. Generate a script file for the restore action.

MCT USE ONLY. STUDENT USE PROHIBITED

MIAMI\SQLINSTANCE2 from the backup

of AdventureWorks.

1. Add a new query to the project, connecting to

MIAMI.

2. Execute the sp_addsubscription stored procedure in the AdventureWorks database with the following options:

• @publication = 'AWTables'

• @subscriber = 'MIAMI\SQLINSTANCE2'

• @destination_db = 'AdventureWorks'

• @subscription_type = 'push'

• @sync_type = 'initialize with backup'

• @backupdevicetype = 'location of the backup file'

Establish a baseline latency measurement by using a tracer token.

1. Use Replication Monitor to view the

AWTables publication.

2. Add a tracer token and view the latency measurement for the AWTables publication.

Procedure Answer Key

Creating a SQL Server Scripts project named AWReplication

You must perform the following steps to create a SQL Server Scripts project named AWReplication. 1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click

SQL Server Management Studio.

2. In the Connect to Server dialog box, specify the values in the following table and then click

Connect.

Property Value

Server type Database Engine

Server name MIAMI

Authentication Windows Authentication

3. On the File menu, point to New and then click Project.

4. In the New Project dialog box, specify the values in the following table and then click OK.

Property Value

MCT USE ONLY. STUDENT USE PROHIBITED

Create directory for solution

Cleared

Creating a publication on MIAMI named AWTables that contains all tables in AdventureWorks

You must perform the following steps to create a publication on MIAMI named AWTables that contains all tables in AdventureWorks.

1. If Object Explorer is not visible, click Object Explorer on the View menu.

2. In Object Explorer, expand Replication, right-click Local Publications, and then click New

Publication.

5. On the New Publication Wizard page, click Next.

6. On the Distributor page, click Next to use MIAMI as its own distributor.

7. On the Snapshot Folder page, click Next to use the default snapshot folder location. 8. On the Publication Database page, click AdventureWorks to choose it as the publication

database and then click Next.

9. On the Publication Type page, click Transactional publication and then click Next. 10. On the Articles page, select Tables, and then click Next.

11. Click Next on the Filter Table Rows page.

12. On the Snapshot Agent page, leave all check boxes unselected and click Next. 13. On the Agent Security page, click Security Settings.

14. Enter MIAMI\Student as the Process account. Enter Pa$$w0rd in the Password and

Confirm Password boxes. Leave By impersonating the process account selected and then

click OK.

15. Click Next on the Agent Security page.

16. On the Wizard Actions page, select the Create the publication and Generate a script file

with steps to create the publication check boxes and then click Next.

17. On the Script File Properties page, change the File name to

D:\Labfiles\Starter\AWReplication\PublicationScript.sql and then click Next.

18. On the Complete the Wizard page, enter AWTables as the Publication name and then click

Finish.

19. On the Creating Publication page, wait until all actions have been completed, check that there are no errors, and then click Close.

20. On the Project menu, click Add Existing Item.

21. Add D:\Labfiles\Starter\AWReplication\PublicationScript.sql. When prompted, connect to

MIAMI by using Windows authentication.

22. On the File menu click Save All.

Configuring the AWTables publication for initialization from backup

You must perform the following steps to configure the AWTables publication for initialization from backup.

MCT USE ONLY. STUDENT USE PROHIBITED

3. In the Select a Page pane, click Subscription Options.

4. Set the Allow initialization from backup files property to True, and then click OK.

Backing up the AdventureWorks database on MIAMI

You must perform the following steps to back up the AdventureWorks database on MIAMI 1. In Object Explorer, expand the Databases folder.

2. Right-click AdventureWorks, point to Tasks, and then click Back Up. 3. Click Full as the Backup type.

4. Note the default backup destination in the Destination section.

5. Click the arrow for the Script button and then click Script Action to File. Save the file as D:\Labfiles\Starter\AWReplication\Backup.sql.

6. Click OK to back up the database. When the backup is complete, click OK to close the message box.

7. On the Project menu, click Add Existing Item.

8. Add D:\Labfiles\Starter\AWReplication\Backup.sql. When prompted, connect to MIAMI by using Windows authentication.

9. On the File menu, click Save All.

Restore the AdventureWorks database on MIAMI\SQLINSTANCE2.

You must perform the following steps to restore the AdventureWorks database on

MIAMI\SQLINSTANCE2.

1. In Object Explorer, click the Connect button on the toolbar and then click Database Engine. 2. In the Connect to Server dialog box, specify the values in the following table and then click

Connect.

Property Value

Server type Database Engine

Server name MIAMI\SQLINSTANCE2

Authentication Windows Authentication

3. In Object Explorer, right-click the Databases folder for MIAMI\SQLINSTANCE2 and then click Restore Database.

4. In the To database box, type AdventureWorks.

5. Select From device and then click the ellipsis button (...). 6. Click Add, select C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak, and then click OK.

7. Click OK in the Specify Backup dialog box.

MCT USE ONLY. STUDENT USE PROHIBITED

SQL Server\MSSQL.4\MSSQL\Data\AdventureWorks_Data.mdf.

11. Change the Restore As value for AdventureWorks_Log to C:\Program Files\Microsoft

SQL Server\MSSQL.4\MSSQL\Data\AdventureWorks_Log.ldf.

12. In the Restore Database dialog box, click the arrow for the Script button and then click

Script Action to File. Save the file as D:\Labfiles\Starter\AWReplication\Restore.sql.

13. Click OK to restore the database. When the restoration is complete, click OK to close the message box.

14. On the Project menu, click Add Existing Item.

15. Add D:\Labfiles\Starter\AWReplication\Restore.sql. When prompted, connect to

MIAMI\SQLINSTANCE2 by using Windows authentication.

16. On the File menu, click Save All.

Initializing a subscription on MIAMI\SQLINSTANCE2 from the backup of AdventureWorks

You must perform the following steps to initialize a subscription on MIAMI\SQLINSTANCE2 from the backup of AdventureWorks.

1. On the Project menu, click New Query. When prompted, connect to MIAMI by using Windows authentication.

2. In Solution Explorer, rename the new query file to SubscriptionScript.sql. 3. Type the following Transact-SQL code in the new query window:

USE AdventureWorks GO EXEC sp_addsubscription @publication='AWTables', @subscriber = 'MIAMI\SQLINSTANCE2', @destination_db = 'AdventureWorks', @subscription_type='push',

@sync_type = 'initialize with backup', @backupdevicetype = 'disk',

@backupdevicename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'

4. Click the Execute button on the toolbar, and then click Save All on the File menu. 5. In Object Explorer, expand the Replication and Local Subscriptions folder for the

MIAMI\SQLINSTANCE2 server and verify that the subscription has been created.

Establishing a baseline latency measurement by using a tracer token

You must perform the following steps to establish a baseline latency measurement by using a tracer token.

1. In Object Explorer, right-click the Replication folder under the MIAMI server and click

Launch Replication Monitor.

2. In Replication Monitor, expand MIAMI and click the [AdventureWorks]:AWTables publication.

3. On the Tracer Tokens tab, click Insert Tracer. Note the time that the tracer was inserted and the latency measurements that it recorded.

MCT USE ONLY. STUDENT USE PROHIBITED

Discussion

Q Why do you want to establish a performance baseline?

A The most fundamental question that is asked in a production environment is: “How long will it take me to be synchronized.” The amount of time to synchronize is dependent upon knowing the amount of data that needs to be sent along with the amount of time it takes for a transaction to reach the subscriber. Replication Monitor will display the number of commands that are pending transfer. A tracer token is used to measure the time required for a transaction to move from the publisher to the subscriber. By

MCT USE ONLY. STUDENT USE PROHIBITED

Exercise 3: Configuring Reporting Services

Introduction

In this exercise, you will move the Reporting Services internal databases to the new SQL Server instance.

Move the report server database to the new database instance.

Task Supporting information

Detach the Reporting Services databases from MIAMI.

1. Use SQL Server Configuration Manager to stop the SQL Server Reporting Services and SQL Server Agent(MSSQLSERVER) services.

2. Start SQL Server Management Studio and connect to MIAMI by using Windows authentication.

3. Detach the ReportServer and

ReportServerDB databases.

4. Use Windows Explorer to copy the

ReportServer and ReportServerTempDB

database files from C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\Data to C:\Program Files\Microsoft SQL

Server\MSSQL.4\MSSQL\DataRestart the

SQL Server Agent (MSSQSERVER)

service. Attach the Reporting Services database on

MIAMI\SQLINSTANCE2.

1. In Object Explorer, connect to

MIAMI\SQLINSTANCE2.

2. Attach the ReportServer and

ReportServerTempDB databases.

Configure Reporting Services to use the databases on MIAMI\SQLINSTANCE2.

1. View the properties of the AWTables publication.

2. In the Subscription Options category, set the Allow initialization from backup files property to True.

Back up the AdventureWorks database on

MIAMI.

1. Use Object Explorer to back up the

AdventureWorks database on MIAMI,

generating a script file for the action. 2. Add the script file you generated to the

project. Restore the AdventureWorks database on

MIAMI\SQLINSTANCE2.

1. Use Object Explorer to restore the

AdventureWorks database to

MIAMI\SQLINSTANCE2. You must move

the database files as part of the restore process. Generate a script file for the restore action.

MCT USE ONLY. STUDENT USE PROHIBITED

Initialize a subscription on

MIAMI\SQLINSTANCE2 from the backup

of AdventureWorks.

1. Add a new query to the project, connecting to

MIAMI.

2. Execute the sp_addsubscription stored procedure in the AdventureWorks database with the following options:

• @publication = 'AWTables'

• @subscriber = 'MIAMI\SQLINSTANCE2'

• @destination_db = 'AdventureWorks'

• @subscription_type = 'push'

• @sync_type = 'initialize with backup'

• @backupdevicetype = 'location of

the backup file'

Establish a baseline latency measurement by using a tracer token.

1. Use Replication Monitor to view the

AWTables publication.

2. Add a tracer token and view the latency measurement for the AWTables publication.

Procedure Answer Key

Detaching the Reporting Services databases from MIAMI

You must perform the following steps to detach the Reporting Services databases from MIAMI. 1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, point to

Configuration Tools, and click SQL Server Configuration Manager.

2. In the list of SQL Server 2005 services, right-click SQL Server Reporting Services and click Stop.

3. Right-click the SQL Server Agent (MSSQLSERVER) service and click Stop. 4. Minimize SQL Server Configuration Manager.

5. Start SQL Server Management Studio, connecting to the MIAMI Database Engine by using Windows authentication when prompted.

6. In Object Explorer, expand Databases.

7. Right-click the ReportServer database, point to Tasks, and click Detach. Then click OK to detach the database.

8. Repeat the previous step for the ReportServerTempDB database.

9. Use Windows Explorer to move the ReportServer.mdf, ReportServer_Log.ldf,

ReportServerTempDB.mdf, and ReportServerTempDB_Log.ldf database files from

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data to C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data.

MCT USE ONLY. STUDENT USE PROHIBITED

Attaching the Reporting Services database on MIAMI\SQLINSTANCE2

You must perform the following steps to attach the Reporting Services database on

MIAMI\SQLINSTANCE2.

1. In Object Explorer, click Connect and connect to the MIAMI\SQLINSTANCE2 Database Engine by using Windows authentication.

2. Right-click the Databases folder under MIAMI\SQLINSTANCE2 and click Attach. 3. In the Attach Databases dialog box, click the Add button and click the ReportServer.mdf

file; then click Add again and click the ReportServerTempDB.mdf file. Click OK.

Configuring Reporting Services to use the databases on MIAMI\SQLINSTANCE2

You must perform the following steps to configure Reporting Services to use the databases on

MIAMI\SQLINSTANCE2.

1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, point to

Configuration Tools, and click Reporting Services Configuration. When prompted, connect to the MSSQLSERVER instance on MIAMI.

2. Click Database Setup, and then type MIAMI\SQLINSTANCE2 in the Server Name box and click Connect. Then select ReportServer in the Database Name box.

3. Click Apply and then click OK to connect to MIAMI\SQLINSTANCE2.

4. When the changes have been applied, click Server Status and click Start. Verify that the Reporting Services service starts, and then click Exit.

Discussion

Q What should you do to document Reporting Services in a run book?

A Answers will vary. The following are some examples of information that you can document in a run book:

• Periodic maintenance tasks: Review of execution logs and performance counters • Architectural aspects for quick reference: Role of each report server if there are multiple

servers

• Security aspects: Credentials used by shared data sources • Reporting Services specific information such as the following:

• Storage path of the encryption key backup

• Source of subscriber information for data-driven subscriptions • Service Level Agreement issues such as:

• Critical reports

• Maximum processing time allowed

• Schedule for refreshing reports

• Expected recipients

• Delivery channels