Creating a queryable failover

Hi,
We have a failover database that is currently run using log shipping,
this means that the fail over is unusable.
What we would like to do is use a tool such as replication to instead
ensure that the fail over database is kept in sync and we can also use
the fail over database to run out reporting jobs.
We investigated SQL Server 2005 and found that with two way replication
this would work wonderfully well except for the fact that we are unable
to replicate new tables without creating a new snap shot.
As our database's are about 150GB in size creating an restoring a
snapshot takes about 2 days for SQL server, meaning that everytime we
add a new table the failover will be unavailable for 2 days, which is
unacceptable for both a fail over and reporting.
Is there any other way we can do this ? Happy to provide more details
if need.
- Barry
barry at 5thfinger dot comHow about database mirroring and creating a snapshot of the mirrored databas
e?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<blat001@.gmail.com> wrote in message news:1159315688.269386.201360@.d34g2000cwd.googlegroups.
com...
> Hi,
> We have a failover database that is currently run using log shipping,
> this means that the fail over is unusable.
> What we would like to do is use a tool such as replication to instead
> ensure that the fail over database is kept in sync and we can also use
> the fail over database to run out reporting jobs.
> We investigated SQL Server 2005 and found that with two way replication
> this would work wonderfully well except for the fact that we are unable
> to replicate new tables without creating a new snap shot.
> As our database's are about 150GB in size creating an restoring a
> snapshot takes about 2 days for SQL server, meaning that everytime we
> add a new table the failover will be unavailable for 2 days, which is
> unacceptable for both a fail over and reporting.
> Is there any other way we can do this ? Happy to provide more details
> if need.
> - Barry
> barry at 5thfinger dot com
>|||Barry - a simple way of achieving this setup is to use transactional
replication with queued updating subscribers. This can take care of identity
range issues and provide you with a means of getting new articles easily
incorporated into the setup.
As Tibor mentions, Mirroring and Snapshots is also now to be considered as
an alternative in this space, assuming your reporting requirements don't
include the use of FTI. This has the (huge) added advantage of automatic
failover if you have a witness and high safety mode enabled.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||You might want to add the new articles to a different publication. This way
you would not have to create the entire snapshot. What form of replication
are you using? Merge? Bi-directional transactional replication and peer to
peer do not require the entire snapshot to be regeneretated.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<blat001@.gmail.com> wrote in message
news:1159315688.269386.201360@.d34g2000cwd.googlegroups.com...
> Hi,
> We have a failover database that is currently run using log shipping,
> this means that the fail over is unusable.
> What we would like to do is use a tool such as replication to instead
> ensure that the fail over database is kept in sync and we can also use
> the fail over database to run out reporting jobs.
> We investigated SQL Server 2005 and found that with two way replication
> this would work wonderfully well except for the fact that we are unable
> to replicate new tables without creating a new snap shot.
> As our database's are about 150GB in size creating an restoring a
> snapshot takes about 2 days for SQL server, meaning that everytime we
> add a new table the failover will be unavailable for 2 days, which is
> unacceptable for both a fail over and reporting.
> Is there any other way we can do this ? Happy to provide more details
> if need.
> - Barry
> barry at 5thfinger dot com
>