Log shipping for report server? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

High Availability (2005)

Log shipping for report server?

Author

Topic

gravyface
Starting Member

16 Posts

Posted - 2008-03-17 : 19:39:19

Hello all,

I have two SQL 2005 Standard Edition database servers, one's the production box and the other is not in use. I'd like to use the secondary server mainly for running reports on but I'd also like to use it as a warm standby as the reports will be read only.

So far, it looks like mirroring is out of the question (asynchronous not available in Standard and more importantly, you apparently can't directly access the mirrored database). Log Shipping seems to be simple/reliable way of achieving what I want, but I do have a few questions:

- what kind of downtime will the report users experience during the transaction log restore process? i.e. if I leave it running during the default 15 minute intervals, will that kill the session/connections to the database on the secondary/reporting server every 15 minutes when the transaction log is processed by the secondary server? Both the reports/main application are IIS/ASP apps running on the same box; I'm not sure how/if the app(s) handle database connection losses.

- are there any performance trade-offs with log shipping? From what I've read, there doesn't seem to be, but I'd like to know what others have experienced.

- I've also read that you can set up a 3rd server to monitor the log shipping: can this be a non-SQL server (i.e. a 2003 standard box on the same domain)?

TIA!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-17 : 20:38:53

'' what kind of downtime will the report users experience during the transaction log restore process? i.e. if I leave it running during the default 15 minute intervals, will that kill the session/connections to the database on the secondary/reporting server every 15 minutes when the transaction log is processed by the secondary server?"

while restoring a transaction log, the database won't be accessible and if you are doing any index operation then transaction log will be huge . This will take more time to restore.

Yes you can.It has to be SQL Server.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-17 : 23:40:53

You can put log shipping monitor on secondary server.

gravyface
Starting Member

16 Posts

Posted - 2008-03-18 : 07:54:06

Hmm. The downtime sounds like it would be a bit annoying for the report users.
I read somewhere else in the MS forums that you can queue up the logs, but not actually restore them, so that you can restore all of them at once at a more convenient time (say noon and midnight, for instance).

Anyone tried this before?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-18 : 09:28:00

It depends on which option you choose:
1) If you choose Disconnect users while restoring, DB won't be accessible

2) If you choose Delay restoring backups at least, delay the restore process on the secondary server

gravyface
Starting Member

16 Posts

Posted - 2008-03-18 : 22:42:50

Thanks for the help so far, guys. One more thing: what actually happens during the transaction log backup job? i.e. does SQL compare previous successfully copied/restored transactions with new transactions and only the most recent changes are saved/shipped? Not sure what the difference between log shipping and a normal backup/restore is, data-wise.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-19 : 07:29:40

Log shipping is like taking Transaction log backup on primary server and ships the log through network and restores in Secondary Server.It tracks only new changes than last transaction log backup.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-21 : 00:23:24

Log backup takes committed transactions since last log backup.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-21 : 01:27:37

I highly recommend using transactional replication instead of log shipping for your reporting environment.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-21 : 09:51:50

All tables need to have Primary key for transactional replication to take place.

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-03-21 : 12:21:04

quote:
Originally posted by tkizer

I highly recommend using transactional replication instead of log shipping for your reporting environment.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

We are having SQL2000 servers:
I have created a Server1.ReportingDB where I do transactional replication from Server1.DB1 and GreatplainsServer.DB2, the reporting db is used just for running reports. I am getting error when user's are using a tool on the Greatplains database and it does'nt allow them to do anything and gives the below error:
"Cannot alter the table RM00101 beacuse it's being published for replication", Per the err it appears that user's are trying to alter the table and since trans replication does'nt allow any schema changes, so I had to stop the transactional replication on the great plains db. So in this situation can I do this:
1. Log shipping of about 10 Greatplains tables from GreatPlians server to Server1.ReportingDB. Copying teh data at night is fine.
2. Transcational replication of another set of tables between server1.db1 to Server1.Reportingdb. Please note that transactional replication is betwen the same server SERVER1.
I am not sure once we do log shipping of teh reporting db, if we can also perform transactional replication on the samme db. Any ideas, thanks!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-21 : 13:48:42

quote:
Originally posted by sodeep

All tables need to have Primary key for transactional replication to take place.

All tables should have a primary key and a clustered index!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-21 : 13:50:47

quote:
Originally posted by sqlserverdeveloper

quote:
Originally posted by tkizer

I highly recommend using transactional replication instead of log shipping for your reporting environment.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

We are having SQL2000 servers:
I have created a Server1.ReportingDB where I do transactional replication from Server1.DB1 and GreatplainsServer.DB2, the reporting db is used just for running reports. I am getting error when user's are using a tool on the Greatplains database and it does'nt allow them to do anything and gives the below error:
"Cannot alter the table RM00101 beacuse it's being published for replication", Per the err it appears that user's are trying to alter the table and since trans replication does'nt allow any schema changes, so I had to stop the transactional replication on the great plains db. So in this situation can I do this:
1. Log shipping of about 10 Greatplains tables from GreatPlians server to Server1.ReportingDB. Copying teh data at night is fine.
2. Transcational replication of another set of tables between server1.db1 to Server1.Reportingdb. Please note that transactional replication is betwen the same server SERVER1.
I am not sure once we do log shipping of teh reporting db, if we can also perform transactional replication on the samme db. Any ideas, thanks!!

Your users are doing more than reporting if they are trying to alter the schema. Reports should be doing read operations only.

Yes you can do log shipping and transactional replication from the primary/publisher.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-03-21 : 14:56:02

[Your users are doing more than reporting if they are trying to alter the schema. Reports should be doing read operations only.
Yes you can do log shipping and transactional replication from the primary/publisher.]
Actually the users are using the tool in the source db(GreatPlains database).

If I do the following 2 steps to the same Server1.ReportingDB:
1. Apply Log shipping of about 10 Greatplains tables from GreatPlians server(Source) to Server1.ReportingDB.
2. Transcational replication of another 20 tables between server1.db1 to Server1.Reportingdb.
Note: Log shipping and transactional replication is applied to the same destination Server1.Reportingdb.

When the tran log backup's are applied on teh reporting db, those backup's will have 10 Greatplains tables where as the
destination reporting db will have 10 greatplians tables + 20 replication tables, so will tran log backup's kind of overwrite
the reporting database or will it just apply the changes to the applicable tables in the reporting db?? ANd there will not be any problem to the log shipping right, since log shipping is also applied to the same destination Server1.Reportingdb. sorry, if it's a silly question.
Just to make sure while the log shipping i.e at the time of applying the tran log's on teh reporting db, the reporting db will
be unavailable for users right. Thanks!!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-21 : 17:49:34

You can't set log shipping on tables.

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-03-22 : 20:04:04

quote:
Originally posted by rmiao

You can't set log shipping on tables.

I am sorry got confused with transactional replication, that's right, log shipping is like restoring database transactional log backups from primary to the destination reporting database. Can you please suggest the best approach to apply in my situation, Thanks!!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-22 : 22:19:03

Why can't replicate tables from GreatPlians server?

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-03-24 : 11:35:05

quote:
Originally posted by rmiao

Why can't replicate tables from GreatPlians server?

Actually before I was using transactional replication on the greatplains database until I got the below error:
"Cannot alter the table RM00101 beacuse it's being published for replication"
I got the above error when user's were using a tool on the greatplains db, per the err it appears that user's are trying to alter the table(source) and since trans replication does'nt allow any schema changes, I had to stop the transactional replication on the great plains db.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-24 : 11:55:35

Yes transaction rep supports schema changes in SQL server 2005. Just
go to publisher properties-subscribers-Enable Replication schema changes- but you have to do through T-SQL only for changing Column)

bgodfrey
Starting Member

4 Posts

Posted - 2008-03-24 : 13:42:06

I'm guessing that your user is using the Account Merger tool? I'm having the exact same problem. What I don't understand is, is this an error due to do the database trying to be put into single user mode?

quote:
Originally posted by sqlserverdeveloper

quote:
Originally posted by rmiao

Why can't replicate tables from GreatPlians server?

Actually before I was using transactional replication on the greatplains database until I got the below error:
"Cannot alter the table RM00101 beacuse it's being published for replication"
I got the above error when user's were using a tool on the greatplains db, per the err it appears that user's are trying to alter the table(source) and since trans replication does'nt allow any schema changes, I had to stop the transactional replication on the great plains db.

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-03-24 : 14:59:53

quote:
Originally posted by sodeep

Yes transaction rep supports schema changes in SQL server 2005. Just
go to publisher properties-subscribers-Enable Replication schema changes- but you have to do through T-SQL only for changing Column)

The source publisher is on SQL2000.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources