I found a bug in Management Studio, which can be reproduced with following steps:
1. Create a new publication with the 'New Publication Wizard'
2. Select a database which contains at least 2 tables.
3. Select 'transactional publicatioon'
4. Select at least 2 tables as articles
5. Set article properties for all table articles
6. Change 'Statement Delivery' to 'Insert statement', 'Update statement' and 'Delete statement' and save the article properties.
7. If you now check the properties for a single article you find that the 'Statement Delivery' is set to 'Do not replicate...'. If you generate this publication and create a subscritpion you will see, that actual no replication take place.
It took me a while to find out why my replication doesn't work! :-((
Wolfgang Kunk
PS : Management Studio version is 9.00.1399.00
Unfortunately that part of the wizard is kind of broke, and it won't get fixed for SP2 either. A similar issue was raised in an earlier post as well - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=984751&SiteID=1.
The workaround is to either use TSQL scripts to set everything up, or set everything up with UI and then immediately issue a sp_changearticle to set it.
As a side note, may I ask why you want to use "Insert/Update/Delete statement" instead of the default stored proc? This can have bad performance impact against a SQL subscriber.
|||Normally we use T-SQL scripts to set up replication. So it is not a real big problem.I wanted to see if it is a known bug!
Can you please explain what the bad performance impact can be?
As far as I know the default stored procs execute nothing else then a simple insert, update or delete statement. So I cannot see a bad impact. The only thing is that the optimizer optimzes a stored proc once while it has to optimize insert, update ans delete-statements every time.
Wolfgang Kunk|||
When using stored procs, each proc is fired off as an individual RPC, and the proc itself generates a plan that can be reused over and over for optimal performance.
When using SQL, we fit as many commands as we can (I think up to 500) into a single parameterized sp_executesql and execute it all at once. Meaning one single sp_executesql call can have anywhere from one to 500 different ins/upd/del statements - this generates one single plan. The next sp_executesql call will most likely be different, and generate a totally different plan. So what happens is every call will most likely have to be compiled because you'll never get plan reuse. In an OLTP scenario where you're replicating thousands of commands a second, you'lll definitely see an impact in performance.
For proof, apply a mixed/random workload to the publisher and then trace the distribution agent calls at the subscriber using SQL and stored procs, you'll see what I mean.
SQL is used mostly with heterogeneous subscriber cases and the very rare case when customer has a purpose for SQL subscriber cases.
|||Thanks for the explanation.At the moment we only use heterogenous subscriber, where we work with SQL. So I used SQL for my first MS-MS Replication as well. I will take your recommendation in account for my further tests!
Wolfgang Kunk
