Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
SQL Server 2005 Replication Question
1 view
Skip to first unread message
Fraz
Apr 28, 2010, 2:32:01 PM4/28/10
to
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.
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.
Chris
May 5, 2010, 10:50:04 PM5/5/10
to
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.
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!
0 new messages