Altering Table Replicated

How can i change my Table Structure that is replicated?

I need to add a new field.

In SQL Server 2005 you can use alter table syntax to add/remove/change columns in a replicated table. Check out the following link for more information -- http://msdn2.microsoft.com/en-us/library/ms151870.aspx

If you are using SQL 2000, you are limited to the functionality of sp_repladdcolumn and sp_repldropcolumn. SQL Server 2000 Books Online will give you more information on the syntax of these procs.

Hope this helps,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

Altering constraint in a column in a publication

Hi All,
I have a replicated (published) database with 1 push subscription to it on
another server. Replication type is Transactional with "pushing" occuring
every 2 hours.
1) In BOL it is mentioned, for making a change (besides adding or dropping a
column) in a published table -
a) delete all subscriptions to the publication
b) remove the article/object from the publication
c) change the table as required
d) add the article back to the publication
e) create the subscription again
My questions -
- Do I always need to go through the entire subscription creation process
each time I need to alter a table in my publication? I mean for a table
change that will take 2 minutes, I need to spend 3 hours (thats the time the
snapshot creation takes for me) creating the subscription?
- In step a), does it mean removing the subsribing database ? What about the
subcribing server ?
2) In BOL, its mentioned that to delete subscriptions through Enterprise
Manager, I need to go to SQL Server Group -> <Registration Name> ->
Replication -> Publications -> <Publication name>. In the right pane, I'll
see the subscription. I need to right click on it and say delete.
My questions -
- If I go to Tools -> Replication -> Configure Publishing, Subscribers and
Distributors, go to the Subscribers tab and remove the tick on the
subscriber, is it the same thing as deleting the subcription?
- Secondly, instead of going through the process of creating a new
subscription after altering my table, if I just go back to the above
mentioned location and tick the required subscriber, is it the same thing as
creating a subscription?
- If answers to both above questions is Yes, can I replace steps a) and e)
in question 1) with above two steps (unticking/ticking)?
Unrelated queries -
- If I disable publishing through option "Disable Publishing" in right click
of SQL Server Group -> <Registration Name> -> Replication, is there any
option to "enable" the publishing or do I need to go through the publication
creation process?
- How do I monitor the performance of replication?
Salil.
Salil,
some changes can be made using sp_addscriptexec; it depends on what you are
trying. If this isn't permitted, you can do a no-sync initialization if you
want to avoid the cost of the snapshot. If you go down this path, be sure
that the data is synchronized first.
Dropping the subscription doesn't require you to drop the subscriber's
database, and is distinct from enabling a subscriber.
Disabling publishing has to be followed by enabling publishing if you want
to use replication again.
You can monitor the performance using the replication-specific counters in
Performance Monitor. Also you can query the replication history tables for
short-term monitoring, and msdistribution_status in transactional
replication.
HTH,
Paul Ibison
|||Thank you Paul.
Point 1 (about no-sync initialization) and 4 (replication monitoring)
definitely helps.
But my query about "ticking/unticking" still remains unanswered.
i.e. If I do -
1) Untick subscribing server
2) remove article from publisher
3) alter article
4) add article back to publisher
5) tick subscribing server
Is 1 and 5 the same as "deleting" and "adding" subscirbers respectively?
Salil.
"Paul Ibison" wrote:

> Salil,
> some changes can be made using sp_addscriptexec; it depends on what you are
> trying. If this isn't permitted, you can do a no-sync initialization if you
> want to avoid the cost of the snapshot. If you go down this path, be sure
> that the data is synchronized first.
> Dropping the subscription doesn't require you to drop the subscriber's
> database, and is distinct from enabling a subscriber.
> Disabling publishing has to be followed by enabling publishing if you want
> to use replication again.
> You can monitor the performance using the replication-specific counters in
> Performance Monitor. Also you can query the replication history tables for
> short-term monitoring, and msdistribution_status in transactional
> replication.
> HTH,
> Paul Ibison
>
>
|||Salil,
unticking the subscriber in the distributor properties box will unsubscribe
all subscriptions to this server, not just the one you are concerned about.
Afterwards you'll need to recheck this box and subsequently resubscribe to
the publication.
HTH,
Paul Ibison
|||Thank you Paul.
"Paul Ibison" wrote:

> Salil,
> unticking the subscriber in the distributor properties box will unsubscribe
> all subscriptions to this server, not just the one you are concerned about.
> Afterwards you'll need to recheck this box and subsequently resubscribe to
> the publication.
> HTH,
> Paul Ibison
>
>

Altering a column on a replicated table

Tom,
nice that someone read it
If you're running these commands as a script, you'll need
a GO after each command, otherwise you can just run them
individually one-by-one.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
OK, that worked and I saw the queue reader agent process the commands and
the distribution agent's last action taken now reads "The initial snapshot
for article 'RequistionDetail' is not yet available'.
Now what do I need to do to generate a new snapshot of this one article in
order to get these changes to propogate over to the my subscriber?
Thanks-Tom
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:0f4b01c51511$e4181ce0$a401280a@.phx.gbl...
> Tom,
> nice that someone read it
> If you're running these commands as a script, you'll need
> a GO after each command, otherwise you can just run them
> individually one-by-one.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Tom,
why are you using , @.force_reinit_subscription = 1?
Ordinarily this is left out and there is no invalidation
of the snapshot - the changes are propagated as a result
of the sp_repl... commands using hte existing replication
framework. If you want to snapshot the table then have a
look at the other method in the article where you drop
the subscription to the article then drop the article.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Altering a column on a NoSync Replicated Table

Hi,
I saw an article on www.replicationanswers.com, about Altering a column on a
Replicated Table (from Paul Ibison):
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
I DID THE SAME FOR NO-SYNC INITITIALIZATION, BUT THE STRUCTURE OF THE FIELD
IN SUBCRIBER IS THE SAME, THIS CHANGE IS NOT BEING PUBLISHED IN THE
SUBSCRIBER.
CAN SOMEBODY HELP ME TO SOLVE THIS ISSUE FOR NO-SYNC INITIALIZATION, HOW THE
SCRIPT SHOULD LOOK LIKE OR WHAT DO IA HEVE TO DO?
Thanks,
BaniSQL.
Isn't @.sync_type = automatic the default one, I mean if you don't specify it
will take the automatic one. I tried like that, but than some foreign keys
referencing that table were firing.
I'm trying to alter a column, is not another way of doing it beside this one
droping the whole article and replicating again. This soltion is fine for me
as logn is it will work, but the problem is that the foeign keys are firing
and some related records to other related tables can't find.
Please help.
Thnx,
BaniSQL
"Paul Ibison" wrote:

> It all depends on whether you want to do an automatic one or another nosync
> one. The easiest way is to drop the article and subscription then readd it
> as @.sync_type = automatic.
> Cheers,
> Paul Ibison
>
>

ALTER TABLE SWITCH PARTITION on a replicated table

A customer wants to implement table partitioning on a replicated table.

They want to hold 13 months of data in the table and roll off the earliest/oldest month to an identical archive table. The table has a date field and partitioning by month makes sense all around.

So SWITCH PARTITION is the obvious solution to this, except for the fact that the table is replicated (transactional w/no subscriber updates).

What are his architectural or practical solutions to using table partitioning and replication?

thx

I'm sorry but i did not understand your question exactly.

Did you mean something like : "What are the benefits of using Table Partitioning with Replication?" ?

Ekrem ?nsoy

MCP, MCDST, MCDBA, MCAD.Net, MCSD.Net, MCSA, MCSE

|||

BOL says that SWITCH PARTITION cannot be used on a replicated table.

So if my customer wants to implement a partition function/scheme and switch a partition out on a monthly basis, it appears that this cannot be done.

What other options would they have to roll data on and off their table on a monthly basis (other than SELECT INTO the archive table followed by a DELETE on the original table)?

thx

|||That is the only option. SWITCH is not supported on replicated tables. The reason for this is very straightforward. A SWITCH operation is a metadata only operation. These do not get picked up by replication and so can not be applied to the subscriber. Since it can not be applied to a subscriber, allowing this operation would create a subscriber which is out of synch with the publisher with no way of putting it back in synch. So, if it is replicated, you have to use either an insert or a select into in order to move the data to the other table and then come back and delete it off the source table thereby allowing it to flow through replication.

Alter Table fails because column is being replicated

This is a follow up to my post 7/30/2007
<please review>
I have been unsuccessful in removing this column
do I have to rebuild from scripts then migrate data?
Thanks
Mark J. Soule
Can you confirm that you have removed the constraint, run
sp_removedbreplication (assuming the database isn't used as a publisher or
subscriber) and run sp_MSunmarkreplinfo and still you can't change the table?
Doing all of these should be enough.
Cheers,
Paul Ibison
|||Hi Paul;
Yes I removed all identity range constraints and ran MSunmarkreplinfo.
I gave up and re-created the entire database from scripts then imported
data to the new database.
It was a pain, but it is complete.
thanks
MJ
"Paul Ibison" wrote:

> Can you confirm that you have removed the constraint, run
> sp_removedbreplication (assuming the database isn't used as a publisher or
> subscriber) and run sp_MSunmarkreplinfo and still you can't change the table?
> Doing all of these should be enough.
> Cheers,
> Paul Ibison

Alter table

I know I can't do an alter table on a replicated table because it is
being replicated. But, in my software, if I need to modify a table I do
an alter table and add the new column.
What is the easy way, via SQL script, to see if this machine is a
distributor/publisher for replication, in which case I need to do the
sp_repladdcolumn, or a subscriber, in which case I need to do nothing
because the dist/pub will do it, or neither, in which case I need to do
the alter table?
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Darin,
I'd probably use something like this:
declare @.mytablename varchar(100)
set @.mytablename = 'testtr'
if exists(SELECT name FROM sysarticles where name =
@.mytablename)
or exists(SELECT name FROM sysarticles where name =
@.mytablename)
select 'exists'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Did you mean for both of the select statements to be the same?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Sorry - second one is for merge...
Actually it lacked a bit more code which I've added. There are 2 versions
and the second one would be more elegant (you'll need to test it).
Cheers,
Paul Ibison
declare @.mytablename varchar(100)
set @.mytablename = 'testtr'
if (select object_id('sysarticles')) is not null
begin
if exists (SELECT name FROM sysarticles where name =
@.mytablename)
select 'yes'
end
if (select object_id('sysmergearticles')) is not null
begin
if exists(SELECT name FROM sysmergearticles where name =
@.mytablename)
select 'yes'
end
if (select replinfo from sysobjects where name = @.mytablename) > 0
select 'yes'

alter column of a replicated table?

How do I alter the column of the replicated table ?
If you have SQL Server 2005 then alter table will work for most changes. If
it's SQL 2000 then there are some workarounds. Please take a look at these 2
articles:
http://www.replicationanswers.com/AlterSchema2005.asp
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||For SQL 2000 you have to pipe the values of the column you wish to change to
a temp table along with key information. Then use sp_repldropcolumn to drop
the column and sp_repladdcolumn to add it back with the new width/datatype.
Then push the content back into the base table from the temp table.
For SQL 2005 with replicate_ddl = true by default you can use alter table
statements.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:D3070003-28E4-4568-93FC-7A2E4DF94B39@.microsoft.com...
> How do I alter the column of the replicated table ?
>

Almost Replicated - I think

I have a new server, new instance of SQL on the network
with an old server,old SQL db. I attempted to set up
replication by creating a snapshot and making new server
(Win 2k3) a subscriber to publisher/distributor. I get
the following error:
Invalid column name ', '.
(Source: NewServer (Data source); Error number: 207)
Confused (1) because NewServer had nothing on it (only
standard SQL install db and (2) don't know where to go
next.
Any help, ideas?
TIA
Rob
Rob,
if you have been replicating a view then I have seen this before. This
problem occurs because the Snapshot Agent always sets the QUOTED_IDENTIFIER
option to ON, regardless of the actual setting. Therefore, if the stored
procedures or views use double quotation marks, the Distribution Agent or
the Merge Agent assumes the default behavior of using double quotation marks
for identifiers only. To get round this, you can change the object script to
refer to literals using single quotes, or use DTS to transfer the objects.
If this is not the issue, I came across this error in merge replication that
might be of use:
http://support.microsoft.com/default...b;en-us;821535
HTH,
Paul Ibison