March 9, 2009 at 2:33 pm
Hello. First time poster here. I'm a developer turned DBA, and I've never set up replication.
--A description of our setup:
We have a production SQL Server 2005 instance that lives on a VMWare server. We want to replicate all the databases on this publishing SQL Server instance to a subscribing SQL Server 2005 instance on a separate VMware server. The distribution server will live on the same VMWare server as the subscriber. We plan on initializing the subscriber by restoring form the previous night's production backup - this way we can avoid locking the production tables for a long period of time when we begin replication
--A few questions:
1) Since the distributor and subscriber will live on the same server, we want to keep file sizes as small as we can during replication. Would Transactional Replication be a better choice than Snapshot Replication? I'm under the impression that Snapshot Replication moves the entire contents of your publications each time (Wouldn't this mean, at some point during the replication process, you would have thre copies of your data; one on publisher, one on distributor, and one on subscriber?)
2) The most important DB coming from our publishing SQL Server is still set, unfortunately, to compatibility level 80. We want to change this, but not right now because we want to do a little more testing first. If I set up the distributor and subscriber as compatibility level 90, will the replication work ok? Will it break anything if I change the publisher compatibility level to 90 in the future?
3) Our main production DB is also, unfortunately, only on SQL Server Service Pack 2. If we set up the distributor and subscriber with service pack 4, will replication still work? Will there be a problem if I upgrade the Publisher's service pack in the future?
Thanks, in advance, for any help. It will definitely be appreciated.
March 10, 2009 at 7:06 am
Clint (3/9/2009)
Hello. First time poster here. I'm a developer turned DBA, and I've never set up replication.--A description of our setup:
We have a production SQL Server 2005 instance that lives on a VMWare server. We want to replicate all the databases on this publishing SQL Server instance to a subscribing SQL Server 2005 instance on a separate VMware server. The distribution server will live on the same VMWare server as the subscriber. We plan on initializing the subscriber by restoring form the previous night's production backup - this way we can avoid locking the production tables for a long period of time when we begin replication
--A few questions:
1) Since the distributor and subscriber will live on the same server, we want to keep file sizes as small as we can during replication. Would Transactional Replication be a better choice than Snapshot Replication? I'm under the impression that Snapshot Replication moves the entire contents of your publications each time (Wouldn't this mean, at some point during the replication process, you would have thre copies of your data; one on publisher, one on distributor, and one on subscriber?)
You need transactional replication here. Snapshot replication will copy the contents from Publisher to Subscriber on a certain interval. This can cause lock up. You just have to make sure that the data is Synched between the Publisher and subscriber.
One thing about snapshot publication, It is not going to keep a copy of your data in the Distributor as well. But it will create files with data in it.
2) The most important DB coming from our publishing SQL Server is still set, unfortunately, to compatibility level 80. We want to change this, but not right now because we want to do a little more testing first. If I set up the distributor and subscriber as compatibility level 90, will the replication work ok? Will it break anything if I change the publisher compatibility level to 90 in the future?
I have never seen this issue. You can actually replicate from SQL 2000 to SQL 2005 or vice versa.
3) Our main production DB is also, unfortunately, only on SQL Server Service Pack 2. If we set up the distributor and subscriber with service pack 4, will replication still work? Will there be a problem if I upgrade the Publisher's service pack in the future?
It should not be problem. But when you do upgrade the Publishers service pack, you will have to restart the server.
The only thing that I have a bad feeling about is having the distributor and Subscriber in the same DB. If your DBs are quite busy, this will put additional load on the Subscriber since Distributor will get busy and has to run clean up Jobs.
-Roy
March 10, 2009 at 2:13 pm
Thanks for the reply. Our subscriber is only going to be used for testing against "near-production" data, in case something is suspicious on our publisher. The publisher will, eventually, get busier. However, for now the only choice is to put the distributor and the subscriber on the same server.
Even if our subscriber is only used for occasional testing at the moment, do you think that would be too big of a strain on the server? We can always move the distributor and/or subscriber later, right?
Thanks again.
March 10, 2009 at 2:15 pm
I mean, I know you can't give me a definitive answer about performance without knowing our system specs. I just want to make sure this is possible, and that we can 'rearrange' our setup if we need.
thanks
March 10, 2009 at 2:22 pm
It wont be that much of a load on the subscriber. Hopefully your subscriber is a pretty good server. 🙂
-Roy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply