Replication newbie

[John]

We have 2 SQL 2005 servers. One in the local office and a Second at a co-lo
in another state.
What I am needing to do, to replicate the local one, to the co-lo. I am
thinking that Transactional would be best as it would keep an almost realtime
copy.

The part I am not sure about, is if we add a table, or modify a table, will
that change get replicated out as well?

[Ben Thul]

Before you embark down the replication path, what need are you trying
to serve? Replication may or may not be the right answer depending.
It sounds like your need may be to have a backup copy at your co-lo,
in which case log shipping or mirroring might serve you better.

But, to answer your question, some schema changes are handled
automatically while others aren't. For instance, adding a new table
to the published database isn't handled automatically, but changes to
already replicated tables are (assuming you've configured it for
this).

[John]

it is so that we would have a backup copy. I havent heard of 'log shipping'
before, but wouldn't mirroring take a lot of bandwidth?

"Ben Thul" wrote:

> .
>

[Ben Thul]

Ah... so you are trying to accomplish some sort of high availability.
In that case, I'd definitely suggest either mirroring or log-
shipping. I'll answer your question about bandwidth with another
question: how much is your data (and uptime) worth?

Relevant BOL links:

Log shipping: http://msdn.microsoft.com/en-us/library/ms187103%28SQL.90%29.aspx
Mirroring: http://msdn.microsoft.com/en-us/library/ms177412(SQL.90).aspx
High availability: http://msdn.microsoft.com/en-us/library/ms190202%28SQL.90%29.aspx

HTH!

[John]

I am sure that as long as data was restored within an hour, that would be OK.

Would Log-Shipping work if a table was modified by adding a field? or if a
new table was created in a database?

"Ben Thul" wrote:

> .
>

[Ben Thul]

Log shipping works by applying transaction log dumps from the primary
to the secondary. So, any change that happens at the primary will
happen at the secondary once the log that includes that change is
applied. So, both ddl and dml should propagate.

However, as with most things, I invite you to try it. You shouldn't
be leveraging your business on what some crackpot on the internet said
without testing it out. And yes, I realize I just called myself a
crackpot. :) "Trust, but verify".

[John]

LOL ok, thank you for your help, I appreciate it.

"Ben Thul" wrote:

> > > > > > What I am needing to do, to replicate the local one, to the co-lo.. I am

> > > > > > thinking that Transactional would be best as it would keep an almost realtime
> > > > > > copy.
> >
> > > > > > The part I am not sure about, is if we add a table, or modify a table, will
> > > > > > that change get replicated out as well?
> >
> > > > > .
> >
> > > .
> >
> >
>

> .
>