Hilary Cotter - How do I buy your book on merge replication?

How do I buy your book on merge replication?
The site doesn't really explain. Thanks for the pointer in previous posts.
Well its not done yet. When it is printed it should be available from
nwsu.com and amazon.com.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:%23JA272UgEHA.644@.tk2msftngp13.phx.gbl...
> How do I buy your book on merge replication?
> The site doesn't really explain. Thanks for the pointer in previous posts.
>
|||and when transactional?
it shoud be released now.
On Fri, 13 Aug 2004 12:15:29 -0400, "Hilary Cotter" <hilaryk@.att.net>
wrote:

>Well its not done yet. When it is printed it should be available from
>nwsu.com and amazon.com.
|||The transactional book is being typeset, and has been since 7/22. We are
hoping it will go to press next week. After this it will be a 4-5 week print
process.
I am sorry about the delays, some of them have been due to final editing
processes, which will make the book an even higher quality product.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Thomas Hase" <tohas@.freenet.de> wrote in message
news:411e3897.55027546@.news.t-online.de...
> and when transactional?
> it shoud be released now.
>
> On Fri, 13 Aug 2004 12:15:29 -0400, "Hilary Cotter" <hilaryk@.att.net>
> wrote:
>
|||Any idea whem it will be released. I have a project in about 6-8 weeks which
it sounds really good for.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ehtuVEVgEHA.636@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Well its not done yet. When it is printed it should be available from
> nwsu.com and amazon.com.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:%23JA272UgEHA.644@.tk2msftngp13.phx.gbl...
posts.
>

Hilary and Paul : SQL 2005 Book and Materials

I considered the idea, but have decided to produce a few
articles on the main changes that I'm interested in
instead - merge over the internet and peer-to-peer
transactional. Hilary's the man for books, and he'll be
able to update you about his plans.
BTW to be honest as MVPs we don't have much of an
advantage over anyone else in this regard - we still have
to do the research, trial and error, digging through
system stored procedures etc, although some changes we
hear about a little earlier. I know Kalen was able to
interview PMs for the various groups but that was
exceptional and we don't really have such access. If this
is something you're interested in - ie close contact with
the developers - then now is the time we can all do it.
Have a good look at the Beta2 and try to figure the
replication features out, then post your questions in the
Beta2 newsgroups
(http://communities.microsoft.com/new...s/default.asp?
icp=sqlserver2005&slcid=us). I have used these and the
Yukon ones, and the questions are answered by the actual
TSQL code developers - the people we don't usually see on
the public newsgroups, so this is pretty valuable stuff.
Regards,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul is right. Please post your comments about SQL server 2005 betas. We
will be delighted to have your feedback.
thanks - Deepak
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1f5e01c4f7c1$8cafb250$a401280a@.phx.gbl...
> I considered the idea, but have decided to produce a few
> articles on the main changes that I'm interested in
> instead - merge over the internet and peer-to-peer
> transactional. Hilary's the man for books, and he'll be
> able to update you about his plans.
> BTW to be honest as MVPs we don't have much of an
> advantage over anyone else in this regard - we still have
> to do the research, trial and error, digging through
> system stored procedures etc, although some changes we
> hear about a little earlier. I know Kalen was able to
> interview PMs for the various groups but that was
> exceptional and we don't really have such access. If this
> is something you're interested in - ie close contact with
> the developers - then now is the time we can all do it.
> Have a good look at the Beta2 and try to figure the
> replication features out, then post your questions in the
> Beta2 newsgroups
> (http://communities.microsoft.com/new...s/default.asp?
> icp=sqlserver2005&slcid=us). I have used these and the
> Yukon ones, and the questions are answered by the actual
> TSQL code developers - the people we don't usually see on
> the public newsgroups, so this is pretty valuable stuff.
> Regards,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

higher compatibility level already exists

I am attempting to create a merge publication from SQL 2005 to SQL
2000. I am checking the 2005 & 2000 boxes for compatability, but
receive this message.
Publication 'edi' cannot be added to database 'EDI', because a
publication with a higher compatibility level already exists. All
merge publications in a database must have the same compatibiliy
level.
Publication 'edi' does not exist.
Changed database context to 'EDI'. (Microsoft SQL Server, Error:
21528)
I am creating the publications on the colo server (2005). The
publications will be filtered (one for each location). The main
office & all locations will be subscribers. All stores are running
SQL 2000 & the colo & main office SQL 2005.
AHIA,
Larry...
Hi Larry
I think what has happened here is that you have already created a
publication with a higher compatibility level in this database (i.e. SQL
2005). You can no longer create publications for a lower compatibility level
now.
IIRC this depends on specific settings, and it could be a single article, or
setting - which is preventing down-level compatibility publications.
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
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1185461541.304316.20350@.l70g2000hse.googlegro ups.com...
>I am attempting to create a merge publication from SQL 2005 to SQL
> 2000. I am checking the 2005 & 2000 boxes for compatability, but
> receive this message.
> Publication 'edi' cannot be added to database 'EDI', because a
> publication with a higher compatibility level already exists. All
> merge publications in a database must have the same compatibiliy
> level.
> Publication 'edi' does not exist.
> Changed database context to 'EDI'. (Microsoft SQL Server, Error:
> 21528)
> I am creating the publications on the colo server (2005). The
> publications will be filtered (one for each location). The main
> office & all locations will be subscribers. All stores are running
> SQL 2000 & the colo & main office SQL 2005.
> AHIA,
> Larry...
>
|||Thanks Hilary...
sql

High CPU utilization on Merge Replication with SQL 2005 Mobile

I have a question for anyone who mas some tips/pointers for optimizing SQL merge replication publications.

The front end web server is running IIS 6.0 on Windows 2003 x86 Server Standard (Server A). The back end database server is running SQL 2000 Standard on Windows 2003 x86 Standard (Server B). The merge replication clients connect via HTTPS over the Internet from a custom C#.NET 2005 application using SQL 2005 Mobile running on Windows Mobile 5.0 (Client).

The publication itself has several filters on it. The entry point uses the user's Windows username to start the filter. Based on the user, it then filters the records in multiple tables. There are 68 articles and 44 filter statements. The filters extend multiple layers deep, in other words they are not all filtering off the HOST_NAME() variable, some tables filter from records in tables that filter from the HOST_NAME() variable. The publication is set to minimize data sent to the clients, and considers a subscription out of date if it has not synced in the last 4 days. All the rowguids are indexed as well.

There are approximately 35 clients actively using the application at any given time. On average, a client will initiate a merge replication 3-4 times per hour from 8am-5pm. Generally, a sync will take between 10 seconds and 2 minutes to complete, with most of them being around 30 seconds on average.

When a client starts a sync, there is a spike to about 50% on the server's CPU graph. If multiple clients attempt to sync at the same time the CPU utilization can be pushed to 100% for extended periods (more than 30 seconds).

I recently completed a project to increase the bandwidth available to the clients, and plan to reduce the number of filters significantly (although this will obviously increase the amount of data going to the clients and the storage needs on the individual devices). I also plan on changing the setting to not minimize the amount of data sent to the clients.

Having said all that, does anyone have any information about how to further optimize merge publications to mobile clients? The next publication will be on SQL 2005 x64 Standard if I can solve the issues in the text environment. I would like to enhance the publication as much as possible to make the end user experience better than it currently is.

Thanks!

You're talking about CPU usage at the publisher, correct?

Can you double check that all columns involved in the merge join filters are indexed as well? If the columns are not indexed, this leads to table scans during syncs which can result in high CPU usage.

Are you also getting conflicts? There's a performance issue (which will be fixed in SP1) that can slow things down due to missing indexes on some conflict tables, but this shouldn't be an issue unless you're getting hundreds and hundreds of conflicts.

ALso, how "deep" are your filters? Do the merge join filters have 1 to many relationships, or many to many (see the @.join_unique_key parameter). The more levels deep you are, or any level that contains a @.join_unique_key = 0, can negatively affect performance.

Regardless, you can always run profiler at the publisher and trace a single subscriber to see which procs are consuming the most time. You can start with RPC:completed or SP:completed, and just grab the duration. You'll quickly see which procs are the problematic one. From there, you can then enable SP:StatmentEnded and enable ExecutionPlan to see exactly what statement and why it's slow.

|||

Indexes were definetly a piece of the puzzle. The @.join_unique_key was also in play, so thanks for putting me on to that one. For anyone else who is using Merge Replication with SQL Mobile, there are 2 very useful articles:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_replmergepartitioned.asp

http://msdn2.microsoft.com/en-us/library/ms147840.aspx

High CPU utilization on Merge Replication with SQL 2005 Mobile

I have a question for anyone who mas some tips/pointers for optimizing SQL merge replication publications.

The front end web server is running IIS 6.0 on Windows 2003 x86 Server Standard (Server A). The back end database server is running SQL 2000 Standard on Windows 2003 x86 Standard (Server B). The merge replication clients connect via HTTPS over the Internet from a custom C#.NET 2005 application using SQL 2005 Mobile running on Windows Mobile 5.0 (Client).

The publication itself has several filters on it. The entry point uses the user's Windows username to start the filter. Based on the user, it then filters the records in multiple tables. There are 68 articles and 44 filter statements. The filters extend multiple layers deep, in other words they are not all filtering off the HOST_NAME() variable, some tables filter from records in tables that filter from the HOST_NAME() variable. The publication is set to minimize data sent to the clients, and considers a subscription out of date if it has not synced in the last 4 days. All the rowguids are indexed as well.

There are approximately 35 clients actively using the application at any given time. On average, a client will initiate a merge replication 3-4 times per hour from 8am-5pm. Generally, a sync will take between 10 seconds and 2 minutes to complete, with most of them being around 30 seconds on average.

When a client starts a sync, there is a spike to about 50% on the server's CPU graph. If multiple clients attempt to sync at the same time the CPU utilization can be pushed to 100% for extended periods (more than 30 seconds).

I recently completed a project to increase the bandwidth available to the clients, and plan to reduce the number of filters significantly (although this will obviously increase the amount of data going to the clients and the storage needs on the individual devices). I also plan on changing the setting to not minimize the amount of data sent to the clients.

Having said all that, does anyone have any information about how to further optimize merge publications to mobile clients? The next publication will be on SQL 2005 x64 Standard if I can solve the issues in the text environment. I would like to enhance the publication as much as possible to make the end user experience better than it currently is.

Thanks!

You're talking about CPU usage at the publisher, correct?

Can you double check that all columns involved in the merge join filters are indexed as well? If the columns are not indexed, this leads to table scans during syncs which can result in high CPU usage.

Are you also getting conflicts? There's a performance issue (which will be fixed in SP1) that can slow things down due to missing indexes on some conflict tables, but this shouldn't be an issue unless you're getting hundreds and hundreds of conflicts.

ALso, how "deep" are your filters? Do the merge join filters have 1 to many relationships, or many to many (see the @.join_unique_key parameter). The more levels deep you are, or any level that contains a @.join_unique_key = 0, can negatively affect performance.

Regardless, you can always run profiler at the publisher and trace a single subscriber to see which procs are consuming the most time. You can start with RPC:completed or SP:completed, and just grab the duration. You'll quickly see which procs are the problematic one. From there, you can then enable SP:StatmentEnded and enable ExecutionPlan to see exactly what statement and why it's slow.

|||

Indexes were definetly a piece of the puzzle. The @.join_unique_key was also in play, so thanks for putting me on to that one. For anyone else who is using Merge Replication with SQL Mobile, there are 2 very useful articles:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_replmergepartitioned.asp

http://msdn2.microsoft.com/en-us/library/ms147840.aspx

Hiding data in replicated tables

Hello,
I have a merge replication setup between two sql 2000 servers. It works
fine, but there is some data in one table
that I wish to hide - at the subscriber database.
This data is in 4 columns in one table. It would be best if I could exclude
the table altogether from the replication process
but this cannot be done because the table is referenced by a foreign key
constraint with another table required in the replication process.
Also I cannot vertically filter out the columns because the columns are not
nullable. I did try selecting out the data by using the 'where' clause
and trying to eliminate the data that I wish to hide from the result set,
however this did not work.
Am not sure how I can go about hiding this data because it should not be
seen at the subscriber.
Any help will be appreciated.
Thanks,
Marise
How about hiding the data by using a view on the subscriber?
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
I am not too sure what you mean by hiding data using a view on the
subscriber...please explain some more how this can be done.
Because this is data hiding at the database level. i.e. anyone who logs
into the database and runs a select query should not be able to see these
four columns in this one table.
Thanks,
Marise
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u4Xlhg$dFHA.3712@.TK2MSFTNGP12.phx.gbl...
> How about hiding the data by using a view on the subscriber?
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Nothing special here - as long as they don't have select rights on the
table, the only way they'll be able to get to see any data is through the
view.
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Thanks for the response. The replicated database sits away from our location
and the user name that we use to do replication has been assigned to us - we
do not have the capability to alter database permissions. I wanted to find a
way to restrict the data itself being copied over at all.
Please let me know if there is a way.
Thanks,
Marise
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u2kqR4AfFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Nothing special here - as long as they don't have select rights on the
> table, the only way they'll be able to get to see any data is through the
> view.
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>