Installing and Configuring Analysis Services

Exercise 1: Steps you through the process of installing SQL Server Analysis Services in a named instance. The instance will be named ASvc and will include Integration Services and management components, including BIDS.

Installing Analysis services

Installing and Configuring Integration Services

Exercise 2 steps you through the installation of Integration Services in a named instance. You can install Integration Services as a component within an existing instance as well. If you notice that Exercise 2 is very similar to Exercise 1, it's because you use the same installation process to install an SSIS named instance as you do for an SSAS named instance. The only difference is in the features you select.

In Exercise 3, you will export data from the AdventureWorks database into an Excel spreadsheet. The point of the exercise is to help beginning DBAs move beyond the fear of the interface. When building SSIS packages, the BIDS interface can be a bit overwhelming, but the process of creating a package is really quite simple. It includes four primary steps:

1. You must define the Connection Managers. The Connection Managers are simply connections to the data sources and destinations used in the SSIS package.

2. You define the Data Flow data pumps and transformations. The data pumps are either data flow sources or data flow destinations. The transformations define what should be done to the data (for example, copy it from a source to a destination).

3. You should test the package to ensure proper operations. This action can be accomplished with the internal debugging tools.

4. Finally, you will save the package and potentially schedule it to run on a regular basis if needed.

Troubleshooting and Debugging an SSIS Package

It's not really enough to create a package and trust that it will work. You'll also need to troubleshoot packages. The debug toolset within BIDS allows you to do this.

Scheduling Your Package to Run Automatically

Now that you know your package is working, you can schedule it to run automatically. This scheduling can be accomplished by saving the package on a SQL Server instance and then using the SQL Server Agent within that instance to run the package. Packages can be saved into multiple instances using the File Save Copy of Package As option within BIDS.

Exercise 4 steps you through the process of saving the package on an instance of SQL Server and then scheduling the package to run automatically. As you perform this exercise, you'll also see how you can encrypt the data for secure package access.