February 14, 2008 at 4:15 am
Ok, i am new here and also new to SQL Server in general (i just graduated and got a job a month ago :)), so please bare with me if i sound like a complete noob.
Ok here is the situation: we have a server running SQL Server 2005 Standard Edition and we host a lot of customer databases. The amount of databases we hold is increasing dramatically over time so we have aquired a new server. Basically we want to be able to replicate the data bi-directionally between the servers so we can split the read/write access of the databases between the two servers. We want to store ALL the databases on BOTH servers so that, should one fail, the half of our customers that have read/write access on that server can simply be switched to the second server.
I have been reading up for some time and found "Peer-to-Peer Transactional Replication" which seemed like it provided exactly that HOWEVER, i did notice that this type of replication is only available in Enterprise Edition, which at the moment, we quite simply cant afford to pay out for.
So my problem is how to provide the described functionality in the Workgroup or Standard editions - i naturally assumed that Transactional Replication would be the way to go but others seem to suggest Merge Replication. I know that the standard edition provides "Database Mirroring" but that is not going to provide the functionality that we need as it is just a method for backup.
I am new to this so i apologise, i am just a wee bit stuck so if someone could point me in the right direction....i would GREATLY appreciate it.
Thanks in advance.
February 14, 2008 at 5:33 am
Transactional replication can be configured to replicate in both directions, but in one direction it will basically be a batch of stored procedures (not very efficient). You may end up with users making changes to the same record on both sides causing replication conflicts - who's change do you keep?
Merge replication is designed to handle these situations, but requires GUID and TIMESTAMP columns on all of your tables. This schema change may be more than you can really handle, but the replication type is better suited to what you are trying to do.
However, replication is typically a bad idea for some kind of load balancing solution like you are describing. Are your servers going to be in the same data center, or are you in a split environment? If you are in the same data center, the simplest solution is to scale up rather than scale out. Get a really nice 64 bit server with lots of memory and redundant components. If you need true redundency, look at mirroring or clustering. I understnad you are trying to save money, but you are likely to spend twice as much in manpower on a "simple" solution.
February 14, 2008 at 5:49 am
To be honest, i agree with the idea of scaling up, and the new servers are meant to be very high end (Xeon Quad Core, 4GB Ram), this is the just the siutation i was approached with. Thanks for the info by the way 🙂
Although a quick point, there would be no conflicts with data being changed on both servers and then having to decide which changes to commit...because the databases will only be read and written to on the server the customer is allocated to and then those changes will be replicated to the other server.
Having said this i take your point, and with good enough servers i am guessing it would probably be simpler, more efficient and most likely cheaper, to just run them all on the same server and just backup to the second server? I think the orignal idea was that if one server failed, we would only need to move half of the customers to other server, as opposed to having to move everyone. At the moment we have 80 customer databases, and are likely to be adding another 300 or so over the course of the year. Possibly more. Anymore advice would be appreicated.
thanks again 🙂
February 14, 2008 at 7:34 am
As an update, i looked into Database Mirroring, and it appears people are having problems with scenarios where they have a lot of databases to mirror. Apparently microsoft recommends only mirroring 10 databases on a particular instance which is obviously a problem when i need to be able to do hundreds.
Assuming i need to be able to back up to a second server reguarly, and also be able to switch a database over to the backup should it fail (and not necessarily a server fail, possibly a corrupt database or something), what would be the best way to go about this? Is there some kind of limit to amount of databases that can be replicated? Thanks again in advance....
February 14, 2008 at 9:43 am
Transactional replication on hundreds of databases is a lot of lag reader agents - I would not recommend it.
If you can handle a warm-standby situation, log shipping will probably be the least overhead. If you go this route, it is worth purchasing a third-party log shipping manager of some sort that has been tested to work with hundreds of databases. Some of these tools will also help you with failover as well.
Since this forum is sponsored by Red-Gate, I would suggest you look at their tools first.
http://www.red-gate.com/products/sql_backup/technical_papers/log_shipping.htm
February 14, 2008 at 9:53 am
I've been down the merge replication road....don't go there, at least for the situation you are describing. Requires table changes which is usually not feasible. On a limited budget I would go log shipping too. Have your read only reporting type users hit the warm standby server and everyone else doing transactions hit the live server.
Start putting your proposal together for clustering. Present it and hang on to it. Eventually it sounds like you guys are headed in that direction. If you put together a decent proposal, you will get your manager's attention and show you have more than just technical skills with sql....
😉
February 15, 2008 at 1:14 am
Ok cheers everyone. Gonna sit down and think this through, my superior seems to be in a rush about it all but ill be damned if im gonna commit to a particular solution just yet 😛
February 15, 2008 at 1:53 am
Ok i think Log Shipping is the way to go. Had a quick look and it seems likea viable solution for my setup, Redgate SQL Backup looks pretty good so i think ill take a look at that and see how it goes. Thanks again guys for the advice. Think ill stick around here, friendly peoples 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply