SCCM diet

Online notes for reference

Tag Archives: SCCM 2012 SQL reporting

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

In Part 1 I backed up the SCCM database and copied the backed up data base files to a different SQL server. I also attached the data base files and created the database

In Part 2 I created a SQL job to run daily . This job will copy the backed up files from SCCM database server. Then this job will attach the copied database files .

In Part 3 I exported the certificate from SQL server and imported in on a machine where reports will be authored.

In Part 4 I created data source from SQL reporting services server to SQL01 server and verified connection is working.

Part 5

In part 5 I am going to create SCCM reports using the data source created in Part 4 and then publish reports to SQL reporting website.

Open report builder and to system center icon and click on options

DBRepl48

Configure the report server as http://<sql reporting services server name>/reportserver and click ok

DBRepl49

Now on the bottom of report builder console click on connect

DBRepl50

As seen below , Report builder is not connected to reporting server and can use the data sources from the servers.

DBRepl51

Click on new – Data source

 

 

DBRepl52

Provide the name of data source ( LocalSQL01DataSource in this case) and click on browse

 

DBRepl53

Browse to folder on reporting server , SCCM Reports from SQL01 and select the SQL01 data source

 

DBRepl54

Now click on test connection

 

DBRepl55

Now you are able to connect to SQL01  database using a data source stored on reporting server from a window 7 client machine on the network. Click OK

 

 

DBRepl56

As seen the data source appears under Data sources on the left side menu in report builder window

 

DBRepl57

Up until now I am able to connect to database server . Next step is add some data from the database to create a report.

Subset of data from the database is called dataset. Dataset can tables , views and at time can contain joins between two tables or two or more sql views .

 

 

DBRepl58

Provide the name of Dataset

select data source from drop down and click on query designer

 

DBRepl59

When you click on query designer , It will take you inside CM_R01 database on server SQL01 .

Of all the data that is available in this data base , I am going to select a small amount of data to create a dataset.

 

 

DBRepl60

Expand views and select v_R_System and click OK

 

 

DBRepl61

You can review this query and what columns are listed and click OK

 

 

DBRepl62

Now in report builder console under datasets the data set is display . This is from view v_R_System

 

 

DBRepl63

Finally ………………………………………. Its time to create a report 🙂

On the ribbon click on insert and select insert table

 

 

 

 

DBRepl64

This by default will add a blank table

 

 

DBRepl65

Drag and drop the fields you want from data set to table , I added Name0, Obsolete0 and client0 and click on run

 

 

DBRepl66

You can view what the report looks like now

 

 

DBRepl67

Click on save and browse to the folder created to storing SCCM reports from SQL01 .

Provide the name of report and click save

 

 

DBRepl68

Report is generated from alternate SQL01 server and published to SQL reporting services server too

 

 

DBRepl69

 

 

Additional notes – There are multiple ways to author SQL reports , I am using report builder

You can store data source locally too , You can also store reports locally and publish them later .

 

This concludes Part 5

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

In Part 1 I backed up the SCCM database and copied the backed up data base files to a different SQL server. I also attached the data base files and created the database

In Part 2 I created a SQL job to run daily . This job will copy the backed up files from SCCM database server. Then this job will attach the copied database files .

In Part 3 I exported the certificate from SQL server and imported in on a machine where reports will be authored.

Part 4

In part 4 I am going to install Report Builder 3.0 on client machine Win7 and configure data source for creating SQL reports. SQL reporting services is running from SCCM Primary server . See network diagram in Part 1

At this time you should have report builder 3.0 installed for SQL 2012 on a Windows 7 machine . If you do not have it installed download it from here and install on the workstation

DBRepl41a

Once Report builder is installed .

Open SQL reporting services , In my case it is http://cm01/reports.

Click on New folder ( Only for keeping reports separate)

 

 

DBRepl42

Provide the name of the folder and click ok.

 

 

DBRepl43 DBRepl44

After the new folder is created , Go to folder SCCM reports from SQL01

Click on New Data Source

Data Source contains the connection information for a particular database.

 

 

DBRepl45

Provide the name to the data source , SQL01 DataSource

Check – Enable this data source

Data Source type – Microsoft SQL server

In the connection string type in the following

(Change the name of Initial_Catalog to your database name and data source to your SQL server name)

Persist Security Info=False;Initial Catalog=CM_R01;
Data Source=sql01.labserv.net;
Encrypt=True;TrustServerCertificate=True

As mentioned in part 1 user labserv\cmreports has read rights to CM_R01 database. Once this database is replicated same rights are assigned to the replicated database.

Check box – Use as windows credentials when connecting to the data source

 

DBRepl46

 

Click on Test connection

and ensure that data source is able to connect successfully.

 

DBRepl47

 

 

 

 

This concludes Part 4

 

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

In Part 1 I backed up the SCCM database and copied the backed up data base files to a different SQL server. I also attached the data base files and created the database

In Part 2 I created a SQL job to run daily . This job will copy the backed up files from SCCM database server. Then this job will attach the copied database files .

Part 3

Part 1 & Part 2 were dedicated to standing up the alternate SCCM database.

In Part 3 I am going to detail the steps necessary to prepare machine from where reports will be authored.

In order to author SQL reports from a workstation , SQL database server ( SQL01 in this case) needs to trust the workstation.

This is done by exporting the server certificate from SQL database server where copied database is hosted from and importing it on workstation where reports will be authored.

Perform these steps on the SQL database server where replicated database is hosted (SQL01 in this case)

Open mmc – click on file and add remove snap-ins

Select Certificated and click on add

DBRepl23

Select computer account and click next

 

DBRepl24

Select local computer and click finish

 

DBRepl25

Click OK

 

DBRepl26

Now go to Certificates -> Personal->Certificates

 

DBRepl27

Select the Server authentication certificate and right click -> All tasks ->Export

 

DBRepl28

On the welcome certificate export wizard click next

 

 

DBRepl29

Leave default selected , No, Do not export the private key  and click next

 

 

DBRepl30

 

Leave default selected DER encoded binary X.509 and click next

DBRepl31

Browse and provide the path to the certificate and a name of the file and click next

DBRepl32

Click finish to complete the export

 

 

DBRepl33

Click OK and finish

 

DBRepl34

Copy the exported certificate to the workstation which will be used to author reports. This steps needs to be on all the machines which will be used to creating reports.

Right click the certificate and click on install certificate

 

 

DBRepl35

This will open the import certificate wizard , Click next

 

DBRepl37

 

Browse to certificate store and select Trusted Root Certification Authorities store and click next

DBRepl38

Click finish to finish the import

 

 

DBRepl39

Click OK

 

 

DBRepl40

To verify if the certificate is present in the certificate store , Open mmc , add certificate snap-in for local computer.

Go to Trust root certification authorities and on the right side as highlighted SQL01 certificate is present.

DBRepl41

 

 

This concludes Part 3

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

In Part 1 I backed up the SCCM database and copied the backed up data base files to a different SQL server. I also attached the data base files and created the database

Part 2

In part 2 I am going to create a SQL job which will automate this task to run daily .This job is scheduled a time later than SCCM Backup time so that the latest backup files are present when job runs.

Open SQL management studio on server (On the replicated DB server) SQL01 in this case.

Go to SQL server agent , right click and New Job

DBRepl11

On the general menu provide the name of SQL job

 

DBRepl12

Click on Steps and and click on New

DBRepl13

First step will be close the connections to SQL server .

Add the following command to put the database in single user mode and click OK

ALTER DATABASE CM_R01 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBRepl14

Next Step is to detach DB . This step is needed because we need to copy the DB files from SCCM server and overwrite them. If you do not detach the database files will not be overwritten

SP_DETACH_DB ‘CM_R01’, ‘TRUE’

(Change the name of the data base in the above command)

DBRepl15

Next step is to copy the files from SCCM primary server to database server using the script as described in part 1

“c:\dbcopy\dbcopy.ps1”

DBRepl16

Once the data base files are copied next step is to attach the database to SQL server

CREATE DATABASE CM_R01
ON(FILENAME = ‘C:\DBCopy\CM_R01.mdf’),
(FILENAME = ‘C:\DBCopy\CM_R01_log.ldf’)
FOR ATTACH;
GO

Click OK

DBRepl17

Once all the steps in the job are complete. Next step is to schedule this job to occur daily sometime after the SCCM backups finish.

Since SCCM backups in my lab are scheduled to run at 1:00 AM everyday I have scheduled the job to run everyday at 4:00 AM to give enough time to backup to finish.

DBRepl18

Your job will appear under SQL Server agent – Jobs

DBRepl19

If you want to manually run the job to test if it runs correctly , Right click the job and select start the job at step 1

DBRepl20

You can always check the job history for troubleshooting purposes by right clicking the job and clicking on view history

DBRepl21

By expanding the step , each step is detailed. If there are any failure those will be recorded here.

DBRepl22

 

This concludes part 2

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