April 14, 2012 at 8:56 am
I need advice on what is the best method to use. I recently purchased 2 new servers with 2008r2 server enterprise and SQL 2008 r2 enterprise. I need configure the SQL server for either clustering or replication. My problem is that we have low network bandwidth between sites through a sonic wall VPN tunnel (ADSL). I had originally considered clustering but after reading through several blogs, it seems that clustering would not be the best choice. Replication is the next option. Thinking that I could setup a 3 node replication with one node being passive. I now find out that the software vendor does not support 2 way replication. Apparently they use an older version schema and have no intension of upgrading. My company is now heavily invested in both the software and now the hardware. It is on me to get this operational. Any help is greatly appreciated. Currently the remote users are frustrated because of the extreme amount of time it takes to access the database.
April 14, 2012 at 8:47 pm
Hbruce (4/14/2012)
I need advice on what is the best method to use. I recently purchased 2 new servers with 2008r2 server enterprise and SQL 2008 r2 enterprise. I need configure the SQL server for either clustering or replication. My problem is that we have low network bandwidth between sites through a sonic wall VPN tunnel (ADSL). I had originally considered clustering but after reading through several blogs, it seems that clustering would not be the best choice. Replication is the next option. Thinking that I could setup a 3 node replication with one node being passive. I now find out that the software vendor does not support 2 way replication. Apparently they use an older version schema and have no intension of upgrading. My company is now heavily invested in both the software and now the hardware. It is on me to get this operational. Any help is greatly appreciated. Currently the remote users are frustrated because of the extreme amount of time it takes to access the database.
What is it you are trying to achieve?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 15, 2012 at 6:30 am
If your problem is around performance, I would say neither.
Replication's good for scale-out, but it's complicated to implement if you can't just redirect the reporting (read-only) workload to a second server.
Clustering is for high availability and has no performance impact at all.
If you're having severe performance problems, consider getting a consultant in. It's work I've done many times and I generally get very good results very fast. If getting someone in is not an option, maybe start with these articles
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2012 at 8:28 am
Thanks for your reply,
The issue here is we are using a mapping software called fusion. The remote site users are experiancing long delays in accessing data (Due to the large size of the database) from the main site. My solution is to have a copy of the database at the remote site. Any changes in data that they make will only be specific to their location. However the database at the main site must be kept current with the remote database. Overnight synchronization would be sufficient. Then perhaps once a week (Weekends) update the remote database so that it is identical. I must point out that the size of data changes are small, its the size of the databse that is substancial. The last time I played with SQL was like 6 years ago and technology has improved. Its time I take current training, however the powers that be insist I have this in place first. Kinda like putting the cart before the horse I know that that's how it is.
April 15, 2012 at 8:33 am
Thanks GilaMonster ,
I will check out those links for sure. Most likely I will end up hiring an expert.
April 15, 2012 at 10:37 am
Hbruce (4/15/2012)
The remote site users are experiancing long delays in accessing data (Due to the large size of the database) from the main site.
The size of he database has absolutely nothing to do with it. If the database has 500GB worth of data it's your problem if you're to return it all in your query 😉
Start by looking at your queries and checking network bandwidth and server NIC configurations.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 15, 2012 at 10:57 am
Hbruce (4/15/2012)
The remote site users are experiancing long delays in accessing data (Due to the large size of the database) from the main site.
Nothing to do with the size of the DB. I've had 1TB+ databases serving data to 5 or 6 regions across the country across reasonably slow links. The trick is to minimise the data that the regions need and to make sure that the queries are optimal.
My solution is to have a copy of the database at the remote site. Any changes in data that they make will only be specific to their location. However the database at the main site must be kept current with the remote database. Overnight synchronization would be sufficient. Then perhaps once a week (Weekends) update the remote database so that it is identical.
That's not necessarily simple to do. It's going to require either merge replication or a set of SSIS packages and you need to account for things like data conflicts (2 or more regions change the same record).
SQL Server doesn't scale-out easily. It can be done, but it has to be done carefully and designed properly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2012 at 10:58 am
Hbruce (4/15/2012)
I will check out those links for sure. Most likely I will end up hiring an expert.
What part of the world are you in? I might be able to suggest someone.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2012 at 2:15 pm
Well thanks for the assistance. I am in Alberta Canada, in a small town called Innisfail. I should point out that I am not a DBA, I am a Network Administrator that got handed the job of fixing this issue. The database is just one of many issues since I took this position 4 months ago. The remote site does not yet have a DC which I am currently working on so that I could actually have them as a member site for the domain rather then just users on a separate subnet. The more I read (and I have been doing plenty of that) The more I realize that I will need help. I will be discussing this with my managers this week.
April 15, 2012 at 2:20 pm
Way out of my neck of the woods, I'm afraid. I know some people in the USA who are very good at this sort of thing, but none are cheap. If the managers approve, I can give you some suggestions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2012 at 7:40 pm
Hello again, took me some time to find this old post, but I wanted GilaMonster to know that I mangaed to figure out my replication.
It was a lot of trial and error and plenty of testing. It seemed that each time I solved a problem another issue raised its ugly head. But perserverance paid off. I now have a fully functional two way peer to peer replication working between sites and across different subnets on a very poor network [low bandwith/high latency] connection.
I am replicating 4 separate databases and dispite our network issue it is very fast. I am extremly happy with the results. I would like to thank SQL ServerCentral.com, I made heavy use of this website to solve the many issues that were presented. I most definatley learned a lot and I found the subject matter very interesting. so much so that I have signed up for a SQL Boot camp in the fall, and I am planning to obtain my MCITP certification.
Cheers
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply