We are currently using a single primary site of System Center 2012 R2 Configuration Manager SP1. The database is hosted on a separate computer that only supports this Configuration Manager site. The SQL server is on a Windows Server 2008 R2 server and Microsoft SQL Server is version 2008 R2 as well.

A team outside of the Config. Mgr team would like to write custom reports against the data in our CM database. My manager is concerned about this other group writing some reports or queries that may adversely affect the performance of our site. I am researching options for offloading their work to a copy of the database. Is there a best practice for this? I've come across information on reporting against a database replica based on SQL snapshots. Also on a replica database.

Obviously we want to keep the impact of replication to our current SQL server low. What is the best way to do this? Would it help to simply move our reporting services point off of the SQL server?

Thanks in advance.

Gary Knigge

Setting up replication is unsupported and may cause you issues. The general guidance here is to use a full restore of the DB -- this is what Microsoft IT does as well as many others.

Moving reporting services to another system usually provides little benefit. It all depends upon how the reports themselves are written. Typically for most standard reports, the overwhelming majority of work is handled by the SQL Server DB engine; however, it is possible to add some or even a lot of processing to the report itself which of course would shift the work to the SSRS engine. For the built-in ConfigMgr reports, most of the work is handled by the DB engine as the reports heavily leverage stored procs and functions in the DB.

Does the other team need real time access to the data?

Can they work with 7 days old data? If not why not?

 what about 24 hours? If not, why not?

As Jason has pointed out, offloading ssrs will not help, at all.

You can set it up using an AG (if you upgrade SQL to 2012) with a read-only database replica with the SSRS connecting to the DB on that AG replica. You can't use it in a failover capacity for CM, but Microsoft did validate this approach for us to use.

However as Jason and Garth already stated, there should be no issues with just reporting directly from the CM database unless you are an exceptionally large environment or are using reports that aren't very well optimised. Just size your SQL accordingly and it should be fine.

Thank you so much to everyone for the very useful replies. I appreciate it.

Gary