Discussion:
Replication newbie
(too old to reply)
John
2010-05-20 23:40:01 UTC
Permalink
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
2010-05-21 03:03:19 UTC
Permalink
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).
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?
John
2010-05-21 14:53:01 UTC
Permalink
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?
Post by 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).
Post by 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
2010-05-21 16:26:43 UTC
Permalink
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!
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?
Post by 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).
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?
.
John
2010-05-21 17:56:01 UTC
Permalink
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?
Post by 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?
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!
Post by 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?
Post by 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).
Post by 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
2010-05-21 18:50:47 UTC
Permalink
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".
Post by 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?
Post by 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?
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!
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?
Post by 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).
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?
.
.
John
2010-05-21 20:56:01 UTC
Permalink
LOL ok, thank you for your help, I appreciate it.
Post by 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".
Post by 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?
Post by 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?
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!
Post by 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?
Post by 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).
Post by 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?
.
.
.
Loading...