Prev: Oracle publisher
Next: Err:The merge process could not enumerate deletions at the 'Publis
From: Fraz on 28 Apr 2010 14:32
I am looking into possible options for replicating data for reporting
purposes. I have found transactional replication to work best for realtime
reporting, source used for user transactions and target for reporting. I have
come across a large vendor database 50GB on SQL Server 2005 where reports are
taking too long and we are trying to separate it by using transactional
replication but most of the key tables don't have primary keys and we don't
want to change the database schema by adding PK. What are other options we
could have; log shipping, DB Mirroring the target is not usable for
reporting. Any SQL Server built-in tool or third party tool that could
achieve same results as transactional replication but most of the tables
don't have PKs. Any suggestion on this is appreciated. Thanks.
From: Chris on 5 May 2010 22:50
Log shipping is only sql option, secondary db is in read only mode. However
updating secondary by restoring a log will disconnect all users. If you are
only looking to restore every 2 hours or so or nightly this may work, if
users do not like the latency, well, take the time to add pks.

you might want to consider adding identity column to end of table, it is
self populating and most likely will not break your app.

Trans repl is the way to go!

chris
sqldatabasesolutions.com

"Fraz" wrote:

> I am looking into possible options for replicating data for reporting
> purposes. I have found transactional replication to work best for realtime
> reporting, source used for user transactions and target for reporting. I have
> come across a large vendor database 50GB on SQL Server 2005 where reports are
> taking too long and we are trying to separate it by using transactional
> replication but most of the key tables don't have primary keys and we don't
> want to change the database schema by adding PK. What are other options we
> could have; log shipping, DB Mirroring the target is not usable for
> reporting. Any SQL Server built-in tool or third party tool that could
> achieve same results as transactional replication but most of the tables
> don't have PKs. Any suggestion on this is appreciated. Thanks.
 | 
Pages: 1
Prev: Oracle publisher
Next: Err:The merge process could not enumerate deletions at the 'Publis