Moving data for reporting

[joshsackett]

Hi all,
I have 5 databases, each about 20GB in size. I need to copy the data to
a new server for reporting purposes. Initially I wanted to setup
transactional replication; however, the database schemas often change
(adding or modifying tables) and in order to accomodate those changes
through replication I would need to send a new snapshot each time a
change occurs. This is very time consuming.

So I decided instead to use log shipping, until I realized the
reporting databases would be unavailable during the period when the
transaction log was being applied. That also will not work.

So what does everyone else use? My only requirements are that the
reporting databases be available during business hours and there be
very little administration when it comes to changing the database
schema.

Thanks,
Josh

[Simon Hayes]

"joshsackett" <jo*********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Hi all,
I have 5 databases, each about 20GB in size. I need to copy the data to
a new server for reporting purposes. Initially I wanted to setup
transactional replication; however, the database schemas often change
(adding or modifying tables) and in order to accomodate those changes
through replication I would need to send a new snapshot each time a
change occurs. This is very time consuming.

So I decided instead to use log shipping, until I realized the
reporting databases would be unavailable during the period when the
transaction log was being applied. That also will not work.

So what does everyone else use? My only requirements are that the
reporting databases be available during business hours and there be
very little administration when it comes to changing the database
schema.

Thanks,
Josh

What about simply using backup and restore? If they only have to be
available during business hours, then you have all night to do that, unless
of course you have multiple offices in multiple time zones, which would
reduce your maintenance window.

Simon

[joshsackett]

I need the data to be current, as they are reporting databases. The
most lag-time I can have is about 15 minutes.

[Simon Hayes]

"joshsackett" <jo*********@gmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

I need the data to be current, as they are reporting databases. The
most lag-time I can have is about 15 minutes.

Ah, well you didn't include that in your "only" requirements... :-) Probably
transactional replication is still the best option, given the time
constraint, and if you can manage the schema changes; you can replicate
adding and dropping columns, but whole tables may be more awkward.
Personally, I would be concerned if the schema is constantly changing, and
especially if it's changing during working hours, but I appreciate that this
may be out of your control. If you can implement schema changes only during
defined and planned maintenance windows, then you should be able to handle
the snapshots at the same time.

You might also want to post in microsoft.public.sqlserver.replication, given
that replication is quite a specialized area.

Simon

[joshsackett]

I suppose I did miss one of the more important requirements...

I think you are right and I've been going at this the wrong way.
Instead of trying to back fill schema changes on the fly it should be
built into a process.

Thanks.