do I need Indexed Views for Replication

sql2k sp3
Howdy all. Ive been trying to avoid using Indexed Views
for Replication in my scenario because Im not totally
faliliar with the whole schemabinding deal. Plus I like
the convenience of sp_repladdcolumn. I was going to use
Custom Sync Objects instead but I think they may not work
for me. As you've all read in my previous posts, my
Subscriber has a different schema than my publisher. But I
just realized its different in ways I didnt see and now I
think Indexed Views are my only way to go.
I currently have a reporting server that at night has dts
packages that truncate the tables and repopulate from
production. But what I just realized is that some of the
destination tables are actually made up from two or more
source tables.
Source tables:
create table table1 (c1 int primary key,c2 varchar(10))
create table table2 (c1 int,c2 varchar(10))
Destination table:
create table bla(c1 int,c2 varchar(10),c3 varchar(10))
Example insert from nightly DTS package:
insert into bla(c1, c2, c3)
select t1.c1,t1.c2,t2.c2
from table1 t1
inner join table2 t2 on t1.c1 = t2.c1
So as you see, the destination is made up from two source
tables. I wrote a view for the initial population and the
data bcp'd successfully. But when I tried to insert a row
I had problems because of column c3. It makes sense that I
would need an Indexed View for this scenario because using
Custom Sync Objects the data is still coming from the
table, not a view. And since the Publisher doesnt have
column c3, it would seem a view would be needed. However,
before I flip flop back to and Indexed View, I would like
to see what you pros think.
TIA, ChrisR
is it possible to convert the DTS package to a publication and replicate
from the DTS source server to what is currenly your publisher and also to
your subscriber?
What are your requirements? It looks like data changes at discrete intervals
and then is static until the next package is run. Replication is the
replication of transactions or deltas (merge). If you have no real time
requirements DTS might be the best solution end to end. Either that or
perhaps snapshot replication.
Secondly I take it you are making a mistake in the schema.
I hope you mean this
create table table1 (c1 int primary key,c2 varchar(10))
create table table2 (c1 int,c3 varchar(10))
Destination table:
create table bla(c1 int,c2 varchar(10),c3 varchar(10))
Instead of this

> create table table1 (c1 int primary key,c2 varchar(10))
> create table table2 (c1 int,c2 varchar(10))
> Destination table:
> create table bla(c1 int,c2 varchar(10),c3 varchar(10))
As you can see there is no c3. Again we are going to need complete schemas
to address this, and tell us exactly what you are trying to do, and give the
exact requirements.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:03da01c49796$8bcb46a0$a301280a@.phx.gbl...
> sql2k sp3
> Howdy all. Ive been trying to avoid using Indexed Views
> for Replication in my scenario because Im not totally
> faliliar with the whole schemabinding deal. Plus I like
> the convenience of sp_repladdcolumn. I was going to use
> Custom Sync Objects instead but I think they may not work
> for me. As you've all read in my previous posts, my
> Subscriber has a different schema than my publisher. But I
> just realized its different in ways I didnt see and now I
> think Indexed Views are my only way to go.
> I currently have a reporting server that at night has dts
> packages that truncate the tables and repopulate from
> production. But what I just realized is that some of the
> destination tables are actually made up from two or more
> source tables.
> Source tables:
> create table table1 (c1 int primary key,c2 varchar(10))
> create table table2 (c1 int,c2 varchar(10))
> Destination table:
> create table bla(c1 int,c2 varchar(10),c3 varchar(10))
>
> Example insert from nightly DTS package:
> insert into bla(c1, c2, c3)
> select t1.c1,t1.c2,t2.c2
> from table1 t1
> inner join table2 t2 on t1.c1 = t2.c1
>
> So as you see, the destination is made up from two source
> tables. I wrote a view for the initial population and the
> data bcp'd successfully. But when I tried to insert a row
> I had problems because of column c3. It makes sense that I
> would need an Indexed View for this scenario because using
> Custom Sync Objects the data is still coming from the
> table, not a view. And since the Publisher doesnt have
> column c3, it would seem a view would be needed. However,
> before I flip flop back to and Indexed View, I would like
> to see what you pros think.
>
> TIA, ChrisR
>
>
|||> What are your requirements? It looks like data changes at discrete
intervals
> and then is static until the next package is run.
Those have been the requirements. Now I need it done continuosly.

> Secondly I take it you are making a mistake in the schema.
Yes. You are correct.
I'll have to post back everything later. Based on what you're seeing, have
you ever had a setup like this without Indexed Views? I dont think it can be
done, but know that you have lots more experience.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uvySyK$lEHA.2020@.TK2MSFTNGP09.phx.gbl...
> is it possible to convert the DTS package to a publication and replicate
> from the DTS source server to what is currenly your publisher and also to
> your subscriber?
> What are your requirements? It looks like data changes at discrete
intervals
> and then is static until the next package is run. Replication is the
> replication of transactions or deltas (merge). If you have no real time
> requirements DTS might be the best solution end to end. Either that or
> perhaps snapshot replication.
> Secondly I take it you are making a mistake in the schema.
> I hope you mean this
> create table table1 (c1 int primary key,c2 varchar(10))
> create table table2 (c1 int,c3 varchar(10))
> Destination table:
> create table bla(c1 int,c2 varchar(10),c3 varchar(10))
> Instead of this
>
> As you can see there is no c3. Again we are going to need complete schemas
> to address this, and tell us exactly what you are trying to do, and give
the
> exact requirements.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:03da01c49796$8bcb46a0$a301280a@.phx.gbl...
>
|||I'm still kind of confused.
It sounds like there are 3 servers, server 1, server 2, and server 3.
Currently a DTS package reads table 1 and table 2 on server 1, and sends
data to server 2 to a table called table 3. Then server 2 publishes table 3
to server 3 to a table called table 4.
All of these tables are different.
Does this describe your situation.
I'd replicate from table 1 & 2 to server 2 table 3, and have a second
publication on server 1 replicating to table 4 on server 3. Replicating two
tables to 1 is a little more complex as you need to figure out which table
will generate a complete row.
It if table 1 contributes column a, b, c, and table 2 contributes column d,
you need the log reader to wirte a command to the distribution database when
there will be a complete set of columns for the row, and there has to be
some relationship between table 1 and table 2. Normally there is an
intersection table or join table and key off this table as a row in the join
table means that there is a row in table 1 and table 2. Then you also have
to replicate table 1 and table 2 as well, so that you have all the columns
for table 3 on the subscriber. The store procedure has to read tables 1 and
tables 2 to build table 3.
I normally use custom sync objects for this sort of stuff. Indexed views are
an option, but there is a space and performance penalty to pay (slight) for
using them, and they aren't resilient to schema changes.
As you have seen custom sync objects aren't problem free either, but they
seem to be an option in your case.
"ChrisR" <chris@.noemail.com> wrote in message
news:%23AsburDmEHA.2024@.TK2MSFTNGP10.phx.gbl...
> intervals
> Those have been the requirements. Now I need it done continuosly.
>
> Yes. You are correct.
> I'll have to post back everything later. Based on what you're seeing, have
> you ever had a setup like this without Indexed Views? I dont think it can
be[vbcol=seagreen]
> done, but know that you have lots more experience.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uvySyK$lEHA.2020@.TK2MSFTNGP09.phx.gbl...
to[vbcol=seagreen]
> intervals
schemas
> the
>
|||No. Two servers. Currently, a DTS Package runs nightly to populate one from
the other. I need to change that and start using replications because it
needs to be done continuosly. As you mentioned earlier:
Source table:
create table table1 (c1 int primary key,c2 varchar(10))
create table table2 (c1 int,c3 varchar(10))
Destination table:
create table bla(c1 int,c2 varchar(10),c3 varchar(10))
These tables cannot be changed. I was able to replicate this scenario using
Indexed Views, but not with Custom Sync Objects. Using CSO's I wab able to
BCP the snapshot with a view that matched the destination table, but the
insert and updates were causing problems because the source is two tables
and the destination is only one.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u7FMWzEmEHA.592@.TK2MSFTNGP11.phx.gbl...
> I'm still kind of confused.
> It sounds like there are 3 servers, server 1, server 2, and server 3.
> Currently a DTS package reads table 1 and table 2 on server 1, and sends
> data to server 2 to a table called table 3. Then server 2 publishes table
3
> to server 3 to a table called table 4.
> All of these tables are different.
> Does this describe your situation.
> I'd replicate from table 1 & 2 to server 2 table 3, and have a second
> publication on server 1 replicating to table 4 on server 3. Replicating
two
> tables to 1 is a little more complex as you need to figure out which table
> will generate a complete row.
> It if table 1 contributes column a, b, c, and table 2 contributes column
d,
> you need the log reader to wirte a command to the distribution database
when
> there will be a complete set of columns for the row, and there has to be
> some relationship between table 1 and table 2. Normally there is an
> intersection table or join table and key off this table as a row in the
join
> table means that there is a row in table 1 and table 2. Then you also have
> to replicate table 1 and table 2 as well, so that you have all the columns
> for table 3 on the subscriber. The store procedure has to read tables 1
and
> tables 2 to build table 3.
> I normally use custom sync objects for this sort of stuff. Indexed views
are
> an option, but there is a space and performance penalty to pay (slight)
for[vbcol=seagreen]
> using them, and they aren't resilient to schema changes.
> As you have seen custom sync objects aren't problem free either, but they
> seem to be an option in your case.
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:%23AsburDmEHA.2024@.TK2MSFTNGP10.phx.gbl...
have[vbcol=seagreen]
can[vbcol=seagreen]
> be
replicate[vbcol=seagreen]
> to
time[vbcol=seagreen]
> schemas
give
>
|||Is there a one to one mapping of table 1 to table 2? Which is the parent and
which is the child?
"ChrisR" <chris@.noemail.com> wrote in message
news:uIQdpGFmEHA.1520@.TK2MSFTNGP10.phx.gbl...
> No. Two servers. Currently, a DTS Package runs nightly to populate one
from
> the other. I need to change that and start using replications because it
> needs to be done continuosly. As you mentioned earlier:
> Source table:
> create table table1 (c1 int primary key,c2 varchar(10))
> create table table2 (c1 int,c3 varchar(10))
> Destination table:
> create table bla(c1 int,c2 varchar(10),c3 varchar(10))
>
> These tables cannot be changed. I was able to replicate this scenario
using[vbcol=seagreen]
> Indexed Views, but not with Custom Sync Objects. Using CSO's I wab able to
> BCP the snapshot with a view that matched the destination table, but the
> insert and updates were causing problems because the source is two tables
> and the destination is only one.
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u7FMWzEmEHA.592@.TK2MSFTNGP11.phx.gbl...
table[vbcol=seagreen]
> 3
> two
table[vbcol=seagreen]
> d,
> when
> join
have[vbcol=seagreen]
columns[vbcol=seagreen]
> and
> are
> for
they[vbcol=seagreen]
> have
> can
> replicate
also[vbcol=seagreen]
> time
or
> give
>
|||For this scenario yes, there is a one to one. Table1 being the parent.
However this is only one of my scenarios. I will also be dealing with some
one to many, and many to many. How does this affect the outcome out of
curiosity. FYI I wont be replicating the DRI. Also. I was just rereading
you're previous reply:

>It if table 1 contributes column a, b, c, and table 2 contributes column d,
>you need the log reader to wirte a command to the distribution database
when
>there will be a complete set of columns for the row, and there has to be
>some relationship between table 1 and table 2. Normally there is an
>intersection table or join table and key off this table as a row in the
join
>table means that there is a row in table 1 and table 2. Then you also have
>to replicate table 1 and table 2 as well, so that you have all the columns
>for table 3 on the subscriber. The store procedure has to read tables 1
and
>tables 2 to build table 3.
If Im reading this correctly, it looks like alot of work in place of Indexed
Views. I mean how hard is it to have the Log Reader write a command to the
Dist db when there's a complete set of columns for a row? Are there schema
change problems with Indexed Views other than not being able to use
sp_repladdcolumn?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:epYGQ6FmEHA.596@.TK2MSFTNGP11.phx.gbl...
> Is there a one to one mapping of table 1 to table 2? Which is the parent
and[vbcol=seagreen]
> which is the child?
> "ChrisR" <chris@.noemail.com> wrote in message
> news:uIQdpGFmEHA.1520@.TK2MSFTNGP10.phx.gbl...
> from
> using
to[vbcol=seagreen]
tables[vbcol=seagreen]
sends[vbcol=seagreen]
> table
Replicating[vbcol=seagreen]
> table
column[vbcol=seagreen]
database[vbcol=seagreen]
be[vbcol=seagreen]
the[vbcol=seagreen]
> have
> columns
1[vbcol=seagreen]
ws[vbcol=seagreen]
(slight)[vbcol=seagreen]
> they
seeing,[vbcol=seagreen]
it[vbcol=seagreen]
> also
the[vbcol=seagreen]
that[vbcol=seagreen]
> or
and
>
|||I just re-read this once more:
Then you also have
> to replicate table 1 and table 2 as well, so that you have all the columns
> for table 3 on the subscriber.
If Im reading this correctly, it's one of the things I was afraid of. Are
you saying I need to have table1 and table2 on the Subscriber so I can
create table3? Im pretty sure you are, but wanted to make sure?
As always, thanks so much for all you're help.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u7FMWzEmEHA.592@.TK2MSFTNGP11.phx.gbl...
> I'm still kind of confused.
> It sounds like there are 3 servers, server 1, server 2, and server 3.
> Currently a DTS package reads table 1 and table 2 on server 1, and sends
> data to server 2 to a table called table 3. Then server 2 publishes table
3
> to server 3 to a table called table 4.
> All of these tables are different.
> Does this describe your situation.
> I'd replicate from table 1 & 2 to server 2 table 3, and have a second
> publication on server 1 replicating to table 4 on server 3. Replicating
two
> tables to 1 is a little more complex as you need to figure out which table
> will generate a complete row.
> It if table 1 contributes column a, b, c, and table 2 contributes column
d,
> you need the log reader to wirte a command to the distribution database
when
> there will be a complete set of columns for the row, and there has to be
> some relationship between table 1 and table 2. Normally there is an
> intersection table or join table and key off this table as a row in the
join
> table means that there is a row in table 1 and table 2. Then you also have
> to replicate table 1 and table 2 as well, so that you have all the columns
> for table 3 on the subscriber. The store procedure has to read tables 1
and
> tables 2 to build table 3.
> I normally use custom sync objects for this sort of stuff. Indexed views
are
> an option, but there is a space and performance penalty to pay (slight)
for[vbcol=seagreen]
> using them, and they aren't resilient to schema changes.
> As you have seen custom sync objects aren't problem free either, but they
> seem to be an option in your case.
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:%23AsburDmEHA.2024@.TK2MSFTNGP10.phx.gbl...
have[vbcol=seagreen]
can[vbcol=seagreen]
> be
replicate[vbcol=seagreen]
> to
time[vbcol=seagreen]
> schemas
give
>
|||yes, you will need table 1 and table 2 to be replicated to the subscriber as
well.
Consider this, the log reader will extract all the columns in a table which
is being replicated and construct an insert/update/or delete statement only
consisting of columns from that table.
So if you replicate table 2 the commands written to the distribution agent
will be the pk and c3. Where will the distribution agent get the value for
c2 from?
It will have to join on table 1 in the subscriber to get this value. If its
not there it will never be able to plug it in.
You need to have the log reader key off table 2 when a complete record for
t3 exists which means that c1, c2 & c3 must be either in the proc or some of
these columns must be already on this table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:uD67QgJmEHA.3156@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> I just re-read this once more:
> Then you also have
columns[vbcol=seagreen]
> If Im reading this correctly, it's one of the things I was afraid of. Are
> you saying I need to have table1 and table2 on the Subscriber so I can
> create table3? Im pretty sure you are, but wanted to make sure?
> As always, thanks so much for all you're help.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u7FMWzEmEHA.592@.TK2MSFTNGP11.phx.gbl...
table[vbcol=seagreen]
> 3
> two
table[vbcol=seagreen]
> d,
> when
> join
have[vbcol=seagreen]
columns[vbcol=seagreen]
> and
> are
> for
they[vbcol=seagreen]
> have
> can
> replicate
also[vbcol=seagreen]
> time
or
> give
>
|||This is as I expected. Since this is a denormalized reporting server, I
think I will revert back to Indexed Views. To have both Table1 and Table2 on
the Subscriber so I can make up Table3 just doesnt make sense to me. It
almost seems to me as though its adding another layer of complexity. Can you
tell me if there are any schema change problems to watch out for with
Indexed Views besides the fact that I cant use sp_repladdcolumn? Any other
type of big gotchas that you can think of would be appreciated as well.
Also, whens the book coming out?
Thank you once more,
ChrisR
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ekB3ztOmEHA.1376@.TK2MSFTNGP12.phx.gbl...
> yes, you will need table 1 and table 2 to be replicated to the subscriber
as
> well.
> Consider this, the log reader will extract all the columns in a table
which
> is being replicated and construct an insert/update/or delete statement
only
> consisting of columns from that table.
> So if you replicate table 2 the commands written to the distribution agent
> will be the pk and c3. Where will the distribution agent get the value for
> c2 from?
> It will have to join on table 1 in the subscriber to get this value. If
its
> not there it will never be able to plug it in.
> You need to have the log reader key off table 2 when a complete record for
> t3 exists which means that c1, c2 & c3 must be either in the proc or some
of[vbcol=seagreen]
> these columns must be already on this table.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:uD67QgJmEHA.3156@.TK2MSFTNGP12.phx.gbl...
> columns
Are[vbcol=seagreen]
sends[vbcol=seagreen]
> table
Replicating[vbcol=seagreen]
> table
column[vbcol=seagreen]
database[vbcol=seagreen]
be[vbcol=seagreen]
the[vbcol=seagreen]
> have
> columns
1[vbcol=seagreen]
views[vbcol=seagreen]
(slight)[vbcol=seagreen]
> they
seeing,[vbcol=seagreen]
it[vbcol=seagreen]
> also
the[vbcol=seagreen]
that[vbcol=seagreen]
> or
and
>