Pull Replication vs. Push Replication

Did you know that Pull replication performs much better than Push?

When replicating data over a WAN (Wide Area Network) using Transactional Replication you can improve the transactions delivery rate just by having the Distribution Agent located at the Subscriber (Pull) instead of having the Distribution Agent located at the Distributor (Push).

In an upcoming post I will talk more about that.

The bellow table shows the results of a test I have done over a 160 ms WAN with one server located at NY and the other at TLV.
Both servers are running SQL Server 2005 sp2 build 3282 (CU6) x64 with similar HW.

Type	Status	Transactions	Commands	Sec Delivered	Trans/Sec
Push	17	40000	40000	430	93
Pull	17	40000	40000	205	195

To leave a comment pls follow this link: http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/02/25/pull-replication-vs-push-replication.aspx

-Yaniv
=================================================

Detecting None Pooled Connections (Logins)

Some time ago while working on a problem that SQL Server reached a very high number of User Connections I found out another useful capability that the SQL Trace technology exposes. This is the ability to determine if a given connection is a Pooled Connection or not. Remember that in a "Pooled Environment", that is an environment that uses Connection Pooling, a single connection services more than one user and vise versa, every user browsing your site can be using more than one connection.

To find out at the application's side if all connections to SQL Server are correctly implemented and do "Pool" their connections can be time consuming and also if you are not well familiar with the application(s) it is difficult to be certain that you have covered all spots.
So tackling this issue at one focal point, at the server side, has a clear advantage and makes sense.

Fortunately, you can Trace or Profile your server and easily get to find out those connections that are not correctly implemented at the application and therefore do not make use of Connection Pooling thus establishing a None Pooled Connection to SQL Server.

The above applies to SQL Server 2005 and SQL Server 2008. In those versions when a Login Event is fired the EventSubClass column gets populated with a value of 1 or 2 for a None Pooled Connection or a Pooled Connection respectively.

This behavior does not take place in SQL Server 2000 even though both exists - the Login Event and the EvenSubClass column.

Not all events populate the EventSubClass column but at times it can be valuable as I found in this case.


I have attached the NonePooledLogins.zip file to the post which includes a Profiler template file that can be imported (File --> Templates --> Import Template) into Profiler. The template defines the event /columns combination and the filtering.
In addition the zip file contains the NonePooledLogins.sql script file that creates the same trace but only at the server side (known as SQL Trace) while not using the Profiler application.
=======================================================

My very first blog post - SQLScripter

Technorati Tags: sql,scripts

This is my very first blog I ever post.

I would like to dedicate this post to all my SQLScripter users and let you know that I very much appreciate all the emails, feedback and ideas you have been sharing with me. Thank you!

In the near by future I intend to add more of the requests I have received into an up coming release. Will keep you posted...

For readers who are not familiar with this utility I can say in brief that SQLScripter is a free tool that automates the process of schema generation.

SQLScripter on SQL Server Magazine:

http://www.sqlmag.com/Article/ArticleID/100140/sql_server_100140.html

SQLScripter direct download:

http://www.valinor.co.il/tools-sqlscripter.asp

-Yaniv