Using second SQL server for running reports

I am running SQL 2005 Ent edition.  We have an application that runs on our SQL 2005 server and when users try to run reports off that database, it slows down production.  I want to use the same production database on a different server and have the users run reports off that server so it doesn't impact performance to the other users.  What would be the best way to do this.  I know you can do replication, mirroring, log shipping but i am a newbie to SQL and still learning and looking for expert advice.  Thanks in advance.
LVL 1
klittlejohn1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

indianguru2Commented:
Looking at the problem that you have mentioned, I would suggest you to look at the Reports.
Reports only Select Data from the DB. So instead configuring a replica of your Current Database, Check the procedures/statement used in the report and use Table hints like WITH (NOLOCK) which will not lock the tables while reading.
0
klittlejohn1Author Commented:
Will that help in terms of performance and not using a lot of resources?  That is another issue we run into when large reports are running.  The large reports don't neccessarily lock, it just takes up a lot of resources and i thought putting that on another server would help.
0
mlmccCommented:
Any ideas on why the reports are slowing down the database?

There are many things you can do to improve report performance.

Are the reports complex?

Do you use subreports?

Are you filtering so the filtering is done on the database?

mlmcc
0
indianguru2Commented:
Look for table Functions used in the Stored Procs. Use Temp tables.
0
klittlejohn1Author Commented:
We decided to go with log shipping.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.