August 4, 2010 at 10:25 am
Hi all,
This is my first time formulating a replication architecture and I am hoping that people may be able to point me in the right direction. I have two questions that i hope that you will be able to help me with:
1. I have a case where the publisher-DB is its own DB and the subscriber-DB is another DB (and at the moment, not a snapshot). The former is a complete subset of the latter and the replicated tables remain identical. There are other tables in the subscriber-DB. One may only write to the replicated tables in the subscriber by writing to the publisher DB. Transactional replication looks perfect because of the requirement to have data replicated within seconds. I have just gone through the BoL tutorials and another tutorial online. It seems that the creation of a snapshot is a necessary step. Is this so? Is it not possible to map tables between the publisher and subscriber? I have a create table script that will create the tables on both the publisher and the subscriber. Or can I expand the initial snapshot by adding on more tables?
2. The second problem is more architectural. I have a dilemma between having the two DBs on one instance (on one server) or having each DB on its own instance on the same physical server. We plan to eventually separate the two DBs but until then they share one server. These two DBs are the publisher + distributor and subscriber, btw. With the two instances, one can allocate different numbers of cores and RAM to each instance. The migration to different physical servers should also be easier because the configuration is already set for two distinct servers. The subscriber DB will be a lot more active than the publisher DB. My gut feeling is that SQL Server 2008 can manage 2 DBs better than it can 2 instances but I haven't tested it.
May I have the benefit of your experience please? I realise that this is a 'it all really depends...'-type question. Which scenario is preferable or have I given too little information?
The DBs will be running in SQL Server 2008 Enterprise edition, on virtualised servers on a SAN. When the new hardware arrives sometime next year, the DBs will be split and put onto their own physical hardware. The publisher DB is expected to be small (10-ish GBs). The subscriber DB will at about 10GBs and will grow substantially in a few years.
many thanks for your help
August 5, 2010 at 2:17 pm
gosh (8/4/2010)
My gut feeling is that SQL Server 2008 can manage 2 DBs better than it can 2 instances but I haven't tested it.
Too much going on in a single post... I'll take "infrastructure for $100" 🙂
Databases are handled by SQL Server instances.
SQL Server instances are handled by Operating System.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 6, 2010 at 12:07 am
PaulB » Databases are handled by SQL Server instances.
PaulB » SQL Server instances are handled by Operating System
Hi PaulB,
This I know. I am new to multiple instances on one server at a production level. In the past I simply created new DBs on the same instance and, since clients were usually paying, there was one physical server pro instance.
My question is whether 2 DBs on one instance is a better use of resources than 1 DB per instance. Both scenarios would be on one physical server. This is why I'm looking to draw on people's experience here.
On the one hand, separate instances allow one to allocate different levels of system resources (RAM and numbers of cores, for example) as well as making the logical separation of the two DBs easier. The two DBs are owned by different companies and are bound by transactional replication. When it comes to putting the DBs onto their own individual physical servers, separate instances will ease the work to be done. The publisher is a complete subset of the subscriber.
On the other hand, SQL Server manages its resources quite efficiently and fewer system resources would be used up with a 1-instance-2DB setup. There are fewer services, to start off with. I imagine that replication would be faster too, when it's simply inside the one instance.
BTW, I think that I've found the answer to question number 1. Snapshots are not necessary for transactional replication. I just have to get around the seemingly wanton IDENTITY_INSERT error messages. Some of the publisher tables refuse the distributor with this error while others don't. I have to go read up BoL and have a look at the free chapter in Hilary Cotter's book to see if there is anything there to help me overcome this next hurdle.
Many thanks for your reply
gosh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply