How to shrink the SQL Server Reporting Services log database

Today I did support on a ConfigMgr 2012 environment with local SQL installation, which I prefer! This because the SQL Server Reporting Services (SSRS) log database ReportServer_log.ldf file was claiming almost complete diskspace (30GB). This is because of the Maximum File Size setting which is set to 2TB by default and no Maintenance Plan is active to shrink the file (by design). I will explain how to shrink the file in order to prevent that the logfile stay small.

Before shrinking the file

Start SQL Management Studio for that and expand Databases. Select the ReportServer database and rightclick on it. Choose Properties and Files and change Full recovery to Simple recovery first. Do a rightclick on the ReportServer database again and choose Tasks, Shrink and Files. Under "File type" select Log and click OK.

Have a look at "Available free space"

The ReportServer_log.ldf file should now decreased in size to 1-5MB instead of multiple GB's. In my situation the logfile was shrinken from 30GB to 5MB (after a few minutes). Much better that way, and no need to increase diskspace every time.

Hope you have the same experience as described here!

How to remove an Instance on a SQL Server

Sometimes it's needed to remove an Instance on a SQL Server, because of database migration or SQL Server cleanup. When doing this in a production environment, this is a exciting action. This because of other production databases, which may be running in a different Instance. I've done this a few months ago, and it all went fine.

Just read the guides on Microsoft TechNet first before doing this:

-To uninstall an instance of SQL Server 2008 http://technet.microsoft.com/en-us/library/ms143412(v=sql.100).aspx

-To uninstall an instance of SQL Server 2008 R2 http://technet.microsoft.com/en-us/library/ms143412(v=sql.105).aspx

-To uninstall an instance of SQL Server 2012 http://technet.microsoft.com/en-us/library/ms143412(v=sql.110).aspx

Here are the steps which are needed to remove the Instance. Just make sure which one must be removed and there are no databases on it.

-Open Control Panel, Programs and Features
-Select "Micosoft SQL Server {version}"
-Choose "Uninstall/Change"
-Choose "Remove"
-Setup Support Rules > Next
-Remove SQL Server {version}
-Select Instance > Choose "SQL Instance"
-Select Features > Choose "Specific features" (not shared features)
-Removal Rules > Next
-Ready to Remove > Next
-Removal Progress
-Complete

After following this steps the selected Instance is removed. There's no reboot needed on the SQL Server, and other databases will still have connection. Just make sure reading the guides, and you are fine!

No reports available in ConfigMgr 2012 console

ConfigMgr 2012 has reporting functionality build-in, based on SQL Reporting Services (SRS). Today I had a issue that reports aren't available in the ConfigMgr console. What's going wrong here?

When looking at SRS on the SQL Server everything was configured fine. In Reporting Services Configuration Manager a Service Account was set (a domain user, which is a best practice), and the Report Manager URL was displayed fine also. All ConfigMgr reports were displayed here!

First a few captures to display the issue:

A Windows domain user account is configured in RSCM

Starting the Report Manager URL shows no errors..

But no reports are displayed in the ConfigMgr console

Searching on the web I found an recommendation to use the Local system account as SRS Service Account. I changed above configuration from Windows domain user account to a Local system account and it's working again. Strange thing that Microsoft recommends to use a Windows domain user account, but it's not working that way.

The following captures displays the solution:

Configure the Service Account to use Local system in RSCM

After change have a look in the ConfigMgr console again

Nice to have 423 items in Reports again. Just use Local system in SRS Service Account from now on, to pass this issue.

Installation Prerequisite Check for ConfigMgr 2012

When starting a new ConfigMgr installation there are many prerequisites that must be configured first. In this blog I will mention the most important ones.

The most known requirements are:

• Schema extensions: Configuration Manager Active Directory schema extensions are not required for site server installation, but are recommended to fully support the use of all Configuration Manager features; TechNet
• WSUS SDK on site server: ConfigMgr software update points require at least WSUS v3.0 SP2.  If using a remote software update point, the WSUS administration console must be installed on the site server;
• Microsoft RDC library registered: The Microsoft Remote Differential Compression (RDC) library must be registered for ConfigMgr site server installation; TechNet
• SQL Server service running account: The logon account for the SQL Server service cannot be a local user account or LOCAL SERVICE.  You must configure the SQL Server service to use a valid domain account, NETWORK SERVICE, or LOCAL SYSTEM;
• Verify site server permissions to publish to AD: The site server might be unable to publish to Active Directory. The computer account for the site server must have Full Control permissions to the System Management container in its Active Directory domain; TechNet
• SQL Server version: ConfigMgr sites require a supported SQL Server version with required hotfixes for site database operations to succeed. Before Setup can continue, you must install a supported version of SQL Server on the specified site database server; TechNet
• SQL Server sysadmin rights: Either the user account running Configuration Manager Setup does not have sysadmin SQL Server role permissions on the SQL Server instance selected for site database installation, or the SQL Server instance could not be contacted to verify permissions;
• BITS installed: Background Intelligent Transfer Service (BITS) is required for the management point and distribution point site system roles. Also, check if IIS/BITS services are running properly. Setup cannot continue until BITS is installed and enabled in the IIS settings;

Most other Prerequisite Checks can be found HERE. These are for ConfigMgr 2007, but will still be valid for 2012 installation.

Also check additional SQL Server requirements mentioned in my other blogpost: SQL Server requirements for ConfigMgr 2012 installation

After all this is done, it's possible to have a "All prerequisite rules pass successfully" status before ConfigMgr 2012 installation!

Update 25-6-2012: Clues added for installation!

SQL Server requirements for ConfigMgr 2012 installation

When installing ConfigMgr 2012 it's important to fullfill all SQL Server requirements. The following must be configured during installation:

• The instance of SQL Server in use at each site must use the following collation: SQL_Latin1_General_CP1_CI_AS;
• Only the Database Engine Services feature is required for each site server (Configuration Manager database replication does not require the SQL Server replication feature);
• Configuration Manager requires Windows authentication to validate connections to the database;
• You must use a dedicated instance of SQL Server for each site;
• When you use a database server that is co-located with the site server, limit the memory for SQL Server to 50 to 80 percent of the available addressable system memory. When you use a dedicated SQL Server, limit the memory for SQL Server to 80 to 90 percent of the available addressable system memory;
• Configuration Manager requires SQL Server to reserve a minimum of 8 gigabytes (GB) of memory in the buffer pool used by an instance of SQL Server for the central administration site and primary site and a minimum of 4 gigabytes (GB) for the secondary site;

If SQL Server is installed with a different collation: Latin1_General_CI_AS (for example) it won't work. ConfigMgr 2012 installation will fail during the prerequisite check and no installation is possible at all. Also a workaround to change the default collation don't seem to work then. Best thing is to install a brand new SQL Server with the right requirements.

During SQL Server installation choose the following configuration:

At Server Configuration, choose Collation and select Customize

Select SQL_Latin1_General_CP1_CI_AS instead of default

After that it's possible to have a successful ConfigMgr 2012 installation. Just make sure above requirements are configured.

Error in ConfigMgr after Reporting Services Point installation

In ConfigMgr the default Reporting Point role is installed normally. Then reports will be generated and processed on the ConfigMgr server. There is however the choice to install a Reporting Services Point role on the SQL Server. Then reports will be generated and processed on the SQL Server. Much faster that way, industry standard formatting and the possibility to export reports to many formats!

Just add the Reporting Services Point role to the SQL Server and follow the steps in SQL "Reporting Services Configuration". Create a ReportServer database and virtual directory there. Then go back to the ConfigMgr server and choose "Copy Reports to Reporting Services". Just follow the wizard and specify the SQL Server with Reporting Services installed (and configured!).

After copying reports is done it's possible that an error message is displayed in the ConfigMgr system status. The Message ID displayed is 7403 : SMS SRS web service is not running on SRS Reporting Point server "<SQL server>". Although Reporting Services is running fine and reports can be started it's not quite nicely.

The solution for this is not that hard, but maybe not easy to find. An SQL Server update is needed to resolve this issue. Now the Cumulative Update (CU) package 4 for SQL Server 2008 R2 fix the error 7403. It can be downloaded here: http://support.microsoft.com/kb/2345451/en-us

It's even better to install Service Pack (SP) 1 to include all Cumulative Updates released before. It can be download here: http://www.microsoft.com/download/en/details.aspx?id=26727 

Mandatory need for Reporting Services?
An Reporting Services Point is needed when Power Management (R3) and/or Forefront Endpoint Protection (FEP) integration is used in ConfigMgr. If that's not the case, you can choose between both Reporting solutions!

ConfigMgr and OpsMgr on the same SQL Server?
When multiple Reporting Services instances are needed (for example: ConfigMgr and OpsMgr on the same server), remember to place ConfigMgr in the "default instance". you need to install ConfigMgr first because it wants the default instance where as OpsMgr can bet set to a different one.  So you should be able to have them both use the same server, but not the same instance.

Installing ConfigMgr on a clustered SQL Server Instance

When installing ConfigMgr 2007 on a clustered SQL Server Instance, specific configuration is needed. Then preparing for a ConfigMgr installation must happen on all servers which are part of the SQL cluster. How that works I will explain in this blog. Because it's not always usual to install ConfigMgr on a clustered SQL Server Instance, remember to follow these steps!

First open this TechNet article: How to Install Configuration Manager Using a Clustered SQL Server Instance, which can be found on http://technet.microsoft.com/en-us/library/bb680513.aspx

The steps which are needed:

1. Create the virtual SQL Server cluster to host the site database on an existing Windows Server cluster environment.
2. Add the machine account of the primary site server machine to the Local Administrators group of each Windows Server cluster node computer. This is required to allow the site server to install and configure settings later.
3. In the virtual SQL Server instance, assign the sysadmin SQL Server role to the user account running Configuration Manager Setup.
4. Start Configuration Manager Setup, and select to install Configuration Manager using custom settings.
5. On the Configuration Manager Database Server page of the Configuration Manager Setup Wizard, enter the name of the clustered virtual SQL Server instance to host the site database and the name for the site database to be created on that instance by setup.

Important: During setup, you must enter the name of the virtual SQL Server cluster instance, and not the virtual Windows Server name created by the Windows Server cluster. Installing the site database using the Windows Server cluster virtual instance name will result in the site database being installed on the local hard drive of the active Windows Server cluster node, and it will prevent successful failover if that node fails.

Also have a look at "To verify that the site database was installed successfully", to check if installation was done right!

But what to do when things goes wrong?

The following errors can be shown:

SQL Server sysadmin rights: Either the user account running Configuration Manager Setup does not have sysadmin SQL Server role permissions on the SQL Server instance targeted for site database installation or the SQL Server instance could not be contacted to verify permissions. Setup cannot continue.

SMS Provider Communication: A communication error has been detected between the SMS Provider computer and the site database computer. This error can occur when the site database server is offline or if a valid SPN has not been registered in Active Directory Domain Services for the SQL Server instance hosting the site database. Setup cannot continue.

When this errors are seen during setup (or during prerequisite check), check the following steps: 

• Is the machine account of the primary site server added to the Local Administrators group of each Windows Server cluster node computer?
• Is the virtual SQL Server instance, with the sysadmin SQL Server role assigned to the user account running Configuration Manager Setup?
• Is the name of the virtual SQL Server cluster instance entered during setup? (format: SQL Server cluster name\Instance name)

The solution was found with configuring the clustered SQL Server Instance right:

• The Service Pack level from the clustered SQL Server and the new Instance created must be on the same level.
• Because the new Instance must be installed on each Windows Server cluster node computer, make sure the installation (database/log) paths are same on each node.
• Make sure permissions are set right on the clustered SQL Server Instance. (double check)

This can also be validated using "ODBC Data Source Administrator". Choose the System DSN tab, and add the clustered SQL Server Instance. This because it has nothing to do with ConfigMgr, but all with SQL Server configuration.

I hope this information will be useful for future installations!