May 14, 2008 at 11:54 am
Hi All,
There is a plan that couple of our Web Servers will be moved to different Server farm. The DB server stays here. I want to know if there is anything special I have to keep in mind when setting up DSN/ODBC conncections over the WAN.
Our Sites have both ASP and .NET architecture code in it.
Thanks
-Roy
May 14, 2008 at 12:33 pm
Sorry to hear you are still using ODBC DSN's.
Other than making sure you have a connection that is stable and you have enough bandwidth, a WAN connection is not going to be seen as anything really different than a LAN connection.
May 14, 2008 at 12:41 pm
Yea, I am sorry about it as well..:D
But We DBAs cannot do much about it. Its upto the Programming / Development team to make sure that they move to the latest technology;)
Do you know of any Security issues that I should keep in mind about. Anything out of the ordinary?
-Roy
May 14, 2008 at 12:54 pm
I guess I assume your WAN is either using some kind of VPN tunnel or it is a frame relay or something that is already secure. That would be a question for your network admin. Assuming the connection has already been made secure, you should not have to do anything.
I know it sounds a bit scary, but there is probably nothing you need to do. If you can see the IP address and DNS is resolving the server name as it always has done, MS SQL does not care that the transport level is going across a WAN v.s a LAN. The only way you should be able to tell the difference is if you notice a major performance difference (which you are likely to see) because the bandwidth is significantly less. With some luck, your application development team did a good enough job that this will not be a problem. You may need to adjust packet sizes to help reduce conflicts, but it is all likely to be pretty good if you have not changed any of the defaults - plus these are client-side settings in the connection strings (or DSN's) so they are really the developer's problem.
May 14, 2008 at 1:12 pm
Michael Earl (5/14/2008)
I guess I assume your WAN is either using some kind of VPN tunnel or it is a frame relay or something that is already secure. That would be a question for your network admin. Assuming the connection has already been made secure, you should not have to do anything.I know it sounds a bit scary, but there is probably nothing you need to do. If you can see the IP address and DNS is resolving the server name as it always has done, MS SQL does not care that the transport level is going across a WAN v.s a LAN. The only way you should be able to tell the difference is if you notice a major performance difference (which you are likely to see) because the bandwidth is significantly less. With some luck, your application development team did a good enough job that this will not be a problem. You may need to adjust packet sizes to help reduce conflicts, but it is all likely to be pretty good if you have not changed any of the defaults - plus these are client-side settings in the connection strings (or DSN's) so they are really the developer's problem.
You may also consider disabling named pipes. Because named pipes is more network-intensive ("chatty") than "straight" TCP/IP, you may find that your link performs better over TCP/IP than over named pipes.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2008 at 1:30 pm
Yes, we will be using VPN tunnelling. Also we never enable named Pipes. We always use TCP/IP.
How good is the transactional replication across the WAN? Anyone have any idea? At one point we might have to do replication as well I guess. So better to learn now itself rather than too late into the project. Is it FTP protocol that the replication uses when it is WAN?
-Roy
May 14, 2008 at 1:40 pm
Transactional Replication works fine over a WAN.
Keep in mind that transactional replication, by default, sends one record at a time, not one transaction. It uses a transaction on the subscriber to put the updates or inserts into a single transaction at the subscriber end, but if you run a statement like "UPDATE MyTable SET MyField = 1", it will sent 1 update statement (actually call an update stored procedure) per updated record.
So, with a WAN, big updates being replicated in this way can result in a lot of latency and a great deal of traffic.
May 14, 2008 at 2:12 pm
Thats something I was afraid off. I had a feeling that transactional replication is going to be very very chatty. The bandwidth should be pretty good if the plan works good (And the Network Admins dont screw up).
If anyone thinks of anything else, please do let me know. Thanks
-Roy
May 19, 2008 at 3:06 pm
Another question based on WAN. What would be the effect of Connection pooling? We already have issues with Connection pooling because there are too many open connection and we are getting lots of General Network Error because of that.
Now since it is going to be a WAN, wouldnt the connections be open for a more longer time and cause more issues because of that?
Also does anybody here have experience with working with WAN? What I mean is does anybody have a Database that is being accessed through WAN. I really want to get a feel of how the performance is.
Any info on these is highly appreciated.
Thanks
-Roy
May 20, 2008 at 5:07 am
You should not have "too many" open connections from connection pooling. If you have a central application server that connects to the database (which I assume you have because it is really the point of pooling), your connection pool is a finite number you can configure. If you need another connection and all of the connections in the pool are in use, the application would wait.
So, you may have some pooling configuration issues. Remember that for a connection to come from the pool, the request for a connection must be identical to the connection in the pool - same server, database, connection settings, and security information.
Connection pooling handles disconnects just as well as not using pooling. If a connection is waiting in the pool and there is a network error that causes a disconnect, the connection will be removed from the pool. If there is a network error while a connection is in use, it is the same as if you were not using pooling and you need to handle the error in the application. If you are getting lots of connection errors, try reducing the packet size on your connections.
The fact that it is a WAN is irrelevant. It is the amount of dedicated bandwidth and the packet latency period that matters. Someone with a dedicated T3 is going to have better performance than someone with a fractional T1. You can pre-test your performance to a degree. Get your network admin to monitor the bandwith being used by your SQL server and compare that to the bandwidth you will have available. It's basic math - if you have 2mbps being used by your application, a fractional T1 may work - if you have 150mbps being used by your application you need a very long roll of fiber cable and a bunch of repeaters.
May 20, 2008 at 3:16 pm
The over load of Connection pooling happens in the Web Servers. Not the Database servers. It is the ODBC Driver that throws the error. The best practice to do is to make sure the connection you open is closed properly after the operation is done. But we have some loose ends in our code that keeps the Connection open. We can change the Connection pooling setting on the ODBC and the Database but that is just going to allow more connections. It actually does not solve the issue.
The only reason why I thought it would be an issue was because of the latency. The Web Servers will be hosted literally half way across the world from our DB Server. We will have VPN Tunneling with Web Accelerators. We will have around 45 MB Dedicated bandwidth. But still SQL is very chatty. So I am very concerned about the speed at which the details will be transferred between the Web Server and the Database server.
I also wanted to know How replication should be set up. For Snap shot to work when setting up a transaction Replication, will it be using FTP protocol? The Average amount of Data that is being manipulated in our DB is around 105 KB per second.
Thanks for all your advices. Please keep it coming. 🙂
-Roy
May 21, 2008 at 8:52 am
Ok, so the connection pool error is related to having used up all of the available connections in the pool. Yes, this is caused by not closing connections in the application - when they are closed by an application they are shelved for the pool and can be reused. If they remain open, they cannot be reused. This is also probably a sign that the application has a memory leak or two and should be fixed.
I have never really seen latency become a problem. The farthest location I have worked with was from California to Connecticut. The most interesting thing I have seen related to this is on occasion, traffic would be routed differently. Sometimes this would slow things down a bit, but I think it was more related to the number of routers it went through rather than the actual distance the data had to travel - light moves pretty fast and your data will undoubtedly move over fiber for most if not all of the trip. Going oversees may change this, but I don't think so.
Replication will depend on the latency you need and the size of the databases. Anything more than 30gb or so and I would guess FedEx will be faster than a snapshot. I would recommend not using snapshots to initialize replication if you have this much data - you will probably either want to do a backup, zip the file, transfer it (or mail it - don't count that out), and then restore. As an easier alternative buy a backup product like Red-Gate's that uses compression and this process will be far easier.
May 21, 2008 at 9:06 am
Thanks for the reply. We are using a compressed Back Up. Litespeed is compressing our 270 GIG DB to 40 Gig. We can do Object level restore from it. I think we will just forget about SnapShot and try some other method.
-Roy
May 21, 2008 at 10:07 am
You will probably not be able to copy a 40gb file over your WAN. For initialization, I would suggest you do a backup and FedEx it. While it is in the mail, do transaction log backups or differential backups. Restore the full backup from the mail and then zip and copy the latest differential and transaction log backups and synchronize your second site.
Then, I would set up a disaster plan that has your backups regularly being sent in the mail and differential / transaction log backups done during the time it takes to transfer the file so you can reinitialize if the need arises.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply