Full, Diff & Inc backups in a log shipping environment

Question

Full, Diff & Inc backups in a log shipping environment

chris.downes

Grasshopper

Points: 12
More actions
July 18, 2011 at 3:14 am

#241657

Hi,
We have a production server that the customer wants to run their own queries against. I have said they cannot query the live server but we can log ship the DB they need to one of their servers and run it in standby (read only) mode. As this server will only be for reporting and not as a standby or backup we still need to run a full backup plan on the prod DB.
Is it possible to use log shipping for reporting and still tun transaction log backups on the prod server?

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

If you have log shipping then the log backups taken for the log shipping must be the only ones taken.

You can set log shipping up for read-only. Restore WITH STANDBY rather than WITH NORECOVERY. Restoring another backup will disconnect all users.

Have you considered replication?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 2

If real time reporting is not requried you could consider shared database

http://technet.microsoft.com/en-us/library/ms345392.aspx

Replication is also a good solution

If your planning to make this more permanent feature , then maybe you should consider coming up with a detailed ETL and DW solution now rather than put it for later when making changes will be more difficult.

Jayanth Kurup[/url]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Jayanth_Kurup (7/18/2011)
If real time reporting is not requried you could consider shared database
http://technet.microsoft.com/en-us/library/ms345392.aspx

Do note that with scalable shared databases, all of the databases involved have to be read only. There can't be one read-write and the rest read-only.

Honestly, I've never seen anyone use that feature. It's finicky to setup and few people need just read-only scaleout.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

chris.downes Grasshopper Points: 12 More actions · Answer 4

Hi,

I will be restoring the backups on the destination in standby (read only) once a day at 22:00 so there will no users online to disconnect. So for this scenario I can only run Full & Diff backups on the prod (source) DB server?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Yes, because the log backups are taken by the log shipping. Make sure they run frequently enough for your data-loss allowance and log size.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 6

Wouldn't a database snapshot work as the source , however i think a snapshot would server the purpose of the OP in this case without needing the scalable shared db on top of it.

Jayanth Kurup[/url]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Doesn't solve the performance problems. Snapshot is on the same server as the source DB, hence any queries against a snapshot are still taking resources on the live server. Won't cause blocking, but can still interfere and, especially if there's already memory pressure can even make the situation worse.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

chris.downes Grasshopper Points: 12 More actions · Answer 8

So if we do full and diff backups will the log shipping job truncate the transaction logs to prevent them from growing?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

The log shipping job takes log backups, exactly as a normal log backup job would do.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 10

chris.downes (7/18/2011)Is it possible to use log shipping for reporting and still tun transaction log backups on the prod server?

The point is, you don't need to take additional log backups outside of what log shipping will take. You can configure the backup jobs, copy jobs and restore jobs so that you can maintain the proper PIT recovery you want and still allow for the proper delay between restores on your log shipping instance.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato