March 12, 2009 at 3:08 am
Hi,
In our project , we are having two servers say Server1 and Server2. Both will be restored by a same back up database file(Size is around 400 GB). The restoration time taken for each server is 3 - 4 hours(Totally 8 hours).To reduce the enormous time taken to restore,we are planning to exclude the restoration of Server2, instead of this go for replicating server1 to server2.Server1 will act as Publisher and Server2 will act as Subscriber.
So anyone please tell me the advantages and disadvantages of replication.
Will the Replication helps in our case?
Awaiting your reply ...
Regards,
Pramila
March 12, 2009 at 8:01 am
I'm not sure I understand the objective, are you trying to save time in restoring a DB?
Replication will ensure that the data from Server 1 is replicated to Server 2 it doesn't necessarily mean it will be faster, data has to be transferred to server 2.
Maybe a bit of background on what you are trying to achieve ...
March 12, 2009 at 8:01 am
Replication will still need to synch data. You will need to restore in Server B atleast once. Afteer that you can set up replication with no Snapshot of data. (That is tell Replication that there is no need for intial Synch since you already have data. How I would approach would be
1. Restore Back up on both servers simultaneously.
2. Set up Server C as distributor. (Preferably Server C, if you dont have it, You can use Server B as your distributor)
3. Start up replication with No Initial Synch.
Since you have not specified if both DBs are going to be updated, I cannot advice on what replication you need.
Also you have not specified if it is being set up as a fail over server, I cannot say if any other method will work fine.
You should try looking up Mirroring, Log Shipping and replication and see what method suits you the best.
Just my .00002 cents.. 🙂
-Roy
March 12, 2009 at 11:30 pm
Hi,
Thanks a lot for your valuable suggestions. 🙂
Let me clearly explain the scenario,
We have a source server say Server A and two more servers say Server B and Server C which are taking backup from Source server(Server A).
All data updations are taken place in the Source server only (Server A) , from which it is restored in the other two servers(Server B and Server C).
Restoration time taken for each server from the source server is 3-4 hours(Totally 8 hours).
Instead of restoring data from Server A to Server C, we are opting to introduce REPLICATION OF DATA from Server B to Server C. This new approach is mainly to reduce the total restoration time.
Will this approch be helpful in our case?
If so,what type of replication will suite our scenario?
Regards,
Pramila
March 13, 2009 at 2:45 am
Hi,
It all depends on WHAT you are using Server B and C for. A solution depends on your requirements. SQL 2005 provides number of different ways (logshipping, mirroring, replication) you can achieve your goal. Besides using inbuilt functionality , if you redesign your database and backup & restore, you can bring down your restore time. This can be easily done by using backup and restore of filegroups ONLY rather than FULL restore , (which I think you are doing right now). But again it depends on the nature of database and its updates.
If you do not wish to get into headache of redesigning or implementing a solution, then invest in third party backup and restore solution like LITESPEED which can bring down backup and restore time.
Deepak
March 13, 2009 at 6:58 am
Hi Pramila,
Here is the problem you are going to face when introducing replication from B to C and B is restored. Every time you restore Server B, it will break replication. That means you will have to reinitialize the Server C.
Since update happens only in Server A, from what I see the best approach would be to set up Transactional replication from Server A to Server B and C. The load on Server A will not be high just because you have replication set up. Set the Distributor in Server B or C.
The Only load on Server A will be send the replication commands to the distributor. You just need to set up replication from Back Up with Transaction Replication.
-Roy
March 13, 2009 at 7:22 am
Hi,
Thanks a lot 🙂
Below are my doubts,
1. Can we done replication server accross domains?, because the domain of Server A is different from Server B & C.
2. In case of Transactional replication ,there should be an key colunm(Primary/Forgien Key) in the objects of the database to be replicated ?, because we dont have any key colunms in the objects.
Regards,
Pramila
March 13, 2009 at 7:26 am
Good news is that you can replicate across domain.
Bad news is you need primary key for transactional replication.
-Roy
March 13, 2009 at 7:57 am
I'm not certain about the cross domain question but I would assume that if you have a domain trust in place you would probably be successful.
However, I'm still a bit suspect of using replication for your situation based on the fact that you used backups to solve this in the past. If you are doing massive data updates on ServerA, possibly overnight data loads or monthly data loads then you are going to have problems with replication keeping up. If the activity on ServerA is strictly normal OLTP type activity then it should work fine. If you are performing significant batch updates then I would recommend, as one poster did already, to buy a backup compression solution and use that as you will see your restore times decreased using that alone and will not have to worry about the latency that can be introduced by batch processing on replicated articles (tables).
Hopefully this helps with your consideration.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 13, 2009 at 8:10 am
You will need to add your service account to the second machine for Cross domain replication.
If it is Bulk Updates and Inserts, I will agree with David. But since you do not have any Primary key, replication is out of question. Like the previous poster said, the best method now is to have some Back up utility like Litespeed to take care of this problem.
-Roy
March 20, 2009 at 10:27 pm
Thanks a lot for all your clarifications 🙂
March 23, 2009 at 1:24 pm
Just one more note. Adding replication to your primary server does not come free. There is always a price to pay 😉
* Noel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply