| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2006-02-01 : 12:19:23
|
| I have got a new server to be used as report server beside my SQL box. I can set a job to copy all tables into new server and users get their reports from that server. I have another idea that use the server as a stand by server. I mean using log shipping. This way the users can get their reports using their readonly IDs and if the main server went down, we can switch to the second one and save the business time. Is the second idea the best approach? What are the Pros and Cons of the second approach?Any better idea?Thanks,Canada DBA |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-02-01 : 13:24:22
|
| You can implement log shipping, but you can't restore transaction logs to an active database, so your reporting services on server2 will always be behind server1. I highly recommend log shipping myself. One experience with a RAID controler failure killing my "protected" data was enough for me.I would guess it's possible to setup reporting services on server2 and have it refer to the database on server1. This may give you some performance benefits, you don't mention why you want a 2nd server for reporting purposes. If you go this route, you could use server2 for reporting services and receiving / archiving / log shipping / backups. Reporting Services, running on server2 uses the DB on server1 and gives up-to-the-minute reports. If server1 dies, you've got a fully restorable DB on server2. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-01 : 13:32:26
|
| You can run reports on the second server if the database is being log shipped to, however users will get disconnected each time the transaction log is restored. For this reason, you should not use the log shipped database for reporting purposes. It should only be used for quick adhoc queries that you don't want to run on the production database. I'd recommend replicating your database for reporting purposes. You could have the replicated database on server2. Then you could also log ship your database to server2. The replicated database would use a different name though, such as ProdDbName_Reports.BTW, neither of these servers should be used as a Report Server, if we are talking about Reporting Services. Report Server should not be on the same box as SQL Server, although that would require a license. You could have the Report Server databases on either of these, but not the Reporting Services engine.Tara Kizeraka tduggan |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-02-01 : 14:29:01
|
quote:
Originally posted by tkizer
BTW, neither of these servers should be used as a Report Server, if we are talking about Reporting Services.
For performance reasons I presume? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-01 : 14:37:00
|
| Yes!Tara Kizeraka tduggan |
|
|
|
CanadaDBA
583 Posts |
Posted - 2006-02-01 : 15:53:51
|
| Hi Sam,I am confused with "reporting services". Some users in my environment used to use EM to connect ti the production server and get their reports. Is it what you mean with the term? I would guess it's possible to setup on server2 and have it refer to the database on server1. This may give you some performance benefits, you don't mention why you want a 2nd server for reporting purposes. I want a 2nd server to reduce loads on the 1st server. Does it make sense?If you go this route, you could use server2 for reporting services and receiving / archiving / log shipping / backups. Reporting Services, running on server2 uses the DB on server1 and gives up-to-the-minute reports. If server1 dies, you've got a fully restorable DB on server2.Which rout? Log shipping?Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-01 : 16:09:03
|
| I guess we need to know what you mean by reports. Are you talking about adhoc queries or are they application reports designed by tools like Reporting Services or Crystal Reports?Tara Kizeraka tduggan |
|
|
|
CanadaDBA
583 Posts |
Posted - 2006-02-15 : 13:48:45
|
quote:
Originally posted by tkizer
I guess we need to know what you mean by reports. Are you talking about adhoc queries or are they application reports designed by tools like Reporting Services or Crystal Reports?Tara Kizeraka tduggan
Hi Tara,The users use EM or QA to get adhoc reports. There is no Crystal Report or such programs.ThanksCanada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-15 : 13:56:58
|
| Then I'd suggest log shipping to the second server. Just make your users aware that they will get disconnected each time a RESTORE LOG is executed as it needs exclusive access to the database.Tara Kizeraka tduggan |
|
|
|
CanadaDBA
583 Posts |
Posted - 2006-02-15 : 14:42:51
|
| Do you suggest I use the server not only for this type of reporting but also as a backup server where if the primary went down I can use it. If yes, what are your suggestions and how?Thanks,Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-15 : 14:49:52
|
| Yes. You can do this with log shipping.Tara Kizeraka tduggan |
|
|
|
CanadaDBA
583 Posts |
Posted - 2006-02-16 : 08:05:07
|
| Well, I will implement Log Shipping. Can any one give me some directions about how I can use the second server in case the primary went down? What's the best practice?Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-16 : 12:42:50
|
| If you log ship to the secondary server, then you just need to apply the last transaction log backup but specify WITH RECOVERY this time.Tara Kizeraka tduggan |
|
|
|
|
A second server