Replication database setup on same instance

Question

Replication database setup on same instance

am-244616

SSCarpal Tunnel

Points: 4936
More actions
January 19, 2009 at 7:50 am

#204156

Guys,
I have 4gb size database on sql instance whose server 2gb RAM, I am planning to setup replication for this database (transaction replication from backup) on the same instance. I also plan to increase the RAM on the box to 4gb before I setup the replication. Are there any issues with setting up replication on the same instance.
Any suggestions and inputs would be helpful
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

dbamohsin SSCommitted Points: 1948 More actions · Answer 1

Personally dont see this being a problem.

We have a one instance transaction replication setup - to copy real time data from live into a reporting database - to split load

My DBA Ramblings - SQL Server | Oracle | MySQL | MongoDB | Access[/url]

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

It depends on how busy your DB server is. Keep in mind that you will have to have a Distributor as well. I will always go for three seperate servers. One the Main server (Keep it as it is), second for the distributor (The clean of distributor can be a Performance hog) and third for the replicated DB. Or else you might get IO bottle necks.

-Roy

Alan Vogan SSCrazy Points: 2513 More actions · Answer 3

Hello,

I'm also in the process of setting up transactional replication for reporting purposes. We have a 188GB DB on SQL 2005. Could you explain a little bit more about the 3 server set-up for publisher, distributor, and subscriber or recommend an article to review?

Thanks,

Alan

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

Well, it is quite simple.

Server Number 1. The Publisher. That is your main production DB where all the transactions takes place. You do not want to disturb this set up.

Server Number 2. The Distributor. This does not have to be a super heavy Server. Any Blade would do. The Database wont grow big. If you have 2 Gig RAM with dual CPU, that would be sufficient.

Server Number 3. The Subscriber. Should be as good as the production DB. Since all the reports are going to run from that.

Make sure you have set the Publisher DB with Option FULL. Then you can just follow the GUI from replication to set it up rest of the replication. I always make sure that replication runs under a Seperate user.

-Roy

Alan Vogan SSCrazy Points: 2513 More actions · Answer 5

Thanks Roy,

Another question; How/Where do I set up the publisher server and distributor server separately? Do I do it from the distributor server? Do I set-up something on the publisher server first and then work from the distributor server. I'm not seeing where it gets separated in the GUI, perhaps I should be using scripts?

It gets a little confusing because the documentation will sometimes refer to them as one and the same and sometimes not.

I'm sure I just haven't read far enough yet, but any pointers would be appreciated.

Alan

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

The first thing that need to be done is to set up the distributor. Actually if you are setting it with defaults and simple replication, just follow the GUI. That will help you a lot. (Do couple of test runs with a test set up before you run it in Production)

You can search this site itself to learn more about replication since there are lots of articles written about it.

-Roy

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

What I usually do is use Scripts. I set up the distributor with the GUI. And after that it is all Scripts I use.

Then I know what I am doing for sure. One thing I forgot to mention, make sure there is enough disk space for the Snap Shot files.

Its a pretty big topic. So it is not easy to express everything into an answer in a forum.

-Roy

Alan Vogan SSCrazy Points: 2513 More actions · Answer 8

Roy Ernest (1/20/2009)
The first thing that need to be done is to set up the distributor. Actually if you are setting it with defaults and simple replication, just follow the GUI. That will help you a lot. (Do couple of test runs with a test set up before you run it in Production)
You can search this site itself to learn more about replication since there are lots of articles written about it.

Excellent. I've set up the distributor already and tested with it. I'm putting some scripts together now, so it sounds like I'm going down the right path.

Thanks for the thoughts.

Alan

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

Glad to be of help. Make sure you test it out throughly and script those things out. And then look at the scripts. Then you will learn how to fine tune it. How to set up the Timi Out time, batch size ect.

-Roy