Many pubs or single pub with dynamic filters ??

Hello...
Some background: We have merge replication set up between SQL 2000 and
mobile devices running SQL Mobile. Each of our customers has a unique
customer id and currently we have a separate publication for each one...that
has a static filter such as "WHERE CustomerID = 12345". And on each device,
it subscribes to its own publication...based on the Customer ID.
What I am thinking about...in the near future, as our customer base grows,
is that we'll be faced with maintaining lots of Publications on the server -
one for each Customer ID.
So, would it be better to have a single publication that has dynamic
filters...based on the HostName property...which I'd set in code on the
device (based on the Customer ID) or keep things the way we have them now?
I am mostly concerned about replication/synchronization performance between
the server and the mobile devices...some of which use GPRS for connectivity.
I don't want to sacrifce any performance gains, if there are any, by having
lots of publications.
thanks for any thoughts.
- will
Here are the issues that you need to consider when moving to a single
publisher model:
1. Concurrency: syncing multiple subscribers at the same time is not
optimized in SQL2000, which will end up serializing the merge process. In
SQL2005, performance is greatly improved by allowing multiple subscribers to
sync at the same time.
2. Partitioning: When a subscriber with a given ID syncs, SQL2000 will scan
the publisher's articles for changes pertaining to that subscriber. This
process has its performance implications for large publication with large
number of changes. In SQL2005, this issue was also addressed by the use of
partition groups.
Regards,
Rafik
This posting is provided "AS IS" with no warranties, and confers no rights.
"dw" <dw@.discussions.microsoft.com> wrote in message
news:7730CDFB-81A8-437B-80A5-C5B0131814B9@.microsoft.com...
> Hello...
> Some background: We have merge replication set up between SQL 2000 and
> mobile devices running SQL Mobile. Each of our customers has a unique
> customer id and currently we have a separate publication for each
> one...that
> has a static filter such as "WHERE CustomerID = 12345". And on each
> device,
> it subscribes to its own publication...based on the Customer ID.
> What I am thinking about...in the near future, as our customer base
> grows,
> is that we'll be faced with maintaining lots of Publications on the
> server -
> one for each Customer ID.
> So, would it be better to have a single publication that has dynamic
> filters...based on the HostName property...which I'd set in code on the
> device (based on the Customer ID) or keep things the way we have them now?
> I am mostly concerned about replication/synchronization performance
> between
> the server and the mobile devices...some of which use GPRS for
> connectivity.
> I don't want to sacrifce any performance gains, if there are any, by
> having
> lots of publications.
> thanks for any thoughts.
> - will
|||Hi...
Thanks for the quotes from the BOL. However, we cannot move to SQL 2005 for
a while...so these won't help me now and for the near future.
Any other thoughts regarding my question...which is simply, "Is it better to
have one publication that multiple subscribers sync with...using their
Customer ID in the filtering of the data, or is it better to have multiple
publications that are unique to each subscriber?" And, when I ask which is
"better", I mean from a replication/synchronization performance aspect...from
the subscriber's end of the things.
thanks
- will
"Rafik Robeal" wrote:

> Here are the issues that you need to consider when moving to a single
> publisher model:
>
> 1. Concurrency: syncing multiple subscribers at the same time is not
> optimized in SQL2000, which will end up serializing the merge process. In
> SQL2005, performance is greatly improved by allowing multiple subscribers to
> sync at the same time.
>
> 2. Partitioning: When a subscriber with a given ID syncs, SQL2000 will scan
> the publisher's articles for changes pertaining to that subscriber. This
> process has its performance implications for large publication with large
> number of changes. In SQL2005, this issue was also addressed by the use of
> partition groups.
>
> Regards,
> Rafik
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:7730CDFB-81A8-437B-80A5-C5B0131814B9@.microsoft.com...
>
>
|||I have found that static filters perform better than dynamic ones. Obviously
however, there is more management (DBA work) to do.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the reply and info. That is what I was thinking...that the
performance would be better forindividual publications based on a static
filter...but figured I should toss this question out to the group, in case I
was missing something.
thanks
- will
"Paul Ibison" wrote:

> I have found that static filters perform better than dynamic ones. Obviously
> however, there is more management (DBA work) to do.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Manipulating Text,nText data types filed in tsql

I have to run a dynamic sql that i save in the database as a TEXT data type(due to a large size of the sql.) from a .NET app. Now i have to run this sql from the stored proc that returns the results back to .net app. I am running this dynamic sql with sp_executesql like this..

EXEC sp_executesql@.Statement,N'@.param1 varchar(3),@.param2 varchar(1)',@.param1,@.param2,
GO

As i can't declare text,ntext etc variables in T-Sql(stored proc), so i am using this method in pulling the text type field "Statement".

DECLARE@.Statement varbinary(16)
SELECT@.Statement = TEXTPTR(Statement)
FROM table1
READTEXT table1.statement@.Statement 0 16566

So far so good, the issue is how to convert @.Statment varbinary to nText to get it passed in sp_executesql.

Note:- i can't use Exec to run the dynamic sql becuase i need to pass the params from the .net app and Exec proc doesn't take param from the stored proc from where it is called.

I would appreciate if any body respond to this.

Yes, the limitation of using NTEXT as variable causes the problem. I know a workaround, but... you still need EXEC to warp the execution of sp_executesql. Read this:

sp_executesql and Long SQL Strings in SQL 2000
There is a limitation with sp_executesql on SQL 2000 and SQL 7, since you cannot use longer SQL strings than 4000 characters. (On SQL 2005, use nvarchar(MAX) to avoid this problem.) If you want to use sp_executesql despite you query string is longer, because you want to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():

DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2)
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
EXEC('EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2)'',
@.state = ''' + @.state + '''')
This works, because the @.stmt parameter to sp_executesql is ntext, so by itself, it does not have any limitation in size.

You can even use output parameters by using INSERT-EXEC, as in this example:

CREATE TABLE #result (cnt int NOT NULL)
DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2),
@.mycnt int
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT @.cnt = COUNT(*)'
SELECT @.sql2 = N'FROM dbo.authors WHERE state = @.state'
INSERT #result (cnt)
EXEC('DECLARE @.cnt int
EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2),
@.cnt int OUTPUT'',
@.state = ''' + @.state + ''',
@.cnt = @.cnt OUTPUT
SELECT @.cnt')
SELECT @.mycnt = cnt FROM #result
You have my understanding if you think this is too messy to be worth it.

So you can break the NTEXT statement from your table into several NVARCHAR(4000) strings, then pass the strings as parameter for sp_executesql. The original wonderful artilc can be found here:

http://www.sommarskog.se/dynamic_sql.html

|||Thanks, I had to break the query into pieces to get this working.