Create an archive MS SQL Server for reporting

Hello. I am currently working on a project for SCCM 2012. In the past, all reporting was done against the SCCM server itself. With SCCM 2012, I would like to copy the data to another server regularly so that reports could be run against it. I have some experience with this but not a great deal. What is the best way to do this? Would I restore the SCCM database to the archive server and setup some kind of replication job that runs regularly?

Thank you for any information.
LorrecAsked:
Who is Participating?
 
LorrecConnect With a Mentor Author Commented:
Wow. Thanks for the excellent suggestions. I will investigate each further.
0
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
What version of SQL Server are you using?
I would recommend looking into Mirroring if it is available to you.
0
 
Matt BowlerConnect With a Mentor DB team leadCommented:
If your reporting is going to be drawing on data from most of the tables in the database then I would consider using a 'whole database' solution like log-shipping with standby which is relatively easy to configure and manage and is a nice robust solution.

http://msdn.microsoft.com/en-us/library/ms190640(v=sql.100).aspx

If your only looking at a small subset of tables then replication begins to make more sense, despit being slightly more complex to implement and maintain.

http://msdn.microsoft.com/en-us/library/ms151866(v=sql.100).aspx
0
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
If it's data change quantity is not large then Snap shot replication is the good option for you.

Mirroring may eat your performance.
0
 
LorrecAuthor Commented:
Thank you for the information.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.