First off, I'm an ASP.Net developer and not a SQL DB admin.

One of my clients uses a program that uses a SQL Server database. They
want to run intensive intra-day reports against a replicated database
on a different server as to not affect their mission critical
operations. I was going to set up transactional replication for them.
However, the database has 300+ tables and not one of them has a primary
key index! I can't touch the database, so transactional replication is
out the window.

Is there another way to "replicate" a database via a DTS package. I was
thinking that I could do something like this:

1. Restore the reporting database from the original.
2. Clear the transaction log on the original.
3. Every fifteen minutes or so run a package that does the following:
a. Restores the reporting database via the transaction log from the
original using a DTS package.
b. Saves a copy of the transaction log for archive purposes.
c. Resets the original tran log.

Is this possible or just way off base? If possible, could someone give
me some pointers on creating such a package? If not, what would you
suggest to synchronize data to a reporting database on a different
server?

Thanks so much,

Jason

Why not do Snapshot then?

You could run reports at given intervals and do the smapshot immediately
prior to this.

Sure you could do it using DTS but it isn't really its forte. You would
need to handle for all 300 tables the INSERT, UPDATE, DELETE statements
against the source.

Basically the setup would be labour intensive although it would work. You
would not get as "real time" as T Replication either as the cost of onvoking
the DTS package every 5 seconds or so would be prohibitive.


--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


<jjbutera (AT) hotmail (DOT) com> wrote

Snapshot replication won't work because the reports are very ad-hoc and
run whenever by analysts throughout the day. What about transaction log
restores?

You may want to look at "Log Shipping" which is very easy to setup and
maintain.
The database could be setup as a "ReadOnly" Stand by Server and also work as
a reporting server.

Edmund

"jjbutera (AT) hotmail (DOT) com" wrote:

The only problem I can see with the T Log solution is that your users cannot
run reports whilst doing a restore and that may be a problem. If you do not
know when they are going to be in or out of the DB then this may be an
issue.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Ed" <Ed (AT) discussions (DOT) microsoft.com> wrote

What would you suggest as the best solution if replication cannot be
used?

Log shipping - whether manual or through the wizard won't really do it for
you as the restore of the log only succeeds if the user connections have
been dropped. The same would apply to shipping the database backups.
Transactional replication is out because you don't have PKs, unless you
create loads of indexed views but this doesn't seem viable.
So, what's left? Merge replication is a posibility but it'll change the
schema of existing articles to add a guid column if one doesn't already
exist (along with the rowgiuid attribute). If your code can cope with this
(no unqualified insert into x select * from y queries) then this is a
possibility. One other avenue you might want to look at is the
Synchronization toolkit from Redgate
(http://www.red-gate.com/sql_comparis...on_toolkit.htm)
which allows you to automate the use of DataCompare. Finally, database
mirroring with database snapshots in SQL Server 2005 is designed for exactly
your requirements, so this might be an option for the future.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)