October 15, 2014 at 1:48 pm
Greetings,
I'm interested in your recommendations for method of keeping our Staging server data up-to-date with production data.
We're running SQL Server 2008 R2 STD.
Currently the production server is located in one location, and the staging/development databases are in another physical location.
We use log shipping to keep a "mirror" (backup) server in our development location up-to-date with production data, for disaster recovery purposes. This backup server, however, is in "norecovery" mode. I don't know if I can use this "local (physical location)" backup server as the source for my data or not... I suspect not, since it's continually restoring in norecovery mode.
I can copy daily full database backups from Production to Staging, and create an automated restore script to update the databases (and I'll then need to fix orphan users, etc), but I'm wondering if there are "better" methods I could use.
Can I in some way make use of the log shipping already going to the backup server and also restore those files (but with recovery) to the Staging server?
I appreciate any suggestions you might have.
Thanks,
Bill
October 16, 2014 at 4:38 am
How about using the same backups to log ship to your staging server and on the last log backup you restore use the with Standby option?
For example to have the DB available on your staging server between say the hours of 9AM and 5PM.
1. Have one job to copy the transaction log backups that runs every day at say 15 minute intervals to keep all the backups available on the staging instance.
2. Have a job to disconnect and disable all user logins from your staging DB at 05:00PM
3. Have a job that runs every 5 minutes between the hours of 5:01PM and 08:45AM which restores any log files available
4. Have a job that runs at 08:55AM that restores the last transaction log backup and restores with standby
5. Have a job to enable all user logins on your staging DB at 08:59AM
MCITP SQL 2005, MCSA SQL 2012
October 16, 2014 at 6:28 am
Thanks for the feedback on this.
I wondered about that (using the same log shipping files). I like the idea, especially since the backup files have already been copied over the VPN from the remote production server. It saves me from copying everything from remote a second time.
One possible problem - my (limited) understanding of STANDBY mode is that the data in staging would be read-only, correct? That will cause some problems, as some of the applications being tested on the staging server do various writes to the db as users are accessing the app - even if they're not directly editing user-data. The apps will likely throw errors if they cannot write to the db.
Also, if the data on staging is modified by someone running some tests, that will impact log shipping from being able to continue restores from production, correct?
Thanks again,
Bill
October 16, 2014 at 6:30 am
BrainDonor (10/16/2014)
Have you looked at Replication?We use it extensively here for precisely that purpose. Data is copied across regularly and the staging data can be read.
Thanks BrainDonor.
I have not loooked at Replication as of yet. I figured I was already pulling data via log shipping, so if I could avoid copying data over a second time, that would be preferred. I also didn't want add any load that my impact the production server.
I will give replication a second look, however.
I appreciate the feedback.
Bill
October 16, 2014 at 6:35 am
Bill Dykstra (10/16/2014)
BrainDonor (10/16/2014)
Have you looked at Replication?We use it extensively here for precisely that purpose. Data is copied across regularly and the staging data can be read.
Thanks BrainDonor.
I have not loooked at Replication as of yet. I figured I was already pulling data via log shipping, so if I could avoid copying data over a second time, that would be preferred. I also didn't want add any load that my impact the production server.
I will give replication a second look, however.
I appreciate the feedback.
Bill
No problem. Specifically, look at a Pull Subscription. The load is then on the Distributor and Subscriber for obtaining the data.
There's a Stairways series on SSC for Replication http://www.sqlservercentral.com/stairway/72401/ or a small demo for scripting a Pull Subscription on my blog.
October 20, 2014 at 4:05 am
Bill Dykstra (10/16/2014)
Thanks for the feedback on this.I wondered about that (using the same log shipping files). I like the idea, especially since the backup files have already been copied over the VPN from the remote production server. It saves me from copying everything from remote a second time.
One possible problem - my (limited) understanding of STANDBY mode is that the data in staging would be read-only, correct? That will cause some problems, as some of the applications being tested on the staging server do various writes to the db as users are accessing the app - even if they're not directly editing user-data. The apps will likely throw errors if they cannot write to the db.
Also, if the data on staging is modified by someone running some tests, that will impact log shipping from being able to continue restores from production, correct?
Thanks again,
Bill
Sorry for the slow reply I have been away for a few days,
Yes the standby option will give you read only access to the DB's so no modification is possible.
MCITP SQL 2005, MCSA SQL 2012
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply