SQL Server security best practice

Security! This is the word comes in mind of every concerned person when it come...

Change the Collation Settings in MS SQL Server

This post will show you how to change the collation settings in MS SQL Server for specific database...

Resolve collation conflict

In this post I will show you how you can resolve collation conflict error...

Book: SQL Server 2008 High Availability

In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring...

Why to recompile Stored Procedure

Generally, we create views and stored procedures (proc here after) ...


Some Tips for Merge Replication

* SQLAgent service should run under same domain account, which has local admin privilege and “Log in as a service” permission. This requires on both the server. In case, if SQL Server is not part of any Domain. We can use the user account which has identical name and password at servers i.e. ReplUser on SRVA with password ReplUser! and identically ReplUser on SRVB with password ReplUser!
* User account that is used to run the SQLAgent services have to have sysadmin privilege on both server, and it should be added in to db_owner fixed db role for database which is being replicated/replicating.
* Should include all the tables which are referenced, this will help to keep data consistency.
* If there are text/image column we need to update those column explicitly Updatetext statement. http://msdn2.microsoft.com/en-us/library/aa260658(SQL.80).aspx
* Try to avoid using Identity column as PK but if it is already implemented modify the property of Identity column as ‘Not for Replication’.
* Define both server (Publisher and Subscriber) as remote server at each other.
* Allocate adequate space for distribution, subscription and publication db.
* Avoid using Identity column as PK but if it is already implemented modify the property of Identity column as ‘Not for Replication’.
* Rowguid column is must, which also can be added at the time of configuring Merge replication.
* sp_dropmergearticle can be used to drop article from publication.
* sp_addmergearticle can be used to add article to publication.
Below are some links that will be usefull for replication troubleshooting, they are:
Troubleshooting Merge Replication http://support.microsoft.com/kb/315521 and
Managing Identity values in Replication environment http://msdn2.microsoft.com/en-us/library/aa237098(SQL.80).aspx
Violation of Primary Key (PK) Constraint http://support.microsoft.com/kb/813494

Merge agent incorrectly deletes rows after PK violation error
Adding and Droping an article in existing publication http://msdn2.microsoft.com/en-us/library/ms152493.aspx
Working with BLOB data in Replciation http://technet.microsoft.com/en-us/library/ms151206.aspx


Estimating the size of Table

Many time it happens we were wrong with the Estimation of Table Size, wrong estimation of Row Size can trouble us with the growth of the database and ultimately we are ending up with the space crunch, and all this because of wrong Estimation of Table Size. So what we should consider the most with Estimating Table Sizing!!! Nullbitmap is the most important criteria here to take into consideration whenever you do a Estimation of Row Size, here is an excellent article on Row Size Estimation http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx and http://msdn.microsoft.com/en-us/library/ms175991.aspx