One of the first things you'll probably notice is the Surface Area Configuration Tool, introduced in SQL 2005, is not there. Actually it's still part of the server, only its moved. Now you access it by connecting to the server using the Management Studio, right clicking on the server name and choose "Facets" from the menu. When the View Facet screen opens, select "Surface Area Configuration" from the Facet combo box. Notice that by default all options are turned off; I specifically turned Database Mail on.

Moving a reporting database

Another task I just finished in regards to SSRS was moving the reporting database. We have an older server we were using for developing the report on. The system team gave us a virtual server (we're going to run virtual when we go live). I wanted to copy the existing catalogs to the new server so I wouldn't have to re-deploy them all. These are the steps I took. In my case the new report server was already installed and configured

1. Backup the encryption key on the source server using the Report Server Configuration Manager. This should be done regardless.
2. Backup the reporting database on the source server.
3. Restore the backup from the source to the destination server.
4. Apply the encryption key from the source onto the destination.

After I completed these steps all the reports and their data sources and credentials appeared on the new server's Report Manager. Hopefully I haven't missed anything.

Getting data into a page header in SSRS

As I've mentioned before, we're moving MS Access97 reports to SSRS 2008. Recently I came across another issue, repeating data in a page header. You may or may not know it, but you can't directly add a data field to a page/report headers and footers. If you want your data to repeat you need to employ a workaround. One way is to use parameters and use a second data set for the parameters. I tried this method, but I had a problem when the page changed; the header didn't always mirror what was in my group. I also tried group headers that repeated on each page, but the report I'm working with also has sub reports that may or may not span pages. I found when that happened the group headers didn't repeat properly.

The solution I came up with is to set the values in code. The first step was to declare a public variable for each field I needed on my page header. The second step was to create a function that set the value of the variables to a parameter passed into the function. I then added a hidden text box on the body of my report that called my functioned and passed in the values I needed on the page header. Finally I added the page header section, added a text box for each field I needed, and used an expression to call the public variable I declared in the first step.

This isn't my invention; there are examples of this on the web. I don't have a source handy but I'll cite a few that I used later.