Prev: Statistics Creation on SQL 2005
Next: BCP Qusetion
From: Fraz on 30 Apr 2010 11:04
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: TheSQLGuru on 30 Apr 2010 16:19
not sure about third party options here.

but you are mistaken that logshipping and mirroring cannot be used for
reporting. for log shipping the only catch is that connected users will
prevent applying new tlogs, but that can be solved by accepting lag on
applying logs and/or disconnecting users to apply what you need to. for
mirroring you would use a snapshot and report against that. again you will
not have 'up-to-the-second' data like you might with replication but it is
certainly workable.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Fraz" <Fraz(a)discussions.microsoft.com> wrote in message
news:4CD0B3CF-7B24-4CDF-B37D-3E77711B2D27(a)microsoft.com...
>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: Statistics Creation on SQL 2005
Next: BCP Qusetion