467,894 Members | 1,446 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,894 developers. It's quick & easy.

Moving data for reporting

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

Jul 23 '05 #1
  • viewed: 962
Share:
4 Replies

"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
Jul 23 '05 #2
I need the data to be current, as they are reporting databases. The
most lag-time I can have is about 15 minutes.

Jul 23 '05 #3

"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
Jul 23 '05 #4
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.

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Ryan | last post: by
1 post views Thread by C4rtm4N | last post: by
7 posts views Thread by =?Utf-8?B?TW9iaWxlTWFu?= | last post: by
1 post views Thread by =?Utf-8?B?UmljaA==?= | last post: by
5 posts views Thread by Sanjay Pais | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.