.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Setting up replication for first time----Need Suggestions please ?

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server

Hi Guys

I have a requirement to set up the transactional replication on one of the database in my Production server to offload some reporting users. Size of database is 90 GB. Being the most busiest and critical this is the only database on the server.

Our Server is Window Server 2003, Processors 2 (4 core each), 12 GB RAM, 836 GB Space Drive.  The log files, data file, tempdb file exist on the same drive. SQL Server is : SQL 2008, memory : 2GB to MAX 10GB and 2 GB for OS. Right now the performance is fine. Every thing runs good.

Now I have a Question What factors can affect the performance ? how the performance will be affected if I set up Distributor on the same Server ? Can you please suggest me some ways how i can avoid the performance problem  in the production once it gets started. 

What Could be done before replication that performance is not hit on the production.





View Complete Post

More Related Resource Links

Real-time data replication

Are there any ways to replicate sql server production database to a standby database? I notice that there is a feature called 'continuous replication', does it do in real-time? what is the time-lag? Are there any potential data loss (i.e., production transaction committed but production db corrupted before the transaction is sent to standby)? Is the standby database available for read access during the replication?

Setting a date time field to now


Hi all

MOSS 2007 - Visual Studio Sequantial Workflow.

I have a field in an item that I want to update within my Workflow code.

Field Definition:

<Field Type="DateTime" DisplayName="Date Approved/Declined" Required="FALSE" Format="DateOnly" ID="{0c1d9bb7-52f9-4b83-8d26-eda6c9879921}" SourceID=

Setting Session variables at the time of login authentication


Dear Rahul,

I am having a website in which I am setting nearly 20 Session variables at the time of login authentication. ( Ex : Name, EmpID, Dept, Location, Email...).

Can u please tell which is one of the following is the better way to get Login user details 

 1) Through session variables at the time of login 

 2) In each and every page by calling the Stored Procedure to get login user details


Regards >> HemaBhushan 


Setting Publisher as distributor also in Transactional replication from SQL 2008 to SQL 2008


Can some one please tell how will the performance of the Publisher be affected if I use the same server as the distributor ? How can i get the best performance from Publisher in case i use this as distributor as well ?


any ideas or links  would be appreciated 




Need replication architecture assistance (near-real time merge repl across 2 separate companies and


I've read several different replication scenarios and technologies native to SQL Server (we are talking about SQL Server 2005) but have some questions.

I have a project to integrate/replicate two network-separated SQL Server 2005 databases.  Each database resides in a physically separate location at different companies.  They will not have a shared network environment and will not allow direct connection between the two databases.

We need to keep each database in sync as far as customer records.  Near real time is preferable.  There is not master.  One could add/delete/modify a customer record in one database and it needs to reflect in the other database. 


The closest we could get for connectivity architecture looks like this:

1.  Each database is on the separate internal networks for each company.

2.  Each organization HAS agreed to setup a DMZ on their sides specifically for this exchange.  Each respective DMZ will be able to communicate with their own internal SQL Server and have a fiber connect directly connecting each organization's dmz.

SO.... is there a good way to replicate from InternalSQL_1 - DMZ1 - DMZ2 - InternalSQL_2 near real time?

What would your recommendation be?  Pretty sure I'd setup both internal InternalSQL as Pub/Sub/Dist but I'm solici

Setting up Transactional Replication using No-Sync


I am setting up Transactional Replication for my database for reporting purpose. I only need the tables with all their indexes , all stored procedures used by reports, UDFS, and views.

Many tables have identity columns but are already set with  “Not for replication’ setting. I do not want any triggers to fire on the subscriber. Also I do not need any foreign keys and check constraints on the subscriber.

I can setup transaction replication with above requirements when I start from scratch and initialize the subscriber schema by generating the initial snapshot from publisher.

But I want to setup subscriber using publisher database backup and setup replication with no-sync option. Since subscriber database is created from backup all triggers, foreign keys, check constraints etc are come created on subscriber.

How to minimize time when creating 2005 merge replication with very large data files and log files?


Dear all,

We're using window server 2003 sp1 + sql ent 2005 sp2 with merge replication between 2 servers. Since 1 of server sql is down, we need to create merge replication again. However, our data file size is 60Gb and log size is 40 GB and when we create merge replication it may need more than 10 hours to finish which is unacceptable since when creating replication the online db normal service operation may be affected. Is there any other way that can reduce the replication creation time within 5 hours to finish? Thanks a lot.


P.S: we are using full recovery model and plan to change to simple recovery model. Any help?




Merge Replication Infinite Loop & Time Problem


Hi all,

We have merge replication system between Sql Server 2008 R2 64bit Enterprise Edition (As a Publisher&Distributor) and Sql Server 2008 R2 Express Edition (As 800+ Subscribers). We use only "https " method as transfer method. We have 30 tables and we have Joins & Filters. Our system work normally for 5-6 months.

But for last 10 days, sometimes (generally initial snapshot being downloaded), it takes over 14 hours to replicate!! I think an infinite loop is being occured during the process. I trace the publication server with Sql Server Profiler 2008 and here is the trace file.

Lots of times, i see this line "exec sp_MSmergeupdatelastsyncinfo '8C01EA1F-153D-47BC-ACD3-F8439ACB7B76',2,N'Merge completed with no data changes processed .'" (and also other lines which repeat similar to this one) with same/different  Session ID's. And when i first see this line is after 3 seconds the start time of the Process!! And after , 6 seconds again same line, and after 13 seconds again same line and it goes on like this. I think, in fact synchronization process is finished after 5-6 seconds (normally because there is no any data change in fact!) but for some reason, it continues to same process over and over.

Is this a bug or anything else, h

Round off time to the nearest minute

How would you round this up to the nearest minute? There isn't a built in function to do this so you have to use a little bit of maths to get there. There are 60 seconds in a minute. We already have 38 seconds on the clock. So we need to add on 60 - 38 = 22 more seconds.

Multi-threading in .NET: Introduction and suggestions

This article uses the C# type shorthands throughout - int for Int32 etc. I hope this makes it easier for C# developers to read, and won't impede any other developers too much. It also only talks about the C# ways of declaring variables to be volatile and locking monitors. Developers using other languages can find the equivalents in their own preferred environment, I'm sure.

Performance Tests: Precise Run Time Measurements with System.Diagnostics.Stopwatch

Everybody who does performance optimization stumbles sooner or later over the Stopwatch class in the System.Diagnostics namespace. And everybody has noticed that the measurements of the same function on the same computer can differ 25% -30% in run time. This article shows how single threaded test programs must be designed to get an accuracy of 0.1% - 0.2% out of the Stopwatch class. With this accuracy, algorithms can be tested and compared.

Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Data Types - Date and Time in SqlServer

Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

How to programmatically add controls to Windows forms at run time by using Visual C#

Create a Windows Forms Application
Start Visual Studio .NET or Visual Studio 2005 or a later version, and create a new Visual C# Windows Application project named WinControls. Form1 is added to the project by default.
Double-click Form1 to create and view the Form1_Load event procedure.
Add private instance variables to the Form1 class to work with common Windows controls. The Form1 class starts as follows:

.NET 4 Web Application Startup Time

I was chatting with Jonathan Hawkins and some of the folks on the ASP.NET team about performance and Jonathan mentioned the startup time for large ASP.NET applications is improved on .NET 4. There are some improvements in the CLR and in ASP.NET itself that helped. If you have a giant app, you should do some tests.

Built-in Functions - Date and Time Functions

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

Essential SQL Server Date, Time and DateTime Functions

The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend