Replication Best Practice Question: Split Publications

I am working on a replication design and getting closer to implementation. One of my major concerns is maintaining and updating stored procs and/or user defined functions.

The current design is a single publication, including tables, procs and functions (no views yet). All told there are about 686 articles in the publication. The tables are horizontally partitioned using dynamic filters based on the hostname of the subscriber. There are around 50 subscribers. Most will have small databases (< 250 MB). A few (2-3) will have much larger databases. I am mostly concerned that whenever I have to update a proc or function, I have to re-initialize the subscriptions and that pushes a TON of data out over the network and may interrupt service at the subscriber locations.

I see three options for the procs and functions:
1. Include them in the publication with the data tables
2. Place them in a separate (snapshot only) publication
3. Exclude them entirely from replication and maintain them manually

I am starting to lean towards option #2; but I am a bit concerned about maintaining a duplicate set of replication agents for each subscriber.

Any thoughts and/or comments?

Regards,

hmscott

Am I overlooking something? Is there an option that I have not considered?I maintain them manually. All replication does is replace procs, and I can do that without involving replication.|||I maintain them manually. All replication does is replace procs, and I can do that without involving replication.

How many subscribers?|||Ok - I don't really feel qualified on this subject and don't have that much related experience (I use replication to get disparate data sources into one single, read only point rather than the other way round) however you aren't getting much joy so I might as well wade in as not.

I have read a couple of articles that advocate an approach very like point 2. One, as I recall, suggested one publication per article type which does seem a bit over the top. Anway - although this would require administration of more agents it sounds preferable to administering everything manually.

Can I find my sources? Can I buggery - you will just have to take my word for it that I have read at least two articles that suggest this very set up. I will continue hunting through my favourites.|||Pootle -

Thanks for your comments. I'm going to experiment a bit with this approach in the next fiew days.

Is it me, or does it seem that there's a dearth of information on the net regarding SQL replication? I have found a few sites (http://www.replicationanswers.com/Default.asp, http://www.dyessconsulting.com, and a few articles in http://www.sqlservercentral.com), but it just seems that there's not a whole lot of "hard" information (ie, mostly it's a lot of generic stuff with few practical examples).

Thanks again, I'll try to remember to post back with an update as things progress.

Regards,

hmscott|||That is my experience too. I believe Hilary Cotter is extremely knowledgeable and also active on some forum(s) if you really get no joy here.

replication autogenerated procs - interesting ;2 notation

I was looking to modify how the INSERT happens with regards to replication only to find my solution in the proc itself. When I edit the proc this is what I am displayed in SQL QA or EM:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER procedure "sp_MSins_dboMEETING" @.c1 int,@.c2 int,@.c3 int,@.c4 varchar(250),@.c5 datetime,@.c6 datetime,@.c7 bit,@.c8 char(1),@.c9 datetime,@.c10 bit,@.c11 bit,@.c12 bit,@.c13 datetime,@.c14 smallint,@.c15 datetime,@.c16 smallint,@.c17 binary(8),@.c18 bit,@.c19 bit,@.c20 bit,@.c21 varchar(1000)

AS
BEGIN

insert into "dbo"."MEETING"(
"MEETING_ID", "MEETING_TYPE_ID", "MEETING_STATUS_ID", "TITLE", "START_DATE", "END_DATE", "PUBLISH_IND", "GROUP_IND", "PUBLISH_DATE", "MY_ADVISORS", "SUBMITTED_IND", "ACTIVE_IND", "CREATE_DATE", "CREATED_BY", "LAST_UPDATE_DATE", "LAST_UPDATED_BY", "DATE_INDEXED", "ON_DEMAND_IND", "NOT_REPORTED_IND", "MAJOR_PROJECT_IND", "MAJOR_PROJECT_COMMENT"
)

values (
@.c1, @.c2, @.c3, @.c4, @.c5, @.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, @.c13, @.c14, @.c15, @.c16, @.c17, @.c18, @.c19, @.c20, @.c21
)

END

GO

create procedure "sp_MSins_dboMEETING";2 @.c1 int,@.c2 int,@.c3 int,@.c4 varchar(250),@.c5 datetime,@.c6 datetime,@.c7 bit,@.c8 char(1),@.c9 datetime,@.c10 bit,@.c11 bit,@.c12 bit,@.c13 datetime,@.c14 smallint,@.c15 datetime,@.c16 smallint,@.c17 binary(8),@.c18 bit,@.c19 bit,@.c20 bit,@.c21 varchar(1000)
as
if exists ( select * from "dbo"."MEETING"
where "MEETING_ID" = @.c1
)
begin
update "dbo"."MEETING" set "MEETING_TYPE_ID" = @.c2,"MEETING_STATUS_ID" = @.c3,"TITLE" = @.c4,"START_DATE" = @.c5,"END_DATE" = @.c6,"PUBLISH_IND" = @.c7,"GROUP_IND" = @.c8,"PUBLISH_DATE" = @.c9,"MY_ADVISORS" = @.c10,"SUBMITTED_IND" = @.c11,"ACTIVE_IND" = @.c12,"CREATE_DATE" = @.c13,"CREATED_BY" = @.c14,"LAST_UPDATE_DATE" = @.c15,"LAST_UPDATED_BY" = @.c16,"DATE_INDEXED" = @.c17,"ON_DEMAND_IND" = @.c18,"NOT_REPORTED_IND" = @.c19,"MAJOR_PROJECT_IND" = @.c20,"MAJOR_PROJECT_COMMENT" = @.c21
where "MEETING_ID" = @.c1
end
else
begin
insert into "dbo"."MEETING" ( "MEETING_ID","MEETING_TYPE_ID","MEETING_STATUS_ID","TITLE","START_DATE","END_DATE","PUBLISH_IND","GROUP_IND","PUBLISH_DATE","MY_ADVISORS","SUBMITTED_IND","ACTIVE_IND","CREATE_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","DATE_INDEXED","ON_DEMAND_IND","NOT_REPORTED_IND","MAJOR_PROJECT_IND","MAJOR_PROJECT_COMMENT" ) values ( @.c1,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8,@.c9,@.c10,@.c11,@.c12 ,@.c13,@.c14,@.c15,@.c16,@.c17,@.c18,@.c19,@.c20,@.c21 )
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Is the second version listed at the bottom like a comment or can it actually get called? I am going to script out all of these procs and save them, and then remove the first version (listed at the top) and the use the second version since it does what I need. I just thought it was interesting to see two stored procedures in a single definition, never seen the "PROC_NAME";2 notation, have you? If so please tell me what it does, is it just a way to create a second version of the procedure in a comment type fashion or is it used another way?From Books online's Create Procedure reference;number
Is an optional integer used to group procedures of the same name so they can be
dropped together with a single DROP PROCEDURE statement. For example, the
procedures used with an application called orders may be named orderproc;1,
orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
entire group. If the name contains delimited identifiers, the number should
not be included as part of the identifier; use the appropriate delimiter around
procedure_name only.
I'll admit that I have neither used this functionality, nor ever seen it used. It may be that the application specifies which version of the procedure is executed by including the number in the call. And in that case, I wouldn't go dropping or commenting out the code, since it could break your app.