Make Changes to Published Database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vincent Cotton - 24 Dec 2006 06:01 GMT

I currently have a VB6 app that I am rewriting. I am now using Visual
Studio 2005 and the new app will be a windows forms smart client. I am
using SQL Server 2005 Compact Edition as the local database. I have SQL
Server 2005 Enterprise Edition setup as a merge replication publisher. I
will have a little over 500 subscribers. The system is not yet implemented;
I am still testing at this point. The main problem I am having is when I
want to make changes to the main database (publisher). SSMS always tells me
I can't make changes because it is setup for replication. At this point
it's not a big deal. I just remove the publication, remove the
subscriptions, make changes to my tables and then re-establish the
publication. However, pretty soon when my app goes into production I don't
think this will work to well. I thought from all my research that I would
be able to make changes to my tables such as adding new fields, changing
field names, deleting fields, adding tables and so on, and that those
changes would just propagate down to the subscribers. I'm sure I'm just
missing something. So, my question is how can I make changes to the
publisher without having to remove the publication/subscriptions and start
all over?

Thanks,

Vince

Reply to this Message

Hilary Cotter - 26 Dec 2006 15:47 GMT

with the replicate_ddl setting of sp_addmergepublication set to true (1)
almost all DDL (table changes) should be replicated. Can you verify that
this is the case?

Signature

Hilary Cotter

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

>I currently have a VB6 app that I am rewriting. I am now using Visual
> Studio 2005 and the new app will be a windows forms smart client. I am
[quoted text clipped - 21 lines]
>
> Vince

Reply to this Message

Vincent Cotton - 28 Dec 2006 04:17 GMT

Just to let you know I am very new to SQL Server. I am a "Micro" ISV
programmer not a dba. So, thanks in advance for your patience.

I found the replicate_ddl column in the dbo.sysmergepublications table, but
I can't open to see it's value. It is an (int,not null) and I can see its
default value is ((0)); so, I assume it's value is 0. I am using the SSMS
gui so I don't know how to proceed from here.

Thanks,

Vince

> with the replicate_ddl setting of sp_addmergepublication set to true (1)
> almost all DDL (table changes) should be replicated. Can you verify that
[quoted text clipped - 27 lines]
>>
>> Vince

Reply to this Message

Paul Ibison - 27 Dec 2006 22:33 GMT

Vince,
are you making changes through the GUI (SSMS)? It sounds like you are, but
you'll need to use ALTER TABLE instead (see
http://www.replicationanswers.com/AlterSchema2005.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message

Vincent Cotton - 28 Dec 2006 04:41 GMT

Yes, I am using the SSMS gui. It is really the only thing I feel safe
working with.

I read your post and that is the exact error I get when trying to make
changes to my published tables. I may have bitten off more than I can chew
with this. I guess my real question/problem/concern is this:

Let's say I have 500+ subscribers and they all have between 25 and 50 MB of
data in their local store (SQLce). At some point I will want to make
changes to my program which require new fields, field name changes, new
tables, etc. From what I am encountering I have to go into the SSMS gui,
delete the publication and all subscriptions and then make changes to my
tables. At this point all the users still have their data in their local
SQLce file; this is what I want so far. However, when I recreate my
publication on the central server and the subscribers go to synchronize what
will happen? I assume it will not find the subscription on the server. So,
will the user be required to delete the file and start over with a new
initialization or can I just add that subscription back to the central
server and just propogate only the changes to the subscribers? What about
changes that the subscriber makes to their local SQLce file during the time
that I removed the publication and then recreated it?

I understand I can do this stuff using scripts and stored procedures, etc.,
but I have zero experience with that and I am kind of afraid to mess with
anything other than the SSMS gui when I have this in production.

I am only a programmer not a dba. I understand
indexing/relationships/tables/field types and so on, but TSQL is foreign to
me. Thanks in advance for your patience.

Vince

> Vince,
> are you making changes through the GUI (SSMS)? It sounds like you are, but
> you'll need to use ALTER TABLE instead (see
> http://www.replicationanswers.com/AlterSchema2005.asp).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message

Paul Ibison - 28 Dec 2006 09:23 GMT

Vincent,
you really don't want to reinitialize for all schema changes. If you are
confortable with the SSMS gui, then by all means use that to add articles.
To chnage an existing article, just study ALTER TABLE in BOL - as you're a
programmer, this will be easy to pick up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message