Is 2005 mirroring more reliable than 2000 replication?

[jmille34]

We originally had a single web server with both IIS and sql 2000 server on it. We then bought 2 new servers, put iis and sql 2000 on them and put a load balancing router in front of them, not so much to balance the load but to redirect traffic from 1 to 2 if server 1 went down. This all works well except that sql 2000 replication has been a huge pain in the neck since the beginning. For starters, it insert the rowguid column into all kinds of tables, and that goofed up some stuff. Next, there is the issue where tables that have an identity field do not replicate properly if near-simultaneous inserts occur on both servers. Third, there is the issue where identity fields must be pre-allocated in ranges, ie server 1 gets range 1-1000, server 2 gets 1001-2000, etc.. and as they fill up, they get to 80%, and the server pre-allocates the next range of addresses. This process is clunky, at best. Lastly, and the one that still bites me on the butt once a week, is that microsoft has admitted there is a bug where the above process will not work (the pre-allocation of identity fields) if the merge replication is set to "continuous".. They recommend changing it to run every 1 minute instead, which is not always fast enough during peak traffic period. If the 1000 identities are consumed inside of a minute, which can happen during certain batch processes, each subsequent attempted insert inside of that minute can generate an error, which gets annoying.

Basically, I'm not happy at all with the sql 2000 replication model, and I want to know if sql 2005 offers any significant improvements. If so, I will put the servers back in a single-server setup, bring the 2nd one home, and start from scratch.

 

[ptheriault]

SQL Server 2005 offers better replication than SQL 2000. Such as peer to peer. It is similar to merge. You won't be able to use db mirror for the problem you just described because the mirror db can not be read. it is in a constant state of loading. With that said, I'm not sure what you are doing is the proper appraoch. If you are looking for High Availability you might want to consider the following.
1. Create an active/passive cluster for your SQL server only. You should never put IIS and SQL on the same server as it is a very large security risk. You want your SQL servers behind the firewall.
2. Set up a 3rd Server for database mirroring. You can then create a snap shot of that database for reporting.
3. The mirror would be set up so if you lost the cluster for some reason it would failover to the mirror.
Now you are free to set up a true load balanced web app with F5 or something like that.

- Paul
- Database performance looks fine, it must be the Network!

 

[jmille34]

So what does mirroring do exactly? I thought it was just a newer, smarter form of replication.

 

[ptheriault]

Think of it as real time log shipping. Instead of a monitor it has a witness and it can failover without user intervention.

- Paul
- Database performance looks fine, it must be the Network!

 

[jmille34]

I'm kind of new to sql administration, and I have no idea what those two sentences mean. Truth be told, I'm a little in over my head on this whole project. (jack of all trades, master of none.. certainly not an sql master anyway)

I really really like our setup now, aside from the few problems we're having with the replication. As it stands, server 1 can go down completely, and server 2 will take over 100% within 10 seconds, which is not bad, and I could get it down closer to 3 seconds. The only single point of failure right now is the load balancer, which is solid state, so it is much less prone to crashes like Windows is. And if the router did crash, I could simply give server 1 a live ip address and be up and running again. It seems that if I were to move sql off of the web servers, I would then need 2 web servers and 2 sql servers, which starts getting expensive.

Lastly, I think I read somewhere that SQL 2005 will let you set it up so that it will only respond to certain IP addresses, meaning I could bind it to 127.0.0.1 and/or a private ip address, effectively taking it off the internet. Is that correct?

 

[ptheriault]

I'm not sure about binding SQL Server to an IP address. I havn't heard that but that doesn't mean it doesn't exists. I still find out more and more each day about 2005. you would need 4 servers if you move sql off of the web server but your data would be at a much lower risk to a hacker.
If you are serious about upgrading to 2005 I would look into peer to peer replication.
Here is a link that will explain it to you.

http://msdn2.microsoft.com/en-us/library/ms151196.aspx

- Paul
- Database performance looks fine, it must be the Network!

 

[jmille34]

Ok, thanks, that looks like a great start. I'm not in personal hurry to migrate this whole monster over to new technology for the 2nd time this year, but the current setup is just not performing as well as we'd hoped. Thanks for all the advice.

 

[mrdenny]

I would also recommend moving the SQL Server to it's own hardware. (You would also need something to run as the witness, although the passive node could handle that job).

I personally try to stay away from replication unless it's completly needed and nothing else will work. By using database mirroring with auto failover the database will fail over with a couple of seconds.

The trick is to get the connections to the databases moved over. If your front end application is a .NET 2.0 app (or you are using the SQL 2005 driver I think you can do it with just the driver and a legacy app, I havn't tested this ) you can setup the application to automatically fail over.

Making a fully redundand system isn't cheep. It's just not. There aren't really any shortcuts that can be taken to get a system that works well every time with no problems. Trust me on this one, I build these things for my company all the time.

We've got the money to spend so we've got SAN storage, with clustered servers running SQL. It's great, 3 seconds after a hardware failure (or an admin moving the SQL Server to the other node manually) SQL has moved, and the clients can reconnect. Expensive, but it's the only way to build a 99.999% uptime system.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com