March 27, 2009 at 10:15 am
Mirroring will not work as the mirror has to be in-recovery meaning that it would not be accesible.
The best alternative is database replication though you still have to do a bit of work when changes are made. For what you describe Transactional replication would work well;
http://msdn.microsoft.com/en-us/library/ms151198.aspx
Or you could write some ssis scripts to copy the data that has changed over time.
But the pain-free way would be backup/restore until you have a proper DW setup going
March 27, 2009 at 10:17 am
Mirroring would only create a secondary database that's "in recovery" mode, so you won't be able to access it. I think you should look into replication.
March 27, 2009 at 10:18 am
Sorry, as Steveb said!
March 27, 2009 at 10:19 am
I knew there was something about mirroring that would prevent me from using it...
thanks for the advice. I think I am leaning towards the backup/restore plan while I try to light a fire under a DW.:-)
March 27, 2009 at 10:21 am
no worries! I will look into replicating the data and in the mean time I think my best bet is to get an automated restore setup.
March 27, 2009 at 10:28 am
I thought that you could create a database snapshot of a mirror that you could use to run queries.
March 27, 2009 at 10:31 am
Michael Valentine Jones (3/27/2009)
I thought that you could create a database snapshot of a mirror that you could use to run queries.
oh! that might work. I will look into it.
thanks for the thought.
Meredith
March 27, 2009 at 10:32 am
^Yes, I think you can create a snapshot of the mirror, but only if you've got Enterprise edition.
March 27, 2009 at 10:34 am
akeelm_uk (3/27/2009)
^Yes, I think you can create a snapshot of the mirror, but only if you've got Enterprise edition.
I've got constraint covered.
thanks!
March 27, 2009 at 10:34 am
Having done both, I cannot imagine that Replicating all of the tables in a database would be less work and less maintenance & overhead than Log Shipping.(!) If you are having that many problems with Log shipping then I would suggest doing a Root Cause Analysis of that first, because replicating many, many tables is definitely moving towards higher overhead and more intervention, not away from it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2009 at 10:36 am
I agree with Barry. I can't see replication as less work at all.
March 27, 2009 at 10:42 am
RBarryYoung (3/27/2009)
Having done both, I cannot imagine that Replicating all of the tables in a database would be less work and less maintenance & overhead than Log Shipping.(!) If you are having that many problems with Log shipping then I would suggest doing a Root Cause Analysis of that first, because replicating many, many tables is definitely moving towards higher overhead and more intervention, not away from it.
Good point. I've not had many issues with replication, but as I mentioned I am only replicating a handfull of my total possible tables.
As far as root cause analysis of my logshipping issues there seem to be a few. One issue I have is with the shared folder setup that is needed. On a cluster shared folders on cluster resources need to be recreated after every reboot, or failover. If that doesn't happen then logshipping copies fail. And, of course you really want your backup jobs writting to a cluster resource so that they continue to work in a failover situation.
The other issue that I see most often is that the restore job just doesn't restore the files. I don't get a failure, it just skips the trn logs and tells me that a later trn log must be applied. If I use that same set of files and do a manual restore they work fine and logshipping will most likey do it's thing the next night.
March 27, 2009 at 11:29 am
other solution is use to SAN BOX whith replication like netapps or equalogics solution is verry fast replication depending if you are on lan or Wan replication.
is totaly different aproche and is maybe expensive but is great product for maximum protecttion
http://www.netapp.com/fr/products/protocols/fc-san/
or
March 27, 2009 at 11:32 am
sebastien piche (3/27/2009)
other solution is use to SAN BOX whith replication like netapps or equalogics solution is verry fast replication depending if you are on lan or Wan replication.is totaly different aproche and is maybe expensive but is great product for maximum protecttion
http://www.netapp.com/fr/products/protocols/fc-san/
or
I've got a 3par SAN with replication already in place that my cluster runs on currently. I'm not looking for a DR copy, more a copy of the DB that can be used for querying and reporting. I can't wrap my head around how a SAN replicated copy would work for our use.
March 27, 2009 at 11:43 am
depend with san you have but is possible use clone drive to connect your SQL on this clone on same SAN. and reuse the Same clone id each time you shedule new one.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply