Advanced Log Shipping

We are researching whether we can accomplish the following goal with
log shipping. We have a reporting database that is mostly read-only
that we would like to migrate to log shipping.
Our environment consists of two database servers, one is our
operational database server and the other is a reporting database
server. All the data writes go to our operational database.
Currently, we replicate the operational database to the reporting
database every evening. We intentional do this once a day so that we
have a 24 hour window to correct any data entry issues if they should
occur.
Log shipping sounds easy enough to setup, but here is where it gets
complicated for us. Our reporting database is used via our web portal
application. Our users have the ability to make changes during the day
via the portal. Some of these changes cause writes to both the
reporting database as well as the operational database. The writes to
the reporting database let the users get immediate changes, and the
writes to the operational database ensure the data will be updated upon
the next nightly update.
1) Will these intermittent writes to the reporting database server
prevent a log shipping transaction log restore from completing
successfully?
2) If we do transaction log backups once an hour, I assume we can save
them all and then replay all the tlog backups at one time in the middle
of the night.
We tried to setup replication at one point, but our database schema
would not replicate to a second server and we have not had the
resources to try to resolve the replication issues.
Thank You,
Kevin
Kevin,
log shipping restores the logs using NORECOVERY or STANDBY, both of which
will prevent the editing of the reporting data.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Log shipping target databases are locked read-only. You cannot update a
log-shipped target database. IF you bring it live then you cannot apply any
further transaction logs.
Yes, you can "save up" transaction logs and apply them in sequence.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1133799275.397007.188420@.g43g2000cwa.googlegr oups.com...
> We are researching whether we can accomplish the following goal with
> log shipping. We have a reporting database that is mostly read-only
> that we would like to migrate to log shipping.
> Our environment consists of two database servers, one is our
> operational database server and the other is a reporting database
> server. All the data writes go to our operational database.
> Currently, we replicate the operational database to the reporting
> database every evening. We intentional do this once a day so that we
> have a 24 hour window to correct any data entry issues if they should
> occur.
> Log shipping sounds easy enough to setup, but here is where it gets
> complicated for us. Our reporting database is used via our web portal
> application. Our users have the ability to make changes during the day
> via the portal. Some of these changes cause writes to both the
> reporting database as well as the operational database. The writes to
> the reporting database let the users get immediate changes, and the
> writes to the operational database ensure the data will be updated upon
> the next nightly update.
> 1) Will these intermittent writes to the reporting database server
> prevent a log shipping transaction log restore from completing
> successfully?
> 2) If we do transaction log backups once an hour, I assume we can save
> them all and then replay all the tlog backups at one time in the middle
> of the night.
> We tried to setup replication at one point, but our database schema
> would not replicate to a second server and we have not had the
> resources to try to resolve the replication issues.
> Thank You,
> Kevin
>
|||The business case that we are trying to resolve is that we are moving
our reporting database server to a co-location facility. In the past
we had high speed LAN access between our database servers so nightly
backup and restores of our database where not an issue. Our database
is currently about 7GB and it is growing at about 25% per year. Our
WAN connection is a standard point to point T1. 7GB is too much data
to move across the WAN on a nightly basis. LiteSpeed will shrink the
database to 1.5GB, but still that is very large compared to 35MB
transaction logs.
Is it possible to make a copy of the log shipping target database on
the remote site? In essence could we use the log shipping target
database to stage a production database across the WAN.
Operational Database --> Log Shipping --> Staging Database (RO) -->
Backup/detach/copy/etc --> Reporting Database (RW)
Thank You for the quick feedback,
Kevin
|||Upgrade to SQL Server 2005 and use database snapshots - exactly what they
are designed for.
You can't detach the database and copy, nor can you stop SQL Server and copy
the files and use those as inputs to sp_attach_db.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1133800712.975757.55690@.f14g2000cwb.googlegro ups.com...
> The business case that we are trying to resolve is that we are moving
> our reporting database server to a co-location facility. In the past
> we had high speed LAN access between our database servers so nightly
> backup and restores of our database where not an issue. Our database
> is currently about 7GB and it is growing at about 25% per year. Our
> WAN connection is a standard point to point T1. 7GB is too much data
> to move across the WAN on a nightly basis. LiteSpeed will shrink the
> database to 1.5GB, but still that is very large compared to 35MB
> transaction logs.
> Is it possible to make a copy of the log shipping target database on
> the remote site? In essence could we use the log shipping target
> database to stage a production database across the WAN.
> Operational Database --> Log Shipping --> Staging Database (RO) -->
> Backup/detach/copy/etc --> Reporting Database (RW)
> Thank You for the quick feedback,
> Kevin
>
|||Our final solution is to utilize Double Take. We plan to replicate our
..mdf and .ldf across the wan using double take. Then once a night we
will pause Double Take and copy the .mdf and .ldf out of the replica.
We will then mount that as our reporting database.
So far all seems well. Scripting out an automated way to attach the
database and verify that it is not corrupt and then backout to the
previous database if necessary is proving to be a little tricky. If
only DTS allowed better flow control...
SQL 2005 will be on its way soon, but not soon enough
Thank you all for your input,
Kevin
|||Hello,
Thanks for let me know the current status. If you have any questions or
concerns in future, feel free to post back!
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.