Benefits of SCCM reporting are obvious. Some organisations depend more on SCCM reporting than others. Dataset used for SCCM reporting is the SCCM Database.If there are multiple reports being run , written and tested while there is a lot of client activity can cause database performance to degrade.
Some organisations prefer that SCCM reports be designed , coded and tested on a separate DB server and only the final report be imported to live SQL reporting server that connects to SCCM production database.
In order to achieve this you need a separate SQL server preferably running same version of SQL in the domain.
CM01 – SCCM Primary server , SCCM Database , SQL reporting Services, SCCM reporting services point
SQL 01 – SQL Database server
WIN7 – Workstation running windows 7 , Report builder 3.0 for SQL 2012
domain\cmreports – This is a user account in AD and has read permissions on SCCM database on server CM01.
Diagram below describes the systems in use for this lab setup.
Enable shared folders and SCCM Backup
On SCCM Server (CM01 in this case) , Create a folder to store backups and share it.
Go to SCCM 2012 console , Administration and sites . Go to site Maintenance
As highlighted below backup is not enabled on the site . Enable the backups
Then schedule the backups to occur daily or every weekday to your organisation’s standards and procedures.
Now on other SQL server (SQL01 in the case)
Create a shared folder and provide permissions .
I am using a powershell script to copy data base files from server CM01 to server SQL01. I am also storing the files in the shared folder that I just created on server SQL01.
You can down the DBcopy script and robocopy from here
Extract the files and copy them to shared folder.
Open and edit the power shell script using either ISE or any other power shell editor of your choice.
Change the path of $source = \\your sccm server\SCCMBACKUP\<sitecode>Backup\SiteDBserver
Change the path of $destination \\your sql server\DBcopy
Change the path of $log \\your sql server\DBCopy\DBCopy.log
Here are the switches for robocopy command
S- copy subdirectories, But not empty ones
E – copy subdirectories, including empty ones
DAT D:DATA A:ATTRIBUTE T:TIMESTAMP
R – Number of retries after failed attempt , Default is 1 million
/LOG – Name of log time to be created
Save the script and run it .
Important – Run this script only after making sure that SCCM backup has run on SCCM primary server (CM01) and the backup files are present in backup folder c:\SCCMBackup on SCCM server.
Once the script finishes running . Database file and log file of SCCM database has been copied to sql server SQL01 .
Review the CopyDB.log to verify the size of database and log files.
Now log on with an account that is SQL admin and rights to add database on SQL server and run the following commands to create a database .( Change the path if needed )
CREATE DATABASE CM_R01
ON(FILENAME = ‘C:\DBCopy\CM_R01.mdf’),
(FILENAME = ‘C:\DBCopy\CM_R01_log.ldf’)
Once the command is run successfully ,Refresh the console . Replicated copy of the SCCM database will show up in SCCM Console.
Now we have a copy of SCCM database running on a separate SQL server.
This concludes Part 1