Database Mirroring or Log Shipping?

I made a mistake when I quoted the technet article. Here is the statement
that says the mirrored db can't be accessed directly:

"Because the mirror database is in a recovering state, it cannot be accessed
directly. You can create database snapshots on the mirror to indirectly read
the mirror database at a point in time."

Again, snapshots are not an option for us b/c we have SQL 2005 STD.

We also can't use a new copy of SQL 2005 Enterprise edition as the mirror
server and take snapshots because as I understand it, you have to keep the
versions/editions all the same for compatibilities sake.

Maybe Log Shipping is an alternative, but thought that I read that Log
Shipping be deprecated in future versions.

Again, advice is greatly appreciated,

--
Josh Blair (hfdev)
HydraForce, Inc.


"hfdev" wrote:

> Hello,
>
> I am researching database mirroring and log shipping to determine if either
> will help my organization achieve a certain goal.
>
> We are looking for a way to make our production ERP database (currently ~30
> GB in size) available in near real-time on another server for read-only
> access from numerous client applications and SSRS.
>
> We have SQL Server 2005 STD so snapshots are out of the question.
>
> Replication is not an option: not supported by ERP vendor, they say the db
> isn't structured in a way that will support replication.
>
> I was thinking database mirroring would be perfect until I read similar
> statements to thie following in a number of older publications:
>
> "The mirror database must be initialized from a restore of the principal
> database with NORECOVERY, followed by restores in sequence of principal
> transaction log backups."
>
> and
>
> "The mirror database must have the same name as the principal database."
>
> Database Mirroring in SQL Server 2005 (published on April 1, 2005)
> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>
> Q: With Database Mirroring on SQL 2005, can you access the data on the
> mirrored copy (secondary) to use as a read-only datasource for client apps
> and SSRS?
>
> Can you recommend any other alternatives?
>
> Thanks in advance,
>
> --
> Josh Blair (hfdev)
> HydraForce, Inc.
 >> Stay informed about: Database Mirroring or Log Shipping? 

Hi Josh,
I think all this is answered in my original post but let me know if not.
Cheers,
Paul Ibison (www.replicationanswers.com)
 >> Stay informed about: Database Mirroring or Log Shipping? 

Paul,

What are the requirements on a database to know if it can be replicated
using Transactional Replication? I ask because our ERP vendor insists that
Transactional Replication is not supported on our ERP database.

Does it require that all tables have PKs? I think there are a few tables
that may not have PKs in our ERP database.

What other requirements?

Thanks,

--
Josh Blair (hfdev)
HydraForce, Inc.


"Paul Ibison" wrote:

> As you have standard edition, snapshots are not available and unlike
> log-shipping, the mirror database can't be restored in standby state (just
> norecovery). Actually mirroring wouldn't be suitable anyway, or at least not
> straightforward, because you'd have to create new snapshots each minute to
> achieve the latency you need, and then change the connection-string
> dynamically.
> For read-only access, log-shipping exists in standard edition and you can
> restore in standby (read-only) state. However this won't be suitable either
> because you need low latency and in the case of log-shipping, the users will
> be kicked off the database each time a log restores.
> Transactional replication to a reporting server could be considered - I'd
> look into this for your solution (assuming all tables have PKs!).
> HTH,
> Paul Ibison (www.replicationanswers.com)
 >> Stay informed about: Database Mirroring or Log Shipping? 

Can you specify the items that you want to be replicated, or avoid
replicating certain items when configuring Transaction Replication?

--
Josh Blair (hfdev)
HydraForce, Inc.


"Paul Ibison" wrote:

> As you have standard edition, snapshots are not available and unlike
> log-shipping, the mirror database can't be restored in standby state (just
> norecovery). Actually mirroring wouldn't be suitable anyway, or at least not
> straightforward, because you'd have to create new snapshots each minute to
> achieve the latency you need, and then change the connection-string
> dynamically.
> For read-only access, log-shipping exists in standard edition and you can
> restore in standby (read-only) state. However this won't be suitable either
> because you need low latency and in the case of log-shipping, the users will
> be kicked off the database each time a log restores.
> Transactional replication to a reporting server could be considered - I'd
> look into this for your solution (assuming all tables have PKs!).
> HTH,
> Paul Ibison (www.replicationanswers.com)
 >> Stay informed about: Database Mirroring or Log Shipping? 

For plain transactional replication, you'll need to have PKs on the
replicated tables.
There are also some issues with text, ntext and image datatypes - have a
look in BOL, but if changes to these columns are not done in a precise way,
the values won't be replicated.
HTH,
Paul Ibison (www.replicationanswers.com)
 >> Stay informed about: Database Mirroring or Log Shipping? 

Yes - you can select the articles (tables, stored procs, udfs, views) you
want to replicate. Some are treated differently to others - eg a replicating
a table will replicate changes to the data in the table, while a replicating
a view won't replicate the changes made to the view definition.
To further answer your question, you can specify which rows and columns you
want to replicate in tables also.
HTH,
Paul Ibison (www.replicationanswers.com)
 >> Stay informed about: Database Mirroring or Log Shipping? 

Hello Josh,

If you would like to set up Log Shipping and afraid of ONLY deprecated in
future versions than you can do it manually too.

Log Shipping nothing but moving tlog from Primary to Secondary once restored
the Full backup on Secondary.

Following might help you to do manual setup.

http://www.nyx.net/~bwunder/sql/logSync.htm

Good Luck!!!


"hfdev" wrote:

> I made a mistake when I quoted the technet article. Here is the statement
> that says the mirrored db can't be accessed directly:
>
> "Because the mirror database is in a recovering state, it cannot be accessed
> directly. You can create database snapshots on the mirror to indirectly read
> the mirror database at a point in time."
>
> Again, snapshots are not an option for us b/c we have SQL 2005 STD.
>
> We also can't use a new copy of SQL 2005 Enterprise edition as the mirror
> server and take snapshots because as I understand it, you have to keep the
> versions/editions all the same for compatibilities sake.
>
> Maybe Log Shipping is an alternative, but thought that I read that Log
> Shipping be deprecated in future versions.
>
> Again, advice is greatly appreciated,
>
> --
> Josh Blair (hfdev)
> HydraForce, Inc.
>
>
> "hfdev" wrote:
>
> > Hello,
> >
> > I am researching database mirroring and log shipping to determine if either
> > will help my organization achieve a certain goal.
> >
> > We are looking for a way to make our production ERP database (currently ~30
> > GB in size) available in near real-time on another server for read-only
> > access from numerous client applications and SSRS.
> >
> > We have SQL Server 2005 STD so snapshots are out of the question.
> >
> > Replication is not an option: not supported by ERP vendor, they say the db
> > isn't structured in a way that will support replication.
> >
> > I was thinking database mirroring would be perfect until I read similar
> > statements to thie following in a number of older publications:
> >
> > "The mirror database must be initialized from a restore of the principal
> > database with NORECOVERY, followed by restores in sequence of principal
> > transaction log backups."
> >
> > and
> >
> > "The mirror database must have the same name as the principal database."
> >
> > Database Mirroring in SQL Server 2005 (published on April 1, 2005)
> > http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
> >
> > Q: With Database Mirroring on SQL 2005, can you access the data on the
> > mirrored copy (secondary) to use as a read-only datasource for client apps
> > and SSRS?
> >
> > Can you recommend any other alternatives?
> >
> > Thanks in advance,
> >
> > --
> > Josh Blair (hfdev)
> > HydraForce, Inc.
 >> Stay informed about: Database Mirroring or Log Shipping? 

To be honest, I'm not too sure where this rumour originated from - as far as
I know log-shipping is not deprecated in SQL Server 2008, so if it were to be
deprecated in SQL Server 2011 that would mean it would be removed in SQL
Server 2014!
HTH,
Paul Ibison (www.replicationanswers.com)
 >> Stay informed about: Database Mirroring or Log Shipping? 

Thanks for the confirmation on this topic.

I tried to setup Transactional Replication on Adventureworks and as you
might guess, it worked very well. Once I had a bit of a grip on the
concepts, I tried a similar test on my ERP database. No go. Most of the
tables use a mechanism other than PKs to uniquely identify records (long
story...this ERP system was originally built on a hierarchical dbms (before
Windows of SQL Server existed) and ported over to SQL 2000 many years ago but
they did some "magic" to make that happen that apparently doesn't make use of
PKs). I have read about techniques used to get around this issue, but if I
muck around with the ERP table structures, they will no longer support my
organization.

Looks like Log Shipping in the Standby state might be my best option now. I
am assuming that the Standby state allows client connections to hit that data
in the secondary database. Is that true?

If I choose Standby mode, I see there is a setting to automatically
disconnect users in the db to restore backups. That seems rather abrupt.
Could I instead choose to not kick them out but configure alerts to warn me
that certain amount of time has passed since the last transaction log has
been restored? Then I could determine which users are keeping things locked
and have them disconnect manually to allow restore to happen?

Thanks,

--
Josh Blair (hfdev)
HydraForce, Inc.


"Paul Ibison" wrote:

> For plain transactional replication, you'll need to have PKs on the
> replicated tables.
> There are also some issues with text, ntext and image datatypes - have a
> look in BOL, but if changes to these columns are not done in a precise way,
> the values won't be replicated.
> HTH,
> Paul Ibison (www.replicationanswers.com)
 >> Stay informed about: Database Mirroring or Log Shipping? 

Hi Josh,
yes - the database would be in read-only mode when in Standby state. There
is a job which does the copies and a separate job which does the restores
(called "load"), and you could disable the second job and run it manually if
necessary.
HTH,
Paul Ibison (www.replicationanswers.com)
 >> Stay informed about: Database Mirroring or Log Shipping? 

Thanks for the scripts FM.

I used SSMS to configure the Log Shipping in 2005 and it seems to get the
job done.

--
Josh Blair (hfdev)
HydraForce, Inc.


"FM" wrote:

> Hello Josh,
>
> If you would like to set up Log Shipping and afraid of ONLY deprecated in
> future versions than you can do it manually too.
>
> Log Shipping nothing but moving tlog from Primary to Secondary once restored
> the Full backup on Secondary.
>
> Following might help you to do manual setup.
>
> http://www.nyx.net/~bwunder/sql/logSync.htm
>
> Good Luck!!!
>
>
> "hfdev" wrote:
>
> > I made a mistake when I quoted the technet article. Here is the statement
> > that says the mirrored db can't be accessed directly:
> >
> > "Because the mirror database is in a recovering state, it cannot be accessed
> > directly. You can create database snapshots on the mirror to indirectly read
> > the mirror database at a point in time."
> >
> > Again, snapshots are not an option for us b/c we have SQL 2005 STD.
> >
> > We also can't use a new copy of SQL 2005 Enterprise edition as the mirror
> > server and take snapshots because as I understand it, you have to keep the
> > versions/editions all the same for compatibilities sake.
> >
> > Maybe Log Shipping is an alternative, but thought that I read that Log
> > Shipping be deprecated in future versions.
> >
> > Again, advice is greatly appreciated,
> >
> > --
> > Josh Blair (hfdev)
> > HydraForce, Inc.
> >
> >
> > "hfdev" wrote:
> >
> > > Hello,
> > >
> > > I am researching database mirroring and log shipping to determine if either
> > > will help my organization achieve a certain goal.
> > >
> > > We are looking for a way to make our production ERP database (currently ~30
> > > GB in size) available in near real-time on another server for read-only
> > > access from numerous client applications and SSRS.
> > >
> > > We have SQL Server 2005 STD so snapshots are out of the question.
> > >
> > > Replication is not an option: not supported by ERP vendor, they say the db
> > > isn't structured in a way that will support replication.
> > >
> > > I was thinking database mirroring would be perfect until I read similar
> > > statements to thie following in a number of older publications:
> > >
> > > "The mirror database must be initialized from a restore of the principal
> > > database with NORECOVERY, followed by restores in sequence of principal
> > > transaction log backups."
> > >
> > > and
> > >
> > > "The mirror database must have the same name as the principal database."
> > >
> > > Database Mirroring in SQL Server 2005 (published on April 1, 2005)
> > > http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
> > >
> > > Q: With Database Mirroring on SQL 2005, can you access the data on the
> > > mirrored copy (secondary) to use as a read-only datasource for client apps
> > > and SSRS?
> > >
> > > Can you recommend any other alternatives?
> > >
> > > Thanks in advance,
> > >
> > > --
> > > Josh Blair (hfdev)
> > > HydraForce, Inc.
 >> Stay informed about: Database Mirroring or Log Shipping? 

Paul, FM,

I just located the text that denotes Log Shipping was going to be
deprecated. It is in first printing of "Microsoft SQL Server 2005 Unleashed"
by Sams Publishing; ISBN 0-672-32824-0

I guess that text is outdated. The 2008 version doesn't appear to be out yet.

--
Josh Blair (hfdev)
HydraForce, Inc.


"Paul Ibison" wrote:

> To be honest, I'm not too sure where this rumour originated from - as far as
> I know log-shipping is not deprecated in SQL Server 2008, so if it were to be
> deprecated in SQL Server 2011 that would mean it would be removed in SQL
> Server 2014!
> HTH,
> Paul Ibison (www.replicationanswers.com)
 >> Stay informed about: Database Mirroring or Log Shipping? 

Probably a bit academic in your case but I'm still not convinced by this -
there's nothing in the list of deprecated features in SQL Server 2008 which
mentions log-shipping, and the SAMS book is not a MS book. Anyway, if you
have decided on another solution that's fine.
Rgds,
Paul Ibison (www.replicationanswers.com)
 >> Stay informed about: Database Mirroring or Log Shipping?