SCCM diet

Online notes for reference

Replicating SCCM DB and adding replicated DB to SCCM Reporting – Part 1

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.

Lab Setup

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.

DBRepl00

Enable shared folders and SCCM Backup

On SCCM Server (CM01 in this case) , Create a folder to store backups and share it.

DBRepl01

Go to SCCM 2012 console , Administration and sites . Go to site Maintenance

 

DBRepl02

As highlighted below backup is not enabled on the site . Enable the backups

DBRepl03

Then schedule the backups to occur daily or every weekday to your organisation’s standards and procedures.

DBRepl04

Now on other SQL server (SQL01 in the case)

Create a shared folder and provide permissions .

DBRepl05

 

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.

DBRepl06

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

/COPY:DAT

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

DBRepl07

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.

DBRepl08

Once the script finishes running . Database file and log file of SCCM database has been copied to sql server SQL01 .

DBRepl09

Review the CopyDB.log to verify the size of database and log files.

DBRepl10

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’)
FOR ATTACH;
GO

DBRepl10a

Once the command is run successfully ,Refresh the console . Replicated copy of the SCCM database will show up in SCCM Console.

DBRepl10b

Now we have a copy of SCCM database running on a separate SQL server.

 

This concludes Part 1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: