How to setup reporting database

Hi!
Our client is using sql2000 sp3a OLTP database. They are also using a lot of
reports that generate high utilization on the server.
We would like to move that load to secondary server. What is the easiest way
to do that without modifying the current database or application?
First thing that comes to my mind is log shipping but as far as I know, in
this case we would get at least 1 min delay between primary and secondary
server.
I think that's great for reports but our clients don't think so...They
request seconds...
Alternative would be transactional replication. Can I replicate the entire
database with all views, stored procedures...?
I also found out that it requires that all the tables have at least one
primary key. Off course NOT all tables have them. How to solve this? Can
PK's be added easy or does it mean that they would have to modify the
database and application? The goal is not to modify the database and
application if posible.
Any sugestions?
tomLog Shipping wouldnt work anyways because the db is in a load state until
you apply the final TLog since you are constantly restoring. Transactional
Replication is pretty common for this. Not having a PK on a table is pretty
rare. Put an Identity column on the tables that don't have one.
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:OQTJVlHqEHA.1644@.tk2msftngp13.phx.gbl...
> Hi!
> Our client is using sql2000 sp3a OLTP database. They are also using a lot
of
> reports that generate high utilization on the server.
> We would like to move that load to secondary server. What is the easiest
way
> to do that without modifying the current database or application?
> First thing that comes to my mind is log shipping but as far as I know, in
> this case we would get at least 1 min delay between primary and secondary
> server.
> I think that's great for reports but our clients don't think so...They
> request seconds...
> Alternative would be transactional replication. Can I replicate the entire
> database with all views, stored procedures...?
> I also found out that it requires that all the tables have at least one
> primary key. Off course NOT all tables have them. How to solve this? Can
> PK's be added easy or does it mean that they would have to modify the
> database and application? The goal is not to modify the database and
> application if posible.
>
> Any sugestions?
>
> tom
>|||P.S.
Yes, you can replicate View, Procs, etc. but then you need a new snapshot
whenever they are modified. I prefer to just copy and paste them personally.
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:OQTJVlHqEHA.1644@.tk2msftngp13.phx.gbl...
> Hi!
> Our client is using sql2000 sp3a OLTP database. They are also using a lot
of
> reports that generate high utilization on the server.
> We would like to move that load to secondary server. What is the easiest
way
> to do that without modifying the current database or application?
> First thing that comes to my mind is log shipping but as far as I know, in
> this case we would get at least 1 min delay between primary and secondary
> server.
> I think that's great for reports but our clients don't think so...They
> request seconds...
> Alternative would be transactional replication. Can I replicate the entire
> database with all views, stored procedures...?
> I also found out that it requires that all the tables have at least one
> primary key. Off course NOT all tables have them. How to solve this? Can
> PK's be added easy or does it mean that they would have to modify the
> database and application? The goal is not to modify the database and
> application if posible.
>
> Any sugestions?
>
> tom
>|||"ChrisR" <chris@.noemail.com> wrote in message
news:ODv4F0JqEHA.376@.TK2MSFTNGP14.phx.gbl...
> Log Shipping wouldnt work anyways because the db is in a load state until
> you apply the final TLog since you are constantly restoring. Transactional
> Replication is pretty common for this. Not having a PK on a table is
pretty
> rare. Put an Identity column on the tables that don't have one.
Well, for reporting you can put the receiver into read-only mode, except
when the log is being applied.
Of course if you do this every minute, you spend most of your time NOT in
read-only mode. ;-)
And yes, this is a case where Joe Celko not withstanding an Identity key can
be a useful PK if you don't have one already.